Database Migrations: Version Your Schema Safely

Database migrations are versioned scripts that evolve your database schema over time. Without them, you are left with a shared “just run this SQL” document, developers with out-of-sync local databases, and deployments that require manual schema changes. Migrations solve all of these problems by treating your schema as code.

The problem migrations solve

Consider a team of three developers working on the same project. Developer A adds a phone_number column to the users table. Developer B adds an orders table. Without migrations, how does Developer C get both changes? How does the CI server know what schema to use for tests? How does production get updated?

Migrations give you a sequential history of schema changes that can be applied and rolled back automatically.

migrations/
├── 001_create_users_table.sql
├── 002_add_phone_to_users.sql
├── 003_create_orders_table.sql
├── 004_add_index_on_orders_user_id.sql
└── 005_add_status_to_orders.sql

The migration tool tracks which migrations have been applied in a metadata table and runs only the ones that have not.

Migration tools by ecosystem

Alembic (Python/SQLAlchemy)

Alembic is the standard migration tool for SQLAlchemy projects. It can auto-generate migrations by comparing your models to the current database state.

# Initialize Alembic
alembic init migrations

# Generate a migration from model changes
alembic revision --autogenerate -m "add phone_number to users"

# Apply all pending migrations
alembic upgrade head

# Roll back one migration
alembic downgrade -1

A generated migration looks like this.

"""add phone_number to users

Revision ID: a1b2c3d4e5f6
Revises: 9z8y7x6w5v4u
Create Date: 2026-07-07 10:30:00.000000
"""
from alembic import op
import sqlalchemy as sa

# revision identifiers
revision = "a1b2c3d4e5f6"
down_revision = "9z8y7x6w5v4u"

def upgrade():
    op.add_column(
        "users",
        sa.Column("phone_number", sa.String(20), nullable=True),
    )

def downgrade():
    op.drop_column("users", "phone_number")

Flyway (Java/JVM)

Flyway uses plain SQL files with a naming convention. No ORM integration is needed.

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total_cents INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- V4__add_shipping_address_to_orders.sql
ALTER TABLE orders
    ADD COLUMN shipping_address_line1 VARCHAR(255),
    ADD COLUMN shipping_address_line2 VARCHAR(255),
    ADD COLUMN shipping_city VARCHAR(100),
    ADD COLUMN shipping_postal_code VARCHAR(20),
    ADD COLUMN shipping_country CHAR(2);

Flyway applies migrations in version order and tracks them in a flyway_schema_history table.

Knex (Node.js)

Knex provides a JavaScript/TypeScript API for writing migrations.

// migrations/20260707_create_orders.js
exports.up = function (knex) {
  return knex.schema.createTable("orders", (table) => {
    table.increments("id").primary();
    table.integer("user_id").unsigned().notNullable().references("users.id");
    table.integer("total_cents").notNullable();
    table.string("status", 20).notNullable().defaultTo("pending");
    table.timestamps(true, true);

    table.index("user_id");
    table.index("status");
  });
};

exports.down = function (knex) {
  return knex.schema.dropTable("orders");
};

# Create a new migration
npx knex migrate:make add_shipping_to_orders

# Run pending migrations
npx knex migrate:latest

# Roll back the last batch
npx knex migrate:rollback

Writing safe migrations

Always write rollback logic

Every migration should have a corresponding downgrade. This is your safety net when a deployment goes wrong.

# Good: reversible migration
def upgrade():
    op.create_table(
        "products",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("price_cents", sa.Integer, nullable=False),
        sa.Column("sku", sa.String(50), unique=True, nullable=False),
    )

def downgrade():
    op.drop_table("products")

Some migrations are not safely reversible. Dropping a column destroys data that cannot be recovered from the migration alone. Document this clearly.

def upgrade():
    op.drop_column("users", "legacy_avatar_url")

def downgrade():
    # WARNING: Data loss - original values cannot be restored
    op.add_column(
        "users",
        sa.Column("legacy_avatar_url", sa.Text, nullable=True),
    )

Make migrations atomic

Each migration should either fully succeed or fully fail. Do not combine unrelated changes.

# Bad: two unrelated changes in one migration
def upgrade():
    op.add_column("users", sa.Column("bio", sa.Text))
    op.create_table("audit_logs", ...)  # Unrelated

# Good: separate migrations for separate concerns
# migration_007_add_user_bio.py
def upgrade():
    op.add_column("users", sa.Column("bio", sa.Text))

# migration_008_create_audit_logs.py
def upgrade():
    op.create_table("audit_logs", ...)

Handle data migrations carefully

Schema migrations change structure. Data migrations change content. Keep them separate and be cautious about performance.

# migration_010_backfill_user_display_name.py
"""
Backfill display_name from first_name + last_name.
Run AFTER migration_009 which adds the display_name column.
"""
from alembic import op

def upgrade():
    # Process in batches to avoid locking the table
    op.execute("""
        UPDATE users
        SET display_name = CONCAT(first_name, ' ', last_name)
        WHERE display_name IS NULL
        AND id BETWEEN 1 AND 10000
    """)
    op.execute("""
        UPDATE users
        SET display_name = CONCAT(first_name, ' ', last_name)
        WHERE display_name IS NULL
        AND id BETWEEN 10001 AND 20000
    """)
    # Continue for remaining batches...

def downgrade():
    op.execute("UPDATE users SET display_name = NULL")

Zero-downtime migrations

In production, you cannot take the database offline to run migrations. Your application is serving traffic while the schema changes. This requires a specific approach.

The expand-and-contract pattern

Instead of renaming a column directly (which breaks running application code), you expand first, migrate data, then contract.

Step 1: Expand - Add the new column alongside the old one.

# Migration 1: Add new column
def upgrade():
    op.add_column("users", sa.Column("email_address", sa.String(255)))

Step 2: Dual write - Deploy application code that writes to both columns.

# Application code during transition
class UserService:
    def update_email(self, user_id: int, email: str):
        db.execute(
            "UPDATE users SET email = %s, email_address = %s WHERE id = %s",
            (email, email, user_id),
        )

Step 3: Backfill - Copy existing data from old column to new.

# Migration 2: Backfill data
def upgrade():
    op.execute("UPDATE users SET email_address = email WHERE email_address IS NULL")

Step 4: Switch reads - Deploy code that reads from the new column.

Step 5: Contract - Drop the old column once all code uses the new one.

# Migration 3: Remove old column (deploy weeks later)
def upgrade():
    op.drop_column("users", "email")

Adding NOT NULL constraints safely

You cannot add a NOT NULL constraint to an existing column if it contains NULL values. And adding a default value while adding the constraint can lock the table on large datasets.

# Safe NOT NULL addition in PostgreSQL
def upgrade():
    # Step 1: Add column as nullable with a default
    op.add_column(
        "orders",
        sa.Column("currency", sa.String(3), nullable=True, server_default="USD"),
    )

    # Step 2: Backfill existing rows
    op.execute("UPDATE orders SET currency = 'USD' WHERE currency IS NULL")

    # Step 3: Add NOT NULL constraint
    op.alter_column("orders", "currency", nullable=False)

Adding indexes without locking

On PostgreSQL, CREATE INDEX locks the table for writes. Use CONCURRENTLY to avoid this.

from alembic import op

def upgrade():
    # This does NOT lock the table
    op.execute(
        "CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at)"
    )

def downgrade():
    op.execute("DROP INDEX CONCURRENTLY idx_orders_created_at")

Note that CREATE INDEX CONCURRENTLY cannot run inside a transaction. In Alembic, you need to disable transaction wrapping for this migration.

# At the top of the migration file
# Disable transaction for this migration
from alembic import context

def run_migrations_offline():
    pass

def run_migrations_online():
    pass

# In env.py, configure:
# context.configure(transaction_per_migration=True)

# Or in the migration itself:
from alembic import op

def upgrade():
    op.execute("COMMIT")  # End the current transaction
    op.execute(
        "CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at)"
    )

Team workflow

Migration conflicts

When two developers create migrations at the same time, they can have conflicting down_revision pointers (both point to the same parent). Most tools detect this.

# Alembic will tell you about branch points
alembic heads
# If you see multiple heads, merge them:
alembic merge -m "merge migration branches" head1 head2

Prevention strategy: use a linear migration naming scheme (timestamps) and resolve conflicts in code review before merging.

CI/CD integration

Run migrations as part of your deployment pipeline, before the new application code starts.

# GitHub Actions deployment
deploy:
  steps:
    - name: Run database migrations
      run: |
        alembic upgrade head
      env:
        DATABASE_URL: ${{ secrets.DATABASE_URL }}

    - name: Deploy application
      run: |
        # Deploy new code only after migrations succeed
        ./deploy.sh

    - name: Verify migration
      run: |
        alembic current

Testing migrations

Test that your migrations work in both directions.

# tests/test_migrations.py
import pytest
from alembic.config import Config
from alembic import command

@pytest.fixture
def alembic_config(test_database_url):
    config = Config("alembic.ini")
    config.set_main_option("sqlalchemy.url", test_database_url)
    return config

def test_migrations_up_and_down(alembic_config):
    """Verify all migrations can be applied and rolled back."""
    # Apply all migrations
    command.upgrade(alembic_config, "head")

    # Roll back all migrations
    command.downgrade(alembic_config, "base")

    # Apply again to make sure nothing is broken
    command.upgrade(alembic_config, "head")

Wrapping Up

Database migrations bring the same discipline to your schema that version control brings to your code. Use them from day one, even on solo projects. Write reversible migrations, keep schema and data changes separate, and use the expand-and-contract pattern for zero-downtime deployments. The upfront effort is small compared to the pain of manually coordinating schema changes across environments and team members.