Parrot – Type-Safe SQL in Gleam, Supports Sqlite, Postgresql and Mysql

To me, Gleam feels like if Elm’s type system met Rust’s syntax. I really like it. But I also really liked Elixir too, I just can’t live without Gleam’s type system anymore.

Even after years, the solutions are unsatisfactory from a database query planner perspective.

    const persons = await db
      .selectFrom('person as p')
      .select([
        'first_name as fn',
        'p.last_name as ln'
      ])
      .execute()

1. Compiles-to-SQL domain specific languages. This category spans from ORM DSLs embedded in another programming language, like Ruby/Rail's ActiveRecord/AREL or Django's ORM; to stand-alone text-based languages like PRQL Pipelined Relational Query Language" (https://prql-lang.org) that a compiler program converts to SQL text or SQL files. The downside to the DSL option is that it requires practitioners be fluent in both the SQL query they want, and in the DLS language - to know how to obtain the SQL query in the DSL.

2. Query fragment literals in the caller programming language, like sql`name = ${name}` in TypeScript (eg https://github.com/gajus/slonik). These are usually thin abstraction over concatenating a `{ queryText: string[], queryArgs: T[] }` structure. The author only needs to be fluent in SQL, and in the caller language, but do less to save you from the expressive limitations of SQL itself.

I've found query fragment composition to be the sweet spot. Easy SQL queries remain trivial to express and understand, since it's Just SQL:

    sql`SELECT * FROM block WHERE id = ${args.id}`

    function selectOfflinePageMetadata(args: { userId: string }) {
      return sql`
        SELECT
          offline_page.id,
          offline_page.space_id,
          offline_page.download_status,
          offline_page.last_downloaded_at,
          offline_page.last_downloaded_version,
          offline_page.last_downloaded_sync_cursor,
          offline_page.target_sync_cursor,
          CASE 
              WHEN EXISTS (
                SELECT 1 FROM offline_action
                WHERE offline_action.impacted_page_id = offline_page.id
                AND offline_action.origin_page_id = offline_page.id
                AND offline_action.autosync_type = 'not_autosynced'
              )
              THEN 1
              ELSE 0
          END AS is_explicitly_offlined_origin,
          CASE
              WHEN EXISTS (
                SELECT 1 FROM offline_action
                WHERE offline_action.impacted_page_id = offline_page.id
                AND offline_action.origin_page_id = offline_page.id
                AND offline_action.autosync_type = 'created_offline'
              )
              THEN 1
              ELSE 0
          END AS is_offline_created_origin,
          CASE
              WHEN EXISTS (
                SELECT 1 FROM offline_action
                WHERE offline_action.impacted_page_id = offline_page.id
                AND offline_action.origin_page_id = offline_page.id
                AND offline_action.autosync_type = 'frecent'
              )
              THEN 1
              ELSE 0
          END AS is_autosynced_origin
        FROM offline_page
        WHERE offline_page.meta_user_id = ${args.userId}
      `
    }

    function selectOfflinePageById(args: {
      userId: string
      pageId: string
    }) {
      const { userId, pageId, } = args
    
      return sql`
        SELECT * FROM (${selectOfflinePageMetadata({ userId })}) WHERE id = ${pageId}
      `
    }
    
    function selectOfflineAutosyncedOrigins(args: {
      userId: string
      spaceId: string
    }) {
      const { userId, spaceId, } = args
    
      return sql`
        WITH offline_page_metadata AS (
          ${selectOfflinePageMetadata({ userId })}
        )
        SELECT offline_page_metadata.* FROM offline_page_metadata
        WHERE space_id = ${spaceId}
        AND is_autosynced_origin = 1
        ORDER BY last_downloaded_at ASC
      `
    }