Use QueryRewriter for pgx/v5 querier interface.

[coady]

What do you want to change?

The Querier interface currently looks like:

func (q *Queries) Example(ctx context.Context, arg ExampleParams) error {
	_, err := q.db.Exec(ctx, example, arg.Field1, arg.Field2)

When using pgx/v5, the generated code could instead use its QueryRewriter interface.

func (p ExampleParams) RewriteQuery(_ context.Context, _ *pgx.Conn, sql string, args []any) (string, []any, error) {
	newArgs := []any{p.Field1, p.Field2}
	return sql, newArgs, nil
}

func (q *Queries) Example(ctx context.Context, arg ExampleParams) error {
	_, err := q.db.Exec(ctx, example, arg)

That would enable compatibility with other libraries which support QueryRewriter, such as pgxmock. Params could then be used as its mock args.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[StevenACoffman]

If sqlc were to support generating QueryRewriter compatible interfaces (using an opt-in config variable to preserve backwards compatibility) it would open up more possibilities besides just support for pgxmock.

Per PGX jackc/pgx#1186

A long-standing request is to support named arguments. e.g.

conn.Exec(ctx,
  `insert into users (id, email, ...) values (:id, :email, ....);`,
  map[string]any{"id": 42, "email": "foo@example.com", ...},
)

// Instead of

conn.Exec(ctx,
  `insert into users (id, email, ...) values ($1, $2, ...);`,
  42, "foo@example.com", ...,
)

The actual PostgreSQL $1 style placeholders are not bad when there are only a few. But it rapidly gets difficult to manage when there are a lot of them especially when certain arguments are used multiple times.

I propose adding QueryRewriter interface defined as something like:

type QueryRewriter struct {
  RewriteQuery(ctx context.Context, conn *pgx.Conn, sql string, args ...any) (newSQL, string, newArgs []any)
}

If a QueryRewriter was the first argument to a query its RewriteQuery method would be called and the query would run on the results.

This would provide a means to add a type NamedArgs map[string]any that implements named arguments via QueryRewriter. In this way named arguments could be added fairly seamlessly.

In addition, QueryRewriter might be useful for several other utilities. e.g. an InsertArgs that is used to create the column list and the values clause of an insert statement. Not sure if anything past NamedArgs would be useful or a good idea but the flexibility of this approach would enable all sorts of experiments like that without needing to put them in pgx.