sqlc and the wrapper spectrum: typed SQL codegen, query builders, and honest ORM tradeoffs

The spectrum, honestly

Before you reach for an ORM or raw SQL, ask what you are actually trading: review clarity, dynamic flexibility, type safety, or boilerplate. Every Go data layer sits somewhere on a four-point spectrum, and each point has a real cost — the dishonest move is pretending yours does not.

Raw database/sql is maximal control and maximal boilerplate: every query repeats the Query/defer Close/Next/Scan/Err ceremony from earlier in this unit, and positional Scan is a standing refactor hazard — reorder two same-typed columns in the SELECT and name lands in email with no error anywhere. Query builders (squirrel is the standard) compose SQL from Go values: the right tool for genuinely dynamic queries — twelve optional admin filters — but the query now exists only at runtime, so nothing checks it before execution. Full ORMs (GORM) maximize ergonomics: relations, hooks, migrations-by-struct. The price is semantic distance — the Hook’s zero-value rule, save-order magic, N+1 queries materializing out of innocent-looking field access — and a review process where nobody sees the SQL that will actually run. sqlc stakes out the fourth point: you write real SQL files, and code generation gives you typed Go functions. The SQL is visible; the boilerplate is generated; the checking happens before the code ships.

sqlc mechanics: SQL in, typed Go out

When you hand-write Scan targets, you own the mapping forever — one reordered column and the bug is silent. sqlc takes that ownership away from you. You write queries in a .sql file, each with a name and a cardinality annotation:

-- name: GetUser :one
SELECT id, email, active FROM users WHERE id = $1;

-- name: ListOrgUsers :many
SELECT id, email FROM users
WHERE org_id = $1 AND active
ORDER BY created_at DESC
LIMIT $2;

-- name: DeactivateUsers :exec
UPDATE users SET active = false, updated_at = now() WHERE id = ANY($1::bigint[]);

sqlc generate parses these together with your schema and emits typed Go:

// Generated. Scan order and field mapping are owned by the generator —
// the positional-swap bug class is gone, because no human writes Scan anymore.
func (q *Queries) GetUser(ctx context.Context, id int64) (GetUserRow, error)
func (q *Queries) ListOrgUsers(ctx context.Context, arg ListOrgUsersParams) ([]ListOrgUsersRow, error)
func (q *Queries) DeactivateUsers(ctx context.Context, ids []int64) error

The check happens at generation time: reference a column that does not exist, pass a string where the schema says bigint, return four columns into a three-field struct — sqlc generate fails, in CI, before review. :one compiles to QueryRow (zero rows still surfaces as sql.ErrNoRows at the call site — sqlc does not hide the semantics of the layer below); :many compiles to the full Query/Close/Err loop, written correctly every time; :exec to ExecContext. The generated Querier interface is the part teams underrate — it is a test seam you did not have to design. Handler tests take a hand-rolled fake; only the repository layer needs a real database.

Two integration points keep it honest at system scale. Transactions: the generated code runs on anything that satisfies its DBTX interface, so WithTx reuses the previous lesson’s helper unchanged —

q := store.New(db) // *Queries bound to the pool

err := withTx(ctx, db, nil, func(tx *sql.Tx) error {
	qtx := q.WithTx(tx) // same generated methods, now riding the pinned connection
	if err := qtx.DeactivateUsers(ctx, ids); err != nil {
		return err
	}
	return qtx.InsertAuditEvent(ctx, auditParams)
})

Migrations: sqlc does not own your schema — a migration tool does (golang-migrate or goose: numbered SQL files, applied in order, recorded in a version table). sqlc reads those same migration files to learn the schema, which closes the drift loop: rename a column in a migration, rerun sqlc generate, and the compiler hands you the complete list of call sites to fix. The same rename with hand-written Scan code compiles fine and corrupts quietly.

Where sqlc stops, it stops abruptly: it generates one static statement per annotation, so a search endpoint with twelve optional filters cannot be one sqlc query. Chaining coalesce($1, col) tricks technically works and reliably produces both unreadable SQL and unusable query plans. That is the builder’s territory — squirrel behind the same repository interface — or a hand-built WHERE over an allowlisted column set. A senior data layer is a composite, not a monoculture.

The team-scale argument

The strongest case for SQL-first is not type safety — it is review. With sqlc, the diff in a pull request is the query: a reviewer can read it, paste it into EXPLAIN ANALYZE, and spot the missing index or the accidental cross join before merge. With an ORM DSL, the reviewer mentally compiles chained method calls into SQL and hopes their mental model matches the library version — the Hook is what it looks like when it does not. The same property pays at 3 a.m.: pg_stat_statements shows you a slow query, and with sqlc you can grep for its literal text and land on one annotated, named query. And the boundary stays honest in the other direction too: reporting queries with window functions, recursive CTEs, EXPLAIN-driven rewrites — those stay raw SQL by their nature, and sqlc happily generates types for most of them, because it never asked the SQL to be simple, only to be static.