Database migrations with Alembic: lessons from production

Deploying schema changes to production databases is one of the most nerve-wracking moments in the release cycle. We've all felt that tension when a migration fails halfway through and you're racing against the clock to roll back. Database migrations with Alembic: lessons from production teaches us how to handle schema evolution safely and repeatably using SQLAlchemy's migration tool.

Why Alembic matters in production systems

Schema changes in production happen constantly. You add a new column for a feature, rename an existing field, create indexes for performance, or adjust constraints. Without a migration framework, you're either applying changes manually (error-prone and unrepeatable) or scripting them ad-hoc (no version control, no rollback mechanism).

Alembic solves this by creating versioned migration files that track every schema change. Each migration is a Python file with upgrade and downgrade functions. You commit these files to version control alongside your application code, giving you a complete audit trail and the ability to move any environment to any known state.

We've learned this matters most when:

• Multiple teams deploy to the same database and need consistent schema versions
• You need to rollback a release and revert schema changes cleanly
• Database replicas or read-only followers need to stay synchronized
• You're running blue-green or canary deployments where old and new code coexist

Autogenerate migrations: the starting point, not the finish line

Alembic's alembic revision --autogenerate command is convenient. It inspects your SQLAlchemy models, compares them to the current database schema, and generates a migration file automatically. On Day 44 of production work, you might run this after adding a new model and think you're done.

Don't. Autogenerate is a time-saver, but it's not magic. It has blind spots:

• It can't detect logical constraints (e.g., "this column should only accept values 0-100")
• It may miss complex index strategies or partitioning logic
• It doesn't understand your data migration needs—columns might need backfilling
• It sometimes generates migrations that conflict with your deployment process

Here's a realistic scenario. You add a NOT NULL column to an existing table with millions of rows:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False)
    phone = Column(String(20), nullable=False)  # New column

Running alembic revision --autogenerate generates:

def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=False))

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

In production, this fails. You can't add a NOT NULL column to a table with existing rows without providing a value for each row. The database rejects it.

The correct migration first adds the column as nullable, backfills existing rows, then adds the NOT NULL constraint:

def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=True))
    # Backfill existing rows with a default value
    op.execute("UPDATE users SET phone = '+1' WHERE phone IS NULL")
    # Now add the constraint
    op.alter_column('users', 'phone', nullable=False)

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

Always review autogenerated migrations with the same rigor you'd apply to hand-written code. Read each operation, think through how it behaves on a live table, and test on a production-like dataset before committing.

Review every generated migration

A code review process for migrations isn't bureaucracy—it's your safety net. On teams we've worked with, we treat migration reviews like security reviews. Someone other than the author checks every migration before it merges.

What are we looking for?

• Locking behavior: Does this operation lock the table? For how long? Will it block production traffic?
• Data loss: Does downgrade truly restore the previous state, or does it discard data?
• Compatibility: Will this migration work with both old and new code during a rolling deploy?
• Performance: On large tables, does the migration run in acceptable time?
• Completeness: Are related indexes, triggers, or constraints included?

Use a shared checklist. Here's a simplified version:

A good review comment might be: "This adds an index CONCURRENTLY, which is correct for a live table. However, test the runtime on staging with production data volume to confirm it completes before the next deploy window."

Run upgrade head on deploy

Deployment strategy matters. The safest pattern is to run migrations before deploying new code, not after. This ensures the schema is ready when your application starts.

In your deployment pipeline, the order should be:

1. Stop old application instances (or mark them as not-ready)
2. Run alembic upgrade head to bring schema to latest version
3. Start new application instances

Why this order? If a migration fails, your application doesn't start, and you know immediately. You can then investigate, fix, and redeploy. If you deploy code first and run migrations after, the application is live but potentially incompatible with the schema—a much worse state.

Here's a realistic deploy script:

#!/bin/bash
set -e

# Stop the app
systemctl stop myapp || true

# Run migrations
cd /opt/myapp
alembic upgrade head

# Start the app
systemctl start myapp

# Verify health
sleep 2
curl http://localhost:8000/health

Use alembic current to check which revision is applied before deploying. Use alembic heads to see what the target version should be. This transparency helps catch deployment order issues early.

For large tables or long-running migrations, use deploy windows or schedule migrations during maintenance periods. Monitor migration runtime in staging to predict production time. Add timeouts to your deployment scripts so a hung migration doesn't leave your app offline indefinitely.

Write data migrations separately

Schema migrations and data migrations are different concerns. A schema migration adds a column. A data migration backfills or transforms existing data. Mixing them makes migrations harder to understand, test, and debug.

Separate them: create one migration for the schema, a second for the data transformation.

Example scenario: You're renaming a column from user_status to status_code, and the values need to change too (e.g., "active" becomes "A", "inactive" becomes "I").

First migration (schema only):

def upgrade():
    op.add_column('users', sa.Column('status_code', sa.String(1), nullable=True))

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

Second migration (data transformation):

def upgrade():
    # Transform existing data
    op.execute("""
        UPDATE users 
        SET status_code = CASE 
            WHEN user_status = 'active' THEN 'A'
            WHEN user_status = 'inactive' THEN 'I'
            ELSE 'U'
        END
        WHERE status_code IS NULL
    """)

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

Third migration (cleanup, after the app is updated):

def upgrade():
    op.drop_column('users', 'user_status')
    op.alter_column('users', 'status_code', nullable=False)

def downgrade():
    op.add_column('users', sa.Column('user_status', sa.String(10)))
    op.alter_column('users', 'status_code', nullable=True)

Why three migrations? This approach lets you:

• Deploy the new column before the app uses it
• Run data transformation on production with monitoring
• Remove the old column only after confirming the app never reads it
• Rollback each step independently if something goes wrong

For large data migrations on live tables, consider doing the transformation in chunks (batch processing) rather than a single UPDATE statement. This reduces lock contention and makes the operation resumable if interrupted.

Common mistakes

Mistake 1: Trusting autogenerate without review. We've seen autogenerated migrations make assumptions about nullable columns, index names, or constraint logic that were wrong for the use case. Always review.

Mistake 2: Not testing downgrade on staging. A migration that upgrades successfully but doesn't downgrade cleanly is useless for rollback. Test both directions before production.

Mistake 3: Adding large data transformations in the upgrade function without batching. An UPDATE on a 10-million-row table will lock the table for minutes. Batch it, test the performance, or do it asynchronously.

Mistake 4: Deploying code and migrations in the wrong order. If you deploy code first, new code may try to use schema that doesn't exist yet. Run migrations first, always.

Mistake 5: Mixing schema and data migrations in one file. It's tempting but makes rollback harder and makes it harder to understand what each step does. Keep them separate.

Mistake 6: Not considering zero-downtime deploys. Some operations (like adding a NOT NULL column) can't be done on a live table without causing downtime. Plan for that, use techniques like adding nullable columns first, or schedule maintenance.

Key takeaways

• Autogenerate migrations as a starting point, but always review and refine them. Autogenerate has blind spots, especially around data transformations and constraints.
• Treat migration reviews like code reviews. Check for locking behavior, data loss, compatibility with rolling deploys, and performance on production-scale data.
• Run alembic upgrade head before deploying application code, not after. This ensures the schema is ready and catches migration failures immediately.
• Separate schema migrations from data migrations. Create distinct migration files for schema changes, data transformations, and cleanup. This makes rollback safer and logic clearer.
• Test migrations on a staging database with production data volume. Monitor migration runtime and plan for locking behavior. Batch large data transformations to avoid table locks.
• Keep downgrade functions working. A migration that doesn't downgrade cleanly is a liability in production.
• Use alembic current and alembic heads to verify migration state during deployments. Transparency prevents surprises.