Skip to content

Type-safe SQL in Go with sqlc

A flow from schema.sql and query.sql files through an sqlc box into generated Go files with typed structs

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.

sqlc is a command-line tool, not a library you import at run time. Any of these works:

Terminal window
brew install sqlc
go install github.com/sqlc-dev/sqlc/cmd/sqlc@v1.31.1
docker run --rm -v "$(pwd):/src" -w /src sqlc/sqlc generate

Recent 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.

shop/
├── go.mod
├── main.go
├── sqlc.yaml
├── schema.sql
├── query.sql
└── db/ <- generated by sqlc

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.

Each query in query.sql gets a comment with a name and an annotation that says what the generated function returns:

-- name: GetProductBySKU :one
SELECT * FROM products
WHERE sku = $1;
-- name: ListLowStock :many
SELECT * FROM products
WHERE stock < $1
ORDER BY stock, sku;
-- name: CreateProduct :one
INSERT INTO products (sku, name, stock, notes)
VALUES ($1, $2, $3, $4)
RETURNING *;
-- name: TakeStock :execrows
UPDATE products
SET stock = stock - @quantity::integer
WHERE id = @id AND stock >= @quantity::integer;
-- name: CreateOrder :one
INSERT INTO orders (product_id, quantity)
VALUES ($1, $2)
RETURNING id, created_at;
-- name: DeleteProduct :exec
DELETE FROM products WHERE id = $1;

The annotations you’ll use most:

AnnotationGenerated method returns
:oneone row as a struct, and an error (no-rows is an error)
:manya slice of structs, and an error
:execonly an error
:execrowsthe number of affected rows (int64), and an error
:execresultthe 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.

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:

Terminal window
sqlc generate

sqlc 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.

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 :one
SELECT id, sku, name, stock, notes FROM products
WHERE 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.

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.

WithTx returns a copy of Queries that sends every query through the transaction. The pattern in PlaceOrder is the one to copy:

  1. Begin a transaction.
  2. defer tx.Rollback(ctx) immediately. If the function returns early with an error, the transaction is rolled back. After a successful Commit, the rollback does nothing.
  3. Do all the work through q.WithTx(tx).
  4. 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.

Remove sql_package from sqlc.yaml and regenerate. The differences:

  • DBTX uses ExecContext, QueryContext, QueryRowContext and PrepareContext, which *sql.DB, *sql.Conn and *sql.Tx all have.
  • Nullable columns become sql.NullString, sql.NullInt32 and so on, and timestamptz becomes time.Time.
  • WithTx takes a *sql.Tx, and Commit and Rollback don’t take a context.
  • :one returns sql.ErrNoRows when 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.

These go under gen.go in sqlc.yaml:

  • emit_json_tags: true adds json:"..." tags to the structs, if you return them from an API directly.
  • emit_pointers_for_null_types: true uses *string instead of pgtype.Text for nullable columns (pgx only).
  • overrides maps a database type or a specific column to a Go type, for example timestamptz to time.Time, or a uuid column to github.com/google/uuid.UUID.
  • 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. Run sqlc diff in CI: it fails when the committed code doesn’t match what sqlc generate would produce.
  • sqlc vet runs lint rules on your queries, including rules you write yourself in CEL. It can also run EXPLAIN against a real database.
  • Dynamic queries are awkward. sqlc generates one function per fixed SQL string. For optional filters, use sqlc.narg with COALESCE or (@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.
  • IN lists in PostgreSQL. Use WHERE id = ANY(@ids::bigint[]) and pass a []int64. The sqlc.slice macro 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 schema at its directory so both read the same files.

© 2026 David Li · Home · Blog · RSS ·Classic site · Built with Astro, Starlight and three.js · Logos:Font Awesome Free (CC BY 4.0)