sqlc Dynamic Queries Without Losing Type Safety in Go

A sqlc query is a string that is fixed when you run the generator. That is why it can be type checked, and it is why "show products matching whatever the user ticked" is the question people ask after the tutorial. The honest answer is that the SQL has to carry the flexibility, and that PostgreSQL offers enough tools to do that for filters, lists and sorting. Where it does not, a query builder next to sqlc is a normal and sensible answer.

This article runs the patterns against a 200,000 row table on PostgreSQL 18.6 with sqlc v1.30.0 and pgx v5.11.0, on Go 1.25.6, and flags the one place they cost something. It follows the sqlc tutorial and pairs with the keyset pagination article, which covers the paging half of a search screen.

The table

create table products (
    id       bigserial primary key,
    name     text not null,
    category text not null,
    price    int not null,
    stock    int not null
);
create index products_category on products (category);
create index products_price on products (price);
insert into products (name, category, price, stock)
select 'product ' || i, (array['tools','toys','books','food','garden'])[1 + i % 5], 1 + (i * 37) % 500, i % 40 from generate_series(1, 200000) i;
analyze products;

Two indexes, on category and on price, give the planner something to choose between. The data is 200,000 products spread over five categories and prices from 1 to 500.

Optional filters with sqlc.narg

The pattern is a condition that is true when its argument is NULL:

-- name: SearchOptional :many
-- Each filter is optional: a NULL argument switches it off.
select id, name, category, price from products
where (sqlc.narg('category')::text is null or category = sqlc.narg('category'))
  and (sqlc.narg('max_price')::int is null or price <= sqlc.narg('max_price'))
  and (sqlc.narg('in_stock')::bool is null or (stock > 0) = sqlc.narg('in_stock'))
order by id
limit 5;

-- name: SearchFixed :many
-- The same question with the filters written out: only the ones that are used.
select id, name, category, price from products
where category = $1 and price <= $2
order by id
limit 5;

-- name: ByIDs :many
select id, name from products where id = any(@ids::bigint[]) order by id;

-- name: Sorted :many
select id, name, price from products
order by
  case when @sort_by::text = 'price' then price end asc,
  case when @sort_by::text = 'name' then name end asc,
  id
limit 3;

-- name: CountFiltered :one
select count(*) from products
where (sqlc.narg('category')::text is null or category = sqlc.narg('category'));

sqlc.narg('category') declares a nullable named argument. The ::text cast tells PostgreSQL its type, since a bare NULL has none. The generated parameter struct has pointer fields (this lab sets emit_pointers_for_null_types) so a caller sets only the filters it wants:

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5
        emit_pointers_for_null_types: true

package main

import (
	"context"
	"fmt"
	"log"
	"strings"

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

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

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func s(v string) *string { return &v }
func i(v int32) *int32   { return &v }
func b(v bool) *bool     { return &v }

// plan returns the first line of EXPLAIN for a query with the given arguments, using the generic plan a prepared statement may fall back to.
func plan(ctx context.Context, pool *pgxpool.Pool, sql string, args ...any) string {
	rows, err := pool.Query(ctx, "explain (costs off) "+sql, args...)
	must(err)
	lines, err := pgx.CollectRows(rows, pgx.RowTo[string])
	must(err)
	return strings.TrimSpace(strings.Join(lines, " | "))
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)

	// 1. The same function answers different questions depending on which arguments are set.
	for _, c := range []struct {
		label string
		p     SearchOptionalParams
	}{
		{"no filter", SearchOptionalParams{}},
		{"category only", SearchOptionalParams{Category: s("books")}},
		{"category and max price", SearchOptionalParams{Category: s("books"), MaxPrice: i(10)}},
		{"in stock only", SearchOptionalParams{InStock: b(true)}},
	} {
		rows, err := q.SearchOptional(ctx, c.p)
		must(err)
		fmt.Printf("1. %-24s -> %d rows, first id %d\n", c.label, len(rows), rows[0].ID)
	}

	// 2. A list of ids as one array argument.
	byIDs, err := q.ByIDs(ctx, []int64{5, 50, 500, 5000000})
	must(err)
	fmt.Println("2. id = any(@ids) with four ids (one does not exist) returned", len(byIDs), "rows")

	// 3. Ordering chosen by an argument.
	for _, by := range []string{"price", "name"} {
		rows, err := q.Sorted(ctx, by)
		must(err)
		fmt.Printf("3. sorted by %-5s first rows: %v\n", by, rows)
	}

	// 4. What the optional filter does to the plan.
	fmt.Println("4. query plans for category = 'books' and price <= 10:")
	fmt.Println("   written out:        ", plan(ctx, pool, "select id from products where category = 'books' and price <= 10 order by id limit 5"))
	fmt.Println("   optional pattern, constants:", plan(ctx, pool, "select id from products where ('books'::text is null or category = 'books') and (10::int is null or price <= 10) order by id limit 5"))
	var n int64
	n, err = q.CountFiltered(ctx, s("books"))
	must(err)
	fmt.Println("5. CountFiltered with a category:", n)
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0691
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

1. no filter                -> 5 rows, first id 1
1. category only            -> 5 rows, first id 2
1. category and max price   -> 5 rows, first id 257
1. in stock only            -> 5 rows, first id 1
2. id = any(@ids) with four ids (one does not exist) returned 3 rows
3. sorted by price first rows: [{500 product 500 1} {1000 product 1000 1} {1500 product 1500 1}]
3. sorted by name  first rows: [{1 product 1 38} {10 product 10 371} {100 product 100 201}]
4. query plans for category = 'books' and price <= 10:
   written out:         Limit |   ->  Index Scan using products_pkey on products |         Filter: ((price <= 10) AND (category = 'books'::text))
   optional pattern, constants: Limit |   ->  Index Scan using products_pkey on products |         Filter: ((price <= 10) AND (category = 'books'::text))
5. CountFiltered with a category: 40000

One query, four shapes. SearchOptional ran with no filter, with a category, with a category and a maximum price, and with in stock only. Each returned five rows (the query limits to five), starting at ids 1, 2, 257 and 1 respectively. The category filter moved the first id to 2, and adding a price cap moved it to 257, which is the first books product with price 10 or less. The same generated function, with the same type checked parameters, answered all four.

The convention to remember: a NULL argument means "do not filter on this". The consequence is that you cannot use NULL to mean "the column is NULL". If you need that, add a separate boolean argument.

A list of values

ByIDs takes @ids::bigint[] and compares with = any(...). The generated method takes a []int64. The call passed four ids, one of which does not exist, and got three rows. There is no placeholder string to build, no length limit from the number of parameters, and no risk of building a malformed in ( list by hand. This is the PostgreSQL answer. MySQL and SQLite use sqlc.slice, as the config article shows.

An empty slice is valid here: any('{}') is false for every row, so the query returns nothing. The explicit check you may want is "an empty list means no filter", which is the optional pattern again with cardinality(@ids) = 0 or id = any(@ids).

A sort chosen by the caller

Sorted orders by an argument:

order by
  case when @sort_by::text = 'price' then price end asc,
  case when @sort_by::text = 'name' then name end asc,
  id

Each case yields the column when its name matches and NULL otherwise, and NULLs tie, so only the chosen column orders the rows, and id breaks the remaining ties. The lab sorted by price and got ids 500, 1000, 1500, each with price 1, and by name and got product 1, product 10, product 100, which is text order, not numeric.

Two limits. A sort expression like this cannot use an index, because the expression is not the indexed column, so the planner sorts all matching rows before it applies the limit. And the direction cannot be an argument in the same way without another pair of cases per direction. For a sort that must be fast, write one query per supported sort and choose between them in Go, with a switch on a typed value. That is more code, and every query is checked and can use its own index.

Where the pattern costs something

The optional filter reads well and type checks, and it has one real cost: the planner has to produce a plan for every combination in one statement.

The lab asked PostgreSQL for the plan of the written out query (where category = 'books' and price <= 10) and of the optional pattern with the same values filled in as constants. They were identical, a scan of the primary key index with a filter, and for the five row limit that is a good plan, since the first matches turn up quickly. With constants, PostgreSQL folds 'books'::text is null to false while planning, and the optional branch disappears. That is why the plans match.

With parameters the story differs. A prepared statement (which pgx uses by default through its statement cache, covered in the statement cache article) may switch, after five executions, to a generic plan chosen without knowing the arguments. PostgreSQL's documentation says the generic plan is used when its estimated cost is not much worse than the average custom plan, and a generic plan for ($1 is null or category = $1) cannot use the category index as a lookup, because it must also be correct when $1 is NULL. The lab did not measure that effect, so treat it as the documented risk and test your own query: run explain (analyze) with plan_cache_mode = force_generic_plan set and compare it with the default.

When that matters, the options are, in order of effort:

1. Split the query: one for the filter combinations that dominate traffic (category alone, category and price), and the optional form for the long tail.
2. Set plan_cache_mode = force_custom_plan for the session, which re-plans each call with the real values. It costs planning time on every execution.
3. Build the SQL in Go for the endpoint with many optional filters, with a builder such as squirrel or goqu, as the library comparison discusses, and keep sqlc for everything else.

Counting with the same shape

CountFiltered reuses the optional category filter and returned 40,000, one fifth of the table. Counts are where an optional filter is cheapest to leave alone: they scan the same rows whichever way they are planned. Keep in mind that a count over a large table is itself slow, and a search page that shows "40,000 results" is paying for that number on every request.

A decision list

• A few optional filters, modest tables: the narg pattern is fine, and its type safety is worth more than the planning risk.
• Large tables with a dominant filter: write that case as its own query.
• A list of ids: = any(@ids), always.
• A user chosen sort on a big table: one query per sort, switch in Go.
• Many optional filters with an unpredictable mix: a query builder for that endpoint.

The named argument syntax is in sqlc's reference for named parameters, and the planner behaviour in PostgreSQL's PREPARE documentation and its note on plan_cache_mode.