What's new in pgx v5: QueryRewriter and NamedArgs

In this second part of the pgx v5 series, we are going to discuss the QueryRewriter interface and NamedArgs. Let’s first check out the signature of the Exec method of the pgxpool.Pool struct.

1
2
3
4
5
// Exec acquires a connection from the Pool and executes the given SQL.
// SQL can be either a prepared statement name or an SQL string.
// Arguments should be referenced positionally from the SQL string as $1, $2, etc.
// The acquired connection is returned to the pool when the Exec function returns.
func (p *Pool) Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)

According to this, we can execute some SQL query by providing the context, SQL query string and arguments to the Exec. But is this the only option? Let’s look at this example:

 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
49
50
51
52
package main

import (
	"context"
	"fmt"
	"os"

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

type account struct {
	ID       string
	Username string
	Role     string
	Address  pgtype.Text
	ZipCode  pgtype.Text
}

func (a *account) RewriteQuery(ctx context.Context, conn *pgx.Conn, sql string, args []any) (newSQL string, newArgs []any, err error) {
	return `INSERT INTO accounts (id, username, role, address, zip_code) VALUES ($1, $2, $3, $4, $5) ON CONFLICT DO NOTHING`,
		[]any{a.ID, a.Username, a.Role, a.Address, a.ZipCode}, nil
}

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()

	user := &account{
		ID:       xid.New().String(),
		Username: "simpson",
		Role:     "user",
		Address:  pgtype.Text{String: "742 Evergreen Terrace", Valid: true},
		ZipCode:  pgtype.Text{String: "80085", Valid: true},
	}

	if _, err := dbpool.Exec(ctx, "", user); err != nil {
		exit("failed insert", err)
	}
}

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

QueryRewriter

Here we can see that we called Exec with empty SQL query string. How is this possible? Well, pgx v5 contains an internal magic which checks if an object implements QueryRewriter interface and in such case calls RewriteQuery method on the object. In the example above we really do not rewrite the query but we just generate it. However, if we provided a non empty SQL query string, we could also use this method to rewrite it for whatever reason. Here we could use some complex logic, but in this simple example we have just achieved kind of encapsulation by having insert query inside the object we want to insert into the database.

NamedArgs

If you have used PostgreSQL previously, you very likely know that is supports parameter placeholders like $1, $2 and so on. This is very useful for prepared statements but also for security reasons. Let’s look at this example:

1
INSERT INTO accounts (id, username, role, address, zip_code) VALUES ($1, $2, $3, $4, $5)

By using placeholders for arguments we can achieve significant performance benefits since we can prepare this query just once and then execute it multiple times using different arguments. pgx prepares statements for us automatically but this is efficient just if we use placeholders. Like said above, the other benefit is that PostgreSQL will also do sanity checks of our arguments so we don’t have to check the user input manually but instead let the PostgreSQL do it of us. Yes, this prevents the SQL injection as well.

Over the top of this, pgx v5 introduces the named arguments or named placeholder. This way we can use names of parameters instead of variables like $1, $2, etc. Here is an example:

 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
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()

	user := account{
		ID:       xid.New().String(),
		Username: "simpson",
		Role:     "user",
		Address:  pgtype.Text{String: "742 Evergreen Terrace", Valid: true},
		ZipCode:  pgtype.Text{String: "80085", Valid: true},
	}

	if _, err := dbpool.Exec(ctx,
		"INSERT INTO accounts (id, username, role, address, zip_code) VALUES (@id, @username, @role, @address, @zip)",
		pgx.NamedArgs{
			"id":       user.ID,
			"username": user.Username,
			"role":     user.Role,
			"address":  user.Address,
			"zip":      user.ZipCode,
		}); err != nil {
		exit("failed insert", err)
	}
}

As you can see, the syntax is @name and then instead of passing the arguments we pass the pgx.NamedArgs map with all fields values.

Here we come to the end of this part and in the next one we are going to discuss about CollectRows, RowToStructByName, CollectOneRow, RowToStructByPos and ForEachRow. Stay tuned.

QueryRewriter#

NamedArgs#

QueryRewriter

NamedArgs