Type-safe SQL in Go with sqlc

sqlc reads your schema and your SQL queries, checks them, and generates Go functions with typed parameters and result structs. You keep writing real SQL, and you get compile-time errors in Go when a query’s columns change instead of a runtime scan error. It sits between hand-written database/sql code, where every Scan call is yours to get wrong, and an ORM, which writes the SQL for you.
Examples use sqlc 1.31, Go 1.26 and pgx v5.10 against PostgreSQL. The Go code that uses the generated package was type-checked, but not run against a database. The generated code shown below is trimmed, and the exact output depends on your sqlc version.
Install
Section titled “Install”sqlc is a command-line tool, not a library you import at run time. Any of these works:
brew install sqlcgo install github.com/sqlc-dev/sqlc/cmd/sqlc@v1.31.1docker run --rm -v "$(pwd):/src" -w /src sqlc/sqlc generateRecent sqlc versions need Go 1.21 or later to go install. There are also pre-built binaries for Linux, macOS and Windows, and a snap package on Ubuntu.
The project
Section titled “The project”shop/├── go.mod├── main.go├── sqlc.yaml├── schema.sql├── query.sql└── db/ <- generated by sqlcSchema
Section titled “Schema”schema.sql is plain DDL. sqlc parses it to learn the tables and column types. It does not run it against a database:
CREATE TABLE products ( id bigserial PRIMARY KEY, sku text NOT NULL UNIQUE, name text NOT NULL, stock integer NOT NULL CHECK (stock >= 0), notes text);
CREATE TABLE orders ( id bigserial PRIMARY KEY, product_id bigint NOT NULL REFERENCES products (id), quantity integer NOT NULL CHECK (quantity > 0), created_at timestamptz NOT NULL DEFAULT now());If you use a migration tool, point schema at the migrations directory instead. sqlc understands the file formats of the common Go migration tools and applies the “up” parts in order.
Queries
Section titled “Queries”Each query in query.sql gets a comment with a name and an annotation that says what the generated function returns:
-- name: GetProductBySKU :oneSELECT * FROM productsWHERE sku = $1;
-- name: ListLowStock :manySELECT * FROM productsWHERE stock < $1ORDER BY stock, sku;
-- name: CreateProduct :oneINSERT INTO products (sku, name, stock, notes)VALUES ($1, $2, $3, $4)RETURNING *;
-- name: TakeStock :execrowsUPDATE productsSET stock = stock - @quantity::integerWHERE id = @id AND stock >= @quantity::integer;
-- name: CreateOrder :oneINSERT INTO orders (product_id, quantity)VALUES ($1, $2)RETURNING id, created_at;
-- name: DeleteProduct :execDELETE FROM products WHERE id = $1;The annotations you’ll use most:
| Annotation | Generated method returns |
|---|---|
:one | one row as a struct, and an error (no-rows is an error) |
:many | a slice of structs, and an error |
:exec | only an error |
:execrows | the number of affected rows (int64), and an error |
:execresult | the driver’s result value (sql.Result with database/sql), and an error |
pgx also gets :batchexec, :batchmany, :batchone and :copyfrom for batched queries and bulk inserts.
@quantity is shorthand for sqlc.arg(quantity), a named parameter. Without a name, sqlc names parameters after the column they’re compared with (sku, stock). For an expression like stock - $1 there’s no column to borrow a name from, so you’d get a field called something like Column1. The ::integer cast tells sqlc the type, since it can’t always infer one from arithmetic.
Configuration
Section titled “Configuration”sqlc.yaml, in the version 2 format:
version: "2"sql: - engine: "postgresql" schema: "schema.sql" queries: "query.sql" gen: go: package: "db" out: "db" sql_package: "pgx/v5"sql_package picks the driver the generated code talks to. Leave it out to get database/sql code instead (more on that below). Run:
sqlc generatesqlc now parses every query against the schema. A typo in a column name, a wrong number of parameters, or a type mismatch fails here, with the file and line, before any Go is compiled.
What it generates
Section titled “What it generates”Three files in db/. models.go has one struct per table:
type Product struct { ID int64 Sku string Name string Stock int32 Notes pgtype.Text}bigserial becomes int64, integer becomes int32, and the nullable notes column becomes pgtype.Text, which has String and Valid fields. With database/sql it would be sql.NullString.
db.go has the Queries type and the interface it needs:
type DBTX interface { Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error) Query(context.Context, string, ...interface{}) (pgx.Rows, error) QueryRow(context.Context, string, ...interface{}) pgx.Row}
func New(db DBTX) *Queries { return &Queries{db: db}}
func (q *Queries) WithTx(tx pgx.Tx) *Queries { return &Queries{ db: tx, }}query.sql.go has one method per query. SELECT * has been expanded to the real column list, and the scan code is written for you:
const getProductBySKU = `-- name: GetProductBySKU :oneSELECT id, sku, name, stock, notes FROM productsWHERE sku = $1`
func (q *Queries) GetProductBySKU(ctx context.Context, sku string) (Product, error) { row := q.db.QueryRow(ctx, getProductBySKU, sku) var i Product err := row.Scan( &i.ID, &i.Sku, &i.Name, &i.Stock, &i.Notes, ) return i, err}Queries with more than one parameter take a generated params struct (CreateProductParams, TakeStockParams{Quantity int32; ID int64}), and queries that return only some columns get a row struct (CreateOrderRow{ID int64; CreatedAt pgtype.Timestamptz}). The other methods are ListLowStock(ctx, stock int32) ([]Product, error), TakeStock(ctx, arg TakeStockParams) (int64, error) and DeleteProduct(ctx, id int64) error.
Commit the generated code. It’s ordinary Go that reviewers can read, and builds don’t need sqlc installed.
Using it with pgx
Section titled “Using it with pgx”pgxpool.Pool, pgx.Conn and pgx.Tx all satisfy DBTX, so db.New accepts any of them. Add pgx with go get github.com/jackc/pgx/v5, then:
package main
import ( "context" "errors" "fmt" "log" "os"
"github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgtype" "github.com/jackc/pgx/v5/pgxpool"
"example.com/shop/db")
var ErrOutOfStock = errors.New("not enough stock")
// PlaceOrder takes stock and records the order in one transaction.func PlaceOrder(ctx context.Context, pool *pgxpool.Pool, sku string, qty int32) (db.CreateOrderRow, error) { tx, err := pool.Begin(ctx) if err != nil { return db.CreateOrderRow{}, err } defer tx.Rollback(ctx) // no-op once Commit has succeeded
q := db.New(pool).WithTx(tx)
p, err := q.GetProductBySKU(ctx, sku) if errors.Is(err, pgx.ErrNoRows) { return db.CreateOrderRow{}, fmt.Errorf("unknown sku %q", sku) } else if err != nil { return db.CreateOrderRow{}, err }
n, err := q.TakeStock(ctx, db.TakeStockParams{Quantity: qty, ID: p.ID}) if err != nil { return db.CreateOrderRow{}, err } if n == 0 { return db.CreateOrderRow{}, ErrOutOfStock }
order, err := q.CreateOrder(ctx, db.CreateOrderParams{ProductID: p.ID, Quantity: qty}) if err != nil { return db.CreateOrderRow{}, err } return order, tx.Commit(ctx)}
func main() { ctx := context.Background() pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL")) if err != nil { log.Fatal(err) } defer pool.Close()
q := db.New(pool)
lamp, err := q.CreateProduct(ctx, db.CreateProductParams{ Sku: "LAMP-01", Name: "Desk lamp", Stock: 5, Notes: pgtype.Text{String: "warm white", Valid: true}, }) if err != nil { log.Fatal(err) } fmt.Printf("created %s (id %d)\n", lamp.Sku, lamp.ID)
order, err := PlaceOrder(ctx, pool, "LAMP-01", 3) if err != nil { log.Fatal(err) } fmt.Println("order", order.ID, "at", order.CreatedAt.Time)
// Only 2 left, so this one is refused and its transaction rolled back. if _, err := PlaceOrder(ctx, pool, "LAMP-01", 3); errors.Is(err, ErrOutOfStock) { fmt.Println("second order refused:", err) }
low, err := q.ListLowStock(ctx, 5) if err != nil { log.Fatal(err) } for _, p := range low { fmt.Printf("%s: %d left\n", p.Sku, p.Stock) }}Create the tables with psql "$DATABASE_URL" -f schema.sql and run go run .. Run it twice and CreateProduct fails on the second run, because sku is unique.
Transactions with WithTx
Section titled “Transactions with WithTx”WithTx returns a copy of Queries that sends every query through the transaction. The pattern in PlaceOrder is the one to copy:
Begina transaction.defer tx.Rollback(ctx)immediately. If the function returns early with an error, the transaction is rolled back. After a successfulCommit, the rollback does nothing.- Do all the work through
q.WithTx(tx). return ..., tx.Commit(ctx)so a failed commit is reported.
TakeStock checks stock >= quantity in the WHERE clause and reports rows affected, so two concurrent orders can’t both take the last item. The row lock taken by the UPDATE serialises them. pgx also has pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error { ... }), which commits if the function returns nil and rolls back otherwise.
Using database/sql instead
Section titled “Using database/sql instead”Remove sql_package from sqlc.yaml and regenerate. The differences:
DBTXusesExecContext,QueryContext,QueryRowContextandPrepareContext, which*sql.DB,*sql.Connand*sql.Txall have.- Nullable columns become
sql.NullString,sql.NullInt32and so on, andtimestamptzbecomestime.Time. WithTxtakes a*sql.Tx, andCommitandRollbackdon’t take a context.:onereturnssql.ErrNoRowswhen nothing matches.
You can still use pgx as the driver through its stdlib package:
import ( "database/sql"
_ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver)
conn, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))q := db.New(conn)I’d pick pgx/v5 for a PostgreSQL-only service: you get :copyfrom, batches and PostgreSQL types such as arrays without adapters. Pick database/sql when the code has to work with other databases or with libraries that expect a *sql.DB.
Options worth knowing
Section titled “Options worth knowing”These go under gen.go in sqlc.yaml:
emit_json_tags: trueaddsjson:"..."tags to the structs, if you return them from an API directly.emit_pointers_for_null_types: trueuses*stringinstead ofpgtype.Textfor nullable columns (pgx only).overridesmaps a database type or a specific column to a Go type, for exampletimestamptztotime.Time, or auuidcolumn togithub.com/google/uuid.UUID.
Gotchas
Section titled “Gotchas”- Regenerate after every schema change.
SELECT *is expanded at generate time. Add a column and forget to regenerate, and the code keeps selecting the old list. Runsqlc diffin CI: it fails when the committed code doesn’t match whatsqlc generatewould produce. sqlc vetruns lint rules on your queries, including rules you write yourself in CEL. It can also runEXPLAINagainst a real database.- Dynamic queries are awkward. sqlc generates one function per fixed SQL string. For optional filters, use
sqlc.nargwithCOALESCEor(@x::text IS NULL OR col = @x)patterns. For truly dynamic queries (user-built sorting and filtering), write those few by hand or use a query builder next to sqlc. INlists in PostgreSQL. UseWHERE id = ANY(@ids::bigint[])and pass a[]int64. Thesqlc.slicemacro is for MySQL and SQLite.- sqlc doesn’t manage your schema. It reads DDL; it doesn’t apply it. Use a migration tool for that, and point
schemaat its directory so both read the same files.
Further reading
Section titled “Further reading”© 2026 David Li · Home · Blog · RSS ·Classic site · Built with Astro, Starlight and three.js · Logos:Font Awesome Free (CC BY 4.0)