sqlc With pgx v5 Setup Steps and Pitfalls to Avoid

Most sqlc projects that start fresh on PostgreSQL choose pgx v5 as the driver, and the choice is one line, sql_package: pgx/v5. What it changes is bigger than the line suggests. The generated code stops going through database/sql and talks to pgx directly, which unlocks the batch and copy kinds, the richer pgtype values and the pool's own configuration. It also moves a few decisions into your code that the standard library had hidden.

This article walks through the setup and runs one program that shows what the generated code accepts and returns, on PostgreSQL 18.6 with sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6. It follows the sqlc tutorial and the configuration article, and the driver itself is introduced in the pgx tutorial, within the database cornerstone.

Setup in four steps

1. Install the driver: go get github.com/jackc/pgx/[email protected]. The pgxpool package is part of the same module.
2. Set the generator option in sqlc.yaml:

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5

1. Write the schema and the queries. The lab uses a table of readings with a timestamp, a numeric, a nullable text and a text array:

create table readings (
    id    bigserial primary key,
    taken timestamptz not null,
    value numeric(8,3) not null,
    note  text,
    tags  text[] not null default '{}'
);

-- name: Add :one
insert into readings (taken, value, note, tags) values ($1, $2, $3, $4) returning *;

-- name: Latest :one
select * from readings order by id desc limit 1;

1. Generate and call: sqlc generate, then New(pool) and the methods.

Nothing in the queries changes between drivers. What changes is the code sqlc writes around them: parameter types, null wrappers, and the extra kinds available.

One Queries type, three connection kinds

The generated db.go declares the interface that everything depends on:

type DBTX interface {
	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
	QueryRow(context.Context, string, ...interface{}) pgx.Row
}

func New(db DBTX) *Queries

*pgxpool.Pool, *pgx.Conn and pgx.Tx all have exactly those methods, so any of them goes into New. The lab does it three times with the same query:

package main

import (
	"context"
	"fmt"
	"log"
	"math/big"
	"time"

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

const dsn = "postgres://postgres:[email protected]:55432/sqlc0696?sslmode=disable"

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

// counter is a pgx QueryTracer that counts statements.
type counter struct{ n int }

func (c *counter) TraceQueryStart(ctx context.Context, _ *pgx.Conn, _ pgx.TraceQueryStartData) context.Context {
	c.n++
	return ctx
}
func (c *counter) TraceQueryEnd(context.Context, *pgx.Conn, pgx.TraceQueryEndData) {}

func main() {
	ctx := context.Background()
	cfg, err := pgxpool.ParseConfig(dsn)
	must(err)
	tr := &counter{}
	cfg.ConnConfig.Tracer = tr
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	must(err)
	defer pool.Close()

	p := AddParams{
		Taken: pgtype.Timestamptz{Time: time.Date(2026, 1, 2, 3, 4, 5, 0, time.UTC), Valid: true},
		Value: pgtype.Numeric{Int: big.NewInt(12345), Exp: -3, Valid: true},
		Note:  pgtype.Text{String: "hello", Valid: true},
		Tags:  []string{"a", "b"},
	}

	// One Queries type, three things that satisfy its DBTX interface.
	conn, err := pgx.Connect(ctx, dsn)
	must(err)
	defer conn.Close(ctx)
	tx, err := pool.Begin(ctx)
	must(err)

	for _, c := range []struct {
		name string
		db   DBTX
	}{{"*pgxpool.Pool", pool}, {"*pgx.Conn", conn}, {"pgx.Tx", tx}} {
		r, err := New(c.db).Add(ctx, p)
		must(err)
		fmt.Printf("1. Add through %-14s -> id %d\n", c.name, r.ID)
	}
	must(tx.Commit(ctx))

	r, err := New(pool).Latest(ctx)
	must(err)
	f, _ := r.Value.Float64Value()
	fmt.Printf("2. pgtype values: Taken %v (valid %v), Value %v exp %d -> %.3f, Note %q, Tags %v\n", r.Taken.Time.UTC(), r.Taken.Valid, r.Value.Int, r.Value.Exp, f.Float64, r.Note.String, r.Tags)

	null := p
	null.Note = pgtype.Text{}
	r, err = New(pool).Add(ctx, null)
	must(err)
	fmt.Println("3. a NULL note reads back with Valid =", r.Note.Valid)

	fmt.Println("4. statements the pool traced (Add, begin, Add, commit, Latest, Add; the *pgx.Conn has its own tracer-less config):", tr.n)
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0696
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

1. Add through *pgxpool.Pool  -> id 1
1. Add through *pgx.Conn      -> id 2
1. Add through pgx.Tx         -> id 3
2. pgtype values: Taken 2026-01-02 03:04:05 +0000 UTC (valid true), Value 12345 exp -3 -> 12.345, Note "hello", Tags [a b]
3. a NULL note reads back with Valid = false
4. statements the pool traced (Add, begin, Add, commit, Latest, Add; the *pgx.Conn has its own tracer-less config): 6

Add ran through the pool, through a separate connection, and through a transaction, and returned ids 1, 2 and 3 in that order. That is the whole mechanism behind the transactions pattern: a transaction is one more thing that satisfies the interface.

Which one to hand your repository depends on the layer. A service's long lived code takes the pool, since the pool checks a connection out per call and returns it. A function that must see its own writes, or hold a lock across several calls, takes a transaction or a pinned connection (pool.Acquire). A single *pgx.Conn is for scripts, migrations and tests, since it is not safe for concurrent use and does not reconnect.

pgtype values

The reading the program stored was a time.Date as pgtype.Timestamptz, a numeric as pgtype.Numeric, a note as pgtype.Text and tags as a []string. What came back:

• The timestamp was 2026-01-02 03:04:05 +0000 UTC with Valid true. pgtype.Timestamptz holds a Time, an InfinityModifier and Valid, and Time is the field to read. If you do not need infinity, an override to time.Time removes the wrapper, as the overrides article shows.
• The numeric was pgtype.Numeric{Int: 12345, Exp: -3}, which is 12.345: the integer 12345 scaled by ten to the minus three. Float64Value() gave 12.345, and that conversion is the lossy step. Keep amounts of money in pgtype.Numeric (or a decimal override) until the last moment, and never go through float64 in the middle of a calculation.
• A NULL note read back as Valid = false, with an empty string in String. That is the reason for the wrapper: in Go an empty string and NULL are different facts, and the Valid flag keeps them apart. When you build a parameter, pgtype.Text{String: "", Valid: true} writes an empty string, and pgtype.Text{} writes NULL. Passing the zero value by accident writes NULL, which is the commonest bug here.
• Tags were a []string, with no wrapper, because a not null array of a built in type maps to a plain slice.

A cheap way to contain the wrappers is to convert at the edge of the data layer. A repository returns domain structs with plain string and time.Time fields, built from the generated rows by one small function per type, and the rest of the program never sees pgtype. The cost is the conversion code, and the gain is that a change in sqlc's type mapping touches one file.

Pitfalls

Configuration is per connection. The program set a Tracer on the pool's ConnConfig and counted statements. The pool traced six statements: the begin, two Add calls (one through the pool, one inside the transaction), the commit, Latest, and the final Add with a NULL note. The separate *pgx.Conn was opened from the DSN directly, with no tracer, so its Add was not counted. If a feature (a tracer, a type registration, a runtime parameter) matters, it must be set on every path that creates a connection. The tracing article shows OpenTelemetry on the same hook, and the connection lifecycle article covers AfterConnect for type registration.

The statement cache and poolers. pgx v5 prepares statements by default and caches them per connection. Behind a pooler in transaction mode, such as PgBouncer, a cached prepared statement can meet a different server connection and fail. The fixes (a query execution mode that does not prepare, or a pooler version with prepared statement support) are in the pgx and PgBouncer article and the statement cache article. The generated code is unaffected, since the setting lives on the pool config.

:batch* and :copyfrom do not exist on database/sql. If a project might need them, start on pgx. Migrating the driver later changes every generated type.

Closing the pool. pool.Close() waits for checked out connections to be returned. A leaked connection (a Queries.Query whose rows were never closed, in hand written code) makes Close hang. The generated methods close their rows, so leaks come from code you wrote.

Contexts are not optional. Every generated method takes a context.Context first. Passing context.Background() everywhere throws away cancellation: a client that disconnects leaves its query running. Pass the request's context, as the context cancellation article explains.

Choosing between pgx and database/sql

For a PostgreSQL only service, pgx is the better default. For code that targets several databases through the same interface, database/sql keeps one API, at the cost of the extras.

Checklist

• Set sql_package: pgx/v5, and emit_pointers_for_null_types if you prefer pointers to Valid.
• Pass the pool to long lived code and a transaction to units of work.
• Convert pgtype values at the repository boundary.
• Never construct a pgtype.Text{} for an empty string: set Valid: true.
• Configure tracing and type registration on the pool config, and open every connection from it.
• Pass request contexts to every call.

The generated interface is described in sqlc's pgx guide, and the value types in the pgtype package and pgxpool documentation.