Over 200 migrations: what we learned about database changes

How migrations run here

The ORM creates the table structure (GORM AutoMigrate). Data changes — plans, texts, corrections — are SQL files that a seeder runs individually and by name on every deploy. There is no table recording what already ran. Every file therefore has to be idempotent: on the second run it must not change anything.

Lesson 1: every UPDATE needs a condition

An UPDATE without a condition overwrites whatever an administrator changed in the meantime, on every deploy — without an error. That's exactly what happened with plans: a large share of the migrations on the plans table wrote unconditionally. Since then, a guard checks that every UPDATE on plans has a real condition.

“Only rows not deleted” explicitly doesn't count as a condition: it protects against tombstones, not against the second run.

Lesson 2: ordering is part of the fix

Four corrections to blog texts sat in the seeder before the step that creates the articles. On running installations nobody noticed, because the articles existed long before. On a fresh installation the corrections hit nothing, and then the uncorrected text arrived. A guard now checks that every correction comes after its creation step.

Lesson 3: “success” doesn't mean “it worked”

• Several statements in one file: without placeholders, the database driver only executed the first statement — a blog seed reported one affected row instead of seven. The seeder now splits files itself.
• Temporary tables: a migration created a temporary table, the following statements ran on other pooled connections and couldn't find it. The seeder still reported success, and duplicate entries survived three runs. Files with their own transaction now run on a single connection.
• Not registered: migration files sat in the repository but weren't called by the seeder — nothing ran on deploy, success was reported. A guard now checks that every file is registered, and since August the seeder exits with an error code when a step fails.

Lesson 4: the ORM doesn't build guarantees

AutoMigrate creates tables and columns — it doesn't recreate partial unique indexes. A guarantee like “there is exactly one default plan” therefore only held where someone had once run the corresponding migration by hand. A dedicated migration now enforces these structural guarantees and warns on data conflicts instead of aborting the deploy.

Lesson 5: seeds that never overwrite reach nobody

Content seeds write with ON CONFLICT DO NOTHING so that edits made in the admin survive. The flip side: a correction to the seed text never reaches a running installation. For help articles, that affected 121 of 147 at one point. Corrections therefore get their own migration that checks for the old text — like the one that shipped this article.

Small things that cost time

• Column name: a Go field is called MetaDesc, its JSON name meta_description. GORM derives the column name from the field — meta_desc. Reading it off an API response gives you the wrong one.
• Question marks: the ? of the JSONB operator is also GORM's placeholder. We write jsonb_exists instead.
• Numbers: two numbers are used twice, one is missing. Our own rule asks for timestamp prefixes — the codebase numbers sequentially.
• Reversals: almost every migration has a down file. None of them runs automatically; they are an emergency plan for manual work.