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.