Why Database Migrations Are Scary

Why Database Migrations Are Scary

It's code that modifies data in production. If something goes wrong, it's not a bug that can be fixed with a hotfix. It's potentially lost or corrupted data.

The fear is reasonable. But fear without process leads to avoiding necessary migrations or rushing them.

The Fundamental Principle

Every migration must be reversible or incrementally applicable. Never make destructive changes in a single step.

If you add a column, the reverse migration removes it. If you change a data type, first add the new column, then migrate the data, then drop the old one.

Non-Destructive Migrations

Adding columns is safe. Dropping columns is destructive.

The safe pattern: stop using the column in code first, deploy, then drop the column in a later migration.

Renaming tables or columns is destructive. The safe pattern: create the new one, copy the data, update the code to use both, deploy, then drop the old one.

The Problem with Long Deploys

If your application has multiple instances and a deploy takes minutes, both old and new code are running during that window.

Migrations must be compatible with both versions. New columns must have a default value or allow nulls until all new code is deployed.

Testing Migrations

Migrations can be tested. Apply the migration to a production clone. Verify that the code works. Verify that the reverse migration works.

It's more work, but less work than restoring a backup at 3 a.m.

Large Migrations

Changes that touch millions of rows cannot run inside a single transaction. The table lock would take the service down.

The pattern: scripts that process in batches, with pauses between batches to avoid saturation. They can take hours or days. The code must continue to work with partially migrated data.

Tools That Help

Versioned migration systems: each migration has a unique identifier, they are applied in order, and applied migrations are recorded.

Prisma, Drizzle, Flyway, Alembic. The tool matters less than using it consistently.

The Process That Works

Every schema change is a separate PR with its own migration. The migration is reviewed like any other code.

In staging, the full migration is applied before merge. In production, it is applied as part of the deploy.

Take backups before significant migrations. Even if you trust the process, insurance exists for a reason.

When to Make Exceptions

New projects with no users can run destructive migrations directly. Caution scales with data criticality.

But the habits you form on small projects are the ones you'll have when the data matters. It's better to practice the correct process every time.