Migrating from Legacy Frameworks to Alembic

Understanding Alembic: The Migration Framework for SQLAlchemy

Alembic is a database migration tool designed specifically for SQLAlchemy, the most popular ORM in the Python ecosystem. It provides a robust system for managing incremental, reversible changes to relational database schemas. Unlike ad-hoc migration scripts or legacy framework-specific solutions, Alembic treats database schema changes as version-controlled, testable, and collaborative artifacts. It generates migration files in pure Python, leveraging SQLAlchemy's DDL generation capabilities while giving developers full control over the migration logic.

At its core, Alembic operates on a simple principle: each database change—whether adding a table, altering a column, or creating an index—is captured in a migration revision. These revisions form a linear or branching chain, each identified by a unique GUID and linked to its parent revision. Alembic tracks which revisions have been applied to a given database through a dedicated alembic_version table, enabling idempotent upgrades and rollbacks across development, staging, and production environments.

Why Migrate Away from Legacy Frameworks

🚀 Deploy your AI agent in 10 minutes

Managed Hermes hosting. Zero DevOps. 100M tokens/mo included.

Try it free →

Many Python web frameworks and ORMs ship with their own migration mechanisms. Django has its built-in makemigrations / migrate system; Flask-SQLAlchemy projects often start with manual CREATE TABLE scripts; and older Pyramid or Pylons applications may use sqlalchemy-migrate or even raw SQL files executed by shell scripts. While these approaches work initially, they introduce significant friction as applications grow:

Framework lock-in: Custom migration systems tie your schema management to a specific web framework, making it painful to extract the data layer into separate services or microservices later.
Limited diff capabilities: Legacy approaches often require developers to manually write migration code without automated comparison between the current database state and the desired ORM model state.
Poor collaboration: Ad-hoc migration scripts frequently conflict when multiple developers work on schema changes simultaneously, lacking a formal branching and merging model.
No downgrade support: Many legacy scripts only go forward, leaving no clean path for rolling back a problematic deployment.
Testing gaps: Without a standardized migration API, automated testing of migration paths against real database engines is difficult to implement consistently.

Alembic addresses all of these concerns while remaining framework-agnostic. It works equally well with Flask, FastAPI, Pyramid, standalone SQLAlchemy applications, or even non-ORM SQLAlchemy Core usage. By adopting Alembic, teams decouple schema evolution from application deployment, gain powerful autogeneration tools, and inherit a battle-tested workflow used by thousands of production systems.

Initial Setup: Installing and Configuring Alembic

Begin by installing Alembic in your project's virtual environment. If you are migrating an existing project that already uses SQLAlchemy, you likely have it installed already; Alembic requires SQLAlchemy as its sole hard dependency.

pip install alembic

Once installed, initialize Alembic in your project root. The alembic init command scaffolds a migration environment with a configuration file and a directory structure for revision files.

alembic init alembic

This command creates the following structure:

your_project/
├── alembic/
│   ├── versions/          # Migration revision files live here
│   ├── env.py             # Migration engine configuration
│   └── script.py.mako     # Template for new revision files
├── alembic.ini            # Main configuration file
└── ... (your application code)

The alembic.ini file contains the database connection URL and various configuration options. For an existing project, point the sqlalchemy.url to your current database:

# alembic.ini
[alembic]
sqlalchemy.url = postgresql://user:password@localhost/mydatabase
script_location = alembic
# Additional configuration...

Next, you must connect Alembic to your application's SQLAlchemy metadata. Open the generated alembic/env.py and import your application's Base or MetaData object. This is the critical bridge that enables autogeneration of migrations from your ORM models.

# alembic/env.py
from alembic import context
from sqlalchemy import engine_from_config, pool

# Import your application's declarative base
from myapp.models import Base  # Your project's Base
from myapp.config import settings

# Alembic Config object
config = context.config

# Set up the target metadata for autogeneration
target_metadata = Base.metadata

def run_migrations_offline():
    """Run migrations in 'offline' mode (SQL output only)."""
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online():
    """Run migrations against a live database connection."""
    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()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

For projects that load database URLs dynamically (from environment variables or secrets managers), it is common to override the URL in env.py rather than hardcoding it in alembic.ini. You can do this by modifying the config object before creating the engine:

def run_migrations_online():
    # Override the URL from application settings
    db_url = settings.DATABASE_URL
    config.set_main_option("sqlalchemy.url", db_url)
    
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    # ... rest of the function

Creating the Initial Migration from an Existing Database

When migrating from a legacy framework, you typically already have a populated database with tables, indexes, and constraints that were created by the old migration system or by manual scripts. You need to capture that current state as your baseline in Alembic without disrupting the existing schema. This is where Alembic's stamping feature shines.

First, generate a migration revision that represents the current database schema as if it were the starting point. Use the --autogenerate flag to compare your SQLAlchemy models against a fresh, empty database. Since your existing database already has these tables, you do not want to actually run this migration—you only want to record it as the baseline.

# Generate the baseline migration (do NOT apply it yet)
alembic revision --autogenerate -m "baseline: initial schema from legacy"

This creates a file like alembic/versions/abc123def456_baseline_initial_schema_from_legacy.py with upgrade() and downgrade() functions that would create all your tables from scratch. Now, instead of running alembic upgrade head, you stamp the database with this revision identifier. Stamping tells Alembic "the database is already at this revision" without executing any SQL.

# Stamp the existing database as being at the baseline revision
alembic stamp abc123def456

After stamping, query the alembic_version table to confirm the stamp took effect:

psql mydatabase -c "SELECT * FROM alembic_version;"
# Returns: abc123def456

From this point forward, any new autogenerated migrations will compare your models against the stamped baseline and produce only the incremental changes. This is the cleanest way to onboard an existing database into Alembic's versioning system.

Handling Databases Without an Alembic Version Table

If your legacy database has never been touched by Alembic, the alembic_version table does not exist yet. The alembic stamp command will automatically create it. However, in some locked-down environments where your application database user lacks CREATE TABLE privileges, you may need to have a DBA create the table manually:

CREATE TABLE IF NOT EXISTS alembic_version (
    version_num VARCHAR(32) NOT NULL,
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);
INSERT INTO alembic_version (version_num) VALUES ('abc123def456');

Writing Incremental Migrations After the Baseline

Once the baseline is stamped, you can evolve your schema using Alembic's autogeneration capabilities. Suppose you add a new model to your application:

# myapp/models.py
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String(100), nullable=False, unique=True)
    created_at = Column(DateTime, server_default="now()")

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    body = Column(String, nullable=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    published_at = Column(DateTime, nullable=True)

To capture the addition of the posts table, run autogenerate with a descriptive message:

alembic revision --autogenerate -m "add posts table with foreign key to users"

Alembic inspects the current database (stamped at the baseline) and compares it to the metadata derived from your models. It detects that posts does not exist in the database and generates the corresponding DDL. The resulting migration file looks like this:

"""add posts table with foreign key to users

Revision ID: def789abc012
Revises: abc123def456
Create Date: 2025-06-15 14:30:00.123456
"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic
revision = 'def789abc012'
down_revision = 'abc123def456'
branch_labels = None
depends_on = None

def upgrade():
    op.create_table(
        'posts',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(length=255), nullable=False),
        sa.Column('body', sa.String(), nullable=True),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('published_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['user_id'], ['users.id']),
        sa.PrimaryKeyConstraint('id')
    )

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

Apply the migration with:

alembic upgrade head

To roll back this specific change, use the downgrade command targeting the previous revision:

alembic downgrade abc123def456

This executes op.drop_table('posts') and updates the alembic_version table back to the baseline revision.

Handling Complex Schema Changes That Autogenerate Misses

Alembic's autogeneration is powerful but not omniscient. Certain operations cannot be detected by simple model-to-database comparison and must be written manually. These include:

Renaming a column or table: Alembic sees a removal and an addition, not a rename.
Changing column types with data migration: Converting a string column to an integer requires explicit USING clauses.
Splitting or merging tables: Complex refactors that involve data movement.
Database-specific features: Adding PostgreSQL extensions, custom types, or partial indexes.
Data migrations: Populating new columns based on existing data.

For these cases, create an empty revision and write the migration logic yourself:

alembic revision -m "rename user email column from address to email"

Then edit the generated file to include the appropriate Alembic operations:

"""rename user email column from address to email

Revision ID: ghi012jkl345
Revises: def789abc012
Create Date: 2025-06-20 09:15:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = 'ghi012jkl345'
down_revision = 'def789abc012'

def upgrade():
    # Use alter_column with a rename operation
    op.alter_column('users', 'address', new_column_name='email',
                    existing_type=sa.String(255), existing_nullable=True)

def downgrade():
    op.alter_column('users', 'email', new_column_name='address',
                    existing_type=sa.String(255), existing_nullable=True)

For a type conversion with data preservation, combine multiple operations within a single migration. Here is an example converting a VARCHAR column storing numeric strings into a proper INTEGER column:

"""convert age column from VARCHAR to INTEGER with data migration

Revision ID: mno456pqr789
Revises: ghi012jkl345
Create Date: 2025-07-01 11:00:00.000000
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = 'mno456pqr789'
down_revision = 'ghi012jkl345'

def upgrade():
    # Step 1: Add a temporary column with the desired type
    op.add_column('users', sa.Column('age_int', sa.Integer(), nullable=True))
    
    # Step 2: Migrate data using raw SQL (safe within a transaction)
    op.execute("""
        UPDATE users SET age_int = CAST(age AS INTEGER)
        WHERE age IS NOT NULL AND age ~ '^[0-9]+$'
    """)
    
    # Step 3: Drop the old column
    op.drop_column('users', 'age')
    
    # Step 4: Rename the new column to the original name
    op.alter_column('users', 'age_int', new_column_name='age',
                    existing_type=sa.Integer(), existing_nullable=True)

def downgrade():
    op.add_column('users', sa.Column('age_str', sa.String(10), nullable=True))
    op.execute("""
        UPDATE users SET age_str = CAST(age AS VARCHAR)
        WHERE age IS NOT NULL
    """)
    op.drop_column('users', 'age')
    op.alter_column('users', 'age_str', new_column_name='age',
                    existing_type=sa.String(10), existing_nullable=True)

Using Raw SQL in Migrations

When Alembic's operation API does not support a specific DDL command, use op.execute() to run raw SQL. This is particularly useful for database-specific features like creating PostgreSQL functions, setting up triggers, or managing extensions:

def upgrade():
    op.execute("""
        CREATE OR REPLACE FUNCTION update_updated_at_column()
        RETURNS TRIGGER AS $$
        BEGIN
            NEW.updated_at = NOW();
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    """)
    op.execute("""
        CREATE TRIGGER trigger_users_updated_at
        BEFORE UPDATE ON users
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    """)

def downgrade():
    op.execute("DROP TRIGGER IF EXISTS trigger_users_updated_at ON users")
    op.execute("DROP FUNCTION IF EXISTS update_updated_at_column()")

Integrating Alembic into Your Deployment Pipeline

In a legacy framework migration scenario, you are replacing an old migration system with Alembic. This means updating your deployment scripts, CI/CD pipelines, and potentially your application startup code. Here is a recommended approach for different environments.

Running Migrations Before Application Startup

A common pattern in containerized deployments is to run migrations as part of the application entrypoint. Create a script that upgrades to the latest revision before starting the web server:

#!/usr/bin/env python
# scripts/run_migrations.py
import subprocess
import sys
from myapp.config import settings

def run_migrations():
    """Run pending Alembic migrations before app startup."""
    result = subprocess.run(
        ["alembic", "upgrade", "head"],
        env={"DATABASE_URL": settings.DATABASE_URL},
        capture_output=True,
        text=True
    )
    if result.returncode != 0:
        print(f"Migration failed: {result.stderr}")
        sys.exit(1)
    print(f"Migration output: {result.stdout}")

if __name__ == "__main__":
    run_migrations()

In a Docker entrypoint script, call this before launching the application proper:

#!/bin/bash
# docker-entrypoint.sh
python /app/scripts/run_migrations.py
exec uvicorn myapp.main:app --host 0.0.0.0 --port 8000

Testing Migrations in CI/CD

Automated testing of migrations against a real database engine catches schema problems before they reach production. Set up a CI step that stamps a fresh database with the current head and then runs a full downgrade-upgrade cycle:

# .github/workflows/test_migrations.yml (example snippet)
- name: Test Alembic migrations
  run: |
    # Start a temporary PostgreSQL container
    docker run -d --name pg-test -p 5433:5432 \
      -e POSTGRES_PASSWORD=testpass postgres:16
    
    # Wait for PostgreSQL to be ready
    sleep 5
    
    # Run all migrations to head
    DATABASE_URL="postgresql://postgres:testpass@localhost:5433/testdb" \
      alembic upgrade head
    
    # Downgrade to base (the initial revision)
    DATABASE_URL="postgresql://postgres:testpass@localhost:5433/testdb" \
      alembic downgrade abc123def456
    
    # Upgrade again to head
    DATABASE_URL="postgresql://postgres:testpass@localhost:5433/testdb" \
      alembic upgrade head
    
    # Verify the alembic_version table shows head
    psql $DATABASE_URL -c "SELECT version_num FROM alembic_version;"

This cycle validates that both your upgrade and downgrade paths function correctly and that the schema converges to the expected state.

Branching and Merging Migrations

When multiple developers create migrations against the same parent revision, Alembic handles this by creating branches. Each branch represents an independent line of schema changes. Before merging into a shared branch (like main), you need to resolve the branching by creating a merge revision.

Suppose two developers independently create revisions ghi012 and jkl345, both listing def789 as their down_revision. When you pull both changes, Alembic's history graph looks like a fork:

abc123 (baseline)
  └── def789 (add posts table)
        ├── ghi012 (rename email column)     # Developer A
        └── jkl345 (add profile table)       # Developer B

To merge these branches, use the alembic merge command:

alembic merge ghi012 jkl345 -m "merge email rename and profile table branches"

This creates a new revision that depends on both heads. Its upgrade() and downgrade() functions are typically empty, as the individual migrations already contain the necessary operations:

"""merge email rename and profile table branches

Revision ID: merge_001
Revises: ghi012jkl345, jkl345mno678
Create Date: 2025-07-15 16:00:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = 'merge_001'
down_revision = ('ghi012jkl345', 'jkl345mno678')

def upgrade():
    # Both branches already applied their changes;
    # nothing additional needed for the merge itself.
    pass

def downgrade():
    pass

After merging, the history becomes linear from the perspective of the merge point:

abc123
  └── def789
        ├── ghi012 ──┐
        ├── jkl345 ──┤
        └── merge_001 (depends on both)

Best Practices for Migration Management

Always review autogenerated migrations before applying them. Alembic's diff engine can occasionally produce operations that work but are not optimal. For example, it might generate a DROP TABLE followed by CREATE TABLE instead of an ALTER TABLE when a column type changes. Read the generated code and adjust it to use the most appropriate operation.
Make each migration atomic and focused. A migration should represent one logical schema change. Adding a table, adding a column, and creating an index should ideally be three separate revisions. This makes troubleshooting easier and allows selective rollbacks when needed.
Write meaningful, descriptive revision messages. The -m message becomes part of the revision filename and the docstring. Messages like "add user_id foreign key to orders table" are far more useful than "update schema" when reviewing history months later.
Test downgrade paths religiously. A migration is only complete when its downgrade() function works correctly. In production, the ability to roll back a problematic migration can save hours of downtime. If a downgrade is genuinely impossible (e.g., dropping a column with irrecoverable data), document this clearly and consider raising an exception in downgrade().
Never edit a migration that has been applied to any shared database. Once a revision reaches a staging or production environment, it is immutable. Editing its SQL or changing its revision ID will corrupt the alembic_version history. Always create a new revision to fix mistakes.
Keep alembic.ini out of version control (or use a template with placeholder values). The file contains database credentials. Instead, commit a alembic.ini.example and let each environment supply the actual URL via environment variables or secrets management.
Use conditional migrations for environment-specific features. If your application supports both PostgreSQL and SQLite, use Alembic's dialect inspection to branch within a migration:

def upgrade():
    dialect_name = op.get_context().dialect.name
    
    if dialect_name == 'postgresql':
        op.execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")
    
    # Common operations for all dialects
    op.create_table('audit_logs',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('action', sa.String(255)),
        sa.Column('timestamp', sa.DateTime(), server_default=sa.func.now())
    )
    
    if dialect_name == 'postgresql':
        op.create_index(
            'ix_audit_logs_timestamp_brin',
            'audit_logs',
            ['timestamp'],
            postgresql_using='brin'
        )

Common Pitfalls When Migrating from Legacy Systems

Schema drift between models and database: Legacy frameworks often allowed the database schema to diverge from the ORM model definitions. Before running your first Alembic autogeneration, audit your database against your models. Use alembic check (available in recent versions) or manually compare SHOW CREATE TABLE output against your model definitions.
Missing or mismatched Alembic version table: If multiple developers attempt to stamp the same database independently, the version table can become inconsistent. Designate one person or CI job to perform the initial stamping and communicate the baseline revision ID to the entire team.
Large autogenerated migrations with unintended drops: When your models have changed significantly since the last manual migration, Alembic may generate a revision that drops and recreates tables. Always inspect the generated code carefully. For major refactors, consider writing the migration manually to preserve data.
Transaction boundaries: Some databases (notably MySQL with certain storage engines) do not support transactional DDL. Alembic emits a warning and may need batch mode enabled. Configure this in env.py if your legacy database requires it.
Ignoring the down_revision chain: When manually creating revisions, ensure the down_revision identifier exactly matches the previous head. A broken chain prevents Alembic from computing the upgrade path and produces confusing errors about "multiple heads" or "no head revision."

Migrating Data Alongside Schema Changes

Legacy migration systems often interleaved DDL and DML (data manipulation) in the same script. Alembic supports this pattern through op.execute() and the bulk operations API. When adding a new required column to an existing table, you typically need to populate it with default values before adding the NOT NULL constraint:

"""add status column to orders with backfill

Revision ID: stu789vwx012
Revises: merge_001
Create Date: 2025-08-01 08:00:00.000000
"""
from alembic import op
import sqlalchemy as sa

revision = 'stu789vwx012'
down_revision = 'merge_001'

def upgrade():
    # Step 1: Add as nullable first
    op.add_column('orders', sa.Column('status', sa.String(20), nullable=True))
    
    # Step 2: Backfill existing rows
    op.execute("""
        UPDATE orders SET status = 'pending'
        WHERE status IS NULL
    """)
    
    # Step 3: Add the NOT NULL constraint
    op.alter_column('orders', 'status',
                    existing_type=sa.String(20),
                    nullable=False,
                    server_default='pending')

def downgrade():
    op.drop_column('orders', 'status')

For bulk data transformations involving millions of rows, consider batching the updates to avoid long-running transactions that lock tables:

def upgrade():
    op.add_column('events', sa.Column('processed_at', sa.DateTime(), nullable=True))
    
    # Batch update in chunks to avoid excessive locking
    op.execute("""
        UPDATE events SET processed_at = created_at
        WHERE processed_at IS NULL AND id IN (
            SELECT id FROM events WHERE processed_at IS NULL LIMIT 10000
        )
    """)
    # Repeat as needed in a loop; in practice, use a Python loop
    # with op.execute() inside a migration that runs multiple batches.

Working with Multiple Database Environments

Projects often need to manage migrations across development, staging, and production databases. Alembic's configuration supports multiple environments through sections in alembic.ini or by overriding values programmatically in env.py. A clean approach for cloud-native applications is to supply the database URL entirely through environment variables and keep alembic.ini free of credentials:

# alembic/env.py (multi-environment version)
from alembic import context
from sqlalchemy import create_engine
import os

def get_database_url():
    """Resolve database URL from environment, with fallback."""
    return os.environ.get(
        "DATABASE_URL",
        "postgresql://localhost/mydb_dev"
    )

def run_migrations_online():
    engine = create_engine(get_database_url())
    with engine.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=Base.metadata,
            # Compare types more strictly in production
            compare_type=True,
        )
        with context.begin_transaction():
            context.run_migrations()

# Offline mode can generate SQL scripts for DBA review
def run_migrations_offline():
    context.configure(
        url=get_database_url(),
        target_metadata=Base.metadata,
        literal_binds=True,
    )
    with context.begin_transaction():
        context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

To generate a SQL script for review before applying to production, use offline mode:

DATABASE_URL="postgresql://prod_host/proddb" \
  alembic upgrade head --sql > migration_script.sql

This produces a complete SQL file that a DBA can review and apply manually if your deployment policy requires it.

Conclusion

Migrating from legacy framework migration systems to Alembic is an investment in long-term maintainability, collaboration, and deployment safety. The process centers on establishing a clean baseline that captures your existing database state, then leveraging Alembic's autogeneration, stamping, and branching features to manage every subsequent schema change with precision. By decoupling schema management from any particular web framework, you gain the flexibility to refactor your application architecture, extract services, or adopt new frameworks without rethinking your database evolution strategy.

The key to a successful migration lies in careful preparation: auditing your current schema against your models, setting up the env.py bridge correctly, stamping the baseline revision on all environments consistently, and integrating migration execution into your deployment pipeline with thorough automated testing. Once Alembic is in place, you inherit a rich ecosystem of operations, robust autogeneration, and a clear version history that transforms database schema management from a source of anxiety into a controlled, repeatable engineering practice.