Database Migration Best Practices for Production Systems

Why Database Migrations Are Scary

Database migrations in production are terrifying because they are irreversible in ways that code deployments are not. You can roll back a bad code deployment in seconds with a container image swap. Rolling back a database migration that dropped a column, transformed data types, or restructured tables is dramatically harder — sometimes impossible without data loss.

Yet migrations are inevitable. Business requirements evolve, schemas need to change, and data models that were perfect six months ago need updating. The question is not whether to migrate — it is how to migrate safely.

Zero-Downtime Migration Principles

The golden rule: never make a breaking change in a single deployment. Instead, split every breaking migration into multiple non-breaking steps:

Adding a Column

This is the simplest migration. Add the column with a default value or allow NULL. Deploy code that writes to the new column. Backfill existing rows. Deploy code that reads from the new column. This can be done in a single deployment since adding a nullable column does not break existing queries.

Removing a Column

This requires two deployments:

1. Deploy 1: Remove all code references to the column. The column still exists in the database but nothing reads from or writes to it.

2. Deploy 2: Run the migration to drop the column.

If you drop the column first, any running server instance that references it will throw errors during the deployment window.

Renaming a Column

Never rename directly. Instead:

1. Add the new column

2. Deploy code that writes to both old and new columns

3. Backfill the new column from the old column

4. Deploy code that reads from the new column

5. Deploy code that stops writing to the old column

6. Drop the old column

Yes, this is six steps for a rename. That is the cost of zero downtime.

Versioned Migrations with Modern ORMs

Drizzle ORM

Drizzle Kit generates SQL migration files from your schema changes. Run drizzle-kit generate to create a numbered migration file. Run drizzle-kit migrate to apply pending migrations. Each migration is tracked in a drizzle migrations table.

Drizzle's approach is SQL-first — you can inspect and modify the generated SQL before applying it. This is valuable for production systems where you need to review exactly what will run.

Prisma

Prisma Migrate generates migrations from changes to your schema.prisma file. Run npx prisma migrate dev to create and apply migrations in development. Run npx prisma migrate deploy in production to apply pending migrations without generating new ones.

Prisma tracks migrations in a _prisma_migrations table and prevents drift between your schema file and the database.

Rollback Strategies

Every migration should have a corresponding rollback plan:

Automated Rollback Scripts

For every migration file, create a corresponding down migration. Drizzle and Prisma both support this pattern. In CI/CD, test that applying and then rolling back a migration leaves the database in its original state.

Data Backfill Patterns

Large table backfills can lock rows and degrade performance. Use batched updates:

Testing Migrations

Database migrations do not have to be scary. They just have to be planned. Want help with a complex migration? Reach out.