My Schema Ships in the Binary — 300 Lines of Embedded Migrations and Three Files Named 0002
Table of Contents
There is a file in my repo called internal/database/migrator.go. It is a little over 300 lines including comments, it has no dependencies beyond lib/pq, and it has moved my agent platform’s Postgres schema through sixteen versions without a single “run the migrate tool first” comment in a deploy ticket. This is a post about why I trust 300 lines more than a dependency, and about the four ways it can still bite me — because I’m not going to pretend hand-rolled means bite-free.
The whole system is eight lines of interesting code
The design fits in a comment block:
//go:embed migrations/*.sql
var migrationFS embed.FS
func (m *Migrator) RunMigrations() error {
m.createMigrationsTable() // IF NOT EXISTS, obviously
files, _ := fs.ReadDir(migrationFS, "migrations")
// collect *.sql, sort.Strings(...)
for _, fileName := range migrationFiles {
m.runEmbeddedMigration(fileName) // skip if applied; else tx
}
return nil
}
And the per-file path:
func (m *Migrator) runEmbeddedMigration(fileName string) error {
// SELECT COUNT(*) FROM schema_migrations WHERE version = $1 → skip if > 0
content, _ := fs.ReadFile(migrationFS, "migrations/"+fileName)
tx, _ := m.db.Begin()
defer tx.Rollback()
tx.Exec(string(content)) // whole file, one tx
tx.Exec("INSERT INTO schema_migrations (version) VALUES ($1)", fileName)
return tx.Commit()
}
schema_migrations is a two-column table — version VARCHAR(255) PRIMARY KEY, applied_at TIMESTAMPTZ — and RunMigrations() is called from internal/database/db.go on the way to opening the connection pool. Boot the binary, get a current schema. That’s it.
Three things this buys me that a separate migrate step does not, and I use all three daily:
- The migration set and the code that requires it are the same artifact. A job executor that reads
wakes.next_due_atcannot be deployed against a schema that lacks the column, because they are the same binary. The entire class of “migrated the DB but rolled back the deploy” incidents is structurally impossible. - There is no working-directory assumption.
go:embedmeans the SQL is in the ELF, not on disk relative to a CWD that Docker quietly changed. My earlier life, where migrations lived in a folder that a container entrypoint had to find, produced a real outage; the embed version produced none. - Cold start is one command.
docker compose upgives me a database that0001_initial_schema.sqlbuilt from nothing. A fresh clone has never once needed me to open a psql prompt.
Three files named 0002, and why nobody died
Here is the part that makes people reach for the golang-migrate docs. My migrations directory currently holds:
0002_add_error_message_to_jobs.sql
0002_add_memory_config.sql
0002_add_wasm_source_code.sql
Three migrations, one version prefix, no conflict. Because my version key is the full filename, not a parsed integer, and my ordering is sort.Strings over the whole name. The three 0002s apply in lexicographic order, each records its own row, and three agents (including a past me with a merge conflict and too much confidence) each added a migration without coordinating a number. Under a numeric-version scheme those three files are a crash on boot and a blameless blame game; under filename-as-version they’re a Tuesday.
This is a real trade-off, not a free win: numeric prefixes are how teams see ordering and collisions at a glance. I’ve traded that visibility for merge-friendliness, and the only time the ordering ambiguity actually matters is when two migrations touch the same table — which, in a system where each migration is additive and small, has not happened.
The Postgres tax and the Postgres dividend
Dividend: Postgres DDL is transactional. The whole file runs inside one tx, so a migration that fails on statement 40 of 0012_tenancy.sql (users, api_keys, owner_id on five tables, backfill, indexes) leaves nothing half-applied. On MySQL I’d have needed per-statement gymnastics; here defer tx.Rollback() is the entire recovery story.
Tax: anything that refuses to run inside a transaction can’t be a migration. CREATE INDEX CONCURRENTLY is the classic. My response has been 0009_optimize_job_queries.sql and 0010_add_query_optimization_indexes.sql — plain CREATE INDEX, taken during a boot when the fleet is already drained. Fine at my scale (single-node Postgres, tables small enough that index builds are seconds). If your jobs table is 200M rows and you can’t stop the world, this whole post is inadmissible evidence and you should go hire a migration tool with lock timeouts and a DBA.
Four traps I live with
Being honest about the parts the README doesn’t mention:
GetMigrationVersion()lies, by design. It returnsORDER BY applied_at DESC LIMIT 1— the most recently applied file, not the highest one. When the out-of-order 0002s get backfilled onto a live database, “current version” reports0002_add_wasm_source_code.sql, which sounds like a time machine. Fix, when I get to it: order by version, or stop pretending a single scalar version exists — the honest state is a set.- No content hash. The version is a filename. If I edit
0008_add_skills_table.sqlafter it has been applied, every existing database silently keeps the old schema and still reports the file as applied. Filename-addressed migrations assume history is immutable. It mostly is, ingit log, but “mostly” is exactly the word that shows up in postmortems. - There are no down migrations. Every rollback is a forward-fix commit. This is fashionable now (gh-ost, Flyway teams, everyone says undo is a fantasy), and at my scale it’s correct — but it means the emergency playbook is “write a new 00xx”, so the new 00xx had better be written before the incident.
- Boot-time races are only mostly benign. Two replicas starting simultaneously both see
COUNT(*) = 0, both begin, one commits its insert, the other gets a primary-key violation onschema_migrationsand dies. The restart fixes it. So my migration system’s concurrency control is “crash once, win on retry.” It works. It is not what I’d write for a 50-replica rollout, where the loser’s crash also fails its readiness probe and starts a small stampede.
When 300 lines is the right amount
I’d make the same call again under the same conditions: one Postgres, migrations that are pure additive DDL, a fleet measured in single digits, and a binary that already owns the schema’s semantics. Hand-rolling is a liability when you need out-of-order safety, down paths, lock-aware concurrency, or a team that can’t read the 300 lines before adding a file — which is to say, when you need a product. The failure mode of the small system is silent drift (trap #2); the failure mode of the big one is that migrate is step two of a deploy that nobody runs.
If you want the shape without my baggage, the parts worth stealing into any tool are: version = filename, one transaction per file, skip-if-applied before opening the file at all, and record the version in the same transaction as the DDL. Those four decisions are what make my three files named 0002 a story I can tell at all.