sqlc Migrations Together With goose or Atlas Explained

A schema defined twice will eventually disagree with itself. The usual way to get there is a schema.sql that sqlc reads and a migrations/ folder that the deploy applies, kept in step by whoever remembers. The fix is to have one. sqlc can read a migrations folder directly, so the files your migration tool applies to the database are also the files sqlc learns the types from.

This article shows that arrangement with goose v3.26.0, sqlc v1.30.0 and PostgreSQL 18.6, with three migrations that create a table, add a column and rename one. It sits in the database cornerstone and connects two earlier topics: goose as the runner and the sqlc tutorial as the generator.

Point sqlc at the folder

The only change from a one file setup is the schema value:

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

schema: migrations is a directory. sqlc reads every .sql file in it in lexical order, which is why migration tools number their files with a zero padded prefix: 00001 sorts before 00002 as text, where 1 and 10 and 2 would not. For each file it replays the DDL (create table, alter table, drop column, create index) against an in memory model of the catalog. When it finishes, the model is the schema after all migrations, and that is what queries are checked against.

sqlc recognises the file formats of several runners. The documented set is golang-migrate, goose, dbmate, sql-migrate and tern. What each needs is different, and it comes down to the "down" half of a file:

• golang-migrate uses paired files (000001_x.up.sql and 000001_x.down.sql). sqlc reads the .up.sql files and skips the .down.sql ones.
• goose keeps both directions in one file, separated by -- +goose Up and -- +goose Down comments. sqlc reads only the section under Up.
• dbmate, sql-migrate and tern use their own markers, and sqlc understands each.

That is a real hazard if your tool is not on the list. A file with both directions in it and no markers sqlc recognises would apply the up statements and then the down ones in sequence, and the model would end with the table dropped again.

The three migrations

-- +goose Up
create table users (
    id    bigserial primary key,
    email text not null unique
);

-- +goose Down
drop table users;

-- +goose Up
alter table users add column plan text not null default 'free';

-- +goose Down
alter table users drop column plan;

-- +goose Up
alter table users rename column email to address;
create index users_plan on users (plan);

-- +goose Down
drop index users_plan;
alter table users rename column address to email;

The third is the interesting one for a code generator. It renames email to address and adds an index. sqlc replays the rename, so the final users table has address, not email, and a query that still said where email = $1 would fail at generate time with column "email" does not exist. The index is irrelevant to types, and sqlc accepts it and moves on.

The queries use the new name:

-- name: ByAddress :one
select id, address, plan from users where address = $1;

-- name: CreateUser :one
insert into users (address) values ($1) returning id, address, plan;

One source for both tools

The program applies the migrations with goose, then calls the generated code. The migration files are embedded with go:embed, so the binary carries exactly what sqlc read:

package main

import (
	"context"
	"database/sql"
	"embed"
	"fmt"
	"io/fs"
	"log"

	"github.com/jackc/pgx/v5/pgxpool"
	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var migrations embed.FS

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

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

func main() {
	ctx := context.Background()

	// goose applies the same files that sqlc read to learn the schema.
	std, err := sql.Open("pgx", dsn)
	must(err)
	defer std.Close()
	sub, err := fs.Sub(migrations, "migrations")
	must(err)
	p, err := goose.NewProvider(goose.DialectPostgres, std, sub)
	must(err)
	res, err := p.Up(ctx)
	must(err)
	fmt.Printf("1. goose applied %d migrations; sqlc generated code from the same %d files\n", len(res), len(res))

	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)
	u, err := q.CreateUser(ctx, "[email protected]")
	must(err)
	fmt.Printf("2. the generated struct follows all three migrations: %+v\n", u)
	got, err := q.ByAddress(ctx, "[email protected]")
	must(err)
	fmt.Println("   the renamed column works in a query:", got.Address, "| the added column has its default:", got.Plan)
}

The lab's run.sh loads an empty database, runs sqlc generate and then the program:

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

1. goose applied 3 migrations; sqlc generated code from the same 3 files
2. the generated struct follows all three migrations: {ID:1 Address:[email protected] Plan:free}
   the renamed column works in a query: [email protected] | the added column has its default: free

Read the two lines of output.

"goose applied 3 migrations; sqlc generated code from the same 3 files". goose's provider ran each file in order and reported three results. Nothing in the program tells sqlc which files exist, since it read the folder at generate time. The two tools agree because they share the folder.

The struct follows all three migrations. CreateUser returned {ID:1 Address:[email protected] Plan:free}. The Plan field exists only because migration two added the column, and Address is the name after migration three. The free came from the column's default, filled in by PostgreSQL when the insert did not mention it, and returned by the returning clause.

The renamed column works in a query. ByAddress found the row by the new name. Had the SQL kept email, there would be no program to run: the generate step would have failed first.

What the arrangement protects you from

Consider the day someone drops a column in a migration. With a separate schema file nothing forces the file to change, and the generated code still has the field. It compiles, and a query that selects it fails at run time against the real database. With the folder as the source, the next sqlc generate reports the missing column in the query that uses it, and the build stops on a developer's machine.

The cost is that your migrations must be statements sqlc can parse. Most are. Trouble comes from three places:

• do $$ ... $$ blocks and create function bodies are not parsed for their effect on tables, so a table created inside a PL/pgSQL block is invisible to sqlc.
• Data migrations (insert, update) are ignored, which is fine, since they do not change types.
• Extensions: create extension pgcrypto; is accepted, but a column of an extension type (citext, ltree, vector) may map to interface{} until you add an override.

When a migration does something sqlc cannot see, keep a small hand written file next to the migrations folder and list both in schema:

schema:
  - migrations
  - extra/types.sql

Atlas

Atlas works differently. In its versioned mode it also writes plain SQL files into a directory, along with an atlas.sum file that holds a hash chain for tamper detection. Because the migration files are SQL, sqlc can be pointed at the directory the same way. This article did not run Atlas, so treat that as a design note and not a result: check that the atlas.sum file does not confuse your sqlc version (it is not a .sql file, so it is expected to be ignored), and confirm the generated types in a pull request before you rely on it.

The other mode, declarative, is a single desired state file. That one is simpler still: it is one schema file, and schema: schema.hcl is not valid, but exporting the desired state as SQL (atlas schema inspect --format "{{ sql . }}") gives sqlc the file it needs.

A short routine

1. Write the migration. Apply it locally with your runner.
2. Run sqlc generate. Read the diff: a new field, a renamed one, a removed one.
3. Fix the callers the compiler reports.
4. Commit migration, queries and generated code together, in one change.

Two rules keep the habit honest. Never edit a migration that has been applied anywhere. sqlc will happily replay the edit, and the generated code will describe a schema that production does not have. Commit the generated code in the same change as the migration, so a revert takes both.

The folder behaviour is described in sqlc's guide to handling SQL migrations, and goose's annotations are in the goose documentation.