sqlc Vet and Linting Your Queries Before They Ship

A typed query layer catches errors at build time, but only if something runs at build time. sqlc ships three checks, each aimed at a different mistake, and they are cheap enough to run on every pull request. This article runs all three against a small table on PostgreSQL 18.6 with sqlc v1.30.0, including a rule that worked and one that silently did not.

It belongs with the sqlc tutorial and the testing article: tests check behaviour, and these checks stop broken or risky SQL from reaching the tests at all. They are part of the database cornerstone, and the migration side of CI is in testing migrations in CI.

The three checks

sqlc compile is a fourth command that parses and type checks without writing files, and is a lighter version of generate for a pre commit hook.

The configuration

Rules live in sqlc.yaml, in two places: a top level rules list that defines them, and a rules list inside each sql entry that enables them:

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5
    database:
      uri: ${DATABASE_URL}
    rules:
      - no-select-star
      - no-unbounded-delete
      - sqlc/db-prepare
rules:
  - name: no-select-star
    message: "select * breaks callers when a column is added; list the columns"
    rule: >
      query.sql.matches("(?is)select\\s+\\*") && !query.sql.matches("(?is)count\\(\\*\\)")
  - name: no-unbounded-delete
    message: "a DELETE with no WHERE clause removes every row"
    rule: >
      query.cmd == "exec" && query.sql.matches("(?is)^\\s*delete\\s+from\\s+\\w+\\s*;?\\s*$")

A rule has a name, a message shown when it fires, and a rule expression in CEL, Google's Common Expression Language. The expression sees a query object with fields such as query.sql (the text), query.cmd (one, many, exec...), query.name and query.params. The lab defines two:

• no-select-star fires when the SQL matches select * and is not a count(*).
• no-unbounded-delete fires for an :exec query that is a bare delete from table with nothing after it.

The built in sqlc/db-prepare rule asks the database to prepare each query, which catches things the parser cannot know. It needs a connection, and the entry sets database.uri from ${DATABASE_URL}, which sqlc expands from the environment, so the credentials are not in the file.

The schema and queries are small:

create table notes (
    id   bigserial primary key,
    body text not null,
    tag  text
);

-- name: GetNote :one
select * from notes where id = $1;

-- name: ListBodies :many
select id, body from notes order by id;

-- name: DeleteAll :exec
delete from notes;

-- name: Count :one
select count(*) from notes;

package main

func main() {}

The run

The script runs each command and prints its exit code, because the exit code is what CI acts on:

export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0698
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
export DATABASE_URL="postgres://postgres:[email protected]:55432/$DB?sslmode=disable"
SQLC="$HOME/go/bin/sqlc"
run() { echo "\$ sqlc $1"; "$SQLC" "$@" 2>&1; echo "exit code $?"; echo; }
run vet
run generate
run diff
echo '--- the queries change but the generated code is not regenerated'
printf '\n-- name: Tagged :many\nselect id, body from notes where tag = $1;\n' >> query.sql
run diff
echo '--- a query that names a column that does not exist'
printf '\n-- name: Broken :one\nselect id, title from notes where id = $1;\n' >> query.sql
run generate

$ sqlc vet
query.sql: DeleteAll: no-unbounded-delete: a DELETE with no WHERE clause removes every row
exit code 1

$ sqlc generate
exit code 0

$ sqlc diff
exit code 0

--- the queries change but the generated code is not regenerated
$ sqlc diff
--- a/query.sql.go
+++ b/query.sql.go
@@ -7,6 +7,8 @@
 
 import (
 	"context"
+
+	"github.com/jackc/pgx/v5/pgtype"
 )
 
 const count = `-- name: Count :one
@@ -68,3 +70,32 @@
 	}
 	return items, nil
 }
+
+const tagged = `-- name: Tagged :many
+select id, body from notes where tag = $1
+`
+
+type TaggedRow struct {
+	ID   int64
+	Body string
+}
+
+func (q *Queries) Tagged(ctx context.Context, tag pgtype.Text) ([]TaggedRow, error) {
+	rows, err := q.db.Query(ctx, tagged, tag)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []TaggedRow
+	for rows.Next() {
+		var i TaggedRow
+		if err := rows.Scan(&i.ID, &i.Body); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
exit code 1

--- a query that names a column that does not exist
$ sqlc generate
# package main
query.sql:17:12: column "title" does not exist
exit code 1

sqlc vet found the unbounded delete. The output was one line, query.sql: DeleteAll: no-unbounded-delete: a DELETE with no WHERE clause removes every row, and the exit code was 1. A failed rule fails the step. The message is yours to write, so it is the place to say what to do instead (here, add a condition or use truncate deliberately in a migration).

The select * rule did not fire. GetNote is select * from notes where id = $1, which the rule's regular expression plainly matches, and vet said nothing about it. This is the finding to take away. The generated code shows the star expanded into the table's columns (select id, body, tag from notes), and the likeliest explanation is that rules see that processed text. I did not confirm that in sqlc's source, but the behaviour is what matters: this rule did not fire on a query it plainly should have, and a clean vet run proved nothing about it.

Two ways to deal with that. Write the rule for the shape you can observe, such as the query having no explicit column list in your source, and check it with a query you know must fail before you trust it. Or enforce "no star" outside sqlc, with a lint on the .sql files themselves (a grep in CI is a legitimate answer). The principle holds for every custom rule: write a deliberately bad query, see the rule fire, then delete the query. A rule that has never failed has never been shown to work.

The star expansion has a design consequence too, covered in the tutorial: a select * query returns a struct that follows the table, so adding a column changes the generated type.

db-prepare passed. With a database to talk to, vet asked PostgreSQL to prepare each query and found no problems. Without a connection, the same rule reported database connection required for every query, which is what the lab's first run printed before database.uri was set. The point of the rule is the class of error that needs a server: a function that does not exist, a cast PostgreSQL rejects, an operator with no match. It costs a database in CI, which the service container from the testing article already supplies.

sqlc generate reports precise errors. After the script appended a query that selects title, a column notes does not have, generate stopped with query.sql:17:12: column "title" does not exist and exit code 1. File, line and column point at the word. That is the build time safety the tool exists for, and it is the cheapest check: no database, no rules, just the schema.

sqlc diff finds a stale generated file. The first diff, run right after generate, exited 0 with no output. After the script added a Tagged query without regenerating, diff exited 1 and printed a unified diff of what generate would change: the new method, its row type and the added pgtype import. That diff is the evidence in a pull request that someone changed a query and forgot to regenerate, or edited a generated file by hand.

A CI job

The commands compose into one job, in the order of increasing cost:

sqlc compile
sqlc vet
sqlc generate
git diff --exit-code

The last two lines are one way to ask the same question as sqlc diff: regenerate, then fail if git sees a change. Use either. sqlc diff needs no write access to the checkout, and git diff --exit-code works the same for any generator you add later. Either way, fail the job and show the diff.

Two practical points. Pin the sqlc version in the job, the same as in development: a newer generator writes different code and diff fails for a reason unrelated to the change. And set DATABASE_URL only for the vet step, so a missing variable fails there and not in a step that does not use it.

What else vet can enforce

Rules are CEL expressions, and the useful ones are about shape and not style:

• Every :many query has a limit (query.cmd == "many" && !query.sql.matches("(?i)limit")), with exceptions by name for the ones that must be unbounded.
• No for update outside the lock queries, to keep locking visible in review.
• Updates and deletes have a where, as the lab's delete rule does. Remember the star lesson: test each one with a query that should fail.
• Rules that read the query plan (sqlc exposes PostgreSQL's explain output to CEL when a database is configured) can flag a sequential scan or a high cost, but they depend on realistic data in the CI database, so treat them as advisory. I did not run one here.

Keep the list short. A rule that fires on legitimate code teaches people to add exceptions, and then to ignore the output.

Checklist

• Run compile, vet and diff on every pull request, and fail on a non zero exit.
• Make each custom rule fail once on purpose before relying on it.
• Enable sqlc/db-prepare where CI has a database.
• Do not rely on a regular expression for select *: the star is expanded before rules see it.
• Pin the sqlc version in CI and in development.

The rule language and the query fields are documented in sqlc's vet reference, the other commands in its CLI reference, and CEL in the language definition.