Database Schema Migration Best Practices

Why Schema Migrations Fail

Database schema migrations are among the highest-risk operations in software deployments. A poorly written migration can lock tables for minutes, corrupt data, or create an inconsistent state that's difficult to diagnose. Yet migrations are unavoidable — every product evolves, and so must its schema. The goal isn't to avoid migrations, but to make them safe, reversible, and boring.

The Three Rules of Safe Migrations

1. Never delete before deprecating — remove old columns only after you've verified no code reads them
2. Make changes backward compatible — the new schema must work with the current running application version
3. Always have a rollback — every migration needs a corresponding down migration

Schema Diffing: See What Changed

Before writing a migration, generate a diff between your current schema and the target state. This makes the migration explicit and reviewable:

# Generate schema dumps
mysqldump --no-data production_db > schema_before.sql
mysqldump --no-data staging_db > schema_after.sql

# Compare
diff -u schema_before.sql schema_after.sql

Paste both into DiffChecker Pro's SQL diff mode to get syntax-highlighted comparison. This lets your team review the schema change in a PR comment without needing database access.

Zero-Downtime Migration Patterns

Adding a Column (Safe)

-- Safe: adding a nullable column never blocks
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(500) NULL;

-- Then backfill in batches (never a single UPDATE on millions of rows)
UPDATE users SET avatar_url = generate_avatar(email)
WHERE id BETWEEN 1 AND 10000;

Adding a NOT NULL Column (Risky Without Care)

-- Step 1: Add as nullable
ALTER TABLE orders ADD COLUMN region VARCHAR(50) NULL;

-- Step 2: Backfill
UPDATE orders SET region = 'us-east-1' WHERE region IS NULL;

-- Step 3: Add constraint only after backfill is complete
ALTER TABLE orders MODIFY COLUMN region VARCHAR(50) NOT NULL DEFAULT 'us-east-1';

Renaming a Column (Multi-Phase)

Column renames are the most dangerous operation because they break all running instances of your application simultaneously. Use a multi-phase approach:

1. Add the new column: ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
2. Deploy code that writes to both columns
3. Backfill the new column from the old one
4. Deploy code that reads from the new column only
5. Drop the old column

Index Migrations

Adding indexes on large tables blocks writes in MySQL by default. Use ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE (MySQL 5.6+) or CREATE INDEX CONCURRENTLY (PostgreSQL) to add indexes without locking:

-- MySQL: Online DDL
ALTER TABLE events ADD INDEX idx_user_created (user_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- PostgreSQL: Concurrent index creation
CREATE INDEX CONCURRENTLY idx_events_user_created
  ON events (user_id, created_at);

Migration Tooling

Use a migration framework rather than raw SQL scripts. Good options:

• Laravel Migrations — PHP-native, supports up/down, integrates with Eloquent
• Flyway — Language-agnostic, versioned SQL files, excellent for enterprises
• Liquibase — XML/YAML/JSON format, powerful rollback support
• Alembic — Python/SQLAlchemy ecosystem
• Prisma Migrate — TypeScript-first, auto-generates migrations from schema diff

Reviewing Migrations in Code Review

Every migration file should be reviewed as carefully as application code. In your PR description, include:

• The estimated execution time on production data volumes
• Whether the migration requires downtime
• The rollback procedure
• A link to the schema diff (generated with DiffChecker Pro)

Use DiffChecker Pro to paste the before/after schema and share the link in your PR — reviewers can see exactly what changes without needing database access.

Testing Migrations

# Test up migration
php artisan migrate

# Test rollback
php artisan migrate:rollback

# Test on production-sized data (critical!)
mysqldump production_db | mysql test_db
php artisan migrate --database=test