This is the third part of the pgx v5 series. Let’s discuss the new features from the title.

CollectRows and RowToStructByName

CollectRows allow us to fetch an array of rows and set them into a slice of structs. Further, RowToStructByName is a long awaited feature which allows to store a row into a struct. sqlx module became very popular in Go community exactly for this reason, but pgx now supports something similar. RowToStructByName is a generic function of the func(row CollectableRow) (T, error) and it’s not the only one of this type, there are also RowTo, RowToStructByPos and RowToStructByNameLax. Under the hood, RowToStructByName is using reflection so it may be slightly slower than classic way of scanning the rows, but if you don’t mind this, it is very easy to use. All field names are going to be mapped to lowercased public struct field names and you may also use db struct tags if some field name differs from the struct field name. This explanation may sound difficult, but the following example will actually show that it is actually very easy to use.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

package main

import (
	"context"
	"fmt"
	"os"

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

type account struct {
	ID       string
	Username string
	Role     string
	Address  pgtype.Text
	ZipCode  pgtype.Text `db:"zip_code"`
}

func main() {
	ctx := context.Background()

	dbpool, err := pgxpool.New(ctx, "postgres://postgres@localhost:5432/test")
	if err != nil {
		exit("failed creating pgx pool", err)
	}
	defer dbpool.Close()

	rows, err := dbpool.Query(ctx, "SELECT * FROM accounts")
	if err != nil {
		exit("failed query", err)
	}

	accounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[account])
	if err != nil {
		exit("failed collecting rows", err)
	}

	for _, account := range accounts {
		fmt.Printf("%#v\n", account)
	}
}

func exit(msg string, err error) {
	fmt.Fprintf(os.Stderr, "%s: %v\n", msg, err)
	os.Exit(1)
}

As you can see in the account struct, our field name in the accounts table is zip_code and the struct field name is ZipCode. This simple struct tag, db:"zip_code" allows the correct mapping. For other struct fields we didn’t need to override the mapping hence we have no tags there. Take care that using CollectRows with many returned records may take lot of memory so you may want switch back to using cursor way of fetching the data row by row any maybe sending it to a channel, so kind of streaming.

CollectOneRow and RowToStructByPos

CollectOneRow is similar to CollectRows but as the name says, it fetches just one row meaning if your query produces multiple rows, just the first one will be scanned. Instead of using RowToStructByName function, in the next example we are using RowToStructByPos which maps the returned row fields to the public fields of the struct. Take care that the order (position) of fields must match.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

func main() {
	ctx := context.Background()

	dbpool, err := pgxpool.New(ctx, "postgres://postgres@localhost:5432/test")
	if err != nil {
		exit("failed creating pgx pool", err)
	}
	defer dbpool.Close()

	rows, err := dbpool.Query(ctx, "SELECT * FROM accounts ORDER BY id DESC LIMIT 1")
	if err != nil {
		exit("failed query", err)
	}

	account, err := pgx.CollectOneRow(rows, pgx.RowToStructByPos[account])
	if err != nil {
		exit("failed collecting rows", err)
	}

	fmt.Printf("%#v\n", account)

SQL query in this example fetches the first user account ordered by id which is not quite sensible, but you may want to use this combination with queries that fetch for example single user by username or email, something like that. It’s maybe worth mentioning that RowToStructByPos is also using reflection.

ForEachRow

This functions allows to iterate over all returned rows and execute a given function for each one. This is just a nicer way of using for rows.Next() {} loop.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

func main() {
	ctx := context.Background()

	dbpool, err := pgxpool.New(ctx, "postgres://postgres@localhost:5432/test")
	if err != nil {
		exit("failed creating pgx pool", err)
	}
	defer dbpool.Close()

	rows, err := dbpool.Query(ctx, "SELECT id, username, role, address, zip_code FROM accounts")
	if err != nil {
		exit("failed query", err)
	}

	var (
		id, username, role string
		address, zip       pgtype.Text
	)

	pgx.ForEachRow(rows, []any{&id, &username, &role, &address, &zip}, func() error {
		fmt.Printf("id: %s username: %s role: %s address: %s, zip: %s\n", id, username, role, address.String, zip.String)

		return nil
	})
}

And here we come to the end of part 3 of pgx v5 and in the next one we are going to take a look at SendBatch. Happy coding.