migration-patterns

Migration Patterns

Database Migration Tools

Alembic (Python/SQLAlchemy)

# Initialize
alembic init migrations

# Create migration
alembic revision --autogenerate -m "add users table"

# Apply
alembic upgrade head

# Rollback
alembic downgrade -1

# migrations/versions/001_add_users.py
def upgrade():
    op.create_table(
        "users",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("email", sa.String(255), unique=True, nullable=False),
        sa.Column("created_at", sa.DateTime, server_default=sa.func.now()),
    )
    op.create_index("idx_users_email", "users", ["email"])

def downgrade():
    op.drop_index("idx_users_email")
    op.drop_table("users")

Prisma (TypeScript)

# Create migration
npx prisma migrate dev --name add_users

# Apply in production
npx prisma migrate deploy

# Reset (dev only)
npx prisma migrate reset

Laravel (PHP)

# Create migration
php artisan make:migration create_users_table

# Apply
php artisan migrate

# Rollback
php artisan migrate:rollback --step=1

# Dry run
php artisan migrate --pretend

Django (Python)

# Create migration from models
python manage.py makemigrations

# Apply
python manage.py migrate

# Rollback
python manage.py migrate app_name 0001

# Show plan
python manage.py showmigrations

Flyway (Java/SQL)

flyway migrate
flyway info
flyway undo    # Undo last migration (Teams edition)
flyway repair  # Fix metadata table

Zero-Downtime Migration Strategies

1. Expand-Contract Pattern

Phase 1 (Expand): Add new column, keep old
  ALTER TABLE users ADD COLUMN full_name VARCHAR(200);

Phase 2 (Migrate): Copy data
  UPDATE users SET full_name = first_name || ' ' || last_name;

Phase 3 (Switch): Update code to use new column
  Deploy new code that reads/writes full_name

Phase 4 (Contract): Remove old columns
  ALTER TABLE users DROP COLUMN first_name;
  ALTER TABLE users DROP COLUMN last_name;