pgx in Go as a Postgres Driver and Toolkit Explained

pgx is the PostgreSQL driver most Go programmers end up using, and it is more than a driver. It speaks PostgreSQL's protocol directly, has its own interface that exposes features database/sql cannot (batches, COPY, LISTEN, custom types), a connection pool, and a standard library adapter so other libraries can use it. This tour covers version 5 from the first connection to the pieces you reach for in a real service. Everything runs against PostgreSQL 18.6 on Go 1.25.6 and each point prints its result.

It belongs to the database cornerstone. Later articles go deeper on pools, collecting rows, batches, COPY, transactions and types, and the choice against the standard interface is in pgx or database/sql.

Two ways to use it

pgx offers two interfaces over the same driver. The native interface (github.com/jackc/pgx/v5 and pgxpool) is what this article uses: methods take a context.Context first, results are typed, and every PostgreSQL feature is available. The database/sql adapter (github.com/jackc/pgx/v5/stdlib) registers the driver under the name pgx so you can call sql.Open("pgx", dsn) and use tools built on the standard library, such as sqlx, GORM and migration runners. Pick native for new PostgreSQL only code and the adapter where a dependency needs *sql.DB; both can share a database.

The tour

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"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/lab?sslmode=disable"

type Product struct {
	ID    int64
	Name  string
	Price pgtype.Numeric
	Tags  []string
	Added time.Time
}

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

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()

	// 1. Exec, with the command tag it returns.
	_, err = pool.Exec(ctx, "drop table if exists products664")
	must(err)
	_, err = pool.Exec(ctx, "create table products664 (id bigserial primary key, name text not null, price numeric(10,2) not null, tags text[] not null default '{}', added timestamptz not null default now())")
	must(err)
	tag, err := pool.Exec(ctx, "insert into products664 (name, price, tags) values ($1, $2, $3), ($4, $5, $6)", "keyboard", "49.90", []string{"usb", "input"}, "mouse", "19.50", []string{"usb"})
	must(err)
	fmt.Printf("1. Exec: %q, rows affected %d, insert %v\n", tag.String(), tag.RowsAffected(), tag.Insert())

	// 2. QueryRow and Scan, with Go types for the database types.
	var name string
	var price pgtype.Numeric
	var tags []string
	must(pool.QueryRow(ctx, "select name, price, tags from products664 where name = $1", "keyboard").Scan(&name, &price, &tags))
	f, _ := price.Float64Value()
	fmt.Printf("2. QueryRow: %s costs %.2f and has tags %v\n", name, f.Float64, tags)

	// 3. No rows is pgx.ErrNoRows, and it is the same error database/sql calls sql.ErrNoRows.
	err = pool.QueryRow(ctx, "select name from products664 where id = 999").Scan(&name)
	fmt.Println("3. missing row:", err, "| errors.Is pgx.ErrNoRows:", errors.Is(err, pgx.ErrNoRows))

	// 4. Query and collect into structs by column name.
	rows, err := pool.Query(ctx, "select id, name, price, tags, added from products664 order by id")
	must(err)
	products, err := pgx.CollectRows(rows, pgx.RowToStructByName[Product])
	must(err)
	fmt.Printf("4. CollectRows: %d products, first is %q with %d tags\n", len(products), products[0].Name, len(products[0].Tags))

	// 5. A transaction with BeginFunc: commit on nil, rollback on error.
	err = pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error {
		if _, err := tx.Exec(ctx, "update products664 set price = price + 1"); err != nil {
			return err
		}
		return errors.New("changed my mind")
	})
	var total pgtype.Numeric
	must(pool.QueryRow(ctx, "select sum(price) from products664").Scan(&total))
	tf, _ := total.Float64Value()
	fmt.Printf("5. BeginFunc returned %q; the sum of prices is still %.2f\n", err, tf.Float64)

	// 6. A batch: several statements, one round trip.
	batch := &pgx.Batch{}
	batch.Queue("insert into products664 (name, price) values ($1, $2)", "cable", "5.00")
	batch.Queue("select count(*) from products664")
	br := pool.SendBatch(ctx, batch)
	_, err = br.Exec()
	must(err)
	var n int
	must(br.QueryRow().Scan(&n))
	must(br.Close())
	fmt.Println("6. SendBatch: two statements in one round trip, the count read back is", n)

	// 7. CopyFrom for bulk loads.
	copied, err := pool.CopyFrom(ctx, pgx.Identifier{"products664"}, []string{"name", "price"}, pgx.CopyFromRows([][]any{{"a", "1.00"}, {"b", "2.00"}, {"c", "3.00"}}))
	must(err)
	fmt.Println("7. CopyFrom loaded", copied, "rows")

	// 8. The pool, and what it says about itself.
	s := pool.Stat()
	fmt.Printf("8. pool: max %d, acquired %d, idle %d, total acquires %d\n", s.MaxConns(), s.AcquiredConns(), s.IdleConns(), s.AcquireCount())

	// 9. A single connection, for session state and LISTEN.
	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("9. Acquire: a pooled connection is a *pgx.Conn underneath; backend process id found:", pid > 0)
}

1. Exec: "INSERT 0 2", rows affected 2, insert true
2. QueryRow: keyboard costs 49.90 and has tags [usb input]
3. missing row: no rows in result set | errors.Is pgx.ErrNoRows: true
4. CollectRows: 2 products, first is "keyboard" with 2 tags
5. BeginFunc returned "changed my mind"; the sum of prices is still 69.40
6. SendBatch: two statements in one round trip, the count read back is 3
7. CopyFrom loaded 3 rows
8. pool: max 10, acquired 0, idle 1, total acquires 10
9. Acquire: a pooled connection is a *pgx.Conn underneath; backend process id found: true

Pool and Exec

pgxpool.New(ctx, dsn) creates a pool and verifies it can connect. A *pgxpool.Pool is safe for concurrent use; create one for the life of the program and close it at shutdown. Statements that return no rows use Exec, which returns a command tag: the first block printed "INSERT 0 2", so tag.RowsAffected() was 2 and tag.Insert() true. Parameters use PostgreSQL's $1, $2 placeholders, sent separately from the statement, so there is no injection through them and the server can cache plans. The two rows were inserted with one statement and six arguments, including a Go []string that pgx sent as a PostgreSQL text[].

One row

QueryRow(...).Scan(...) reads a single row. The second block scanned a name, a pgtype.Numeric and a []string from one row. Arrays map to Go slices directly. numeric maps to pgtype.Numeric by default, which keeps full precision and has methods to convert (Float64Value); to scan it into a float64 or a decimal library's type, register the type or scan into a string. When no row matches, the error is pgx.ErrNoRows, and errors.Is(err, pgx.ErrNoRows) was true, as the third line shows. That value is also what database/sql reports as sql.ErrNoRows, via the adapter.

Many rows

Query returns pgx.Rows, which you must close, and the helpers in the next article remove most of that work. The fourth block used pgx.CollectRows(rows, pgx.RowToStructByName[Product]): it read all rows, closed the rows, matched columns to struct fields by name and returned a []Product. The struct has an int64, a string, a pgtype.Numeric, a []string and a time.Time, each filled from the matching column, and no scanning code was written. If you need to stream a large result without building a slice, loop with rows.Next() and rows.Scan, and defer rows.Close().

Transactions

pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error { ... }) begins a transaction, runs the function, commits if it returns nil and rolls back if it returns an error or panics. In the fifth block the function updated every price and then returned an error; the helper rolled back and returned the error, and the sum of prices afterwards was still 69.40, the original total. Prefer it over hand written begin, defer rollback and commit, since the helper cannot forget the rollback on an error path. Savepoints, isolation levels and retry loops are in pgx transactions.

Batches

A pgx.Batch queues several statements and sends them in one round trip. The sixth block queued an insert and a count, sent them with pool.SendBatch, read the first result with br.Exec() and the second with br.QueryRow(), and closed the batch results; the count read back was 3. Results must be read in the order the statements were queued, and Close must be called. On a database a millisecond away, a hundred statements cost a hundred round trips one at a time and one as a batch; see batches and named arguments.

COPY

CopyFrom loads rows through PostgreSQL's binary COPY protocol, the fastest way in. The seventh block loaded three rows from a slice of []any. For large loads feed it from a function so rows stream and never sit in a slice; bulk loading measured it against other approaches and pgx COPY covers the details and its limits.

The pool and a single connection

pool.Stat() reports what the pool is doing: the maximum size (10 here), connections in use, idle connections and the total number of acquires. The eighth block printed max 10, acquired 0, idle 1, total acquires 10 after nine earlier operations: queries borrow a connection and return it, so none was held and one sat idle. When you need one connection for several statements that share session state, such as LISTEN, advisory locks or a temp table, call pool.Acquire(ctx), which returns a *pgxpool.Conn, and defer conn.Release(). The ninth block did it and asked the server for its backend process id. Forgetting Release is the pgx version of a connection leak, covered in pgx mistakes that leak connections.

Types

pgx maps PostgreSQL types to Go types through a registry, pgtype.Map. Most mappings work without configuration: integers, floats, text, booleans, timestamptz to time.Time, uuid to [16]byte or pgtype.UUID, jsonb to []byte, a struct or any, and arrays to slices. Two habits help. Use pointer fields (*string) or pgtype wrappers (pgtype.Text) for nullable columns: a plain string cannot hold NULL and the scan returns an error. And scan numeric into a type that fits your needs, since the default preserves precision at the cost of convenience. Enums, composite types and ranges need a little registration, described in custom types and codecs.

Configuring the connection

pgxpool.ParseConfig(dsn) returns a configuration you can adjust before creating the pool: MaxConns, MinConns, MaxConnLifetime, MaxConnIdleTime, HealthCheckPeriod, and hooks such as AfterConnect to set session parameters or register types, BeforeAcquire to check a connection and AfterRelease to reset it. Settings can also go in the connection string with a pool_ prefix. The meaning and effect of each is in pgxpool settings and the hooks in connection lifecycle. Add a tracer for logging and metrics through ConnConfig.Tracer, as in tracing.

Errors

Server errors arrive as *pgconn.PgError with a SQLSTATE code, a constraint name and more, found with errors.As; handling pgx errors lists the ones you will handle. context cancellation ends calls promptly, and a cancelled connection is closed and replaced, as context cancellation describes.

A service layout that works

A pgx based service tends to settle into the same shape. A small db package owns the pool: a function Connect(ctx, dsn) that parses the configuration, sets the pool limits and hooks, creates the pool, pings it and returns it, and a Close. Everything else receives the *pgxpool.Pool, or better a narrow interface of the methods it uses (Exec, Query, QueryRow), which both *pgxpool.Pool and pgx.Tx satisfy, so the same function can run inside or outside a transaction. That is the DBTX idea from unit of work, and with pgx the interface already exists in the standard shape of its methods.

Repositories hold SQL as string constants next to the functions that use them, take a context.Context first, and return domain types and your own errors, as in handling database errors. Migrations run from a separate command before the service starts. Tests use a real PostgreSQL, with a database per test as in testing migrations in CI, because the behaviours that matter, constraints, types and transactions, are the server's.

NULL, defaults and zero values

NULL is where a first pgx program usually fails. Scanning a NULL into a plain string or int returns an error, since Go has no way to represent it in those types. Three options exist, and each suits a different case. A pointer (*string) is the lightest: nil is NULL, and JSON encoding follows. The pgtype wrappers (pgtype.Text, pgtype.Int8, pgtype.Timestamptz) carry a Valid flag and keep the three states of a value (set, NULL, unknown) explicit, which is useful when you build update statements. sql.NullString and friends also work, since pgx implements database/sql's interfaces. For writing, pass nil or an invalid wrapper to send NULL.

Defaults are the other trap. A Go zero value is a real value to the database: an int of 0 is stored as 0, not as "use the column default". If a column has a default and the field was not set, use a pointer, send nothing for that column (leave it out of the insert), or use default in the SQL. This is the same reason ORMs have the zero value rules discussed in ORM or raw SQL.

Context, timeouts and cancellation

Every pgx call takes a context, and the deadline is enforced by the driver: when it expires the call returns and, because the connection's state is then unknown, pgx closes that connection and the pool opens a new one, as measured in driver cancellation. Put a deadline on every request, from the incoming request context, and set statement_timeout on the server as a second line. Do not pass context.Background() from a handler: a client that has gone away should cancel its queries.

From pgx v4 to v5

Many examples online use v4, with import paths like github.com/jackc/pgx/v4. Version 5 changed several things that break old code, and it is worth knowing them when you read an older tutorial. The pool lives in pgxpool as before but is created with pgxpool.New, and the connection configuration is parsed with ParseConfig. pgtype was rewritten around a Map of codecs, so the old pgtype.Text style structs became simpler wrappers, and custom types are registered differently. Row collection helpers (CollectRows, RowToStructByName) are new and replace a great deal of scanning code. Logging moved from a Logger interface to tracelog and the QueryTracer hook, described in tracing and logging. And errors from QueryRow(...).Scan now use pgx.ErrNoRows with a message no rows in result set. Check the import path in any example before you copy it, and pin the version in go.mod.

Mistakes worth avoiding

• Creating a pool per request. A pool owns connections and a background health check. Create it once.
• Forgetting to close rows or release connections. Use CollectRows, defer rows.Close() and defer conn.Release().
• Reusing a connection across goroutines. A *pgx.Conn is not safe for concurrent use; the pool is. Acquire a connection per goroutine, or use the pool's methods directly.
• Scanning NULL into a non-nullable type, covered above.
• Using a pooled Acquire for LISTEN and returning it. A listening connection must be dedicated, or notifications go to whoever borrows it next.
• Putting values in the SQL string. Use placeholders; see SQL injection.
• Ignoring the error from rows.Err() after a manual loop; CollectRows returns it for you.

A short checklist before shipping

Before a pgx service goes to production, check seven things. The pool has an explicit MaxConns that fits the database's max_connections across all instances. Every call has a context with a deadline. Nullable columns use pointers or pgtype wrappers. Transactions use BeginFunc or a deferred rollback. Server errors are classified by SQLSTATE, never by message text. A tracer or logger records slow statements without logging argument values. And the connection string sets sslmode=verify-full, as in connection strings and SSL modes. Each item is a few lines of code and removes a class of incident.

When to choose pgx

Choose it for a PostgreSQL service, for the performance of the binary protocol, the pool, and for features that matter in practice: COPY, batches, LISTEN, logical replication, precise types. Choose database/sql with the pgx adapter when you must keep portability or use libraries that need it. The tour above ran against the package documentation at pkg.go.dev/github.com/jackc/pgx/v5, and the project is at github.com/jackc/pgx, where the author documents each feature with examples.