Database Migrations | App Dev Guide

Database migrations are version-controlled scripts that change the schema. Add a column, rename a table, create an index—each change is a migration. Migrations live in git alongside your code. Every developer, staging, and production run the same migrations in order, keeping schemas in sync.

Why Migrations Matter

Without migrations, developers modify the schema manually through database GUIs or by running ad-hoc SQL. One developer runs a script that another doesn't know about. Staging and production diverge. A colleague joins and has no idea what schema changes were made. Migrations solve this—all changes are tracked, versioned, and reproducible.

Migrations are essential for team development. Every developer runs migrations and stays in sync. Migrations are essential for deployment—production knows exactly which version of the schema it should have.

The Migration Pattern

Each migration is a timestamped file. 20240101120000_add_users_table.sql. Inside are two parts: up and down. The up function applies the change. The down function reverts it.

Running all migrations up to a point applies all changes. Rolling back runs the down functions in reverse order, undoing changes. This versioning enables rolling back problematic migrations if they cause production issues.

Migration Tools

Prisma Migrate is integrated with Prisma. Change your schema file, run prisma migrate dev, and Prisma creates a migration and applies it. Flyway and Liquibase are database-agnostic, supporting PostgreSQL, MySQL, Oracle. Alembic is Python's standard. Each tool has different philosophies, but all follow the same pattern.

Running Migrations Safely

Always take a backup before running migrations in production. Run migrations on staging first and verify they work. Test the application against the new schema.

Some migrations are risky. Renaming a column breaks application code that reads the old column name. Changing a column type can fail if existing data doesn't fit the new type. Test these carefully on staging before production.

Prefer additive changes when possible. Adding a column is safe. Removing a column risks breaking application code that expects it. Renaming a column is risky because the application must be updated in lockstep.

Zero-Downtime Migrations

For frequently-used applications, you can't take downtime for schema changes. Zero-downtime migrations require a multi-step approach:

Add the new column
Backfill data in the new column
Update application code to write to the new column while reading both old and new
Deploy and monitor
Update code to read only the new column
Deploy
Drop the old column in a future migration

This sounds tedious because it is. But it ensures zero downtime. For less critical changes, a brief maintenance window is acceptable.

Migration Coordination

Migrations must run in order. Migration 001 creates a table. Migration 002 adds a column. If you run 002 before 001, it fails. Git ensures everyone sees migrations in the same order. Merge conflicts on migrations are serious—two developers created incompatible migrations. Resolve carefully.

Never Edit Committed Migrations

Once a migration has run in production, never edit it. If you need to undo a change, create a new migration. Editing a committed migration breaks reproducibility—production and development have run different SQL.

If you realized you made a mistake in a migration before it ships to production, it's OK to edit it locally. Once it's in production, create a new migration to fix it.

Automated Migration Running

Most deployment systems automatically run migrations. Deploy code, then run pending migrations, then verify the application works with the new schema. This is safer than manual migration application.

Some deployment platforms, like Vercel, run migrations automatically during deployments. Others require manual intervention. Understand your deployment process and how migrations fit into it.

Testing Migrations

Test migrations on real data when possible. A migration that works on an empty database might fail on production data with thousands of records, foreign key constraints, and unique constraints. Run migrations on a copy of production data to catch problems early.

Warning

If a migration fails in production, you're in a bad state. The database is partially migrated. Rolling back might fail if rollback migrations have bugs. Always test migrations on production data before deploying to production. A staging environment is invaluable.

Tip

Write migrations to be idempotent. CREATE TABLE IF NOT EXISTS instead of CREATE TABLE. This makes migrations safe to run multiple times without errors. Some tools do this automatically; others require you to be explicit.