Day 094 — sqlc for Type-Safe Queries¶

Month 4 · Week 2 · ⬅ Day 093 · Day 095 ➡ · Journal index

🎯 Learning Objective¶

Generate type-safe Go data-access code from hand-written SQL with sqlc: schema + annotated queries in, compile-checked Go methods out.

📚 Topics¶

The sqlc model: write SQL, generate Go (no ORM, no runtime reflection)
Query annotations :one / :many / :exec / :execrows
sqlc.yaml, schema vs queries, choosing the pgx/v5 driver

📖 Reading / Sources¶

📝 Notes¶

sqlc is a code generator, not an ORM. You write real SQL; sqlc generate parses your schema + queries and emits Go structs and methods. There's no runtime reflection or query builder — the generated code is what you'd hand-write → [[code-generation]].
It needs three things: a schema (your CREATE TABLE DDL — often your migration files), a queries file with annotated SQL, and a sqlc.yaml config. Run sqlc generate (commit the output).
Query annotations are SQL comments naming the method and result shape:
-- name: GetAuthor :one → returns one row + sql.ErrNoRows/pgx.ErrNoRows when missing.
-- name: ListAuthors :many → returns []Author.
-- name: DeleteAuthor :exec → returns only error.
:execrows → returns affected row count; :execresult → driver Result.
Compile-time safety is the whole point: rename a column in the schema and forget to update a query, and sqlc generate fails — you catch it before runtime instead of via a 500 in prod.
Output is a Queries type with a method per query, plus a models.go of structs mapped from your tables. Construct it with New(db) where db is a DBTX (works with database/sql or a pgx pool, depending on config).
Choose the driver in sqlc.yaml: for pgx set sql_package: "pgx/v5"; otherwise it targets database/sql. The generated method signatures differ accordingly.
Placeholders in your SQL use the target dialect ($1 for Postgres). Parameters become typed Go function arguments (or a Params struct when there are several).
Limits: sqlc shines for static queries. Dynamic SQL (optional filters, IN (...) with variable length) is awkward — you fall back to hand-written queries or sqlc.slice() / dynamic builders there.

💻 Code Examples¶

sqlc generates code at build time (external tool), so this shows the inputs and the call site. It pairs naturally with the pgx pool from yesterday.

-- schema.sql  (also feeds golang-migrate, Day 095)
CREATE TABLE authors (
    id   BIGSERIAL PRIMARY KEY,
    name text      NOT NULL,
    bio  text
);

-- query.sql
-- name: GetAuthor :one
SELECT id, name, bio FROM authors WHERE id = $1;

-- name: ListAuthors :many
SELECT id, name, bio FROM authors ORDER BY name;

-- name: CreateAuthor :one
INSERT INTO authors (name, bio) VALUES ($1, $2)
RETURNING id, name, bio;

-- name: DeleteAuthor :exec
DELETE FROM authors WHERE id = $1;

// After `sqlc generate`, the generated package gives you typed methods.
import (
    "context"
    "errors"

    "github.com/jackc/pgx/v5"
    "myapp/db" // generated package
)

func demo(ctx context.Context, pool db.DBTX) error {
    q := db.New(pool) // DBTX is satisfied by *pgxpool.Pool

    a, err := q.CreateAuthor(ctx, db.CreateAuthorParams{
        Name: "Ada",
        Bio:  pgtype.Text{String: "pioneer", Valid: true},
    })
    if err != nil {
        return err
    }

    got, err := q.GetAuthor(ctx, a.ID)
    if errors.Is(err, pgx.ErrNoRows) {
        return errors.New("author vanished")
    }
    _ = got
    return err
}

🏋️ Exercises / Practice¶

Exercise	Status	Link
Migration planner (schema versions sqlc-generated code targets)	✅	exercises/month-04/week-2/migrationplan

🐛 Mistakes Made¶

Edited a generated file by hand; the next sqlc generate wiped it. Generated code is read-only output — change the SQL or config instead.
Tried to build a dynamic WHERE with optional filters in one query and fought sqlc. Split into separate named queries (or dropped to hand-written SQL) where the shape varies.

❓ Open Questions¶

Best pattern for IN ($1, $2, ...) with a variable-length slice under pgx — sqlc.slice() vs = ANY($1)?

🧠 Active Recall (answer without looking)¶

Q: Is sqlc an ORM? What does it actually produce?

A

No. It's a code generator: it parses your schema + annotated SQL and emits plain, type-safe Go structs and methods — no runtime reflection or query builder.

Q: What does the :one vs :many vs :exec annotation control?

A

The generated method's return shape: `:one` returns a single row (+ no-rows error), `:many` returns a slice, `:exec` returns only `error` (no rows).

🪶 Feynman Reflection¶

sqlc reads your SQL the way the database would, then writes the boring Go glue for you — the structs, the Scan calls, the parameter wiring — and type-checks it against your schema. You keep authoring SQL (the skill that transfers everywhere); sqlc removes the hand-written, error-prone marshalling.

🕳️ Knowledge Gaps¶

How sqlc maps Postgres-specific types (arrays, jsonb, enums) into Go under the pgx target.

✅ Summary¶

I understand sqlc's write-SQL/generate-Go workflow, the :one/:many/:exec annotations, the sqlc.yaml schema+queries setup, and why compile-time checking beats runtime ORM surprises.

⏭️ Next Steps / Prep for Tomorrow¶

Day 095: managing the schema itself with golang-migrate.

Time spent	Difficulty	Confidence
90 min	🟦🟦⬜⬜⬜	🟦🟦🟦⬜⬜

Suggested commit: docs(journal): sqlc type-safe queries (day 094)