Fix: sqlc Not Working — sqlc.yaml v2, pgx/v5 Driver, Nullable Types, JSONB, and Array Parameters

The Error

You run sqlc generate and it complains about your config:

$ sqlc generate
# error parsing config: version is required

Or it generates code but the types don’t match what you wrote:

-- query.sql
-- name: GetUser :one
SELECT id, name, email FROM users WHERE id = $1;

// generated.go
type GetUserRow struct {
    ID    int32          // You wanted int64
    Name  sql.NullString // You wanted string
    Email sql.NullString // You wanted *string
}

Or your pgx-specific types panic at runtime:

panic: cannot find encode plan for type pgtype.JSONB

Or passing a slice to IN (...) doesn’t work:

SELECT * FROM users WHERE id IN ($1);  -- Can't pass []int

Why This Happens

sqlc parses your SQL, generates Go code with named functions per query, and statically types arguments and results. Most issues come from one of:

• Wrong config version. sqlc.yaml has v1 and v2 schemas with very different shapes. v2 is current; v1 is deprecated. Mixing them silently produces wrong output.
• Driver mismatch. database/sql and pgx/v5 have different type maps. Picking the wrong sql_package in config generates types your runtime can’t use.
• Schema vs query inference. sqlc infers nullability and types from your CREATE TABLE (schema). If your column is email TEXT (nullable), the generated type is sql.NullString. To get a pointer or non-null type, change the schema or override.
• Postgres-specific syntax for slices. SQL IN ($1) with a Go slice doesn’t work in any driver. sqlc requires ANY($1::int[]) for slice parameters.

A second layer of confusion comes from where sqlc sits in the broader Go database-tooling space. Many users adopt it expecting a Prisma-like ORM and are surprised when it does nothing at runtime. sqlc is a pure code generator — it reads SQL, emits Go functions, and exits. There is no migration runner, no connection management, no fluent query builder, no entity tracking. Errors that look like “sqlc is broken” are often the absence of a feature it was never meant to ship. If your migrations aren’t applied, sqlc still happily generates code from stale schema files and produces compile-time-correct Go that hits a database without those tables.

A third layer is the gap between the schema sqlc parses and the schema your database runs. sqlc’s parser lags Postgres on some recent syntax (generated columns, certain partitioning DDL, window-function edges). When parsing fails on a column, sqlc emits a struct missing that field — no compile error, just a silent omission. Diff generated structs against \d table before trusting them.

Fix 1: Use the v2 Config Schema

# sqlc.yaml
version: "2"
sql:
  - schema: "db/schema.sql"
    queries: "db/queries.sql"
    engine: "postgresql"
    gen:
      go:
        package: "db"
        out: "internal/db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_pointers_for_null_types: true
        emit_prepared_queries: false
        emit_interface: true

Three sections inside sql:

• schema — path(s) to your CREATE TABLE statements.
• queries — path(s) to your -- name: X :one|:many|:exec queries.
• gen.go — output config.

For multi-database projects, list multiple sql blocks:

version: "2"
sql:
  - { schema: "db/main/schema.sql", queries: "db/main/queries.sql", engine: "postgresql", gen: { go: { package: "main_db", out: "internal/main_db" } } }
  - { schema: "db/analytics/schema.sql", queries: "db/analytics/queries.sql", engine: "postgresql", gen: { go: { package: "analytics_db", out: "internal/analytics_db" } } }

Pro Tip: Set emit_pointers_for_null_types: true. Without it, every nullable column becomes sql.NullString / sql.NullInt64 / etc. — verbose and inconvenient. Pointers (*string, *int64) compose better and JSON-encode cleanly.

Fix 2: Pick the Right Driver

For new projects, use pgx/v5:

sql_package: "pgx/v5"

This generates code that uses pgxpool.Pool and pgx.Rows directly. Faster than database/sql and supports the full Postgres feature set (JSONB, arrays, ranges, copy-from, custom types).

For projects still on database/sql:

sql_package: "database/sql"

The generated code uses sql.DB, sql.Rows, sql.NullString. Works with any driver implementing database/sql (lib/pq, jackc/pgx via stdlib wrapper).

Common Mistake: Choosing pgx/v5 but importing lib/pq in your driver setup, or vice versa. The sqlc-generated code is tied to the driver — mismatched runtime fails at the type level.

Fix 3: Override Column Types

For UUID columns from github.com/google/uuid:

sql:
  - schema: "..."
    queries: "..."
    gen:
      go:
        # ...
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
          - db_type: "uuid"
            nullable: true
            go_type: "github.com/google/uuid.NullUUID"

For JSONB columns mapped to a typed struct:

overrides:
  - column: "users.preferences"
    go_type:
      import: "github.com/myorg/types"
      package: "types"
      type: "UserPreferences"

The column matches table.column. Use db_type for type-wide overrides; column for per-field.

For timestamp columns with time.Time:

overrides:
  - db_type: "timestamptz"
    go_type: "time.Time"
  - db_type: "timestamptz"
    nullable: true
    go_type: "*time.Time"

Pro Tip: Keep all overrides in one block. sqlc applies them in order — earlier rules can be shadowed by later ones with the same db_type/column.

Fix 4: Use ANY($1::int[]) for Slice Parameters

IN ($1) doesn’t accept slices in any Postgres Go driver. Use the Postgres array operator:

-- name: GetUsersByIDs :many
SELECT * FROM users WHERE id = ANY($1::bigint[]);

sqlc generates a function that takes []int64:

users, err := q.GetUsersByIDs(ctx, []int64{1, 2, 3})

For text arrays:

-- name: GetUsersByEmails :many
SELECT * FROM users WHERE email = ANY($1::text[]);

For multi-column lookups, use a row constructor:

-- name: GetUsersByCompositeKeys :many
SELECT * FROM users
WHERE (org_id, user_id) IN (
  SELECT * FROM unnest($1::int[], $2::int[]) AS t(o, u)
);

Common Mistake: Writing WHERE id IN (sqlc.slice('ids')) thinking sqlc will expand it. sqlc does have a sqlc.slice() helper, but for Postgres-with-array support the cleaner pattern is ANY($1::type[]).

Fix 5: Query Annotations and Return Types

Every query needs a -- name: X :TYPE annotation:

-- name: CreateUser :one
INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *;

-- name: ListUsers :many
SELECT * FROM users ORDER BY id;

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

-- name: CountUsers :one
SELECT COUNT(*) FROM users;

Return type modifiers:

• :one — exactly one row. ErrNoRows if empty.
• :many — zero or more rows. Returns a slice.
• :exec — no rows returned. Returns only error.
• :execrows — returns affected rows count + error.
• :execresult — returns sql.Result + error.
• :batchone / :batchmany / :batchexec — pgx batch (multiple queries in one network round-trip).

For batch queries with pgx:

-- name: GetUserByID :batchone
SELECT * FROM users WHERE id = $1;

batch := q.GetUserByID(ctx, []int64{1, 2, 3})
batch.QueryRow(func(i int, user User, err error) {
    // Called once per item
})
batch.Close()

Batches send all queries in one network call — massive speedup for N+1 patterns.

Fix 6: Embed Complex JOINs with :json or Struct Composition

For queries that return data from multiple tables, sqlc generates a flat struct by default:

-- name: GetOrderWithUser :one
SELECT o.*, u.name AS user_name, u.email AS user_email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = $1;

type GetOrderWithUserRow struct {
    ID         int64
    Total      int64
    UserID     int64
    UserName   string
    UserEmail  string
}

For better separation, use sqlc.embed:

-- name: GetOrderWithUser :one
SELECT sqlc.embed(o), sqlc.embed(u)
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = $1;

type GetOrderWithUserRow struct {
    Order Order
    User  User
}

sqlc.embed(alias) expands to the alias’s full column list and groups them into a nested struct on the Go side.

Pro Tip: For aggregations returning JSON arrays, output JSONB and let sqlc generate []byte:

-- name: GetUserWithPosts :one
SELECT u.*, json_agg(p.*) AS posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.id = $1
GROUP BY u.id;

Then unmarshal into a typed slice in Go:

row, _ := q.GetUserWithPosts(ctx, id)
var posts []Post
json.Unmarshal(row.Posts, &posts)

Fix 7: COPY FROM for Bulk Insert

For inserting thousands of rows, sqlc can generate COPY FROM (pgx only):

-- name: CreateUsers :copyfrom
INSERT INTO users (name, email) VALUES ($1, $2);

Generates:

err := q.CreateUsers(ctx, []CreateUsersParams{
    {Name: "Alice", Email: "[email protected]"},
    {Name: "Bob", Email: "[email protected]"},
    // ... 10000 more ...
})

Under the hood, this uses Postgres’s COPY FROM STDIN protocol — 10-100x faster than individual INSERTs for bulk loads.

Common Mistake: Using :copyfrom for small inserts. For <100 rows, the overhead vs :many INSERT ... RETURNING is comparable. Reserve :copyfrom for genuinely large bulk loads where you don’t need RETURNING.

Fix 8: Set Up Migrations and Generate Schema

sqlc reads CREATE TABLE statements to infer types — keep them in sync with your migrations:

db/
├── schema.sql           # All CREATE TABLEs (sqlc reads this)
├── queries.sql          # All -- name: X queries
└── migrations/
    ├── 0001_users.up.sql
    ├── 0001_users.down.sql
    ├── 0002_orders.up.sql
    └── 0002_orders.down.sql

Generate schema.sql from migrations periodically:

# Concat all up migrations (with a migration tool):
cat db/migrations/*.up.sql > db/schema.sql

Or use a migration tool like golang-migrate, goose, or atlas that can dump the final schema.

For sqlc to use migrations directly (skip the consolidated schema):

sql:
  - schema: "db/migrations"
    queries: "db/queries.sql"
    engine: "postgresql"
    gen: { go: { package: "db", out: "internal/db" } }

sqlc reads all .sql files in the directory in alphabetical order. Migration filenames need to sort correctly (0001_, 0002_, etc.).

sqlc vs xo vs gnorm vs Atlas: Which Go SQL Codegen Should You Use?

sqlc isn’t the only “SQL to Go” tool. The category splits along two axes: what the tool considers the source of truth (queries, schema, or migrations) and how much it generates (just types, or whole CRUD layers).

sqlc is query-first. You write SQL queries, sqlc parses them, infers the parameter and result types from your schema, and emits one Go function per query. There is no runtime library, no entity tracking, no automatic CRUD. The generated code is plain database/sql or pgx/v5 calls. This makes sqlc ideal when you want full control over SQL and minimal magic — the tradeoff is that you write every query by hand, including the boring SELECT * FROM users WHERE id = $1 boilerplate.

xo is schema-first. Point it at a live database (or DDL file) and it generates a struct per table plus basic CRUD methods (Insert, Update, Delete, ByID). Custom queries are still hand-written and registered separately. xo is closer to a thin “active record” pattern — good for projects that want CRUD for free and only drop to SQL for the interesting cases. It’s less popular than sqlc today but still maintained, and the generated code is friendlier to read than sqlc’s flat function lists.

gnorm is template-driven. It introspects the schema and runs your Go templates over the metadata, so you decide exactly what gets generated. Most flexible, most work — you maintain the templates. Teams use gnorm for DAOs that look unlike sqlc or xo output, or to target languages besides Go from the same schema.

Atlas is schema management rather than code generation. It diffs your declarative schema (HCL or SQL) against the database and applies migrations. No Go type generation. Many sqlc users pair the two: Atlas owns schema and migrations; sqlc generates Go from the schema files Atlas manages.

If you’re starting fresh: use sqlc for queries plus Atlas (or golang-migrate) for schema changes. If you want CRUD without writing it, xo. If you need exotic generated code, gnorm. If you want a full ORM with relationships and lazy loading, look outside the codegen category entirely — Ent or GORM are the Go equivalents of Hibernate/Active Record and trade SQL transparency for developer ergonomics.

Still Not Working?

A few less-obvious failures:

• Generated file has no imports. Run goimports -w internal/db after generate, or set go.sum correctly. sqlc generates standard imports but missing them leaves you with an unused package error.
• pgxpool.Pool doesn’t satisfy the generated interface. With emit_interface: true, sqlc generates a DBTX interface that requires Query/QueryRow/Exec. pgxpool.Pool satisfies it for pgx/v5. If your code uses *sql.DB somewhere, you need to bridge.
• go generate ./... doesn’t run sqlc. sqlc isn’t a Go generator by default. Add //go:generate sqlc generate at the top of a file or run sqlc generate directly in your build.
• JSONB columns return wrong type. Without an override, JSONB is []byte. Override to json.RawMessage, map[string]interface{}, or your custom struct.
• CI build fails: sqlc not found. Install sqlc in CI explicitly via go install or download the binary. It’s not a Go dependency.
• Tests that hit a real DB fail with permission errors. Test DB user needs SELECT/INSERT/UPDATE/DELETE on the relevant tables. sqlc-generated code doesn’t bypass permissions.
• Long compile times after generation. A query with sqlc.embed generates large structs. For 100+ queries with embeds, generated code can be hundreds of KB. Split queries across multiple files and packages.
• pgvector columns fail. No built-in support. Override with a custom type that implements pgtype.Codec, and register the codec with your pool at startup.
• Generated code diffs on every CI run. sqlc emits files with version comments and import order that depends on its parser state. Pin sqlc’s version in go.mod or a .tool-versions file and run sqlc generate in CI as a verification step, not a generation step. If the diff is non-empty, fail the build.
• go test works locally but fails in CI with pq: column does not exist. Your local schema has a column the migrations haven’t added in CI. Always run migrations against the CI database before tests, and never edit schema.sql by hand without a matching migration file.
• Generated structs miss columns added by ALTER TABLE migrations. If schema.sql is built by concatenating migrations in alphabetical order, an ALTER TABLE users ADD COLUMN in a later migration won’t merge into the original CREATE TABLE. Either keep one canonical CREATE TABLE per table updated in place, or have sqlc read the migrations directory directly so it sees both DDL statements in order.

For related Go and database tooling issues, see Go module not found, Postgres relation does not exist, TypeORM query failed, and Prisma migration failed.