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)
	// ...
}

: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 {
	//...
}

emit_query_batch (batching different queries)

The :batchexec, :batchmany, and :batchone annotations above batch the same query with different parameters. If you need to batch different queries into a single round-trip, use the emit_query_batch configuration option instead.

When emit_query_batch is enabled, sqlc generates a QueryBatch type with Queue* methods for each regular query (:one, :many, :exec, :execrows, :execresult). Each Queue* method accepts destination pointers where results are written when ExecuteBatch is called. All queued queries are sent in a single round-trip.

NOTE: This option only works with PostgreSQL using the pgx/v5 driver and outputting Go code.

# sqlc.yaml
version: "2"
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "query.sql"
    gen:
      go:
        package: "db"
        out: "db"
        sql_package: "pgx/v5"
        emit_query_batch: true

-- name: GetUser :one
SELECT * FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT * FROM users ORDER BY id;

-- name: UpdateUser :exec
UPDATE users SET name = $1 WHERE id = $2;

// Generated QueryBatch API:
batch := db.NewQueryBatch()

var user db.User
var found bool
batch.QueueGetUser(userID, &user, &found)

var users []db.User
batch.QueueListUsers(&users)

batch.QueueUpdateUser(db.UpdateUserParams{Name: "Alice", ID: 1})

// Send all queries in one round-trip:
err := queries.ExecuteBatch(ctx, batch)
// user, found, and users are now populated

The QueryBatch.Batch field is exported so you can mix generated Queue* calls with custom pgx batch operations on the same pgx.Batch. This feature can be used alongside :batch* annotations in the same package.

: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.