GoMason

Building the ORM · Course 13

Executing queries with pgx

Connect, run the SQL you built, and scan rows straight into your structs.

65 min · pgx · connection pools · row scanning · interfaces · batching

Course 12 ended with a string and a list of arguments. This course sends them to PostgreSQL and turns the answer back into []User.

There is one mistake in here that produces no error, no panic, and the wrong answer. It is three characters long and almost everyone leaves it out the first time.

The first real query

Install the driver and open a pool:

go get github.com/jackc/pgx/v5
pool, err := pgxpool.New(ctx, "postgres://localhost:5432/mydb?sslmode=disable")
if err != nil {
	return err
}
defer pool.Close()

A pool, not a connection. Postgres forks a backend process per connection, so opening one per request is how you take a database down. pgxpool keeps a set of them, hands one out per query and takes it back after. It is safe for concurrent use — the only type in this ORM that is, besides course 11’s registry.

Now run the SQL from course 12:

sql, args, err := builder.Select[User](qb).Where(builder.Gt("age", 18)).ToSQL()
if err != nil {
	return err
}

rows, err := pool.Query(ctx, sql, args...)
if err != nil {
	return err
}
defer rows.Close()

for rows.Next() {
	var u User
	if err := rows.Scan(&u.ID, &u.Email, &u.Age); err != nil {
		return err
	}
	users = append(users, u)
}
return nil

Read that loop and decide whether it is correct. It compiles, it vets clean, it passes a test against a working database.

Predict: where does the error go?

Type this against a real server. The first two rows are fine and the third divides by zero:

const q = `select 10 / x from (values (1),(2),(0)) t(x)`

rows, err := conn.Query(ctx, q)
fmt.Println("Query error:", err)

var got []int
for rows.Next() {
	var n int
	rows.Scan(&n)
	got = append(got, n)
}
fmt.Println("collected:", got)
fmt.Println("rows.Err():", rows.Err())

Predict all three lines.

Query error: <nil>
collected:   [10 5]
rows.Err():  ERROR: division by zero (SQLSTATE 22012)

Query succeeded. The loop ended normally. rows.Next() returned false exactly as it does at the end of a healthy result set, and the function above would have returned [10, 5] and nil.

Two of three rows, reported as success. A truncated report, a partial invoice, a page of search results missing its tail — and nothing anywhere says so.

rows.Next() returning false means “no more rows”, not “all rows”. The only way to tell the difference is to ask:

if err := rows.Err(); err != nil {
	return nil, err
}

Those three lines go after every rows.Next() loop you will ever write. The error can arrive mid-stream because Postgres streams results — it has already sent you row one when it discovers row three is impossible.

Scanning into a struct

Hand-writing rows.Scan(&u.ID, &u.Email, &u.Age) is the boilerplate the ORM exists to delete. You have what you need to generate it: the registry (course 11) knows the column order, and reflection (course 08) can address the fields.

func scanIntoStruct[T any](rows pgx.Rows, dst *T, table *TableMetadata) error {
	v := reflect.ValueOf(dst).Elem()
	targets := make([]any, 0, len(table.Columns))
	for _, col := range table.Columns {
		field := v.FieldByName(col.GoField)
		targets = append(targets, field.Addr().Interface())
	}
	return rows.Scan(targets...)
}

field.Addr().Interface() is the piece worth pausing on: it produces a *string or *int pointing into the struct, which is exactly what Scan wants. reflect.New from course 08 gave you an addressable value; this is why that mattered.

Predict: what does FieldByName cost?

Course 08 measured FieldByName at 12× the cost of Field(i) for a single field. Predict what that becomes for a 1000-row, 10-column result:

BenchmarkScan1000RowsByName-11     364415 ns/op   0 allocs/op
BenchmarkScan1000RowsByIndex-11     38421 ns/op   0 allocs/op

9.5×. 364 microseconds of the scan spent searching for field names the registry already resolved — comparable to the entire network round trip for the same query.

The name only needs resolving once. A result’s columns cannot change halfway through it, so work out where each column goes on the first row, keep the index, and use it for every row after:

type rowScanner struct {
	table      *TableMetadata
	structType reflect.Type
	fields     []scanField
	targets    []any
}

type scanField struct {
	position int   // the column's position in the result
	index    []int // the field's reflect index path
}

func (s *rowScanner) scan(rows pgx.Rows, dest any) error {
	v := reflect.ValueOf(dest).Elem()
	if s.structType != v.Type() {
		s.plan(rows, v) // first row: FieldByName, once per column
	}
	for _, f := range s.fields {
		s.targets[f.position] = v.FieldByIndex(f.index).Addr().Interface()
	}
	return rows.Scan(s.targets...)
}

Each result loop makes one rowScanner and calls scan for every row. The targets slice is reused too, so a plain row now costs no allocations beyond the struct itself. On a 5-column table the scanner went from about 570 ns to 120 ns a row, and the cost over a hand-written rows.Scan at 1,000 rows fell from 11–42% to 3–6%. Course 08’s rule, measured on your own code: resolve the name once, then use the index.

Predict: why doesn’t the plan just use Position? The metadata already has a Position field holding field.Index[0], sitting right there.

Because the two schema paths disagree about what it means. The reflection parser stores the struct field index, and the AST loader stores a column counter. It looks right on every model registered through reflection and is wrong for the CLI’s. Asking reflect.Type for the index when the first row arrives needs neither path to change.

Now break it. Reuse is where this can go wrong, so write the test first. Scan a row with {"theme":"dark"} in a JSONB column, then a row where that column is NULL, using a single intermediate target held on the scanner.

second row: &{Theme:dark}

The second row inherited the first row’s value, because a NULL writes nothing. That is why the real scanner reuses the slice of targets but builds JSONB and array targets fresh for each row. Cache what the result fixes, never what a row owns.

One interface, two callers

Everything so far used pool.Query. Inside a transaction you have a pgx.Tx instead, and its methods have different signatures.

The wrong answer is to write every builder twice. This ORM did exactly that, and the two copies drifted until the transaction path silently lost LATERAL join support for several releases.

The right answer is course 05’s lesson. Write down the three methods you actually use:

type queryExecutor interface {
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
	Exec(ctx context.Context, sql string, args ...any) (int64, error)
}

*pgxpool.Pool satisfies that already. pgx.Tx does not quite — its Exec returns a CommandTag rather than a count — so a six-line adapter closes the gap:

type txExecutor struct{ tx pgx.Tx }

func (t txExecutor) Exec(ctx context.Context, sql string, args ...any) (int64, error) {
	tag, err := t.tx.Exec(ctx, sql, args...)
	return tag.RowsAffected(), err
}

Three methods, defined by the consumer, with a tiny adapter for the type that does not fit. Eight builders, one execution path, and no way for them to diverge again.

Transactions

tx, err := pool.Begin(ctx)
if err != nil {
	return err
}
defer tx.Rollback(ctx)

if _, err := builder.TxInsert[User](tx).Values(u).ExecReturning(); err != nil {
	return err
}

return tx.Commit(ctx)

defer tx.Rollback(ctx) goes immediately after the error check, before any work. It is course 04’s deferred cleanup doing real safety work: every early return, and any panic, now rolls back. Rolling back an already-committed transaction is a no-op error you ignore, which is what makes the pattern safe.

One thing to know that is not obvious: a transaction is one connection. Two result sets cannot be open on it at once. This ORM hit that with eager loading — the preload query ran while the main result set was still streaming, on the same connection — and the fix was to close the rows before loading relationships:

if len(preloads) > 0 && len(results) > 0 {
	rows.Close()
	// ... now load relationships
}

A defer rows.Close() is still there for every other exit. Closing twice is harmless; closing too late is a deadlock.

One round trip per depth

Preload("Profile", "Posts", "Posts.Comments") needs four queries: users, then profiles and posts, which only need the user IDs, then comments, which need the post IDs. The first two preloads don’t depend on each other, so there is no reason to wait for one before sending the other. pgx’s Batch sends several queries in one pipeline:

batch := &pgx.Batch{}
batch.Queue(profilesSQL, userIDs).Query(func(rows pgx.Rows) error {
	// scan and assign profiles
})
batch.Queue(postsSQL, userIDs).Query(func(rows pgx.Rows) error {
	// scan and assign posts
})
if err := exec.SendBatch(ctx, batch).Close(); err != nil {
	return err
}
// posts are loaded: queue comments on their IDs, send the next batch

The callbacks run inside Close, in the order the queries were queued, and Close has finished reading the batch before it returns. That is the one-connection rule from the transaction section again: the next depth can’t be sent until this one has been read, and here that happens on its own. pgx.Tx has SendBatch too, so the same code runs inside a transaction.

Measured against PostgreSQL 18, one user with two sibling preloads:

                       loopback   1 ms round trip
one query at a time      400µs        6.1ms
one batch                290µs        4.3ms

The saving is a round trip per sibling, so it grows with network distance. Queries that pgx hasn’t seen before cost a second round trip to prepare, for the whole batch at once, exactly as a single Query does.

Predict: how many posts?

Grouping by depth means deciding what each depth contains, and that is where this ORM had a bug for ten months. The loader before batching ran in two passes:

  1. Load every direct preload: "Posts".
  2. For every nested path, load its first field, then the rest on what came back. "Posts.Comments" loads Posts, then Comments on each post.

The hasMany loader gives the field an empty slice if it is nil, runs one = ANY($1) query, and appends each row to its parent.

A user has two posts. Predict len(user.Posts):

users, err := builder.Select[User](qb).
	Preload("Posts").
	Preload("Posts.Comments").
	All(ctx)
4

Pass 1 appended the two posts. Pass 2 loaded Posts again, found a slice that was not nil, and appended the same two rows a second time. Then it loaded comments onto all four, so the duplicates looked completely real. No error, no warning, every request.

It needs both names to happen. Preload("Posts.Comments") on its own was always right, and that is the only form the nested-preload test used. A real app listed both and showed every staff member on a task twice.

Two ingredients made it, and both are worth recognising elsewhere:

  • The work ran once per name, not once per thing. Two strings naming one relationship meant two loads.
  • The operation was not idempotent. A belongsTo loaded twice overwrites the same field and nobody notices. A slice loaded twice grows.

The fix removes the first ingredient. Before loading anything, merge the paths into a tree, so each field exists once however many paths mention it:

type preloadTree map[string]preloadTree

func buildPreloadTree(paths []string) preloadTree {
	root := preloadTree{}
	for _, path := range paths {
		node := root
		for _, field := range strings.Split(path, ".") {
			if node[field] == nil {
				node[field] = preloadTree{}
			}
			node = node[field]
		}
	}
	return root
}

"Posts" and "Posts.Comments" become one Posts node with Comments under it. The tree is also exactly what batching needs: its first level is the first batch, the second level the second.

One parent, many copies

The same release fixed a quieter bug, and it is course 03’s value-versus-pointer rule turning up somewhere unexpected. A post belongs to its author, held by value:

type Post struct {
	ID       int
	AuthorID int
	Author   Author `po:"-,belongsTo,foreignKey(author_id),references(id)"`
}

Two posts by the same author each get their own copy of that author. Preload("Author.Profile") then has to load a profile onto both copies. The loader collected each parent’s key into a map so it could match rows back:

byKey := make(map[interface{}]int) // key -> index of its object

Both copies have key 1, so the second write replaced the first. Only the last post’s author got a profile; the first stayed empty. The map needs every index that has the key:

byKey := make(map[interface{}][]int)

That creates the opposite problem for Author *Author. Both posts now point at one author, so the collection holds the same pointer twice, and a hasMany under it would append each row twice: the first bug again. Collecting the next depth skips a pointer it has already seen:

seen := make(map[uintptr]bool)
add := func(ptr reflect.Value) {
	if !seen[ptr.Pointer()] {
		seen[ptr.Pointer()] = true
		collected = reflect.Append(collected, ptr)
	}
}

Value copies are separate objects and each one is loaded; a shared pointer is one object and is loaded once. The test for this keeps both fields on one model, so a fix for either case that breaks the other fails immediately.

Why not database/sql?

The standard library has a database package. The usual argument for skipping it is that it cannot handle PostgreSQL’s types. Check whether that is still true — scan a text[] through database/sql using pgx’s stdlib driver:

var tags []string
err := db.QueryRow(`select '{go,sql}'::text[]`).Scan(&tags)
database/sql -> []string : [go sql]
pgx native   -> []string : [go sql]

Both work. As of pgx v5.11 on Go 1.27, database/sql scans arrays and ranges directly through the new driver.RowsColumnScanner interface. The capability argument is dead, and if you read it in an older blog post it was true when written.

What is left is cost. Same 1000-row, 4-column query, both paths:

BenchmarkPgxNativeScan-11      300530 ns/op   6004 allocs/op
BenchmarkDatabaseSQLScan-11    576841 ns/op   8664 allocs/op

1.92× slower and 44% more allocations. Every value crosses driver.Value, which has six permitted types, so a PostgreSQL value becomes one of those six and then becomes your Go type.

And some things database/sql has no vocabulary for at all: CopyFrom for bulk loading, SendBatch for pipelining, LISTEN/NOTIFY.

So the honest case for going native is “roughly twice as fast, with access to Postgres-specific features”, not “arrays do not work”. Keep database/sql if you need to swap databases, which is the one thing it genuinely buys you and the one thing this ORM has no interest in.

Build something

Give your builder from course 12 an All method:

func (q *SelectQuery[T]) All(ctx context.Context) ([]T, error)

It calls ToSQL, runs the query, scans each row into a T using the registry’s column order, and returns []T. Take a queryExecutor rather than a *pgxpool.Pool, so a transaction works without a second implementation.

Then write the test that matters. Not “does it return rows” — this one:

  • Run a query that fails on a later row (select 10 / x from (values (1),(0)) t(x) is enough) and assert All returns an error, not a short slice.

Write All without the rows.Err() check first and watch that test fail while every other test passes.

go test -race ./...
Check yourself
func (q *SelectQuery[T]) All(ctx context.Context) ([]T, error) {
	sql, args, err := q.ToSQL()
	if err != nil {
		return nil, err
	}

	rows, err := q.exec.Query(ctx, sql, args...)
	if err != nil {
		return nil, fmt.Errorf("query %q: %w", sql, err)
	}
	defer rows.Close()

	var out []T
	scanner := &rowScanner{table: q.table}
	for rows.Next() {
		var item T
		if err := scanner.scan(rows, &item); err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("query %q: %w", sql, err)
	}
	return out, nil
}

The error wraps the SQL. Course 04’s %w keeps the chain intact so a caller can still errors.Is the pgx error underneath, while the message says which statement failed. This ORM goes further and defines a QueryError carrying the SQL and the args as fields, so a caller can log the statement without the ORM deciding how — errors.As from course 04.

defer rows.Close() and the explicit rows.Err() are both required, and they do different jobs. Close releases the connection back to the pool. Err reports whether the stream ended because it finished or because it broke.

out starts nil and is never preallocated. You do not know the row count, rows will not tell you, and append handles growth. Returning a nil slice for zero rows is fine — course 02 established that len and range both work on it.

Nothing here is in ToSQL. That separation is what let courses 12 and 13 be different lessons, and what lets every test in course 12 run without a database.

Next

Your models have to compile for reflection to see them. The CLI cannot assume that — pebble generate runs on source files mid-refactor. Course 14 reads your structs with go/ast instead, and then has to prove the two readers agree.