An sqlc Cheat Sheet for Queries Config and Overrides

create type status as enum ('draft', 'live');
create table authors (
  id    bigserial primary key,
  name  text not null,
  email text not null unique,
  bio   text
);
create table books (
  id        bigserial primary key,
  author_id bigint not null references authors(id),
  title     text not null,
  pages     int not null check (pages > 0),
  status    status not null default 'draft'
);

-- name: CreateAuthor :one
insert into authors (name, email, bio) values ($1, $2, $3) returning *;

-- name: GetAuthor :one
select * from authors where id = $1;

-- name: ListBooks :many
select * from books where author_id = $1 order by id;

-- name: BooksByStatus :many
select id, title from books where status = sqlc.arg(status)::status order by id;

-- name: SearchBooks :many
select id, title from books
where (sqlc.narg('author_id')::bigint is null or author_id = sqlc.narg('author_id'))
  and (sqlc.narg('min_pages')::int is null or pages >= sqlc.narg('min_pages'))
order by id;

-- name: BooksByIDs :many
select id, title from books where id = any($1::bigint[]) order by id;

-- name: AddBook :one
insert into books (author_id, title, pages) values ($1, $2, $3) returning id;

-- name: Publish :execrows
update books set status = 'live' where id = $1 and status = 'draft';

-- name: RenameAuthor :exec
update authors set name = $2 where id = $1;

-- name: UpsertAuthor :one
insert into authors (name, email) values ($1, $2)
on conflict (email) do update set name = excluded.name
returning id;

-- name: CountByStatus :many
select status, count(*) as n from books group by status order by status;

-- name: BookWithAuthor :one
select sqlc.embed(books), sqlc.embed(authors) from books join authors on authors.id = books.author_id where books.id = $1;

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: db
        out: db
        sql_package: pgx/v5
        emit_json_tags: true
        emit_empty_slices: true
        emit_pointers_for_null_types: true

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"os"

	"example.test/lab/db"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

func main() {
	ctx := context.Background()
	base := "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
	admin, err := pgxpool.New(ctx, base)
	must(err)
	ddl, _ := os.ReadFile("schema.sql")
	_, err = admin.Exec(ctx, "drop schema if exists l0705 cascade; create schema l0705; set search_path = l0705; "+string(ddl))
	must(err)
	pool, err := pgxpool.New(ctx, base+"&search_path=l0705")
	must(err)
	q := db.New(pool)

	bio := "writes Go"
	ada, err := q.CreateAuthor(ctx, db.CreateAuthorParams{Name: "Ada", Email: "[email protected]", Bio: &bio})
	must(err)
	bob, _ := q.CreateAuthor(ctx, db.CreateAuthorParams{Name: "Bob", Email: "[email protected]"})
	fmt.Println(":one with returning *:", ada.ID, ada.Name, "bio:", *ada.Bio, "| nil bio is a nil pointer:", bob.Bio == nil)

	for i, pages := range []int32{120, 300, 450} {
		id, err := q.AddBook(ctx, db.AddBookParams{AuthorID: ada.ID, Title: fmt.Sprintf("Book %d", i+1), Pages: pages})
		must(err)
		_ = id
	}
	books, _ := q.ListBooks(ctx, ada.ID)
	none, _ := q.ListBooks(ctx, bob.ID)
	fmt.Println(":many:", len(books), "books | empty result is a non nil slice:", none != nil, len(none))

	n, _ := q.Publish(ctx, books[1].ID)
	again, _ := q.Publish(ctx, books[1].ID)
	fmt.Println(":execrows publish twice:", n, again)

	live, _ := q.BooksByStatus(ctx, db.StatusLive)
	fmt.Println("enum parameter:", len(live), "live |", db.StatusLive, db.StatusDraft)

	min := int32(200)
	sr, _ := q.SearchBooks(ctx, db.SearchBooksParams{AuthorID: &ada.ID, MinPages: &min})
	all, _ := q.SearchBooks(ctx, db.SearchBooksParams{})
	fmt.Println("narg filters: with both", len(sr), "| with none", len(all))

	byIDs, _ := q.BooksByIDs(ctx, []int64{books[0].ID, books[2].ID})
	fmt.Println("any($1::bigint[]):", len(byIDs))

	must(q.RenameAuthor(ctx, db.RenameAuthorParams{ID: ada.ID, Name: "Ada L"}))
	id2, _ := q.UpsertAuthor(ctx, db.UpsertAuthorParams{Name: "Ada Lovelace", Email: "[email protected]"})
	got, _ := q.GetAuthor(ctx, ada.ID)
	fmt.Println(":exec and upsert:", id2 == ada.ID, got.Name)

	counts, _ := q.CountByStatus(ctx)
	for _, c := range counts {
		fmt.Println("group by:", c.Status, c.N)
	}

	bw, _ := q.BookWithAuthor(ctx, books[0].ID)
	fmt.Println("sqlc.embed:", bw.Book.Title, "by", bw.Author.Name)

	_, err = q.GetAuthor(ctx, 999)
	fmt.Println("missing row is pgx.ErrNoRows:", errors.Is(err, pgx.ErrNoRows))

	tx, _ := pool.Begin(ctx)
	qtx := q.WithTx(tx)
	qtx.RenameAuthor(ctx, db.RenameAuthorParams{ID: ada.ID, Name: "rolled back"})
	tx.Rollback(ctx)
	back, _ := q.GetAuthor(ctx, ada.ID)
	fmt.Println("WithTx then rollback:", back.Name)
}

set -e
~/go/bin/sqlc generate
go mod tidy > /dev/null 2>&1
go run .

:one with returning *: 1 Ada bio: writes Go | nil bio is a nil pointer: true
:many: 3 books | empty result is a non nil slice: true 0
:execrows publish twice: 1 0
enum parameter: 1 live | live draft
narg filters: with both 2 | with none 3
any($1::bigint[]): 2
:exec and upsert: true Ada Lovelace
group by: draft 2
group by: live 1
sqlc.embed: Book 1 by Ada Lovelace
missing row is pgx.ErrNoRows: true
WithTx then rollback: Ada Lovelace