I met with the creators/maintainers of sqlc the other day, and per their request (I promise this wasn’t me complaining randomly, as is usually the case), they asked about the project’s weaknesses. I sent over a few somewhat problematic queries, and of those, I think this is my favorite:

-- name: ClusterGetPage :many
SELECT *
FROM cluster
WHERE team_id = any(@team_id::uuid[])
    AND archived_at IS NULL
    AND parent_id IS NULL
    AND
        CASE WHEN @cursor_specified::boolean THEN
            CASE WHEN @by_id::boolean   AND NOT @descending::boolean THEN id::text    > @cursor_threshold::text
                 WHEN @by_id            AND     @descending          THEN id::text    < @cursor_threshold
                 WHEN @by_name::boolean AND NOT @descending          THEN lower(name) > lower(@cursor_threshold)
                 WHEN @by_name          AND     @descending          THEN lower(name) < lower(@cursor_threshold)
            END
        ELSE
            id = id
        END
ORDER BY
    CASE WHEN @by_id   AND NOT @descending THEN id          END ASC,
    CASE WHEN @by_id   AND     @descending THEN id          END DESC,
    CASE WHEN @by_name AND NOT @descending THEN lower(name) END ASC,
    CASE WHEN @by_name AND     @descending THEN lower(name) END DESC
LIMIT @max;

Unlike a more traditional ORM, sqlc can’t arbitrarily chain expressions. All the SQL that can be in the SQL is in the SQL, which means that any logical branching has to be done with CASE/WHEN expressions.

This monstrosity has a straightforward objective: listing, with pagination. But it does allow pagination along two dimensions (id and name), and with a cursor or without, which is where it gets a little gnarly.

That said, compared to pagination logic whose parts to build a complete query are littered across a half-dozen pagination utility modules, and whose totality is obscured by its piecemeal nature, maybe the sqlc version isn’t so bad?