sqlc Tutorial in Go From Install to Type Safe Queries

sqlc is a code generator, and that one fact explains both what it gives you and what it does not. You write the schema and the queries in SQL. You run sqlc generate. It reads both, asks the type checker what every column and parameter is, and writes ordinary Go: one struct per table, one method per query, no reflection, no runtime dependency beyond the driver. The compiler then does the work that an ORM leaves to run time. If you rename a column in a migration and forget a query, the next sqlc generate fails, and if you change a query's shape the Go callers stop compiling.

This tutorial builds the smallest useful version of that loop against PostgreSQL 18 with the pgx v5 driver, on Go 1.25.6 and sqlc v1.30.0. It is the entry point of the sqlc cluster inside the database cornerstone: configuration, annotations, migrations, overrides and the rest each get their own article, and this one shows the whole path once so the others have something to refer to. If you are choosing between sqlc, an ORM and plain database/sql, the library comparison sets out the trade-off, and the earlier sqlc walk-through on database/sql covers the standard library driver. This one uses pgx, which is where most new PostgreSQL code in Go starts, as the pgx tutorial explains.

What sqlc is, and what it is not

The tool has three inputs and one output. The inputs are a configuration file (sqlc.yaml), a schema (a .sql file or a folder of migrations) and a set of queries, each preceded by a comment that names it and says what it returns. The output is a package of Go source files that you commit like any other code.

It is not an ORM. There is no model layer that you mutate and save, no query builder, no lazy loading and no hidden SELECT. Every statement that runs is a statement you wrote, in the SQL dialect your database speaks, so a window function, a FILTER clause, a recursive CTE or ON CONFLICT is available the day PostgreSQL ships it, as long as sqlc's parser understands the syntax. That last condition is the real limit. sqlc has its own PostgreSQL parser (built on the one from the server's own grammar), and it does not run your query, so it learns types from the schema and from function signatures it knows. An unusual extension function can leave a result column typed as interface{} until you cast it in the SQL.

Three things follow from that design, and they decide when sqlc fits:

• You get compile time checking of SQL. A typo in a column name, a missing parameter and a wrong number of values in an insert are all errors from sqlc generate, in the editor and in CI, not a 500 on a rarely used endpoint.
• You keep full control of the SQL. That is the point for teams that tune queries with EXPLAIN, and it is a cost for teams that wanted the library to write the SQL for them.
• Dynamic queries are the awkward case. A query whose shape changes with the request (optional filters, a user chosen sort) is a fixed string in sqlc. The article on dynamic queries covers the patterns that work.

Install

sqlc is a single Go binary. This lab used the Go toolchain to install the pinned version:

go install github.com/sqlc-dev/sqlc/cmd/[email protected]
sqlc version

The second command prints v1.30.0. Homebrew (brew install sqlc) and a Docker image (sqlc/sqlc) are the other documented routes, and the Docker image is the better choice in CI, where you want the version to be a tag in a file rather than whatever a package manager resolved that morning. Whatever the route, write the version down. Generated code changes between releases, usually in small ways such as a renamed helper or an added Valid method, and a team where two people run different versions will see noisy diffs on files nobody edited.

You do not need a running database to generate code. sqlc reads the schema file. The lab runs PostgreSQL only because the Go program at the end executes the queries.

The schema

sqlc builds its picture of the database from the create table statements you give it, so the schema file is real SQL that PostgreSQL could run as it stands. The lab uses two tables:

create table authors (
    id         bigserial primary key,
    name       text not null,
    bio        text,
    created_at timestamptz not null default now()
);

create table books (
    id        bigserial primary key,
    author_id bigint not null references authors (id),
    title     text not null,
    pages     int not null check (pages > 0)
);

Two columns decide how the Go types come out. bio is nullable, so it will not be a plain string: Go's string cannot be NULL, and sqlc will not pretend otherwise. pages is int not null, so it becomes int32, because PostgreSQL's int is 32 bits and sqlc maps by the database's own width, not by the width of Go's int. Both are the sort of decision you would otherwise make by hand in a struct, and get wrong once.

In a real project the schema is usually your migrations folder rather than one file, and sqlc understands alter table and drop column well enough to replay them in order. The article on migrations shows that arrangement with goose. For a tutorial a single file is clearer.

The queries

Each query is a comment line of the form -- name: <Name> :<kind>, followed by SQL. The name becomes the method. The kind says what the method returns:

-- name: CreateAuthor :one
insert into authors (name, bio) values ($1, $2)
returning *;

-- name: GetAuthor :one
select * from authors where id = $1;

-- name: ListAuthors :many
select * from authors order by name;

-- name: UpdateBio :exec
update authors set bio = $2 where id = $1;

-- name: DeleteAuthor :execrows
delete from authors where id = $1;

-- name: CreateBook :one
insert into books (author_id, title, pages) values ($1, $2, $3)
returning id;

-- name: BooksByAuthor :many
select id, title, pages from books where author_id = $1 order by title;

Here are the kinds used, and what each one generates:

The article on annotations covers the rest (:execresult, :batch*, :copyfrom). Four are enough for this tutorial.

Three details in these queries are worth reading closely, because they show how much sqlc works out for you.

First, select * from authors where id = $1 is not left as a star. sqlc expands it to the table's columns in the generated query string, so the SQL that reaches the server names every column, and the returned struct is exactly the authors table. That has a consequence: add a column to authors, regenerate, and GetAuthor returns a struct with a new field. If you keep a hand written struct somewhere that mirrors the table, it is now out of date. Prefer listing columns in queries whose shape is part of an API.

Second, CreateAuthor takes $1 and $2. sqlc looks at the insert, sees they feed name and bio, and builds a CreateAuthorParams struct with a Name string and a Bio pgtype.Text. A query with one parameter gets a bare argument instead (GetAuthor(ctx, id int64)), and a query with two or more gets the struct. You never write either type.

Third, CreateBook ends in returning id and is marked :one, so the method returns the new id as an int64, not a struct. A query that returns a single column returns that column's type, not a one field struct.

The configuration

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5

This is configuration version 2, the one to use. Each entry under sql is one package of generated code: an engine, a schema, a set of queries and a gen block. The go generator takes a package name, an output directory and a sql_package. pgx/v5 is the setting that matters here. It makes the generated code call pgx's own API (pgxpool.Pool, pgx.Tx, pgtype) instead of database/sql, which is what gives you pgx's batching, COPY and richer types. Leave it out and the output targets database/sql with lib/pq style types such as sql.NullString.

out: . puts the generated files next to the program in the lab. A real project usually gives the output its own directory (internal/db), so a reviewer can see at a glance which files are generated. The configuration article goes through every option, including the MySQL and SQLite engines.

Generate

sqlc generate

That is the whole command. In the lab it printed nothing, which is how a clean run looks, and it wrote three files: db.go, models.go and query.sql.go. Read them once, because they are short and they are the contract.

models.go holds one struct per table. Author has ID int64, Name string, Bio pgtype.Text and CreatedAt pgtype.Timestamptz. The last field is the one people notice first: a timestamptz not null column is pgtype.Timestamptz, not time.Time, because pgx's native type carries the special values infinity and -infinity and a Valid flag. If that is more machinery than you want, an override turns it into time.Time, as the overrides article shows.

db.go holds the entry point. New(db DBTX) *Queries takes anything with the three methods the generated code calls (Exec, Query, QueryRow), which a *pgxpool.Pool, a *pgx.Conn and a pgx.Tx all satisfy. That small interface is the reason transactions need no special support: you build a Queries over the transaction and call the same methods, which the transactions article walks through.

query.sql.go holds one constant per query (the SQL text, with the star expanded), one parameter struct where needed, one row struct where needed, and one method. Here is the shape of GetAuthor, so you know what you are calling:

func (q *Queries) GetAuthor(ctx context.Context, id int64) (Author, error)

Nothing in the file is clever. It calls q.db.QueryRow, scans into the struct's fields in column order, and returns. That plainness is a feature: when a query misbehaves, you can read the generated function and see the exact call pgx receives.

Commit the generated files. Some teams prefer to generate in CI and keep them out of version control. The cost of that choice is that nobody can read the code in a pull request or browse it in the editor without running the tool first, and a failed generation in CI is a worse place to learn about a bad query than a developer's machine. The benefit of committing is that sqlc diff in CI can check that the committed files match the queries, which the article on vet and lint sets up.

Run it

The Go program below uses the generated code against a real database. The lab's run.sh creates a fresh database, loads the schema, runs sqlc generate and then go run ., so the output that follows is a complete round trip.

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0686
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
"$HOME/go/bin/sqlc" generate
go run .

1. CreateAuthor returned a main.Author with id 1 and a bio that is valid: true
   an author with no bio: Bio.Valid is false
2. GetAuthor: Ada
   a missing id: no rows in result set | pgx.ErrNoRows: true
3. ListAuthors returned 2 authors, as a []main.Author
4. UpdateBio is :exec and returns only an error; the new bio is compilers
5. BooksByAuthor returned rows of type main.BooksByAuthorRow: [{2 Engines 200} {1 Notes 90}]
   a check constraint is still the database's job: true
6. DeleteAuthor is :execrows and returned 1
   deleting it again returned 0

The program creates a pool and builds a Queries over it:

package main

import (
	"context"
	"errors"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"
)

const dsn = "postgres://postgres:[email protected]:55432/sqlc0686?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)

	ada, err := q.CreateAuthor(ctx, CreateAuthorParams{Name: "Ada", Bio: pgtype.Text{String: "first programmer", Valid: true}})
	must(err)
	fmt.Printf("1. CreateAuthor returned a %T with id %d and a bio that is valid: %v\n", ada, ada.ID, ada.Bio.Valid)

	grace, err := q.CreateAuthor(ctx, CreateAuthorParams{Name: "Grace"})
	must(err)
	fmt.Println("   an author with no bio: Bio.Valid is", grace.Bio.Valid)

	got, err := q.GetAuthor(ctx, ada.ID)
	must(err)
	fmt.Println("2. GetAuthor:", got.Name)
	_, err = q.GetAuthor(ctx, 999)
	fmt.Println("   a missing id:", err, "| pgx.ErrNoRows:", errors.Is(err, pgx.ErrNoRows))

	list, err := q.ListAuthors(ctx)
	must(err)
	fmt.Printf("3. ListAuthors returned %d authors, as a []%T\n", len(list), list[0])

	must(q.UpdateBio(ctx, UpdateBioParams{ID: grace.ID, Bio: pgtype.Text{String: "compilers", Valid: true}}))
	g2, _ := q.GetAuthor(ctx, grace.ID)
	fmt.Println("4. UpdateBio is :exec and returns only an error; the new bio is", g2.Bio.String)

	for _, t := range []struct {
		title string
		pages int32
	}{{"Notes", 90}, {"Engines", 200}} {
		id, err := q.CreateBook(ctx, CreateBookParams{AuthorID: ada.ID, Title: t.title, Pages: t.pages})
		must(err)
		_ = id
	}
	books, err := q.BooksByAuthor(ctx, ada.ID)
	must(err)
	fmt.Printf("5. BooksByAuthor returned rows of type %T: %v\n", books[0], books)
	_, err = q.CreateBook(ctx, CreateBookParams{AuthorID: ada.ID, Title: "Bad", Pages: 0})
	fmt.Println("   a check constraint is still the database's job:", err != nil)

	n, err := q.DeleteAuthor(ctx, grace.ID)
	must(err)
	fmt.Println("6. DeleteAuthor is :execrows and returned", n)
	n, _ = q.DeleteAuthor(ctx, grace.ID)
	fmt.Println("   deleting it again returned", n)
}

Read the output against it.

Typed results. CreateAuthor returned a main.Author with id 1. The RETURNING * in the SQL became the whole struct, so there is no second query to read back what the database generated (the id and the timestamp). Bio.Valid was true for Ada. For Grace, created with no bio, the program printed Bio.Valid is false. That is how NULL surfaces in this configuration: a struct with the value and a flag, not a zero string that you cannot tell apart from an empty one.

Not found is a value. GetAuthor for id 999 returned the error no rows in result set, and errors.Is(err, pgx.ErrNoRows) was true. A :one query is the only kind that can produce this error, and it is the one you must handle on every call site that looks something up by key. A handler that turns it into a 404 and everything else into a 500 is the usual pattern, and the article on ErrNoRows covers why comparing with errors.Is rather than == matters once errors get wrapped.

Slices and row types. ListAuthors returned two authors as a []main.Author, because the query selected every column of one table and sqlc reuses the model struct when the shapes match. BooksByAuthor selects three of the four columns, so it gets its own type, main.BooksByAuthorRow, with exactly those fields. The rows came back ordered by title (Engines before Notes), and the program printed them with their pages ({2 Engines 200} {1 Notes 90}).

:exec tells you nothing, on purpose. UpdateBio returns an error and no row count. The program re-read the author to show the change took effect. If a missing id should be an error, use :execrows, or add returning and use :one. An :exec that updates zero rows succeeds, which surprises people once.

:execrows is the cheap existence check. DeleteAuthor returned 1, and 0 on the second call. The handler for DELETE /authors/{id} can return 204 when the count is one and 404 when it is zero, with no separate SELECT and no race between the check and the delete.

The database still enforces its own rules. Creating a book with zero pages failed. The check (pages > 0) constraint is PostgreSQL's, and sqlc has no opinion on it: it verified that the insert is valid SQL for the schema, which it is. Validate input in Go where you can give a good error message, and keep the constraint as the backstop that holds even when a script or a person writes to the table directly.

How sqlc decides types

Most of the work in using sqlc is understanding why a field has the type it has. The rules are short.

• Columns map by their PostgreSQL type: bigint to int64, int to int32, smallint to int16, text and varchar to string, boolean to bool, bytea to []byte, jsonb to []byte, arrays to slices of the element type.
• Nullable columns use pgx's pgtype wrappers (pgtype.Text, pgtype.Int4, pgtype.Bool, pgtype.Timestamptz), each a value and a Valid flag. Setting emit_pointers_for_null_types: true turns the simple ones into pointers (*string) if you prefer nil to Valid. The configuration article shows that option's output.
• numeric becomes pgtype.Numeric, which carries arbitrary precision and is awkward to use. For money, override it to a decimal library, as the overrides article does.
• uuid becomes pgtype.UUID unless you override it, and timestamptz becomes pgtype.Timestamptz for the reason above.

When a type is wrong for your code, do not convert by hand at every call site. Fix it once in sqlc.yaml with an override, and every query that touches that column changes at the next generate.

A workflow that holds up

The tutorial's loop is four steps, and each belongs to a different moment.

1. Change the schema in a migration, and apply it to your development database.
2. Write or edit the query in the .sql file, with its name and kind.
3. Run sqlc generate and read the diff in the generated files. The diff is the review: a new field, a changed type, a renamed method.
4. Write the Go that calls it, and let the compiler tell you which call sites the change touched.

Two habits make this loop safe on a team.

Run sqlc generate and sqlc vet in CI, and fail the build if sqlc diff shows a change. That catches the commit where someone edited a query and forgot to regenerate, and the one where a hand edit landed in a generated file. The vet and lint article has the exact rules and the output they print.

Never edit generated files. Every file begins with // Code generated by sqlc. DO NOT EDIT. and the next run overwrites changes silently. If the generated code is not what you want, the fix is in the query, the schema or the configuration.

When sqlc is the wrong tool

It earns its place when most of your queries are known at write time, which describes most CRUD and reporting code. It is a poor fit in three situations.

• Heavily dynamic search. A screen with fifteen optional filters and a user chosen sort needs either an OR pattern that PostgreSQL plans poorly, or a query builder next to sqlc for that one endpoint. Using both in one project is normal.
• A schema that is unknown at build time. Multi-tenant designs that create tables per tenant cannot give sqlc a fixed schema to read.
• Teams that want the library to own the SQL. If nobody on the team is comfortable reading a query plan, an ORM's defaults may serve better than hand written statements nobody can tune.

For the first case, the keyset pagination article and the dynamic queries article show where the line falls.

Checklist for a first project

• Pin the sqlc version in CI (Docker tag or go install [email protected]) and in a README.
• Set sql_package: pgx/v5, and decide on emit_pointers_for_null_types before you write calling code, because changing it later edits every call site.
• Keep generated code in its own directory, committed, with sqlc diff in CI.
• Name queries for what they mean (GetAuthor, BooksByAuthor), not for their SQL.
• Handle pgx.ErrNoRows on every :one call that looks up by key.
• List columns in queries that back a public API, and keep select * for internal ones.
• Put overrides (UUIDs, decimals, timestamps) in the configuration once.
• Keep the database constraints, and validate in Go too.

The behaviour here matches sqlc's own getting started guide for PostgreSQL, its query annotation reference and its type mapping notes for pgx. The pgtype wrappers are documented in the pgx package reference.