Alembic Migrations — Generate, Apply, Rollback

Mental Model

Think of Alembic's autogenerate as a helpful but naive assistant. It sees that a table with a certain name is gone and a new one with a different name appeared, but it doesn't infer intent. It needs explicit instructions from you for complex operations like renames.

Rule: Never apply autogenerated migrations to production without first reviewing the output and manually replacing destructive drop/create commands with appropriate rename operations.

The Setup

You are renaming a central database table from user_profiles to member_profiles. You run Alembic's --autogenerate command expecting a safe migration script.

What Does This Print?

⚠ Broken code

Python

# This is a conceptual representation of a migration code autogenerated
# by Alembic when renaming a table from "user_profiles" to "member_profiles".

def upgrade():
    # Alembic's autogenerate detects this as a DROP and a CREATE!
    # If executed, this drops the old table with all production data.
    op.drop_table('user_profiles')
    op.create_table(
        'member_profiles',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('bio', sa.String(), nullable=True)
    )

def downgrade():
    op.drop_table('member_profiles')
    op.create_table(
        'user_profiles',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('bio', sa.String(), nullable=True)
    )

Think about what happens when this autogenerated script is run on your production database. Will your users' profile bios survive the upgrade?

The Output

What actually happens

CRITICAL INFO: Table 'user_profiles' dropped. CRITICAL INFO: Table 'member_profiles' created. (All data previously stored in 'user_profiles' is permanently lost!)

Alembic's autogenerate feature works by comparing the current metadata objects in your Python environment with the actual tables defined in the database schema. Because it lacks historical context, it cannot infer that a new table definition matches an old dropped table. It views this as two discrete events: a dropped table and an entirely new table. Executing this file in production will drop the source table and delete all associated production data.

Why Python Does This

Alembic's comparison engine runs during alembic revision --autogenerate. Internally, it relies on SQLAlchemy's MetaData.reflect() API. Reflection reads database catalogs (like pg_catalog in PostgreSQL) to construct model representations. Since the model rename happens in your Python code as an edit, and in the database as a missing schema, Alembic compares the list of existing tables. Finding that user_profiles exists in DB but not in metadata, it calls drop_table. Finding member_profiles exists in metadata but not in DB, it calls create_table. It has no internal mechanism to match columns and deduce renames. Developers must manually edit the autogenerated migration scripts to use explicit rename operations like rename_table and alter_column.

The Fix

✓ Corrected pattern

Python

# Correct: Manually refactored migration file using Alembic commands
# to rename the table without dropping data.

def upgrade():
    # FIX: Use op.rename_table to preserve the underlying database partition/data
    op.rename_table('user_profiles', 'member_profiles')
    
    # If indexes or foreign keys exist, rename them manually as well:
    # op.execute('ALTER INDEX user_profiles_pkey RENAME TO member_profiles_pkey;')

def downgrade():
    # FIX: Reverse rename_table to restore state safely
    op.rename_table('member_profiles', 'user_profiles')

Manually editing the migration script to use op.rename_table() directly instructs the database to change the name of the existing table, preserving all its data and indexes, rather than destroying and recreating it.

How This Fails in Real Systems

An engineering team renamed a primary metadata table tracking application subscription options. Relying blindly on autogenerated migrations in their CI/CD pipeline, the migration ran silently in production, dropping the billing configuration of over 10,000 corporate clients before engineering noticed the issue and restored the DB from backups.

Key Takeaway

Never apply autogenerated migrations to production without first reviewing the output and manually replacing destructive drop/create commands with appropriate rename operations.

Common mistake: Developers blindly trust Alembic's autogenerate feature for schema changes involving renaming, not realizing it often interprets a rename as a destructive DROP followed by a CREATE, leading to irreversible data loss.