sqlc Limitations and Workarounds You Should Know First

sqlc has a good reputation, and it deserves one: it turns SQL you wrote into Go you would have written. But the guarantee is narrower than people assume. sqlc checks that a query is valid against your schema and infers types. It does not check that the query does what you meant, that PostgreSQL will accept the statement the way pgx sends it, or that a type it could not work out is the type you wanted. This article runs sixteen small queries through sqlc v1.30.0, prints what it generated for each, and then runs the doubtful ones against PostgreSQL 18.6. The failures are instructive because they are quiet.

It is the counterpart to the sqlc tutorial and sits with vet and diff (which catch some of these), dynamic queries (the big workaround) and joins and sqlc.embed (another source of surprises).

The method

For each case the script writes a one query file, runs sqlc generate against a two table schema and prints either the generated method signature or the first error:

create table users (
  id    bigserial primary key,
  email text not null,
  name  text not null,
  meta  jsonb not null default '{}'
);
create table orders (
  id          bigserial primary key,
  user_id     bigint not null references users(id),
  total_cents int not null,
  status      text not null
);

try() { # name, sql
  mkdir -p case && rm -f case/query.sql && rm -rf case/db && cp schema.sql case/
  printf '%s\n' "$2" > case/query.sql
  cat > case/sqlc.yaml <<Y
version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: db
        out: db
        sql_package: pgx/v5
Y
  out=$(cd case && ~/go/bin/sqlc generate 2>&1)
  if [ -n "$out" ]; then echo "$1: ERROR $(echo "$out" | head -2 | tr '\n' ' ')"; else echo "$1: ok: $(grep -hE '^func \(q \*Queries\)' case/db/query.sql.go | sed -E 's/^func \(q \*Queries\) //; s/ \{$//' | head -3 | tr '\n' ';')"; fi
}
try "order by a parameter" '-- name: Ordered :many
select id, email from users order by $1::text;'
try "sort with case" "-- name: Sorted :many
select id, email from users
order by case when sqlc.arg('by')::text = 'email' then email end asc, id;"
try "variable length in" '-- name: ByIDs :many
select id, email from users where id = any($1::bigint[]);'
try "two tables same column names" '-- name: Joined :many
select * from users u join orders o on o.user_id = u.id;'
try "jsonb function result" "-- name: Doc :one
select jsonb_build_object('id', id, 'email', email) as doc from users where id = \$1;"
try "two statements in one query" '-- name: Two :exec
delete from orders where id = $1; delete from users where id = $1;'
try "duplicate query name" '-- name: Same :one
select id from users limit 1;
-- name: Same :one
select id from orders limit 1;'
try "extension function with a cast" '-- name: SimilarCast :many
select id from users where similarity(email, $1::text) > 0.3;'
try "extension function" '-- name: Similar :many
select id from users where similarity(email, $1) > 0.3;'
try "ambiguous column" '-- name: Amb :many
select id from users u join orders o on o.user_id = u.id;'
try "delete in a cte" '-- name: Gone :one
with d as (delete from orders where id = $1 returning id) select id from d;'
try "window function" '-- name: Ranked :many
select id, rank() over (order by total_cents desc) as r from orders;'
try "recursive cte" '-- name: Tree :many
with recursive n(i) as (select 1 union all select i + 1 from n where i < 5) select i from n;'
try "unnest with ordinality" '-- name: Un :many
select * from unnest($1::int[]) with ordinality as t(v, pos);'
try "array_agg with a cast" '-- name: AggTyped :one
select array_agg(id)::bigint[] as ids from orders where user_id = $1;'
try "array_agg result" '-- name: Agg :one
select array_agg(id) as ids from orders where user_id = $1;'

echo "== the columns sqlc named for select * over a join"
mkdir -p case && printf '%s\n' '-- name: Joined :many
select * from users u join orders o on o.user_id = u.id;' > case/query.sql
(cd case && ~/go/bin/sqlc generate)
awk '/^type JoinedRow struct/,/^}/' case/db/query.sql.go | grep -E '^\s+[A-Z]' | awk '{print "  " $1}' | tr '\n' ' '
echo
rm -rf case

package main

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

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

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, "postgres://postgres:[email protected]:55432/lab?sslmode=disable&search_path=l0704")
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	admin, _ := pgxpool.New(ctx, "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	defer admin.Close()
	admin.Exec(ctx, "drop schema if exists l0704 cascade")
	admin.Exec(ctx, "create schema l0704")
	pool.Exec(ctx, `create table users (id bigserial primary key, email text not null, name text not null);
		insert into users (email, name) values ('[email protected]', 'C'), ('[email protected]', 'A'), ('[email protected]', 'B');`)

	// 1. ORDER BY $1 compiles in sqlc and sorts by a constant.
	for _, by := range []string{"email", "id"} {
		rows, err := pool.Query(ctx, "select email from users order by $1::text", by)
		if err != nil {
			log.Fatal(err)
		}
		var got []string
		for rows.Next() {
			var e string
			rows.Scan(&e)
			got = append(got, e)
		}
		fmt.Printf("order by $1::text with %q: %v\n", by, got)
	}

	// 2. The CASE form does sort, but the direction and the column set are fixed in the SQL.
	rows, _ := pool.Query(ctx, "select email from users order by case when $1::text = 'email' then email end asc, id", "email")
	var sorted []string
	for rows.Next() {
		var e string
		rows.Scan(&e)
		sorted = append(sorted, e)
	}
	fmt.Println("order by case when $1 = 'email':", sorted)

	// 3. Two statements: accepted by sqlc, refused by PostgreSQL's extended protocol.
	_, err = pool.Exec(ctx, "delete from users where id = $1; delete from users where id = $1", int64(1))
	fmt.Println("two statements with a parameter:", strings.SplitN(fmt.Sprint(err), " (", 2)[0])

	// 4. A function sqlc does not know: the parameter became interface{}, and the cast fixes the type.
	_, err = pool.Exec(ctx, "select similarity(email, $1) from users", "ali")
	fmt.Println("similarity() without pg_trgm:", strings.SplitN(fmt.Sprint(err), " (", 2)[0])
}

set -e
bash try.sh
go mod tidy > /dev/null 2>&1
echo "== what PostgreSQL does with the same SQL"
go run .

order by a parameter: ok: Ordered(ctx context.Context, dollar_1 string) ([]OrderedRow, error);
sort with case: ok: Sorted(ctx context.Context, by string) ([]SortedRow, error);
variable length in: ok: ByIDs(ctx context.Context, dollar_1 []int64) ([]ByIDsRow, error);
two tables same column names: ok: Joined(ctx context.Context) ([]JoinedRow, error);
jsonb function result: ok: Doc(ctx context.Context, id int64) ([]byte, error);
two statements in one query: ok: Two(ctx context.Context, id int64) error;
duplicate query name: ERROR # package db query.sql:4:1: duplicate query name: Same 
extension function with a cast: ok: SimilarCast(ctx context.Context, dollar_1 string) ([]int64, error);
extension function: ok: Similar(ctx context.Context, similarity interface{}) ([]int64, error);
ambiguous column: ERROR # package db query.sql:2:8: column reference "id" is ambiguous 
delete in a cte: ok: Gone(ctx context.Context, id int64) (int64, error);
window function: ok: Ranked(ctx context.Context) ([]RankedRow, error);
recursive cte: ok: Tree(ctx context.Context) ([]int32, error);
unnest with ordinality: ok: Un(ctx context.Context, dollar_1 []int32) ([]UnRow, error);
array_agg with a cast: ok: AggTyped(ctx context.Context, userID int64) ([]int64, error);
array_agg result: ok: Agg(ctx context.Context, userID int64) (interface{}, error);
== the columns sqlc named for select * over a join
  ID   Email   Name   Meta   ID_2   UserID   TotalCents   Status 
== what PostgreSQL does with the same SQL
order by $1::text with "email": [[email protected] [email protected] [email protected]]
order by $1::text with "id": [[email protected] [email protected] [email protected]]
order by case when $1 = 'email': [[email protected] [email protected] [email protected]]
two statements with a parameter: ERROR: cannot insert multiple commands into a prepared statement
similarity() without pg_trgm: ERROR: function similarity(text, unknown) does not exist

Everything below quotes that run.

1. ORDER BY a parameter

The most common wish: let the caller choose the sort. The query select id, email from users order by $1::text generated without complaint:

Ordered(ctx context.Context, dollar_1 string) ([]OrderedRow, error)

It compiles, it has a string parameter, and it is wrong. A bind parameter is a value. ORDER BY with a constant sorts nothing, and PostgreSQL does not treat the string 'email' as the column. Run against three users inserted out of order, the output was identical for "email" and for "id": [[email protected] [email protected] [email protected]], the insertion order. No error, no effect.

Three honest ways to get a variable sort:

• A CASE expression. order by case when sqlc.arg('by')::text = 'email' then email end asc, id sorted correctly: [[email protected] [email protected] [email protected]] with by = "email". It works for a fixed set of columns of compatible types. Direction needs another CASE (or two named queries), and every new sort option edits the SQL. Put an index on each column you offer.
• One query per sort. ListByEmail, ListByCreated. Verbose and explicit, and each can have its own index and plan.
• Build that statement at run time with a query builder, validating the column against an allow list (dynamic SQL builders), and keep sqlc for the rest.

2. Two statements in one query

The query delete from orders where id = $1; delete from users where id = $1; generated Two(ctx, id int64) error. sqlc accepted it. PostgreSQL did not:

ERROR: cannot insert multiple commands into a prepared statement

pgx prepares statements by default, and the extended protocol allows one command per statement. The failure arrives the first time the code runs, not when sqlc generates. A multi statement body belongs in a transaction made of separate queries (DeleteOrders, DeleteUser), each a single command, run with WithTx. A with ... delete ... returning CTE can do several writes in one statement when the order does not matter.

3. A function sqlc does not know

select id from users where similarity(email, $1) > 0.3 is valid with the pg_trgm extension. The schema file did not declare the extension, and sqlc did not complain. It generated:

Similar(ctx context.Context, similarity interface{}) ([]int64, error)

The parameter is an interface{} and is named after the function. Against the lab's database, which has no pg_trgm, running the SQL gave function similarity(text, unknown) does not exist. Two lessons. A function sqlc cannot resolve is not an error, it is an untyped parameter, so a type you did not choose can slip into your code. And the schema you give sqlc should declare what your database has (extensions, custom functions) or the types degrade.

The fix for the type is a cast, which is cheap and clear: similarity(email, $1::text) generated SimilarCast(ctx, dollar_1 string). Cast every parameter that is not compared directly to a typed column.

4. Untyped results

Aggregates that sqlc cannot type come back as interface{}. select array_agg(id) as ids from orders where user_id = $1 generated Agg(ctx, userID int64) (interface{}, error). Adding ::bigint[] gave AggTyped(...) ([]int64, error). jsonb_build_object(...) generated []byte, which is correct and leaves the decoding to you. Cast the result of any expression whose type you care about, and treat an interface{} in a generated signature as a bug in the SQL.

5. select * over a join

select * from users u join orders o on o.user_id = u.id generated JoinedRow with these fields: ID Email Name Meta ID_2 UserID TotalCents Status. The second id became ID_2. It works, and it makes the struct depend on the order of the tables and the column lists of both. A column added to orders appears in the struct on the next generation, and a reorder of the join renames fields. List the columns with aliases (o.id as order_id) so the names are yours. sqlc.embed is the other answer, with its own caveat for nullable sides.

6. What sqlc does reject

Not everything is quiet. Two errors came back at generate time, with the line and column:

• query.sql:4:1: duplicate query name: Same for two queries with one name.
• query.sql:2:8: column reference "id" is ambiguous for select id from users u join orders o ....

The ambiguous column is the check that justifies using sqlc at all: PostgreSQL would reject the query at run time, and sqlc rejects it at build time. Valid but surprising queries pass: a CTE with a delete ... returning (Gone(ctx, id) (int64, error)), a window function (rank() over (...)), a recursive CTE and unnest ... with ordinality all generated typed signatures.

The shape of the problem

Every case above has one cause: sqlc checks syntax and types against your schema, not semantics and not the wire protocol. It cannot know that a parameter in ORDER BY is useless, that pgx forbids multiple commands, or that a function it never heard of exists. That gap is covered by three habits, which together make the tool safe:

1. Cast parameters and result expressions. If a generated signature contains interface{}, fix the SQL.
2. Run sqlc vet with rules for what you care about, and test each rule with a query that should fail (vet found a star rule that never fired).
3. Test every query against a real database. A generated method that nothing calls in a test has the same status as untested SQL. The statements in the lab ran in seconds, and a test per query would have caught cases 1 to 3 on the day they were written (sqlc testing).

Other limits worth knowing

Findings from the other sqlc articles in this cluster, each run on the same toolchain:

• sqlc.embed on the nullable side of a left join failed to scan a NULL into a non pointer field (joins).
• An array of a PostgreSQL enum could not be encoded as a parameter, and the workaround is a text array with a cast (enums).
• A custom Go type in an override cannot live in the generated package (overrides).
• A vet rule written as a regular expression for select * never fired, because sqlc expands the star first (vet).
• Dynamic filters need the narg pattern or a different tool (dynamic queries).

None of these are reasons not to use sqlc. They are reasons to know its edges before the first production incident teaches them.

Checklist

• Never put a parameter in ORDER BY; use a CASE, separate queries or a builder.
• One command per query. Use transactions for sequences.
• Declare extensions and functions in the schema file, and cast parameters.
• Treat interface{} in a signature as a defect.
• Alias join columns instead of select *.
• Run generate, vet and database tests in CI.

The query rules are in the sqlc documentation, and the wire protocol limit in the PostgreSQL extended query documentation.