A pgx Cheat Sheet for Pools Queries and Batches in Go

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"time"

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

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable&pool_max_conns=4&pool_max_conn_lifetime=30m&pool_health_check_period=1m"

type Item struct {
	ID    int64
	Name  string
	Qty   int
	Notes *string
	Tags  []string
	Added time.Time
}

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

func main() {
	ctx := context.Background()

	// Pool
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	fmt.Println("pool: max", pool.Config().MaxConns, "| lifetime", pool.Config().MaxConnLifetime)

	// Exec
	_, err = pool.Exec(ctx, "drop table if exists sheet685")
	must(err)
	_, err = pool.Exec(ctx, "create table sheet685 (id bigint primary key, name text not null, qty int not null default 0, notes text, tags text[] not null default '{}', added timestamptz not null default now())")
	must(err)
	tag, err := pool.Exec(ctx, "insert into sheet685 (id, name, qty, tags) values ($1, $2, $3, $4)", 1, "bolt", 10, []string{"steel", "m6"})
	must(err)
	fmt.Println("exec:", tag, tag.RowsAffected())

	// Named arguments
	_, err = pool.Exec(ctx, "insert into sheet685 (id, name, qty) values (@id, @name, @qty)", pgx.NamedArgs{"id": 2, "name": "nut", "qty": 20})
	must(err)

	// QueryRow and Scan
	var name string
	var notes *string
	must(pool.QueryRow(ctx, "select name, notes from sheet685 where id = $1", 1).Scan(&name, &notes))
	fmt.Println("queryrow:", name, "notes nil:", notes == nil)

	// No rows
	err = pool.QueryRow(ctx, "select name from sheet685 where id = 99").Scan(&name)
	fmt.Println("no rows:", errors.Is(err, pgx.ErrNoRows))

	// Collect rows
	rows, _ := pool.Query(ctx, "select id, name, qty, notes, tags, added from sheet685 order by id")
	items, err := pgx.CollectRows(rows, pgx.RowToStructByName[Item])
	must(err)
	fmt.Println("collect:", len(items), "items, first", items[0].Name, items[0].Tags)

	// One row, strictly
	rows, _ = pool.Query(ctx, "select name from sheet685 where id = $1", 2)
	one, err := pgx.CollectExactlyOneRow(rows, pgx.RowTo[string])
	must(err)
	fmt.Println("exactly one:", one)

	// Batch
	b := &pgx.Batch{}
	b.Queue("update sheet685 set qty = qty + 1 where id = $1", 1)
	b.Queue("select qty from sheet685 where id = $1", 1)
	br := pool.SendBatch(ctx, b)
	_, err = br.Exec()
	must(err)
	var qty int
	must(br.QueryRow().Scan(&qty))
	must(br.Close())
	fmt.Println("batch: qty is now", qty)

	// Transaction
	err = pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error {
		if _, err := tx.Exec(ctx, "update sheet685 set qty = qty - 5 where id = 1"); err != nil {
			return err
		}
		return errors.New("roll back")
	})
	must(pool.QueryRow(ctx, "select qty from sheet685 where id = 1").Scan(&qty))
	fmt.Println("transaction:", err, "| qty still", qty)

	// COPY
	n, err := pool.CopyFrom(ctx, pgx.Identifier{"sheet685"}, []string{"id", "name"}, pgx.CopyFromRows([][]any{{10, "a"}, {11, "b"}}))
	must(err)
	fmt.Println("copy:", n, "rows")

	// Errors
	_, err = pool.Exec(ctx, "insert into sheet685 (id, name) values (1, 'dup')")
	var pg *pgconn.PgError
	if errors.As(err, &pg) {
		fmt.Println("error:", pg.Code, pg.ConstraintName)
	}

	// Nullable and arrays
	var text pgtype.Text
	must(pool.QueryRow(ctx, "select notes from sheet685 where id = 1").Scan(&text))
	var tags []string
	must(pool.QueryRow(ctx, "select tags from sheet685 where id = 1").Scan(&tags))
	fmt.Println("types: pgtype.Text valid", text.Valid, "| tags", tags)

	// Any list
	var found int
	must(pool.QueryRow(ctx, "select count(*) from sheet685 where id = any($1)", []int64{1, 2, 99}).Scan(&found))
	fmt.Println("any:", found)

	// Context deadline
	short, cancel := context.WithTimeout(ctx, 100*time.Millisecond)
	defer cancel()
	_, err = pool.Exec(short, "select pg_sleep(1)")
	fmt.Println("deadline:", pgconn.Timeout(err))

	// Acquire
	conn, err := pool.Acquire(ctx)
	must(err)
	defer conn.Release()
	var pid int
	must(conn.QueryRow(ctx, "select pg_backend_pid()").Scan(&pid))
	fmt.Println("acquire: one connection held, pool acquired =", pool.Stat().AcquiredConns())
}

pool: max 4 | lifetime 30m0s
exec: INSERT 0 1 1
queryrow: bolt notes nil: true
no rows: true
collect: 2 items, first bolt [steel m6]
exactly one: nut
batch: qty is now 11
transaction: roll back | qty still 11
copy: 2 rows
error: 23505 sheet685_pkey
types: pgtype.Text valid false | tags [steel m6]
any: 2
deadline: true
acquire: one connection held, pool acquired = 1