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:
- Load every direct preload:
"Posts". - For every nested path, load its first field, then the rest on what came
back.
"Posts.Comments"loadsPosts, thenCommentson 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 assertAllreturns 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.