Lesson 9 · sqlc — typed SQL + the synthesis

sqlc & this repo's config

You write SQL; a tool writes the Go. Simple idea — but this repo runs it through a custom plugin with two mechanisms and a few switches worth knowing by name.

Your win: explain what sqlc does, read a real query annotation, and name the two sqlc mechanisms in eureka and what the v2 one adds.

Heads up — we're borrowing eureka now Your services (spike, notification, conversationmgmt) have no sqlc — they hand-roll dynamic SQL (Lesson 2). sqlc lives only in eureka. So Part 3 anchors on eureka's learning_time and assessment modules. Everything transfers back: it's the same ports-and-adapters shape, with a generated query layer instead of hand-written SQL.

The idea: SQL in, type-safe Go out

You write a plain .sql file with a magic comment; sqlc reads it (plus your schema) and generates Go that calls it — structs for the params and results, a method per query, all checked against the real columns at generation time.¹ Here's a real one from eureka, in full:

internal/eureka/v2/modules/learning_time/repository/postgres/queries/learning_time.sql:1

-- name: BulkRemoveLearningTimesBySessionIDs :exec
UPDATE learning_time
SET deleted_at = NOW()
WHERE session_id = ANY(@session_ids::text[]);

The comment is the whole interface with sqlc. name: becomes the Go method name; :exec is the return shape. @session_ids is a named parameter, and the ::text[] cast tells sqlc it's a []string. That's it — no ORM, no query builder, just annotated SQL.

The annotations you'll actually see

Annotation	Generated method returns
`:one`	`(*T, error)` — and `(nil, nil)` on no rows, never `pgx.ErrNoRows`
`:many`	`([]T, error)`
`:exec`	`error` (for INSERT/UPDATE/DELETE with no return)
`:execresult`	`(pgconn.CommandTag, error)` — when you need rows-affected

The (nil, nil)-on-no-rows behaviour is a repo-wide setting (emit_err_nil_if_no_rows) — so in eureka you never check pgx.ErrNoRows after a :one; a missing row is a nil result, not an error.²

Two mechanisms — don't conflate them

eureka has two sqlc setups, and they're genuinely different tools:

	Legacy — `internal/eureka/sqlc.yaml`	v2 — `internal/eureka/v2/sqlc.yaml`
codegen	stock sqlc + a `genTraced.go` post-processing step for spans	a custom Manabie WASM plugin (`sqlc-gen-go.wasm`)
status	its own header says "Legacy … we will migrate to the new one in v2"	active — all new v2 module queries
build	`make eureka-gen-sqlc`	`make eureka-gen-sqlc-v2`

The v2 config registers a WASM plugin and lists the module queries/ dirs (learning_time, book, assessment, statistic, exporter, ai_tutor). If you see stock gen: go: blocks, you're in legacy; if you see a plugins: block with a .wasm URL, you're in v2.³

What the v2 plugin adds (three switches by name)

emit_tracing — injects an OTel span into every generated method automatically ({Module}Queries.{Method}). No manual span in the query code.
emit_dynamic_filter — lets a query have optional WHERE clauses via -- :if @param guards, so one query serves many filter combinations without hand-concatenating SQL.
go_generate_mock — regenerates a mock of the query interface alongside the code, so usecases can be unit-tested without a DB.

These three are why the team built a custom plugin instead of using stock sqlc.

Dynamic filter — a peek (you'll meet it again)

-- name: ListAssessments :many
SELECT * FROM assessment
WHERE TRUE
    -- :if @course_id
    AND course_id = @course_id
    AND deleted_at IS NULL;

Start with WHERE TRUE so it stays valid when every optional filter is skipped; each -- :if @param drops its clause when the param is nil. That's emit_dynamic_filter doing what you'd otherwise write by hand in a fmt.Sprintf.²

sqlc — official docs

The canonical reference for annotations, config, and the emit_* options. Read "Getting started" and the query-annotations page.

→ docs.sqlc.dev
→ in-repo internal/eureka/v2/sqlc.yaml, .../learning_time/repository/postgres/queries/learning_time.sql, .claude/rules/sqlc-queries.md

Check yourself (from memory)

Q1. What does the -- name: X :exec comment tell sqlc?

name: → the Go method name; :exec → the return shape (just error). The annotation is the whole interface with sqlc.

Q2. How do eureka's two sqlc mechanisms differ?

Legacy = stock sqlc + a genTraced step; v2 = a Manabie WASM plugin adding dynamic filters, auto-tracing, and mock generation.

Q3. After a :one query finds no row, what comes back?

emit_err_nil_if_no_rows makes no-match return (nil, nil). In eureka you never check pgx.ErrNoRows.

Recall: sqlc — the idea, annotations, and the two mechanisms.

what it does + annotations + legacy vs v2, then reveal

Idea: write annotated .sql + schema → sqlc generates type-safe Go (param + result structs, a method per query, columns checked at gen time). Annotation: -- name: X :exec|:one|:many|:execresult → method name + return shape; @param named params, ::text[] → []string. :one returns (nil,nil) on no rows (never pgx.ErrNoRows). Two mechanisms (eureka-only): legacy eureka/sqlc.yaml = stock sqlc + genTraced (being migrated away); v2 eureka/v2/sqlc.yaml = custom Manabie WASM plugin adding emit_tracing (auto span), emit_dynamic_filter (-- :if @param optional WHERE), go_generate_mock. Build: make eureka-gen-sqlc-v2. Your services use zero sqlc.

🎯 Interview one-liner "What's sqlc?" → "You write SQL with a small annotation and it generates type-safe Go — param and result structs and a method per query, checked against the schema at build time, so a renamed column is a compile error, not a runtime one. We run a custom plugin that also injects tracing spans and supports optional WHERE clauses without hand-built SQL."

Next: what sqlc actually generates — the Querier interface — and how a repo adapter wraps it (with a wiring gotcha that panics if you forget it). Ask me if the two-mechanisms split is fuzzy.

1. docs.sqlc.dev; in-repo .../queries/learning_time.sql:1.

2. In-repo: .claude/rules/sqlc-queries.md (annotations, dynamic filter, emit_err_nil_if_no_rows).

3. In-repo (verified): internal/eureka/sqlc.yaml:1-2 (legacy header), internal/eureka/v2/sqlc.yaml (WASM plugin + 6 modules).