Moving the Store to sqlc

Phase 2 wrote every database query by hand: a SQL string in Go, a call to pgx, and a Scan that copies each column into a struct field. Phase 2.1 moves those queries into .sql files and lets a tool called sqlc write the Go code that runs them. Nothing a caller can see changes. The same interfaces, the same tests, the same tenant rule. What changes is who writes the boring part, and when mistakes are caught.

What you will learn

• What sqlc reads, what it writes, and what it checks before your code ever runs.
• How to keep a tenant rule when the SQL lives in files instead of Go.
• How to write optional filters in a query whose text never changes.
• Where sqlc stops, and what to do when a query does not fit.
• How to find a query in a trace by its name.

Words used in this lesson

Why change something that works

Phase 2 chose hand-written SQL so every query, and the tenant predicate inside it, was visible in Go. That worked. It also had costs that grow with every table:

• A query is only checked when a test runs it. A misspelled column, a wrong type, or a Scan with the columns in the wrong order compiles fine and fails at run time.
• Every query repeats the same column list and the same Scan call. Adding a column means finding every one of them.
• Optional filters were built by appending strings, so the exact SQL that ran depended on the request.

Phase 4 adds agents, sessions and messages, and copies whatever pattern the store already has. Moving five query files now is cheaper than moving fifteen later. It is also the reason this phase exists at all: hango is a learning project, and sqlc is worth learning on a real codebase.

The smallest version

You write a query, with a comment that names it and says how many rows it returns:

-- name: GetUser :one
select * from users where tenant_id = @tenant_id and id = @id;

sqlc reads the users table from the migrations, works out that @tenant_id and @id are uuid and that select * returns six columns, and writes this:

type GetUserParams struct {
	TenantID uuid.UUID `db:"tenant_id"`
	ID       uuid.UUID `db:"id"`
}

func (q *Queries) GetUser(ctx context.Context, arg GetUserParams) (User, error) {
	row := q.db.QueryRow(ctx, getUser, arg.TenantID, arg.ID)
	var i User
	err := row.Scan(&i.ID, &i.TenantID, &i.Email, &i.Role, &i.Status, &i.CreatedAt)
	return i, err
}

That is the code phase 2 wrote by hand. If the query names a column that does not exist, sqlc generate fails and no Go is written. If a migration later drops a column the query uses, regenerating fails the same way. The mistake is caught at your desk, not in a test or in production.

The suffix after the name says what shape to return: :one for one row, :many for a slice, :exec for no rows.

How hango is wired

The files

migrations/*.sql              schema, read by sqlc (only the goose Up section)
sqlc.yaml                     settings: where to read, where to write, type choices
internal/store/pg/queries/    the queries, one file per table
internal/store/pg/*.sql.go    generated, one per query file
internal/store/pg/models.go   generated, one struct per table
internal/store/pg/db.go       generated, the Queries type and the DBTX interface
internal/store/pg/user.go …   hand-written, thin: scope in, store types out

Run make generate after changing a query or a migration, and commit the result. make sqlc-check fails if the committed code is stale.

sqlc runs with go run github.com/sqlc-dev/sqlc/cmd/[email protected], pinned in the Makefile. It is not in go.mod. The generated code only imports pgx, which hango already uses, so sqlc never becomes part of the binary. Check it yourself: go list -deps ./cmd/hango | rg sqlc prints nothing. The first run compiles sqlc, which takes a couple of minutes because it includes the real Postgres parser. That parser is C code, so the first run needs cgo and a C compiler (Xcode command line tools on macOS, build-essential on Debian). Later runs are cached.

The generated code stays in package pg

sqlc writes into internal/store/pg itself, not a separate package. Two reasons. The plan caps hango at twenty packages, and a pg/db package would spend one on generated code. More important, only code inside pg can call a generated method, and only pg can reach a database connection. The tenant rule from phase 2 depends on that boundary, and storeboundary_test.go still enforces it.

Types

By default sqlc uses pgx’s own types for anything that can be NULL, such as pgtype.Timestamptz and pgtype.UUID. Those would leak into every caller. sqlc.yaml overrides them:

• uuid becomes the Go 1.27 standard library uuid.UUID, or *uuid.UUID when the column can be NULL.
• timestamptz becomes time.Time, or *time.Time when it can be NULL.
• jsonb becomes json.RawMessage.
• users.role becomes store.Role and tenants.kind becomes store.TenantKind, so the closed sets from phase 2 keep their types.

A rename turns trace_llm_content into TraceLLMContent and api_key_id into APIKeyID, matching Go naming. With those settings, most generated row structs have exactly the same fields, in the same order, as the store types. Go lets you convert between two struct types with identical fields, so the mapper is one expression:

u, err := us.s.db().GetUser(ctx, GetUserParams{TenantID: us.s.scope.TenantID, ID: id})
return store.User(u), mapErr(err)

If someone adds a field to store.User and not to the table, that line stops compiling. The audit event is the one exception: the table stores a missing actor as NULL, while store.AuditEvent uses uuid.Nil(), so toStoreAudit in audit.go maps it field by field.

Where the tenant rule lives now

In phase 2 the rule was: every statement on a tenant-owned table binds tenant_id from the handle. It is the same rule, now written in SQL:

-- name: ListUsers :many
select * from users
 where tenant_id = @tenant_id
   and (sqlc.narg('only_user')::uuid is null or id = sqlc.narg('only_user'))
 order by created_at;

@tenant_id is a required field of ListUsersParams. The only code that fills it is a sub-store method on scoped, which takes the value from s.scope.TenantID. No method takes a tenant argument. The cross-tenant tests from phase 2 run unchanged, and they are what proves the move did not open a hole.

ResolveAPIKey in queries/auth.sql is still the one tenant-less query on a tenant-owned table, because finding the tenant is its job. Only store.AuthLookup calls it.

Optional filters without building strings

A member sees only its own users, keys and audit rows. Phase 2 appended and id = $2 to the SQL string when the caller was a member. The query text depended on the caller.

sqlc wants fixed text, so the filter is always there and a parameter switches it off:

(sqlc.narg('only_user')::uuid is null or id = sqlc.narg('only_user'))

sqlc.narg means “nullable argument”, so the Go field is *uuid.UUID. When it is nil, Postgres sees NULL is null, which is true, and the filter lets every row through. When it holds an id, only that row matches. scoped.ownUserID() returns nil for owners and admins and the caller’s id for a member, which is exactly what the parameter needs. The audit list uses the same trick for since, action and target_id.

The ::uuid cast tells sqlc the type. Without it, sqlc cannot tell what type NULL is null is and generates interface{}.

The limit is still computed in Go, defaulting to 100 and capped at 500, and bound as @row_limit. A limit in SQL is a parameter like any other.

Queries that guard themselves

Two phase 2 queries carry more than a filter, and both moved as they were:

• CreateAPIKey inserts with insert ... select ... from users where u.tenant_id = @tenant_id and u.id = @user_id. If the user is not in the tenant, the select returns no rows, nothing is inserted, and the method returns store.ErrNotFound. A foreign key alone would accept a user from another tenant.
• LockTenant keeps for no key update. Two platform requests that both check “does this tenant have an owner yet?” take the lock first, so the second waits for the first to commit instead of both creating an owner. Inserts into tables that reference the tenant are not blocked.

Transactions

The generated New(db DBTX) takes anything with Exec, Query and QueryRow. hango’s querier interface from phase 2 has those three plus Begin, so it fits. InTx still swaps a pool for a transaction, and New(s.q) runs the same generated code on either.

How to see what the server is doing

sqlc puts the query name at the start of every statement it sends:

-- name: ListUsers :many
select id, tenant_id, email, role, status, created_at from users ...

otelpgx copies the statement into the db.query.text attribute of each database span. The span name stays the SQL verb, such as SELECT, because otelpgx skips a leading sqlc comment when it names the span. In Grafana, open Explore, pick Tempo, and run this TraceQL query:

{ span.db.query.text =~ ".*ListAuditEvents.*" }

Every audit list call in the time range shows up, with its duration, without guessing at SQL text.

How to debug this layer

sqlc generate fails

Read the message. It names the file, the line, and the column or type it could not resolve. Common causes: a column that does not exist in the migrations, a parameter whose type sqlc cannot infer (add a ::type cast), or a migration statement sqlc’s parser does not understand.

make sqlc-check fails

Someone changed a query or a migration without regenerating. Run make generate, look at the diff, and commit it with the change that caused it.

A test fails after editing a query

The generated code is correct for the SQL you wrote, so the SQL is the place to look. Run the failing query in psql with literal values. For an optional filter, check that the Go side sends nil, not a pointer to an empty value: &"" is not NULL, and action = '' matches nothing.

The code does not compile after a migration

That is sqlc doing its job. A column changed type or name, the generated struct changed, and the compiler is showing you every place that used it.

Testing

Nothing new. The phase 2 tests in internal/store/pg, internal/server and internal/app all pass with no edits, which is the evidence that behaviour did not change. One test changed its target: TestAuditStoreNeverUpdatesOrDeletes used to search audit.go for update or delete. The SQL now lives in queries/, and a purge could be added to any query file, so the test reads every statement in every query file (ignoring comments) and fails on one that changes or removes audit_events rows. It still checks audit.go in case someone adds a hand-written query there.

Limits met in practice

• One row struct per query. GetAPIKey, ListAPIKeys, CreateAPIKey and RevokeAPIKey select the same columns but each gets its own ...Row type, because sqlc names types by query. They all convert to store.APIKey, so it costs nothing here, but it is noise in api_keys.sql.go.
• select * in a query is expanded at generate time. Adding a column to users changes GetUser the next time you regenerate. That is usually what you want, and it is why the key queries list their columns: they must never return key_hash.
• sqlc.narg cannot tell “not set” from “set to NULL”. For a filter that is fine. For a partial update (“change the name, leave the email alone”) it is not, because you cannot say “set email to NULL”. Phase 4 will meet this; the usual answer is a separate boolean parameter per field, or a query per update shape.
• sqlc attaches the comment above a query to the generated method’s Go doc, so a comment at the top of a query file ends up on the first method. hango’s query files have no file-level comment for that reason; each note sits on the query it describes.
• Dynamic SQL is out. A sort column chosen at run time, or a filter list of unknown length, cannot be fixed text. That is what the escape hatch is for.

The escape hatch

If a query really cannot be written for sqlc, write it by hand with pgx in the same package, scan it into the generated struct (the db tags make pgx.RowToStructByName work), and add a comment naming the limit. Phase 2.1 needed none.

Trade-offs

• sqlc as the default vs a mix. Splitting queries between sqlc and hand-written code by taste means every reader has to know two styles. One default with a named exception keeps the rule simple.
• Generated code in pg vs its own package. A separate package would be tidier and would cost one of the twenty packages, and anything in it could be imported by more code than the tenant rule allows.
• Type overrides vs pgtype. Overrides make the generated structs look like hand-written ones. The cost is a longer sqlc.yaml and a pointer for every nullable column.
• go run with a pinned version vs a tool line in go.mod. A tool dependency would pull sqlc’s large dependency tree into the module graph. go run pkg@version keeps it out, at the price of a slow first run.
• Committing generated code vs generating in CI. Committed code means building and testing hango never needs sqlc or cgo. make sqlc-check keeps it honest, and phase 16’s CI job runs it.

How goclaw and deepagents do this

goclaw (~/ws/personal/goclaw) uses database/sql with the pgx standard-library driver, sqlx for scanning into structs, golang-migrate for migrations, and hand-written SQL. The reason is SQLite: goclaw also ships a SQLite store (internal/store/sqlitestore), and database/sql is the common ground. sqlc can generate for SQLite too, but each engine needs its own queries and output. hango is Postgres-only, so it can use native pgx and generate one set of code.

langchain deepagents has no SQL layer of its own. It stores state through LangGraph checkpointers and stores, which hide the database behind a key-value interface.

Exercises

1. Add a column. Write a migration that adds display_name text not null default '' to users, run make generate, then go build ./.... Follow the errors: which line is the first to break, and why? (Hint: store.User(u).)
2. Break the tenant rule on purpose. In queries/api_keys.sql, change GetAPIKey’s where tenant_id = @tenant_id and id = @id to where @tenant_id::uuid is not null and id = @id, which keeps the parameter but no longer filters by it. Run make generate and go test ./internal/store/pg/ -run TestCrossTenant -v, and watch the api_keys case fail. Then try deleting tenant_id = @tenant_id and outright: sqlc drops the TenantID parameter, GetAPIKeyParams disappears, and go build fails before any test runs. Restore the query and regenerate.
3. Try a runtime sort. Change ListAuditEvents so the caller can choose occurred_at or action as the sort column. Try it with a parameter (order by @sort_col) and read what Postgres does. Then write it as an escape hatch in audit.go, scanning into AuditEvent.
4. Find a query in Tempo. Start the stack with make obs-up, run hango, call GET /v1/audit-events a few times, and find the ListAuditEvents spans with the TraceQL query above.