All jobs

Check sqlc Generated Code in CI With sqlc diff and sqlc vet

Yes. An agent adds a CI job that fails when sqlc's generated Go code or the mocks are stale, pinned to the sqlc version the repo uses, and opens a pull request.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent first works out what is generated, by which tool, at which version. In a service like techschool/simplebank, sqlc.yaml reads the schema from db/migration and the queries from db/query and writes package db into db/sqlc with sql_package: "pgx/v5" and emit_interface: true. That directory is mixed: account.sql.go, models.go, querier.go and their siblings carry sqlc's header, while store.go, exec_tx.go, the tx_*.go files and the tests are written by hand. The header on the generated files names sqlc v1.22.0. A second generator depends on the first: make mock runs mockgen over the Store interface to write db/mock/store.go, and the README installs mockgen@v1.6.0. The workflow in .github/workflows/test.yml starts a postgres:14-alpine service, installs golang-migrate, and runs make migrateup and make test. Nothing in it checks that the committed generated code matches the SQL.

It then measures the drift that exists today, because a check that fails on its first run gets deleted. sqlc diff regenerates in memory, compares with the files on disk and exits non-zero when they differ, without touching the hand-written files in the same directory. Run on this repository with sqlc 1.22.0 it prints nothing and exits 0. Run with the release that was current when this page was written, 1.31.1, it exits 1 with nine files changed, and every changed line is the version comment in the header. So the generated code is in step with the queries, and the only way to make the check fail spuriously is to let CI pick its own sqlc version. The agent pins 1.22.0 in the workflow through the sqlc-dev/setup-sqlc action, and says in the pull request that upgrading sqlc is a separate change that regenerates all nine files.

The mock is in a different state. Regenerating db/mock/store.go with mockgen v1.6.0 changes 51 of the committed file's 380 lines, all of them comment punctuation and the position of one import; the methods are the same. The committed file came from an older mockgen. The agent regenerates it once in this pull request so that the new step, make mock followed by git diff --exit-code, starts from a clean state. This is the check that catches the common mistake: someone adds a query, runs make sqlc, forgets make mock, and the tests in api stop compiling because MockStore no longer satisfies db.Store.

Last, sqlc vet. The agent adds a database block with the connection URI and the built-in sqlc/db-prepare rule to sqlc.yaml, so that after make migrateup every query is prepared against the migrated PostgreSQL the job already runs. That catches what generation alone lets through. One example in the style this codebase already uses for partial updates: a nullable parameter written as sqlc.narg(currency) IS NULL OR currency = sqlc.narg(currency) with no cast makes sqlc 1.22.0 print a warning, exit 0 and generate a parameter struct with the field missing, while sqlc vet fails it with PostgreSQL's own message that it could not determine the parameter's type. The new steps go into the existing test job, after the migrations and before make test, and the agent proves each one by breaking it in the sandbox: an edited query without regeneration, a new query without make mock, a query that does not prepare.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add CI checks that the generated code in this Go service is up to date. Read sqlc.yaml, Makefile, the header of the generated files in db/sqlc, db/mock/store.go, README.md and .github/workflows/test.yml first. Find the sqlc version in the generated headers and the mockgen version the README installs, and pin both in CI. In the existing test job, after the migrations and before make test, add: sqlc diff; make mock followed by git diff --exit-code on db/mock and worker/mock; and sqlc vet with the built-in sqlc/db-prepare rule, adding a database uri for the job's PostgreSQL to sqlc.yaml. Before adding each step, run it here and report what it says on the unchanged repository; if the committed mocks differ from a fresh make mock, regenerate them once in this pull request and describe the difference. Do not upgrade sqlc or regenerate db/sqlc with a newer version. There is no Docker here: install PostgreSQL from the system packages, create the simple_bank database with the user and password in app.env, apply the migrations with golang-migrate, and run make test to confirm the suite still passes. Then show each check failing: edit a query without regenerating, add a query without make mock, and add a query that does not prepare; revert each. Open a pull request with the output of every command.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

A pull request that changes .github/workflows/test.yml and sqlc.yaml, and regenerates db/mock/store.go once. The description has two parts. The first is the state of the repository before the change: what sqlc diff and a fresh make mock reported, with the versions used. The second is the proof that each new step works, as three short transcripts from the sandbox, each ending in a non-zero exit and followed by the revert. It also includes the make test output from a PostgreSQL the agent set up itself, with the schema applied by the repository's own migrations.

A reviewer looks at the pins. sqlc is fixed at the version in the generated headers, and mockgen at the version the README names, so a contributor who follows the README produces the same bytes as CI. They read the mock diff to confirm it is comments and an import, which the description claims and which is quick to scan. They decide whether sqlc.yaml should hold the database URI literally, as the Makefile's DB_URL already does for local use, or read it from an environment variable. And they note what the checks do not cover: pb/ and the Swagger files from make proto are also generated and are left for a follow-up.

Run it on every pull request

Once merged, the workflow itself runs on every pull request, which is the point; this job is needed once per repository. Where an agent helps on every pull request is the failure. In the console, add an automation on the Project with a github trigger on check_suite.failure and a saved prompt that tells the agent to read the failing step and, when it is the generated-code check, run make sqlc and make mock with the pinned versions and open a pull request with the regenerated files. The event is added after the prompt. Only work by a repository owner, member or collaborator fires the trigger, and pull requests from forks never do.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

Whether a drift check is safe to add depends on facts about the repository that are tedious to collect by hand: which generator version produced the committed files, whether a fresh run reproduces them, whether the mocks were regenerated the last time the interface changed. The agent has a sandbox where it can install the exact versions, run each generator against the unchanged tree and compare. The run keeps every command and what it printed, so the pull request states the starting point with evidence.

It also tests the check, which people skip. A CI step that has never been seen failing is an assumption. In the sandbox the agent breaks the repository three ways, records each failure and puts the tree back, and that record comes back with the result. The reviewer reads three failures they did not have to stage.

Frequently asked questions

Why pin sqlc instead of using the latest release?

sqlc writes its version into the header of every generated file, so a newer release makes sqlc diff report every file as changed even when the generated code is otherwise identical. Pinning the version in the committed headers keeps the check about your SQL. Upgrading is then a deliberate pull request that changes the pin and regenerates.

Does sqlc diff need a database?

No. It parses the schema files and the queries the same way sqlc generate does and compares the result with the files on disk. Only sqlc vet with the sqlc/db-prepare rule connects to PostgreSQL, which is why that step runs after the migrations.

What does sqlc vet add if the code already compiles?

It asks PostgreSQL to prepare each query against the migrated schema. Generation works from sqlc's own reading of the migration files, and the two can disagree, for instance on a parameter whose type PostgreSQL cannot infer. The prepare step reports that with the database's own error before any Go test runs.

More sqlc jobs

More jobs like this: Hand off a long task

Keep reading