upstream-sqlc/docs/reference/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:

```sql
-- name: <name> <command>
```

## `:exec`

The generated method will return the error from
[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

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

```go
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](https://golang.org/pkg/database/sql/#Result) returned by
[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

```sql
-- name: DeleteAllAuthors :execresult
DELETE FROM authors;
```

```go
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](https://golang.org/pkg/database/sql/#Result) returned by
[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

```sql
-- name: DeleteAllAuthors :execrows
DELETE FROM authors;
```

```go
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](https://golang.org/pkg/database/sql/#Result) returned by
[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

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

```go
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](https://golang.org/pkg/database/sql/#DB.QueryContext).

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

```go
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](https://golang.org/pkg/database/sql/#DB.QueryRowContext).

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

```go
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.

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

```go
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.

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

```go
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.

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

```go
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](../howto/insert.md#using-copyfrom)

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