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.

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

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.

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