Database Migrations I Can Roll Back at 2am

The worst database mistake I ever made was a migration with no down step, run at 2am, that dropped a column before I had a backup. I spent three hours reconstructing data from logs. Now every schema change I ship can roll back cleanly, and I've never had that panic again.

Here's how I structure migrations so the rollback is boring.

Every Change Ships With Its Down Step

A migration has two halves. The up step moves the schema forward. The down step moves it back. Most ORM tools generate both for you, and most people delete or ignore the down step because it feels like extra work.

I don't. If I can't write the down step, I don't ship the up step.

The reason is simple. At 2am, half asleep, with an error spiking, I don't want to be writing SQL. I want to run one command that puts the schema back exactly where it was five minutes ago. If I planned the reverse ahead of time, that command already exists.

Writing the down step also forces me to think about whether the change is reversible at all. Adding a column? Easy to reverse, just drop it. Renaming a column? That's two operations pretending to be one, and the naive version loses data on rollback. Dropping a column? There is no honest down step, because the data is gone. The moment I write "down: re-add the column" I realize the data to fill it doesn't exist anymore.

That realization is the whole point. The down step is a test of whether the change is safe, and I'd rather fail that test at my desk than in production.

I test the down step before I deploy. I run up, then immediately run down, then up again. If the schema doesn't return to a working state after that cycle, the migration isn't ready. This catches things like a foreign key that gets dropped on the way down but not recreated, or an index that survives a rollback and collides with the next up run.

The same discipline shows up in how I handle undo across my tools. If you want the deeper context on reversible operations, see The Undo Button Every RAXXO Tool Ships With. The database version is just the same idea with foreign keys attached.

Expand And Contract: One Change, Four Steps

The pattern that saved me is expand and contract. Instead of changing a column in place, I split the change into stages that are each safe on their own.

Say I want to rename user_name to username. The dangerous way is one migration that renames the column. If anything goes wrong mid-deploy, old code looks for user_name and new code looks for username, and one of them is always wrong.

The expand and contract way takes four steps across two or more deploys.

First, expand. I add the new username column alongside the old one. Nothing reads from it yet. This migration is pure addition, so its down step is a clean drop with zero data loss.

Second, backfill. I copy data from user_name into username, and I make the application write to both columns on every update. Now the two columns stay in sync. Old code reads the old column, new code can start reading the new one.

Third, migrate reads. I deploy code that reads from username. The old column still exists and still gets written, so if I need to roll this deploy back, nothing breaks.

Fourth, contract. Once I'm confident the new column is correct and nothing reads the old one, I drop user_name in a separate migration, days later.

Each step is reversible until the very last one. The only irreversible operation, the drop, happens when I'm calm and certain, not in the middle of a deploy. For large tables the backfill runs in batches so I don't lock a 50,000-row table in one transaction. I covered batching big scans in more depth at Binary Search vs Array.includes() on a 50,000-Row Catalog, and the same instinct applies here: never touch every row at once if you can help it.

This feels slower. It is slower. Four deploys instead of one. But I've never corrupted data with this pattern, and the four small steps each take two minutes while the one big step takes three hours when it goes wrong.

The Order Of Deploy And Migrate Matters

The single rule that prevents most 2am incidents: additive migrations run before the deploy, destructive migrations run after.

Think about what happens during a deploy. There's a window where old code and new code both exist. Instances restart one at a time. For thirty seconds, maybe two minutes, the database serves both versions of your application at once.

If I add a column before deploying the code that uses it, both old and new code are fine. Old code ignores the column. New code finds it waiting. The additive migration ran first, so the schema is always ahead of the code.

If I drop a column during that window, old code that still expects it crashes. So drops wait until every instance runs the new code. Destructive migration runs last, after the deploy fully settles.

The order flips depending on direction:

For adding things, migrate then deploy. The schema leads.

For removing things, deploy then migrate. The code leads.

Rollback follows the same logic in reverse. If I roll back a deploy, the old code comes back, and I need the old schema to still support it. That's exactly why the destructive step waited. The column is still there, so the rolled-back code finds everything it expects. No data lost, because I never dropped anything the old code depended on.

This is the piece people skip, and it's the piece that turns a routine rollback into a data-loss event. A rollback should never require a down migration that recreates data. If your rollback plan is "run the down step to re-add the dropped column," you've already lost, because the data to fill it is gone.

I keep a one-line rule in my deploy notes: a rollback must work with the schema that's live right now, without running any down migration at all. If that's not true, the migration order is wrong. For how I watch the deploy window itself, Structured Logging I Add on Day One of Every Project covers the tracing I lean on.

Failures That Need Retries, Not Rollbacks

Not every migration problem is a rollback problem. Some are just a flaky connection, and rolling back is the wrong response.

A long backfill on a big table can fail halfway because the database connection drops, or a lock times out, or a replica falls behind. If I panic and roll back, I've thrown away the half that worked. The better move is making the migration safe to re-run.

I write backfills to be idempotent. Instead of "copy every row," the query is "copy every row where the target is still null." Run it once, it fills 20,000 rows. The connection drops. Run it again, it fills the remaining 30,000 and skips the ones already done. No duplicates, no corruption, no need to start over.

For the actual retry logic around a flaky migration runner, I use the same backoff I use everywhere. If you want the exact code, Retry With Backoff and Jitter is the version I copy into every project. A backfill that hits a transient lock should wait and retry, not fail the whole deploy.

The distinction I hold onto: a schema change that's structurally wrong gets a rollback. A schema change that's structurally fine but hit a network hiccup gets a retry. Mixing those up is how people make a small problem bigger.

I also set a statement timeout on every migration. Without one, a migration that grabs a lock can block every query on the table and bring the whole app down while it waits. With a timeout, the migration fails fast, I see the lock contention, and I run it again during a quieter window. A failed migration that failed in two seconds is a non-event. A migration that hung for ten minutes holding a lock is an outage.

The running database here is Postgres behind a Shopify storefront and a few side services, so I care a lot about not locking tables that serve live traffic. Idempotent, timeout-bounded, re-runnable. That trio keeps a shaky migration from turning into a decision I have to make at 2am.

Bottom Line

If you only change one thing, make it this: write the down step before you write the up step, and test the full up-down-up cycle at your desk. That one habit catches the irreversible changes before they reach production.

Then layer in the order rule. Additive changes migrate before deploy, destructive changes migrate after, and drops wait days behind their own separate migration. A rollback should work against the live schema with no down migration required. If it doesn't, you've ordered something wrong.

Expand and contract feels like overkill until the first time it saves you from a 2am data loss. After that it's just how you rename a column.

I keep these rules in the same operations playbook I build every project from. If you want the full setup I start with, Claude Blueprint walks through how I wire logging, retries, and migrations together from day one. Start there, steal what's useful, and make your next rollback boring.

This article contains affiliate links. If you sign up through them, I may earn a small commission at no extra cost to you. (Ad)