Scan Rows Into Structs in Go the Safe Way With Examples

Reading a row into a struct is the most repeated code in a database program. The standard way is a Scan call with one pointer per column in the right order. It works, and it fails in a way that is hard to notice: add a column to the query and forget to update the scan, or swap two columns of the same type, and values land in the wrong fields with no error. This article compares four ways of scanning on PostgreSQL, using the strictness of each as the deciding factor, on Go 1.25.6 and pgx v5.

The four ways

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/jmoiron/sqlx"
)

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

type User struct {
	ID    int    `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	_, _ = pool.Exec(ctx, `drop table if exists u593; create table u593 (id int primary key, name text, email text, extra text);
		insert into u593 values (1, 'ada', '[email protected]', 'x'), (2, 'grace', '[email protected]', 'y')`)

	// 1. By hand: explicit and verbose.
	db, _ := sql.Open("pgx", dsn)
	defer db.Close()
	rows, _ := db.QueryContext(ctx, `select id, name, email from u593 order by id`)
	var manual []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
			log.Fatal(err)
		}
		manual = append(manual, u)
	}
	rows.Close()
	fmt.Println("by hand:           ", manual)

	// 2. pgx.CollectRows with RowToStructByName: matches columns to fields by name.
	r, _ := pool.Query(ctx, `select id, name, email from u593 order by id`)
	collected, err := pgx.CollectRows(r, pgx.RowToStructByName[User])
	fmt.Println("CollectRows:       ", collected, err)

	// 3. The strictness: an extra column is an error with ByName, ignored with ByNameLax.
	r, _ = pool.Query(ctx, `select id, name, email, extra from u593 order by id`)
	_, err = pgx.CollectRows(r, pgx.RowToStructByName[User])
	fmt.Printf("extra column, strict: %.70s\n", fmt.Sprint(err))
	// ByNameLax is the other direction: struct fields without a column are left at their zero value.
	type UserPlus struct {
		ID      int    `db:"id"`
		Name    string `db:"name"`
		Email   string `db:"email"`
		Missing string `db:"nickname"`
	}
	r, _ = pool.Query(ctx, `select id, name, email from u593 order by id`)
	lax, err := pgx.CollectRows(r, pgx.RowToStructByNameLax[UserPlus])
	fmt.Println("struct field with no column, lax:", lax, err)
	r, _ = pool.Query(ctx, `select id, name, email from u593 order by id`)
	_, err = pgx.CollectRows(r, pgx.RowToStructByName[UserPlus])
	fmt.Printf("struct field with no column, strict: %.60s\n", fmt.Sprint(err))

	// 4. sqlx over database/sql.
	x := sqlx.NewDb(db, "pgx")
	var viaSqlx []User
	err = x.SelectContext(ctx, &viaSqlx, `select id, name, email from u593 order by id`)
	fmt.Println("sqlx.Select:       ", viaSqlx, err)

	// 5. A single row.
	r, _ = pool.Query(ctx, `select id, name, email from u593 where id = 2`)
	one, err := pgx.CollectOneRow(r, pgx.RowToStructByName[User])
	fmt.Println("CollectOneRow:     ", one, err)
	r, _ = pool.Query(ctx, `select id, name, email from u593 where id = 99`)
	_, err = pgx.CollectOneRow(r, pgx.RowToStructByName[User])
	fmt.Println("no row:            ", err)
	_, _ = pool.Exec(ctx, `drop table u593`)
}

go run . 2>&1

by hand:            [{1 ada [email protected]} {2 grace [email protected]}]
CollectRows:        [{1 ada [email protected]} {2 grace [email protected]}] <nil>
extra column, strict: struct doesn't have corresponding row field extra
struct field with no column, lax: [{1 ada [email protected] } {2 grace [email protected] }] <nil>
struct field with no column, strict: cannot find field nickname in returned row
sqlx.Select:        [{1 ada [email protected]} {2 grace [email protected]}] <nil>
CollectOneRow:      {2 grace [email protected]} <nil>
no row:             no rows in result set

The lab loads a table u593 with four columns (id, name, email, extra) and two rows, and scans them into a User struct that has ID, Name and Email.

1. By hand

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

Explicit and dependency-free. Cost: you repeat the column list in two places, the query and the Scan, and nothing checks that they agree except the count of columns. It is also the baseline against which the other three should be judged. The output line by hand: [{1 ada ...} {2 grace ...}] is the reference result.

2. pgx.CollectRows with RowToStructByName

rows, _ := pool.Query(ctx, `select id, name, email from u593 order by id`)
users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])

CollectRows consumes the rows, closes them and returns a slice. RowToStructByName[T] is a row function that maps each column to the struct field with the same name, using the db tag or the field name case-insensitively. The result is identical: CollectRows: [{1 ada ...} {2 grace ...}] <nil>.

Because the mapping is by name, the order of columns in the query does not matter, and adding a column does not shift anything. And it is strict:

• A column that the struct lacks is an error. Selecting extra too produced struct doesn't have corresponding row field extra. The mistake shows up at the first run, naming the column, not as a corrupted field.
• A struct field with no matching column is an error in strict mode. The lab's UserPlus has a Missing field tagged nickname, and the query returns no such column: cannot find field nickname in returned row.

3. RowToStructByNameLax

The lax variant relaxes the second rule: struct fields without a column are left at their zero value. The lab shows it succeeding on UserPlus with Missing empty. It does not relax the first rule: a column without a field is still an error. Lax is for structs that serve several queries returning different subsets of columns. Use it sparingly, since it also hides a typo in a field's tag: the field silently stays empty.

4. sqlx.Select

On database/sql, sqlx provides the same service: x.SelectContext(ctx, &out, query) fills the slice, closing the rows and checking the error. The lab wraps the same database with sqlx.NewDb(db, "pgx") and gets the same two users. Its strictness is similar: a column with no destination is an error (missing destination name). See the sqlx article.

One row

For a single row, pgx.CollectOneRow(rows, pgx.RowToStructByName[User]) returns the struct, or pgx.ErrNoRows if there is none. The lab prints {2 grace [email protected]} <nil> for an existing id and no rows in result set for id 99. pgx.ErrNoRows wraps the standard sql.ErrNoRows in recent versions, so errors.Is(err, sql.ErrNoRows) and errors.Is(err, pgx.ErrNoRows) both work. CollectExactlyOneRow also fails when there are two or more rows, which is what you want for a lookup by a supposedly unique key.

Other row functions cover other shapes: pgx.RowTo[int] for a single column of a scalar type, pgx.RowToMap for a map[string]any, pgx.RowToStructByPos for positional mapping, and pgx.RowToAddrOf[T] for a slice of pointers.

Why name-based wins

Positional scanning has one silent failure mode that name-based mapping removes. Suppose the query is select id, name, email from users and someone adds , phone to the middle: select id, phone, name, email. The Scan has four columns and three pointers: error. But if they swap name and email in the select list, both strings, the scan succeeds and the data is wrong in production until someone notices. By name, the swap is harmless.

The price of the strictness is some friction when queries return more than the struct holds. That friction is the feature. Select the columns you need, and give the struct exactly those.

Tags and naming

pgx matches the db tag first, and then the field name compared without regard to case and underscores. Writing the tag explicitly is clearer, for instance a field ID of type int with the tag db:"id". Use db:"-" to skip a field entirely. Embedded structs are flattened, so shared columns can live in a base struct. Fields that the database returns as NULL need a pointer or a pgtype type; see scanning NULL. For a join with columns from two tables, alias the columns so the names do not collide (o.id as order_id), and see scanning joins for nesting.

Which to use

Memory and streaming

CollectRows and Select build the whole slice. That is correct for pages of results and wrong for exports. For a big result set, iterate: for rows.Next() { ... } with a scan into a reused struct, handle each row, and keep memory flat; pgx.ForEachRow is a helper that takes a callback. Pair this with a query timeout and keyset pagination for very large tables.

Testing the mapping

A cheap and valuable test per query function: run it against a real database with a row in it and assert the struct. It catches tag typos, renamed columns and type changes. The container approach is in PostgreSQL tests, and the rollback technique in transaction rollback tests keeps it fast. The functions are documented in the pgx reference, and the standard-library side of the same problem is in the database/sql Rows reference.

A last practical point is about the row type itself. A struct used for scanning is a description of a query result, not a domain object, so it is fine to have several for one table: a narrow UserSummary for listings, a wide UserDetail for the profile page. Each query is paired with the struct that matches its columns exactly, and the strict name check keeps the pair honest. That is usually better than one struct with every column and half of them empty on any given path.