Migrations

Schema management in gpsystem is deliberately simple: timestamp-prefixed SQL files in the project's migrations/ directory, embedded into the binary, with a generated cmd/migrate runner. There is no schema builder and no external tool to install: migrations run with go run, like everything else in the project.

What gets generated

Every new project starts with this (see new project):

shop/
├── cmd/migrate/main.go              # the runner
└── migrations/
    ├── embed.go                     # //go:embed *.sql
    ├── 20200101000000_init.sql      # empty placeholder, where your first schema goes
    └── 20200101000100_outbox.sql    # the outbox_events table

• 20200101000000_init.sql is an empty skeleton, there only so cmd/migrate compiles from day one (the embed's *.sql pattern expects at least one file). You put your first schema here, or ask the CLI for a new file. The fixed, early timestamp guarantees every later, generation-time-stamped migration sorts after it.
• 20200101000100_outbox.sql creates the outbox's outbox_events table. Its source of truth is the kit's embedded outbox.MigrationSQL constant: the schema the relay's queries depend on. For older, pre-worker projects, the add worker command writes it in at the next available timestamp.
• embed.go is a single //go:embed *.sql directive: every migration becomes part of the binary, making the deploy artifact self-contained.

The file format

Plain SQL, sectioned with goose markers. One file is one migration, with an up and a down direction:

-- +goose Up
-- +goose StatementBegin
CREATE TABLE products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text        NOT NULL,
    price_cents bigint      NOT NULL,
    stock       int         NOT NULL DEFAULT 0,
    created_at  timestamptz NOT NULL DEFAULT now()
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE products;
-- +goose StatementEnd

+goose Up / +goose Down are the two directions; a StatementBegin/StatementEnd pair marks the section between them as a single statement (without it goose splits on semicolons, which matters for multi-line functions and triggers). The filename format is <timestamp>_name.sql: a 14-digit, UTC YYYYMMDDHHMMSS timestamp defines the run order and the version. Because it captures the moment of generation rather than a sequence number, it never collides across branches.

Ask the CLI for a new migration: it assigns the current timestamp and generates the skeleton:

go tool gpsystem new migration create_products_and_orders
# -> migrations/20260720143012_create_products_and_orders.sql

Older, sequentially-numbered projects (00001_init.sql, 00002_outbox.sql) do not need to migrate to the new scheme: new migrations still sort correctly after the old ones, since goose orders both formats numerically.

Details: new migration.

The cmd/migrate binary

The generated runner is short, and worth reading once in your own project. It is roughly this:

cfg := envconf.MustLoad[config]()             // loads only the DB_* variables

pool := pg.MustNewPool(ctx, cfg.DB)           // the same pgx pool seeding uses
db := stdlib.OpenDBFromPool(pool)             // database/sql over the pool

goose.SetBaseFS(migrations.FS)                // works off the embedded files
goose.SetDialect("postgres")

args := os.Args[1:]
if len(args) == 0 {
    args = []string{"up"}                     // no argument: up
}
goose.RunContext(ctx, args[0], db, ".", args[1:]...)

That is: it loads the dbx.Config from the DB_* env variables (the same ones the app uses), and runs goose as a library over the embedded filesystem, on top of a single pgx pool (the same one seeding opens too, when you call it with --seed or the seed subcommand). The command passes straight through to goose:

go run ./cmd/migrate              # = up: all pending migrations, in order
go run ./cmd/migrate up
go run ./cmd/migrate down         # roll back the most recent migration
go run ./cmd/migrate status       # which files ran, which are pending
go run ./cmd/migrate version      # current schema version
go run ./cmd/migrate up-to 3      # up to a given version (the number as an int)
go run ./cmd/migrate up --seed    # migrate, then run the seeders
go run ./cmd/migrate seed         # run the seeders only, no migration

Goose tracks applied migrations in the goose_db_version table (created on first run). Each migration file runs in a transaction: if one statement fails, all statements of that file roll back and the version table does not advance.

--seed/seed runs the project's central seeds/ package; see Seeders.

Why no external migration tool?

The stack has no golang-migrate, no atlas, and you do not install goose's CLI either: goose runs purely as a Go dependency, compiled into your own cmd/migrate binary. Three consequences:

1. The deploy artifact is self-contained. Migrations are embedded into the binary; CI and the production image copy no SQL files and carry no extra tool. The output of go build ./cmd/migrate carries everything.
2. No version drift. The migration runner comes from the same go.mod as everything else: CI cannot run a different goose version than a developer machine.
3. Plain SQL is the source of truth. No DSL and no diff-based magic: what is in the file is what runs, and code review sees exactly that.

Named connections are not migrated

cmd/migrate only ever loads the project's default DB_* connection: it has no notion of the named connections add db adds to a module. Goose's version table is per-database, and a named connection often points at something the service does not own the schema of (a warehouse, a read replica, a legacy database), so there is no generated runner for them. If a named connection does need its own migrated schema, run goose against it by hand or add a second cmd/migrate-<name> binary following the same pattern as the generated one.

Deployment

• Run migrations before the app, as a separate step: an init container, a release-phase command or a deploy pipeline step: go run ./cmd/migrate up (or the prebuilt binary). The app process does not run migrations on startup.
• One runner at a time. Migrations should run as a single step of the deploy, not on every replica in parallel, with multiple replicas, prefer a pipeline step over the init-container-per-pod pattern.
• Forward-compatible schema. During a rolling deploy the old app version is still running while the new schema is already live. Additive changes (new table, new nullable column) just work; split column drops and renames across two deploys.

No schema builder

gpsystem deliberately has no schema builder: migrations are plain SQL. A builder buys portability across databases, which is not needed here (the kit is built on PostgreSQL), at the cost of hiding what DDL actually runs. Plain SQL gives you PostgreSQL's full surface (partial indexes, GENERATED columns, CTE-based backfills), exactly as the database documentation describes them. 20200101000100_outbox.sql is a good example: a partial index (WHERE published_at IS NULL) on unpublished rows, expressed directly and without an abstraction layer in between.