Database Migrations: Tools and Best Practices

Database migrations are version-controlled scripts that evolve your database schema over time — enabling teams to apply, track, and rollback changes safely across development, staging, and production environments.

What You’ll Learn

You’ll master migration tools (Flyway, Liquibase, Alembic), versioning strategies, up/down migration patterns, zero-downtime expand-migrate-contract techniques, rollback planning, and testing migrations before production.

Why Database Migrations Matter

Without migrations, schema changes are manual, error-prone, and unrepeatable. Durga Antivirus Pro’s threat database evolves weekly — new signature formats, new columns, new indexes. Migrations ensure every environment (dev, staging, production) applies changes identically, with full audit trail and rollback capability.

Database Migrations Learning Path

    flowchart LR
  A[Database Design] --> B[SQL Query Optimization]
  B --> C[Database Indexing]
  C --> D[Database Migrations]
  D --> E[CI/CD Pipeline Integration]
  D:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px
  

What Is a Database Migration?

A migration is a script that changes the database schema from one version to the next. Each migration has:

• Version identifier (timestamp, sequence number, or semantic version)
• Up script — the changes to apply
• Down script — how to undo those changes

-- V001__create_users.sql (Up)
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- V001__create_users.sql (Down)
DROP TABLE IF EXISTS users;

Migration Tools

Flyway (Java, but works with any JVM language)

Flyway uses SQL files with a specific naming convention:

-- V1__create_users.sql
-- V2__add_orders_table.sql
-- V3__add_index_on_orders_user_id.sql

# Apply all pending migrations
flyway migrate

# Check status
flyway info

# Undo the last migration (requires Flyway Teams or callback hack)
flyway undo

Liquibase (Java, XML/YAML/JSON/SQL formats)

Liquibase offers more flexible changelog formats:

<!-- db/changelog/db.changelog-master.xml -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
    <include file="db/changelog/001-create-users.xml"/>
    <include file="db/changelog/002-create-orders.xml"/>
</databaseChangeLog>

# Apply pending changes
liquibase update

# Generate a rollback script
liquibase rollbackSQL --tag=v1.0

# Rollback to a specific tag
liquibase rollback v1.0

Alembic (Python, for SQLAlchemy)

Alembic auto-generates migration stubs from your model definitions:

# Create a new migration
alembic revision --autogenerate -m "add orders table"

# Apply migrations
alembic upgrade head

# Rollback one step
alembic downgrade -1

"""002_add_orders_table.py"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'orders',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('total', sa.Numeric(10, 2), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
    )
    op.create_foreign_key('fk_orders_users', 'orders', 'users', ['user_id'], ['id'])

def downgrade():
    op.drop_table('orders')

Versioning Strategies

Timestamp-Based (Alembic default)

20250101_create_users.py
20250102_add_orders.py
20250115_add_index.py

Pros: No conflicts when branching. Cons: Hard to track order without looking at dates.

Sequential Numbering (Flyway default)

V001__create_users.sql
V002__add_orders.sql
V003__add_index.sql

Pros: Clear ordering. Cons: Merge conflicts on branches.

Semantic Versioning

v1.0.0__create_users.sql
v1.1.0__add_orders.sql
v1.2.0__add_index.sql

Best for: Library-style databases where version numbers match application releases.

Up/Down Migrations Pattern

Every migration must have a down script. It’s insurance.

-- V002__add_orders_table.sql (Up)
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- V002__add_orders_table.sql (Down)
DROP TABLE IF EXISTS orders;

Critical: Test that up → down → up cycle works without errors. Some changes (like removing a column) are irreversible because dependent code may break.

Zero-Downtime Migrations (Expand-Migrate-Contract)

Zero-downtime migrations follow the expand-migrate-contract pattern to avoid locking tables or breaking running applications.

Phase 1: Expand

Add the new schema elements without removing old ones:

-- Add new column alongside existing ones
ALTER TABLE users ADD COLUMN display_name VARCHAR(100);
CREATE INDEX idx_users_display_name ON users (display_name);

-- Application now writes to both old and new columns
-- Old code still works (it uses the old column)

Phase 2: Migrate

Backfill existing data and update application code:

-- Backfill: populate new column from old data
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Deploy application update that reads from new column
-- Old column is no longer used by application code

Phase 3: Contract

Remove the old schema elements:

-- Remove old column
ALTER TABLE users DROP COLUMN username;

-- Remove unused indexes
DROP INDEX IF EXISTS idx_users_username;

    flowchart LR
    subgraph Expand
        A1[Add new column] --> A2[Deploy app writes to both]
    end
    subgraph Migrate
        B1[Backfill data] --> B2[Deploy app reads from new]
    end
    subgraph Contract
        C1[Remove old column] --> C2[DROP unused indexes]
    end
    Expand --> Migrate --> Contract
  

Rollback Plans

A rollback plan isn’t just the down migration — it’s a playbook:

1. Identify the rollback trigger — What metric tells you something went wrong? (error rate > 1%, latency spike)
2. Execute down migration — Run the down scripts in reverse order
3. Verify rollback — Run data integrity checks
4. Communicate — Notify the team

# Script: rollback-to-version.sh
#!/bin/bash
TARGET_VERSION=${1:-v1.0}
echo "Rolling back to $TARGET_VERSION..."
liquibase rollback "$TARGET_VERSION"
echo "Verifying data integrity..."
psql -d mydb -f verify-integrity.sql
echo "Rollback complete."

Testing Migrations

Never apply a migration to production without testing it first.

Automated Migration Test

"""test_migrations.py"""
import pytest
from alembic.config import Config
from alembic.command import upgrade, downgrade

def test_migration_cycle():
    """Test that all migrations apply and rollback cleanly."""
    config = Config("alembic.ini")
    
    # Apply all migrations
    upgrade(config, "head")
    
    # Rollback to the start
    downgrade(config, "base")
    
    # Re-apply (tests that down->up cycle works)
    upgrade(config, "head")

def test_data_survives_migration():
    """Test that existing data is preserved."""
    # Insert test data
    # Run migrations
    # Verify data still exists
    pass

Pre-Production Checklist

• Migration runs in under 5 seconds per 1M rows (or has a batch strategy)
• Down migration restores schema and data correctly
• No locking queries during peak hours
• Migration is idempotent (safe to run multiple times)
• Application code is backward-compatible with old schema

How Migrations Work in a CI/CD Pipeline

    flowchart TD
    A[Developer creates migration PR] --> B[CI runs migration tests]
    B --> C{Tests pass?}
    C -->|No| D[Fix migration]
    D --> A
    C -->|Yes| E[Review]
    E --> F[Deploy to staging]
    F --> G[Run migrations in staging]
    G --> H[Integration tests]
    H --> I{Tests pass?}
    I -->|No| J[Rollback staging]
    J --> D
    I -->|Yes| K[Merge to main]
    K --> L[Deploy to production]
    L --> M[Run migrations in production]
    M --> N[Monitor]
    N --> O{OK?}
    O -->|No| P[Execute rollback plan]
    O -->|Yes| Q[Done]
  

Common Database Migration Errors

1. Migration Without a Down Script

Up without down is a one-way door. If something goes wrong, you’re stuck. Always write the down script, even if it’s just DROP TABLE.

2. Running Migrations During Peak Traffic

An ALTER TABLE in MySQL can lock the table for minutes on large datasets. Always run schema changes during maintenance windows or use online DDL tools like pt-online-schema-change.

3. Merge Conflicts on Sequential Version Numbers

Two developers create migration V002 at the same time. Use timestamps (Alembic) or reserve version ranges per developer.

4. Irreversible Migrations

-- You can't restore removed data
ALTER TABLE users DROP COLUMN api_key;

Mark irreversible migrations clearly in the code and ensure the application can tolerate the change before applying.

5. Migrating with Active Connections

Some databases block DDL if connections are open on the affected tables. Use SET lock_timeout or schedule during low traffic.

6. Not Testing Down Migrations

The down migration works on your laptop but fails on production because of data differences. Test both directions with production-like data.

7. Forgetting to Run Migrations in Order

Relying on manual ordering instead of a migration tool leads to “run V003 before V002” disasters. Always use a tool that tracks applied versions.

Practice Questions

1. What are the three phases of a zero-downtime migration?

Expand (add new schema), Migrate (backfill + update app), Contract (remove old schema).

2. Why should every migration have a down script?

So you can roll back safely if the migration causes issues in production. Without a down script, the change is irreversible.

3. What’s the difference between Flyway and Alembic?

Flyway uses raw SQL files with sequential naming. Alembic (Python) auto-generates migration stubs from SQLAlchemy models and supports timestamp-based versioning.

4. How do you test a migration before production?

Run migrations on a staging environment with production-like data. Test both up and down paths. Run integration tests after migration.

5. Challenge: Design a migration strategy for a high-traffic API.

Your API serves 10K requests/second and needs to rename a column that’s heavily used in queries. Design the expand-migrate-contract plan. Answer: Expand by adding the new column (no downtime), update application to write to both and read from new, backfill data in batches of 10K rows, then contract by removing the old column during a maintenance window.

FAQ

Try It Yourself

Create a migration for a simple task management database:

1. Initialize Alembic: alembic init alembic
2. Create a migration that adds a tasks table with id, title, completed, user_id
3. Add a second migration that adds a due_date column
4. Test the upgrade head and downgrade -1 cycle
5. Verify data integrity after each step

What’s Next

You now have a complete migration workflow: version control your schema, test both up and down, use expand-migrate-contract for zero-downtime changes, and always have a rollback plan. Apply this to your next schema change and you’ll never make a manual ALTER TABLE again.