query-annotations.md

Query annotations

sqlc requires each query to have a small comment indicating the name and command. The format of this comment is as follows:

-- name: <name> <command>

:exec

The generated method will return the error from ExecContext.

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

func (q *Queries) DeleteAuthor(ctx context.Context, id int64) error {
	_, err := q.db.ExecContext(ctx, deleteAuthor, id)
	return err
}

:execresult

The generated method will return the sql.Result returned by ExecContext.

-- name: DeleteAllAuthors :execresult
DELETE FROM authors;

func (q *Queries) DeleteAllAuthors(ctx context.Context) (sql.Result, error) {
	return q.db.ExecContext(ctx, deleteAllAuthors)
}

:execrows

The generated method will return the number of affected rows from the result returned by ExecContext.

-- name: DeleteAllAuthors :execrows
DELETE FROM authors;

func (q *Queries) DeleteAllAuthors(ctx context.Context) (int64, error) {
	_, err := q.db.ExecContext(ctx, deleteAllAuthors)
	// ...
}

:execlastid

The generated method will return the number generated by the database from the result returned by ExecContext.

-- name: InsertAuthor :execlastid
INSERT INTO authors (name) VALUES (?);

func (q *Queries) InsertAuthor(ctx context.Context, name string) (int64, error) {
	_, err := q.db.ExecContext(ctx, insertAuthor, name)
	// ...
}

:iter

The generated method will return an iter.Seq that streams records as they are scanned, together with a function that eventually reports the query's error. This needs Go 1.23 or later.

-- name: ListAuthors :iter
SELECT * FROM authors
ORDER BY name;

func (q *Queries) ListAuthors(ctx context.Context) (iter.Seq[Author], func() error) {
	// ...
}

To consume, you can range over the iter.Seq:

authors, ferr := q.ListAuthors(ctx)
for author := range authors {
	// ...
}
if err := ferr(); err != nil {
	// ...
}

The query starts only when the iterator is ranged over, not when the method is called. When q runs on a single connection or transaction, it must not be used inside the for loop, as the query is still reading from it. Once the loop ends, by running out of rows or by break, the rows are closed and the connection is released.

The function returned alongside the iterator reports any error from running the query or scanning its rows, so call it after the loop.

The iterator is single-use: it runs its query once, which matters for a DELETE ... RETURNING or INSERT ... RETURNING. Ranging over it a second time yields nothing, and the returned function then reports an error.

:many

The generated method will return a slice of records via QueryContext.

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

func (q *Queries) ListAuthors(ctx context.Context) ([]Author, error) {
	rows, err := q.db.QueryContext(ctx, listAuthors)
	// ...
}

:one

The generated method will return a single record via QueryRowContext.

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

func (q *Queries) GetAuthor(ctx context.Context, id int64) (Author, error) {
	row := q.db.QueryRowContext(ctx, getAuthor, id)
	// ...
}

:batchexec

NOTE: This command only works with PostgreSQL using the pgx/v4 and pgx/v5 drivers and outputting Go code.

The generated method will return a batch object. The batch object will have the following methods:

• Exec, that takes a func(int, error) parameter,
• Close, to close the batch operation early.

-- name: DeleteBook :batchexec
DELETE FROM books
WHERE book_id = $1;

type DeleteBookBatchResults struct {
	br  pgx.BatchResults
	ind int
}

func (q *Queries) DeleteBook(ctx context.Context, bookID []int32) *DeleteBookBatchResults {
	//...
}
func (b *DeleteBookBatchResults) Exec(f func(int, error)) {
	//...
}
func (b *DeleteBookBatchResults) Close() error {
	//...
}

:batchmany

NOTE: This command only works with PostgreSQL using the pgx/v4 and pgx/v5 drivers and outputting Go code.

The generated method will return a batch object. The batch object will have the following methods:

• Query, that takes a func(int, []T, error) parameter, where T is your query's return type
• Close, to close the batch operation early.

-- name: BooksByTitleYear :batchmany
SELECT * FROM books
WHERE title = $1 AND year = $2;

type BooksByTitleYearBatchResults struct {
	br  pgx.BatchResults
	ind int
}
type BooksByTitleYearParams struct {
	Title string `json:"title"`
	Year  int32  `json:"year"`
}

func (q *Queries) BooksByTitleYear(ctx context.Context, arg []BooksByTitleYearParams) *BooksByTitleYearBatchResults {
	//...
}
func (b *BooksByTitleYearBatchResults) Query(f func(int, []Book, error)) {
	//...
}
func (b *BooksByTitleYearBatchResults) Close() error {
	//...
}

:batchone

NOTE: This command only works with PostgreSQL using the pgx/v4 and pgx/v5 drivers and outputting Go code.

The generated method will return a batch object. The batch object will have the following methods:

• QueryRow, that takes a func(int, T, error) parameter, where T is your query's return type
• Close, to close the batch operation early.

-- name: CreateBook :batchone
INSERT INTO books (
    author_id,
    isbn
) VALUES (
    $1,
    $2
)
RETURNING book_id, author_id, isbn

type CreateBookBatchResults struct {
	br  pgx.BatchResults
	ind int
}
type CreateBookParams struct {
	AuthorID int32  `json:"author_id"`
	Isbn     string `json:"isbn"`
}

func (q *Queries) CreateBook(ctx context.Context, arg []CreateBookParams) *CreateBookBatchResults {
	//...
}
func (b *CreateBookBatchResults) QueryRow(f func(int, Book, error)) {
	//...
}
func (b *CreateBookBatchResults) Close() error {
	//...
}

:copyfrom

__NOTE: This command is driver and package specific, see how to insert

This command is used to insert rows a lot faster than sequential inserts.