Type-safe database code with sqlc and goose

In Part 1 we got a Go service talking to Postgres inside Docker. Now it needs tables and queries. This is where sqlc comes in, and it’s the piece of this stack I’d miss most if I had to give it up.

Why sqlc instead of an ORM

With sqlc you write plain SQL, and it generates Go code with real types for every query’s parameters and results. It reads your schema, checks each query against it, and refuses to generate code if a column doesn’t exist or a type doesn’t match. You keep full control of the SQL, the compiler checks how you call it, and nothing happens through reflection at runtime: the output is ordinary Go you can read.

The trade-off is dynamic queries, like search filters that change shape at runtime. Those are awkward in sqlc, and I write them by hand with pgx when I need them. For the fixed queries that make up most of an API, sqlc is hard to beat.

Migrations with goose

sqlc needs a schema to check queries against, and that schema changes over time. goose manages those changes: each migration is a SQL file with an Up section and a Down section that undoes it.

terminal

go install github.com/pressly/goose/v3/cmd/goose@latest

sql/schema/00001_create_users.sql

-- +goose Up
CREATE TABLE users (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- +goose Down
DROP TABLE users;

sql/schema/00002_create_notes.sql

-- +goose Up
CREATE TABLE notes (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX notes_user_id_idx ON notes (user_id);

-- +goose Down
DROP TABLE notes;

Add two targets to the Makefile from Part 1 to apply and roll back migrations against the Compose database:

Makefile

migrate-up:
	goose -dir sql/schema postgres "$(DB_URL)" up

migrate-down:
	goose -dir sql/schema postgres "$(DB_URL)" down

Before merging a migration, run make migrate-down and then make migrate-up. If the Down section doesn’t cleanly undo the Up, you find out now instead of in the middle of an incident.

Configuring sqlc

terminal

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
go get github.com/google/uuid

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    schema: "sql/schema"
    queries: "sql/queries"
    gen:
      go:
        package: "database"
        out: "internal/database"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_pointers_for_null_types: true
        overrides:
          - db_type: "uuid"
            go_type:
              import: "github.com/google/uuid"
              type: "UUID"
          - db_type: "timestamptz"
            go_type:
              import: "time"
              type: "Time"

A few of these settings matter more than they look:

schema points at the goose migrations. sqlc understands goose’s annotations and ignores the Down sections, so the migrations stay the single source of truth for the schema.
sql_package: pgx/v5 generates code for pgx instead of database/sql, so it plugs straight into the pool from Part 1.
The overrides map Postgres uuid and timestamptz to uuid.UUID and time.Time, which are nicer to work with than the default pgx types.
emit_pointers_for_null_types turns nullable columns into pointers like *string, so nil means NULL.

Writing queries

sql/queries/users.sql

-- name: CreateUser :one
INSERT INTO users (email, name)
VALUES ($1, $2)
RETURNING *;

-- name: GetUserByEmail :one
SELECT * FROM users
WHERE email = $1;

-- name: ListUsers :many
SELECT * FROM users
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;

sql/queries/notes.sql

-- name: CreateNote :one
INSERT INTO notes (user_id, body)
VALUES ($1, $2)
RETURNING *;

-- name: ListNotesByUser :many
SELECT * FROM notes
WHERE user_id = $1
ORDER BY created_at DESC;

The comment above each query is what sqlc reads. The name becomes the Go method, and :one, :many or :exec decides whether you get back a single row, a slice, or only an error. Run sqlc generate, and internal/database fills up with code like this:

internal/database (generated)

// models.go
type User struct {
	ID        uuid.UUID `json:"id"`
	Email     string    `json:"email"`
	Name      string    `json:"name"`
	CreatedAt time.Time `json:"created_at"`
}

// users.sql.go
type CreateUserParams struct {
	Email string `json:"email"`
	Name  string `json:"name"`
}

func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) {
	row := q.db.QueryRow(ctx, createUser, arg.Email, arg.Name)
	var i User
	err := row.Scan(
		&i.ID,
		&i.Email,
		&i.Name,
		&i.CreatedAt,
	)
	return i, err
}

Now change email in one of the queries to a column that doesn’t exist and run sqlc generate again. It fails and tells you which query and which column are wrong. Getting that feedback before the code even compiles is the whole point.

When positional parameters like $1 and $2 get hard to follow, name them with sqlc.arg(email) or the shorthand @email, and the generated struct fields use those names.

Using the generated code

database.New accepts anything that can run queries, and the pgxpool.Pool from Part 1 qualifies, so wiring it up in main is one line: a := &api{pool: pool, q: database.New(pool)}. Here’s a handler that creates a user:

cmd/api/users.go

type api struct {
	pool *pgxpool.Pool
	q    *database.Queries
}

func (a *api) createUser(w http.ResponseWriter, r *http.Request) {
	var in struct {
		Email string `json:"email"`
		Name  string `json:"name"`
	}
	if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
		http.Error(w, "invalid JSON body", http.StatusBadRequest)
		return
	}

	user, err := a.q.CreateUser(r.Context(), database.CreateUserParams{
		Email: in.Email,
		Name:  in.Name,
	})

	var pgErr *pgconn.PgError
	switch {
	case errors.As(err, &pgErr) && pgErr.Code == "23505":
		http.Error(w, "email is already registered", http.StatusConflict)
		return
	case err != nil:
		http.Error(w, "could not create user", http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(http.StatusCreated)
	json.NewEncoder(w).Encode(user)
}

Postgres reports constraint violations with standard error codes, and 23505 is a unique violation, which maps naturally to 409 Conflict. For :one queries that find nothing, pgx returns pgx.ErrNoRows, which usually becomes a 404:

cmd/api/users.go

// Registered with: mux.HandleFunc("GET /users/{email}", a.getUser)
func (a *api) getUser(w http.ResponseWriter, r *http.Request) {
	user, err := a.q.GetUserByEmail(r.Context(), r.PathValue("email"))
	if errors.Is(err, pgx.ErrNoRows) {
		http.Error(w, "user not found", http.StatusNotFound)
		return
	}
	if err != nil {
		http.Error(w, "could not load user", http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(user)
}

Transactions

The generated Queries type has a WithTx method that returns a copy bound to a transaction, so the same typed methods work inside one. Creating a user together with their first note looks like this:

cmd/api/store.go

func (a *api) createUserWithNote(ctx context.Context, p database.CreateUserParams, body string) (database.User, error) {
	tx, err := a.pool.Begin(ctx)
	if err != nil {
		return database.User{}, err
	}
	defer tx.Rollback(ctx)

	q := a.q.WithTx(tx)

	user, err := q.CreateUser(ctx, p)
	if err != nil {
		return database.User{}, err
	}

	_, err = q.CreateNote(ctx, database.CreateNoteParams{
		UserID: user.ID,
		Body:   body,
	})
	if err != nil {
		return database.User{}, err
	}

	return user, tx.Commit(ctx)
}

Deferring Rollback is safe even after a successful Commit: pgx returns an error saying the transaction is already closed, and we ignore it. If anything returns early, the transaction is rolled back for us.

Next up

The schema is under version control, and every query is checked against it. Two things are still manual: we run migrations by hand, and nothing proves the queries behave correctly against a real database. Part 3 automates both and gets the image ready for production.