The Binary Migrates Its Own Database

Part six of the boring stack. The app writes its own SQL. The tables that SQL runs against change over time, and those changes need a process.

The classic process has a separate step: before or after you deploy, someone runs the migration tool against the production database. Sometimes that someone forgets. Then new code runs against an old schema, or old code against a new one.

In my apps, there’s no separate step. The binary migrates its own database when it starts.

Numbered SQL files

Migrations are plain SQL files in one folder, numbered in order:

internal/db/migrations/001_users.sql
internal/db/migrations/002_items.sql
internal/db/migrations/003_items_archived_at.sql

Each file has an “up” part and, where it makes sense, a “down” part, marked with comments that goose understands. A new migration gets the next number after the highest existing one. Nothing else: no generated timestamps, no DSL, no migration classes.

goose keeps track of which numbers have run in a table inside the database itself.

Embedded and applied on startup

Go can embed files into the binary with //go:embed. The migrations folder is embedded, so the binary carries every migration it needs. On startup, before it serves a single request, the app runs every migration that hasn’t run yet.

That has a few consequences I like:

• A deploy is atomic from the schema’s point of view. The new binary starts, upgrades the schema, then serves. There’s no window where the code and the schema disagree.
• A fresh environment sets itself up. Delete the data folder, start the app, and you have an empty, fully migrated database. That’s how I reset my local state.
• Tests use the same migrations. Every test database is built by running the real migrations, so the tests run against the schema production has.

Write migrations that can’t hurt

Running migrations automatically means they must be safe to run unattended. A few rules:

• Add, don’t rename. Adding a column is safe. Renaming one breaks the old binary if you ever roll back.
• Never edit a migration that has run. If a migration was wrong, write a new one that fixes it. Edited history means one database ran a different version than another.
• Keep data changes small. A migration that rewrites a million rows on startup delays the start. For SQLite and the apps I build, that’s rarely a problem, but it’s worth thinking about before it is.

What it costs

• Rollbacks are forward fixes. Going back to an older binary doesn’t undo a migration. I fix forward with a new migration instead, which in practice is what everyone ends up doing anyway.
• A failed migration stops the app. That’s on purpose. I’d rather have an app that refuses to start than one that runs against a half-migrated schema.
• One app per database. Automatic migrations assume the app owns its database. If several services shared one database, you’d need a coordinated process. Mine don’t.

When I’d choose differently

With several app instances starting at the same time against a shared database, I’d run migrations as a separate deploy step, so they can’t race each other. With one server and one SQLite file, the binary is the best place for them.

Next: tests, and why they don’t use mocks.