The problem with sqlc is dynamic queries (mostly dynamic filters and ordering, which don't really affect type safety of the parameter or result-set mappings).

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

There's two different approaches to solving sql composability issues:

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
      `
    }

I haven't worked with many ORMs, but Django's is imo very good in tackling the lack of composability in SQL.

SQL is barely parametrizable, so, in my opinion, the DX is much worse than using a query builder and not much better than simply using strings.

https://github.com/adelsz/pgtyped

https://gleam.run/ - Gleam is a friendly language for building type-safe systems that scale!

The two feel very different. Elixir is introducing gradual set-theoretic typing (https://hexdocs.pm/elixir/1.18.4/gradual-set-theoretic-types...) while Gleam has a static Hindley-Milner style type system.

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.

hi, author here! it does not no :) it takes the json description of the sql queries generated by sqlc and converts them to gleam code. but if sqlc supported that feature, parrot could also take advantage of it.

Typescript is magic: https://kysely.dev/docs/examples/select/aliases

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