Alembic Migrations

Difficulty: Advanced

Alembic is the database migration tool for SQLAlchemy. While SQLAlchemy's create_all() can create tables from scratch, it cannot modify existing tables. Alembic tracks schema changes over time, creating versioned migration scripts that can upgrade or downgrade the database schema. This is essential for production applications where the database schema evolves.

Alembic works by comparing your SQLAlchemy models to the current database schema and generating migration scripts for the differences. Each migration is a Python file with upgrade() and downgrade() functions. The upgrade function applies the change (like adding a column), and the downgrade function reverses it.

The setup process begins with alembic init alembic, which creates an alembic/ directory with configuration files. You configure the database URL in alembic.ini or env.py, and point Alembic to your SQLAlchemy Base metadata so it can detect model changes.

To create a migration, run alembic revision --autogenerate -m "description". Alembic compares your models to the database and generates the migration script. Always review the generated script before running it - autogenerate handles most cases but can miss some changes (like column renames or data migrations).

To apply migrations, run alembic upgrade head (upgrade to the latest version) or alembic upgrade +1 (upgrade one version). To roll back, use alembic downgrade -1 (go back one version) or alembic downgrade base (roll back all migrations). Each migration is tracked in an alembic_version table in the database.

In a team environment, migrations should be committed to version control. When pulling changes from other developers, run alembic upgrade head to apply their migrations. If two developers create migrations simultaneously, one may need to be rebased (alembic merge to create a merge migration).

For production deployments, run migrations as part of the deployment process, before starting the new application version. Many teams include alembic upgrade head in their deployment scripts or Docker entrypoints.

Code examples

Alembic Setup and Configuration

# Terminal commands:
# pip install alembic
# alembic init alembic

# alembic/env.py - Configure the target metadata
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

# Import your models' Base
from app.database import Base
from app.models import User, Post  # Import all models

config = context.config
fileConfig(config.config_file_name)

# Set the target metadata for autogenerate
target_metadata = Base.metadata

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
        )
        with context.begin_transaction():
            context.run_migrations()

run_migrations_online()

# alembic.ini - Set the database URL
# sqlalchemy.url = postgresql://user:pass@localhost/mydb

env.py tells Alembic about your models via target_metadata. All model modules must be imported so Alembic can see them. The database URL is configured in alembic.ini or can be set programmatically in env.py.

Creating and Running Migrations

# Create initial migration
# alembic revision --autogenerate -m "create users table"

# Generated migration file: alembic/versions/abc123_create_users_table.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('username', sa.String(50), nullable=False),
        sa.Column('email', sa.String(100), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
    )
    op.create_index('ix_users_username', 'users', ['username'])

def downgrade():
    op.drop_index('ix_users_username', table_name='users')
    op.drop_table('users')

# Run migrations
# alembic upgrade head     # Apply all pending migrations
# alembic upgrade +1       # Apply next migration
# alembic downgrade -1     # Rollback last migration
# alembic downgrade base   # Rollback all migrations
# alembic current          # Show current revision
# alembic history          # Show migration history

Autogenerate creates upgrade and downgrade functions. upgrade() creates the table with columns, constraints, and indexes. downgrade() reverses everything. Always review autogenerated scripts before running them.

Common Migration Operations

from alembic import op
import sqlalchemy as sa

# Add a column
def upgrade():
    op.add_column('users', sa.Column('bio', sa.String(500), nullable=True))

def downgrade():
    op.drop_column('users', 'bio')

# Rename a column (not auto-detected!)
def upgrade():
    op.alter_column('users', 'username', new_column_name='name')

def downgrade():
    op.alter_column('users', 'name', new_column_name='username')

# Add an index
def upgrade():
    op.create_index('ix_users_email', 'users', ['email'], unique=True)

def downgrade():
    op.drop_index('ix_users_email', table_name='users')

# Data migration (modify data, not just schema)
def upgrade():
    # Add column
    op.add_column('users', sa.Column('is_active', sa.Boolean(), server_default='true'))
    # Update existing rows
    op.execute("UPDATE users SET is_active = true")

def downgrade():
    op.drop_column('users', 'is_active')

Common operations include adding/dropping columns, renaming columns (manual, not auto-detected), creating indexes, and data migrations. Always pair upgrade() with a corresponding downgrade() for reversibility.

Key points

Concepts covered

Alembic, Migrations, Schema Changes, Revision, Upgrade, Downgrade