sqlc Configuration for Postgres MySQL and SQLite Projects

Every sqlc project starts with one file, sqlc.yaml, and most of the surprises in generated code trace back to a line in it. This article covers version 2 of that file: what each key does, how to run several engines from one file, and what actually changes in the Go output when you switch between PostgreSQL, MySQL and SQLite. It builds on the sqlc tutorial, and the output is checked with Go 1.25.6, sqlc v1.30.0 and pgx v5.11.0.

The configuration sits in the middle of the database cornerstone. The choice of driver is the biggest lever in it, and the pgx tutorial explains why sql_package: pgx/v5 is the usual PostgreSQL pick.

The shape of the file

The lab uses one file with three entries, one per engine:

version: "2"
sql:
  - name: postgres
    engine: postgresql
    schema: pg/schema.sql
    queries: pg/query.sql
    gen:
      go:
        package: pgdb
        out: pgdb
        sql_package: pgx/v5
        emit_json_tags: true
        emit_empty_slices: true
  - name: mysql
    engine: mysql
    schema: my/schema.sql
    queries: my/query.sql
    gen:
      go:
        package: mydb
        out: mydb
        sql_package: database/sql
  - name: sqlite
    engine: sqlite
    schema: lite/schema.sql
    queries: lite/query.sql
    gen:
      go:
        package: litedb
        out: litedb
        sql_package: database/sql

Read it from the top.

• version: "2" is required. Version 1 is the older layout and has been deprecated for years; new files should not use it.
• sql is a list. Each element is an independent run of the generator: its own schema, its own queries, its own output. They do not share types, which is why this file can mix engines.
• name is optional. It labels an entry in error messages and in sqlc generate --file selections, and it helps once a file has more than one block.
• engine is postgresql, mysql or sqlite. It selects the parser and the type mapping, and nothing else in the entry changes the dialect.
• schema and queries are paths, relative to the config file. Each can be a file, a directory or a list. A directory is read in lexical order, which is what makes a migrations folder work.
• gen.go is the generator block. package names the Go package, out is the directory it is written to, and sql_package picks the driver API the generated code calls.

A few other keys in the Go block come up in nearly every project:

emit_empty_slices deserves a moment. Without it a query that finds nothing returns a nil slice, and json.Marshal writes null. A front end that expects [] then breaks on the empty case, which is the sort of bug that appears only in a new tenant's first week. The lab sets it for the PostgreSQL entry.

What the engine changes

The three schemas describe the same table. This is the PostgreSQL version:

create table items (
    id    bigserial primary key,
    name  text not null,
    note  text,
    price numeric(10,2) not null,
    added timestamptz not null default now(),
    tags  text[] not null default '{}',
    ok    boolean not null default true
);

and the MySQL and SQLite ones differ only where the dialects do (auto_increment against autoincrement, decimal against real):

create table items (
    id    bigint auto_increment primary key,
    name  varchar(100) not null,
    note  text,
    price decimal(10,2) not null,
    added timestamp not null default current_timestamp,
    ok    boolean not null default true
);

create table items (
    id    integer primary key autoincrement,
    name  text not null,
    note  text,
    price real not null,
    added datetime not null default current_timestamp,
    ok    boolean not null default 1
);

Each engine gets a query file as well. The PostgreSQL one takes numbered parameters:

-- name: GetItem :one
select * from items where id = $1;

-- name: AddItem :one
insert into items (name, note, price) values ($1, $2, $3) returning *;

MySQL and SQLite use ?, and they cannot take an array parameter, so a list of ids goes through sqlc.slice:

-- name: GetItem :one
select * from items where id = ?;

-- name: ListByIDs :many
select * from items where id in (sqlc.slice('ids'));

-- name: GetItem :one
select * from items where id = ?;

-- name: ListByIDs :many
select * from items where id in (sqlc.slice('ids'));

At generate time sqlc rewrites sqlc.slice('ids') into a placeholder list sized at call time (?,?,?). PostgreSQL does not need it, because where id = any(@ids::bigint[]) binds a whole slice as one array, as the dynamic queries article uses.

The program reads the generated Item struct from each package with reflect, so the output below is the real generated types and not a table written from memory:

package main

import (
	"fmt"
	"reflect"

	"example.test/lab/litedb"
	"example.test/lab/mydb"
	"example.test/lab/pgdb"
)

// show prints the Go type sqlc chose for each column of the generated Item struct.
func show(engine string, v any) {
	t := reflect.TypeOf(v)
	fmt.Printf("%-11s %s.Item:\n", engine, t.PkgPath()[len("example.test/lab/"):])
	for i := 0; i < t.NumField(); i++ {
		f := t.Field(i)
		tag := ""
		if j := f.Tag.Get("json"); j != "" {
			tag = `  json:"` + j + `"`
		}
		fmt.Printf("    %-6s %-22s%s\n", f.Name, f.Type, tag)
	}
}

func main() {
	show("PostgreSQL", pgdb.Item{})
	show("MySQL", mydb.Item{})
	show("SQLite", litedb.Item{})
	fmt.Println("the three generated packages compile side by side from one sqlc.yaml:", pgdb.New(nil) != nil, mydb.New(nil) != nil, litedb.New(nil) != nil)
}

set -e
"$HOME/go/bin/sqlc" generate
go run .

PostgreSQL  pgdb.Item:
    ID     int64                   json:"id"
    Name   string                  json:"name"
    Note   pgtype.Text             json:"note"
    Price  pgtype.Numeric          json:"price"
    Added  pgtype.Timestamptz      json:"added"
    Tags   []string                json:"tags"
    Ok     bool                    json:"ok"
MySQL       mydb.Item:
    ID     int64                 
    Name   string                
    Note   sql.NullString        
    Price  string                
    Added  time.Time             
    Ok     bool                  
SQLite      litedb.Item:
    ID     int64                 
    Name   string                
    Note   sql.NullString        
    Price  float64               
    Added  time.Time             
    Ok     bool                  
the three generated packages compile side by side from one sqlc.yaml: true true true

Compare the three field lists in the output.

The integers and text agree. id was int64 on all three, name was string and ok was bool. The engines differ on how they store a boolean, but sqlc maps to a Go bool regardless.

Nullable columns follow the driver, not the engine. note was pgtype.Text in the pgx package and sql.NullString in the two database/sql packages. The choice is sql_package. If you want *string everywhere, emit_pointers_for_null_types: true does that for both.

The price column is where the engines part ways. PostgreSQL's numeric(10,2) became pgtype.Numeric, MySQL's decimal(10,2) became a string, and SQLite's real became float64. MySQL's driver returns decimals as text to avoid losing precision, and sqlc follows that. SQLite has no decimal type, so real is a binary float, and a money column stored that way inherits float rounding. If the amount is money, store integer cents or override the type to a decimal library, as the overrides article does.

Timestamps differ too. timestamptz was pgtype.Timestamptz under pgx, while MySQL's timestamp and SQLite's datetime were time.Time. The MySQL and SQLite drivers parse the value for you, with settings of their own (parseTime=true in the MySQL DSN, which sqlc cannot see).

JSON tags came from one flag. The PostgreSQL entry set emit_json_tags, and the printed struct shows json:"id" and json:"note" on its fields. The other two entries have none. Tags belong on the structs you return from an API, and some teams prefer to map to separate response types so a schema change does not alter the API.

The last line of the output is the proof that matters for monorepos: three packages from three engines built into one binary. The monorepo article uses the same layout for several services.

Settings that save a review

Keep out separate from hand written code. A directory that holds only generated files can be ignored by linters and marked as generated in code review tools, and nobody edits a file there by mistake.

Pick emit_pointers_for_null_types before you write callers. Switching it later changes the type of every nullable field in every query, so every call site that sets or reads one stops compiling. That is useful as a guide to what changed, and a large diff for a setting you could have chosen on day one.

Decide on the driver once. pgx/v5 and database/sql generate different code with different method sets (:copyfrom and :batch* exist only for pgx). Starting on database/sql and moving later means regenerating and fixing every call site.

Use emit_interface for tests only when you need it. The generated Querier interface lets a unit test pass a fake, but a test against a real database, as the testing article sets up, is usually more convincing than a fake that returns what you told it to.

Put the version in a file. sqlc has no version key for itself. Pin it where CI installs it, because a generator upgrade changes the output.

Common mistakes

• A schema path that points at the wrong folder yields "relation does not exist" errors for tables that plainly exist. The path is relative to the config file, not to the working directory.
• Mixing placeholders: $1 on MySQL or ? on PostgreSQL is rejected by the parser. Use the engine's own style in each entry's query files.
• Expecting one Queries type across engines. Each entry is its own package, so pgdb.New and mydb.New are different types. Share behaviour through an interface you define.
• Forgetting sql_package on a pgx project. The default is database/sql, and the generated code then rejects a pgxpool.Pool.

The key reference is in sqlc's configuration file documentation, and the type tables per engine are in its datatype reference. For the drivers behind the lab, see pgx and the MySQL driver's DSN parameters.