Database Migration Tools and Strategies

Why Database Migrations Matter

Database migrations are how you version-control your schema changes. Without a migration system, schema changes are applied manually, untracked, and unrepeatable. Migrations ensure that every environment (dev, staging, production) has the same schema, changes are auditable, and rollbacks are possible.

Migration Tools by Language

| Language | Tool | Pros | |----------|------|------| | Python | Alembic (SQLAlchemy) | Auto-generation, async support | | Node.js | Knex.js | Transactional migrations, seed support | | Ruby | ActiveRecord Migrations | Simple DSL, mature ecosystem | | Java | Flyway, Liquibase | Repeatable migrations, CI/CD friendly | | Go | golang-migrate, Goose | No-dependency binaries | | Rust | Diesel | Type-safe, compile-time checking |

Alembic (Python)

Setup

pip install alembic

alembic init alembic

# alembic/env.py

from myapp.models import Base

target_metadata = Base.metadata

Creating Migrations

# Auto-generate migration

alembic revision --autogenerate -m "add user roles table"

# Apply migrations

alembic upgrade head

# Rollback

alembic downgrade -1

Migration File

"""add user roles table

Revision ID: abc123

Revises: def456

"""

from alembic import op

import sqlalchemy as sa

def upgrade():

op.create_table(

'user_roles',

sa.Column('id', sa.Integer(), nullable=False),

sa.Column('user_id', sa.Integer(), nullable=False),

sa.Column('role', sa.String(length=50), nullable=False),

sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),

sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),

sa.PrimaryKeyConstraint('id')

)

op.create_index('ix_user_roles_user_id', 'user_roles', ['user_id'])

def downgrade():

op.drop_index('ix_user_roles_user_id')

op.drop_table('user_roles')

Flyway (Java)

# Migration naming convention:

# V1__create_users.sql

# V2__add_email_column.sql

# V3__create_orders_table.sql

# Apply migrations

flyway migrate

# Check status

flyway info

# Repair checksums

flyway repair

-- V2__add_email_column.sql

ALTER TABLE users ADD COLUMN email VARCHAR(255) UNIQUE;

-- V3__create_orders_table.sql

CREATE TABLE orders (

id BIGSERIAL PRIMARY KEY,

user_id BIGINT NOT NULL REFERENCES users(id),

total DECIMAL(10,2) NOT NULL,

status VARCHAR(20) NOT NULL DEFAULT 'pending',

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

CREATE INDEX idx_orders_user_id ON orders(user_id);

Strategies for Complex Migrations

Expand-Contract Pattern (Zero Downtime)

For making backward-incompatible changes without downtime:

**Phase 1: Expand**

-- Add new column alongside old one

ALTER TABLE users ADD COLUMN email_new VARCHAR(255);

-- Start writing to both columns

-- Backfill existing rows

UPDATE users SET email_new = email WHERE email_new IS NULL;

**Phase 2: Migrate**

# Application reads from new column, still writes to both

# Deploy application update

**Phase 3: Contract**

-- Stop writing to old column

ALTER TABLE users DROP COLUMN email;

-- Or rename

ALTER TABLE users RENAME COLUMN email TO email_old;

Batch Migrations for Large Tables

"""migrate large table in batches"""

from sqlalchemy import text

def upgrade():

batch_size = 10000

offset = 0

while True:

result = op.execute(

text("""

UPDATE users

SET email_lower = LOWER(email)

WHERE id IN (

SELECT id FROM users

WHERE email_lower IS NULL

ORDER BY id

LIMIT :batch_size

)

RETURNING id

"""),

{'batch_size': batch_size}

)

if result.rowcount == 0:

break

offset += batch_size

print(f"Updated {offset} rows...")

Online Schema Change (pt-online-schema-change)

# For MySQL without downtime

pt-online-schema-change \

--alter "ADD COLUMN email VARCHAR(255)" \

D=database,t=users \

--execute

Migration Best Practices

Always Have a Rollback

Every migration must have a tested downgrade path:

def upgrade():

op.add_column('users', sa.Column('email', sa.String(255)))

def downgrade():

op.drop_column('users', 'email')

Test Migrations

# Test upgrade and downgrade

alembic upgrade head

alembic downgrade base

alembic upgrade head

CI/CD Integration

# .github/workflows/migrate.yml

jobs:

migrate:

runs-on: ubuntu-latest

steps:

- uses: actions/checkout@v4

- name: Run migrations

env:

DATABASE_URL: ${{ secrets.DATABASE_URL }}

run: |

alembic upgrade head

- name: Verify schema

run: |

alembic check # Detect schema drift

Common Mistakes

| Mistake | Consequence | Prevention | |---------|-------------|------------| | No rollback plan | Cannot undo failed migration | Always write downgrade | | Long-running locks | Table locked, queries fail | Use batch/online migrations | | Missing default values | NULL violations in existing rows | Add with default or nullable first | | Schema drift | Environments out of sync | Automate migrations, check in CI | | No data migration | New columns are empty | Backfill after schema change |

Summary

Database migrations bring the same version control discipline to schema changes that Git brings to code. Use tools like Alembic or Flyway, always write reversible migrations with upgrade and downgrade paths, adopt the expand-contract pattern for zero-downtime changes, batch large table modifications, and integrate migrations into your CI/CD pipeline. Test every migration against a copy of production data before deploying to production.