sqlc Query Annotations for one many exec and More Explained

Every sqlc query starts with a comment of the form -- name: GetTask :one. The name becomes the Go method, and the word after the colon, the annotation, decides its signature. There are nine in current use, and picking the right one is most of what there is to learn about writing sqlc queries. This reference lists each with the Go it generates and the behaviour the lab recorded, using sqlc v1.30.0, pgx v5.11.0 and PostgreSQL 18.6 on Go 1.25.6.

It is a companion to the sqlc tutorial inside the database cornerstone. The batch and copy kinds go deeper in the batch article.

One file, ten queries

The lab declares one query of each kind against a small table, then calls them in order:

create table tasks (
    id    bigserial primary key,
    title text not null,
    done  boolean not null default false
);

-- name: GetTask :one
select * from tasks where id = $1;

-- name: ListTasks :many
select * from tasks order by id;

-- name: AddTask :exec
insert into tasks (title) values ($1);

-- name: Finish :execrows
update tasks set done = true where id = $1 and not done;

-- name: AddTaskResult :execresult
insert into tasks (title) values ($1);

-- name: Rename :one
update tasks set title = $2 where id = $1 returning *;

-- name: AddMany :batchexec
insert into tasks (title) values ($1);

-- name: GetMany :batchone
select * from tasks where id = $1;

-- name: ByDone :batchmany
select * from tasks where done = $1 order by id;

-- name: LoadTasks :copyfrom
insert into tasks (title, done) values ($1, $2);

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

package main

import (
	"context"
	"fmt"
	"log"

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

const dsn = "postgres://postgres:[email protected]:55432/sqlc0688?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)

	must(q.AddTask(ctx, "first")) // :exec returns only an error
	res, err := q.AddTaskResult(ctx, "second")
	must(err)
	fmt.Printf("1. :exec returns error; :execresult returns a command tag: %q, %d row\n", res.String(), res.RowsAffected())

	t, err := q.GetTask(ctx, 1) // :one returns one struct
	must(err)
	fmt.Printf("2. :one returns a %T: %+v\n", t, t)

	all, err := q.ListTasks(ctx) // :many returns a slice
	must(err)
	fmt.Printf("3. :many returns a []%T of length %d\n", all[0], len(all))

	n, err := q.Finish(ctx, 1) // :execrows returns the number of rows
	must(err)
	n2, _ := q.Finish(ctx, 1)
	fmt.Printf("4. :execrows returns an int64: %d the first time, %d when it was already done\n", n, n2)

	renamed, err := q.Rename(ctx, RenameParams{ID: 2, Title: "second, renamed"}) // :one on an update with returning
	must(err)
	fmt.Println("5. :one with update ... returning gives the new row:", renamed.Title)

	// :batchexec, :batchone and :batchmany send several calls in one round trip.
	be := q.AddMany(ctx, []string{"b1", "b2", "b3"})
	count := 0
	be.Exec(func(i int, err error) {
		must(err)
		count++
	})
	must(be.Close())
	fmt.Println("6. :batchexec ran", count, "inserts in one round trip")

	bo := q.GetMany(ctx, []int64{1, 2, 999})
	var found, missing int
	bo.QueryRow(func(i int, t Task, err error) {
		if err != nil {
			missing++
			return
		}
		found++
	})
	must(bo.Close())
	fmt.Printf("7. :batchone looked up three ids: %d found, %d missing\n", found, missing)

	bm := q.ByDone(ctx, []bool{true, false})
	sizes := []int{}
	bm.Query(func(i int, ts []Task, err error) {
		must(err)
		sizes = append(sizes, len(ts))
	})
	must(bm.Close())
	fmt.Println("8. :batchmany ran two queries and returned result sets of sizes", sizes)

	copied, err := q.LoadTasks(ctx, []LoadTasksParams{{"c1", false}, {"c2", true}, {"c3", false}}) // :copyfrom uses COPY
	must(err)
	fmt.Println("9. :copyfrom loaded", copied, "rows through the COPY protocol")
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0688
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. :exec returns error; :execresult returns a command tag: "INSERT 0 1", 1 row
2. :one returns a main.Task: {ID:1 Title:first Done:false}
3. :many returns a []main.Task of length 2
4. :execrows returns an int64: 1 the first time, 0 when it was already done
5. :one with update ... returning gives the new row: second, renamed
6. :batchexec ran 3 inserts in one round trip
7. :batchone looked up three ids: 2 found, 1 missing
8. :batchmany ran two queries and returned result sets of sizes [1 4]
9. :copyfrom loaded 3 rows through the COPY protocol

The nine kinds fall into three groups: the ones that return rows, the ones that return a count or status, and the ones that talk to the server in bulk. The table summarises them, and the sections after it go through the output.

Returning rows

:one returns a struct and an error. The lab's GetTask printed {ID:1 Title:first Done:false}, a main.Task. If the query matches no row, the error is pgx.ErrNoRows, and a caller that treats every error as a failure turns a missing record into a 500. If the query can match more than one row, :one returns the first and ignores the rest, which hides a missing limit 1 or a missing unique constraint, so write the where clause so that it can match at most one.

A :one is not limited to selects. Rename is update tasks set title = $2 where id = $1 returning *, and it handed back the renamed row (second, renamed) in the same round trip. The same goes for insert ... returning. That removes the follow up read that ORMs often issue after a write, and removes the window in which another writer changes the row between the two statements.

:many returns a slice. ListTasks returned a []main.Task of length 2. An empty result is a nil slice by default, which marshals to JSON null. If your clients expect [], set emit_empty_slices: true in the configuration, as the config article lists.

Returning a count or a status

:exec returns only an error. AddTask is an insert whose outcome the caller does not need. Remember that it succeeds when zero rows match an update or delete, so a handler that must say "not found" needs one of the next two.

:execrows returns the number of rows affected. Finish is update tasks set done = true where id = $1 and not done, and it returned 1 the first time and 0 when called again for a task that was already done. That gives you three states from one statement: changed, already in that state, and missing (zero rows again, so combine with a lookup if you must tell the last two apart). For a DELETE /things/{id} handler it is the right default, returning 204 on one and 404 on zero with no second query.

:execresult returns the driver's own result. With pgx that is a pgconn.CommandTag, whose String() gave "INSERT 0 1" in the lab and whose RowsAffected() gave 1. Use it when you want the command tag text, or when the shape of the result matters to a layer that already handles sql.Result. For most code :execrows says what you want with less to read.

:execlastid is for MySQL and SQLite on database/sql, where an insert does not return a row and the id comes from LastInsertId. PostgreSQL does not have it, and the idiom there is returning id with :one.

Bulk: batch and copy

These four kinds exist only with sql_package: pgx/v5, because they use features of the pgx API that database/sql has no equivalent for.

:batchexec queues one execution of the statement per element of a slice and sends them together. AddMany took []string{"b1","b2","b3"}, and the callback ran three times, once per statement, with its own error. The loop runs after the batch is sent, so the callback is where you check each result.

:batchone does the same for a query that returns one row each. GetMany looked up ids 1, 2 and 999, and the callback was told about each separately: two found, one missing. A missing row arrives as pgx.ErrNoRows in the callback's err, so one absent id does not fail the other two lookups. That is a different failure model from three sequential GetTask calls, where you would handle three errors yourself.

:batchmany runs a query that returns several rows once per argument. ByDone was called with true and false, and the callback received result sets of sizes 1 and 4.

:copyfrom loads a slice of rows through PostgreSQL's COPY protocol, which is the fastest way to insert many rows. LoadTasks took three rows and returned 3. The statement it annotates is an insert ... values with the columns to load, and the generator rewrites it into a copy. Unlike a batch it is all or nothing: one bad row aborts the whole load, which the batch article shows with a duplicate key.

Choosing quickly

Annotations that are not kinds

Two other comment forms appear inside a query and change the generated code without being a kind. sqlc.arg('name') and sqlc.narg('name') name a parameter and mark it non nullable or nullable, which is how a query gets a field called AfterID instead of Column1. sqlc.embed(table) selects a whole table into a nested struct, which the joins article covers, and sqlc.slice('ids') expands to a variable list on MySQL and SQLite.

A comment placed above a query, other than the name line, becomes the doc comment on the generated method. That is the cheap way to document the contract of a query.

Mistakes to avoid

• Using :exec where the caller needs to know whether a row existed.
• Using :one on a query that can match several rows without a limit 1.
• Reaching for a :batch* kind and then wrapping the whole loop in no transaction. A batch is sent as one unit, but as the transactions article notes, you choose its atomicity.
• Writing :copyfrom for a table with a column the insert does not list. The statement must name every column to load.

The kinds are documented in sqlc's query annotation reference, and the batch and copy support in its pgx notes.