Generated Queries

Intermediate3 min read

Every query in this book so far has been a string literal plus a manual Scan call listing each column in order. That's fine at the scale of one book, and it's exactly where real projects start feeling the cost: add a column, and every Scan call that forgot to update stays wrong until it panics or silently drops data — the compiler can't check a string. sqlc reads your schema and your queries and generates the struct and the Scan call for you, so a column mismatch is a build failure, not a runtime surprise. This lesson doesn't write any SQL — it reads what sqlc already generated and wires it in, which is the actual day-to-day experience of using it.

type Queries struct{ db DBTX }

func (q *Queries) GetTask(ctx context.Context, id int64) (Task, error) {
	row := q.db.QueryRowContext(ctx, getTask, id)
	var i Task
	err := row.Scan(&i.ID, &i.Title, &i.Done, &i.Priority)
	return i, err
}

Recognize the shape? It's QueryRowContext and Scan, the same two calls Chapter 2 taught by hand — sqlc isn't a different way of talking to the database, it's a code generator that writes the boilerplate you'd otherwise write yourself, from the same SQL you'd otherwise embed as a string. Nothing about database/sql changed underneath it.

The seam still needs you

task, err := q.GetTask(ctx, id)
if errors.Is(err, sql.ErrNoRows) {
	return Task{}, ErrNotFound
}

This is the one thing sqlc deliberately does not generate: GetTask returns exactly what Scan gave it, sql.ErrNoRows included, raw. That's correct — sqlc doesn't know your domain has an ErrNotFound, or that a UNIQUE violation on title should become ErrDuplicate rather than a generic failure. Those decisions live in your code, calling the generated Queries the same way SQLStore called *sql.DB directly two lessons ago. Generated code moves where the SQL and the Scan calls live; it does not move the judgment calls about what a database error means to your application. You've now written that translation four times in this book — by hand against *sql.DB, and here against sqlc's generated *Queries — and it's the same discipline both times, because the seam it protects doesn't move.

What you're not asked to do here

The generated files — models.go, db.go, query.sql.go — are given, committed alongside the exercise exactly as sqlc generate produced them. You never run sqlc yourself in this exercise; a real project runs it at authoring time and commits the output, same as here. What's graded is only the calling code: reading Queries' method signatures and writing the two functions that use them correctly.

Try it

Implement GetTaskOrNotFound and CreateUnlessDuplicate against the generated *store.Queries, translating sql.ErrNoRows and a UNIQUE violation the same way you have all book.

Practice

1. Wire Generated Queries into the Store

Intermediate

store/ is generated by sqlc from a schema and three queries (CreateTask, GetTask, ListTasks) — read it, don't edit it.

Implement two functions in main.go:

GetTaskOrNotFound(ctx context.Context, q *store.Queries, id int64) (store.Task, error)

Calls q.GetTask.
Returns ErrNotFound if no such row exists.
Wraps any other failure with %w.

CreateUnlessDuplicate(ctx context.Context, q *store.Queries, title string, priority int) (store.Task, error)

Calls q.CreateTask.
Returns ErrDuplicate if title already exists (a UNIQUE violation).
Wraps any other failure with %w — do not report it as ErrDuplicate.

main.go

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"

	_ "modernc.org/sqlite"

	"submission/store"
)

var ErrNotFound = errors.New("task not found")
var ErrDuplicate = errors.New("task title already exists")

// GetTaskOrNotFound reads a task by id, translating a missing row into
// ErrNotFound.
func GetTaskOrNotFound(ctx context.Context, q *store.Queries, id int64) (store.Task, error) {
	// TODO: implement
	return store.Task{}, fmt.Errorf("not implemented")
}

// CreateUnlessDuplicate creates a task, translating a UNIQUE violation on
// title into ErrDuplicate.
func CreateUnlessDuplicate(ctx context.Context, q *store.Queries, title string, priority int) (store.Task, error) {
	// TODO: implement
	return store.Task{}, fmt.Errorf("not implemented")
}

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	defer db.Close()
	_ = store.New(db)
}

store/db.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.31.1

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
	PrepareContext(context.Context, string) (*sql.Stmt, error)
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

type Queries struct {
	db DBTX
}

func (q *Queries) WithTx(tx *sql.Tx) *Queries {
	return &Queries{
		db: tx,
	}
}

store/models.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.31.1

package store

type Task struct {
	ID       int64
	Title    string
	Done     bool
	Priority int64
}

store/query.sql.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.31.1
// source: query.sql

package store

import (
	"context"
)

const createTask = `-- name: CreateTask :one
INSERT INTO tasks (title, priority) VALUES (?, ?) RETURNING id, title, done, priority
`

type CreateTaskParams struct {
	Title    string
	Priority int64
}

func (q *Queries) CreateTask(ctx context.Context, arg CreateTaskParams) (Task, error) {
	row := q.db.QueryRowContext(ctx, createTask, arg.Title, arg.Priority)
	var i Task
	err := row.Scan(
		&i.ID,
		&i.Title,
		&i.Done,
		&i.Priority,
	)
	return i, err
}

const getTask = `-- name: GetTask :one
SELECT id, title, done, priority FROM tasks WHERE id = ?
`

func (q *Queries) GetTask(ctx context.Context, id int64) (Task, error) {
	row := q.db.QueryRowContext(ctx, getTask, id)
	var i Task
	err := row.Scan(
		&i.ID,
		&i.Title,
		&i.Done,
		&i.Priority,
	)
	return i, err
}

const listTasks = `-- name: ListTasks :many
SELECT id, title, done, priority FROM tasks ORDER BY id
`

func (q *Queries) ListTasks(ctx context.Context) ([]Task, error) {
	rows, err := q.db.QueryContext(ctx, listTasks)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Task
	for rows.Next() {
		var i Task
		if err := rows.Scan(
			&i.ID,
			&i.Title,
			&i.Done,
			&i.Priority,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}