Alembic - Stop Breaking Your Database Every Time You Deploy

Alembic is the database migration tool created by Mike Bayer, the same person who gave us SQLAlchemy. Running the latest version and it works fine with SQLAlchemy 2.0.

Django devs have it easy - migrations are built in. The rest of us had to cobble together schema change scripts until Alembic came along in 2010. Now it's the de facto standard for Python database migrations outside Django.

The Problem It Solves

Ever deployed code and realized your new feature needs a database column that doesn't exist in prod? Or worse, tried to manually run ALTER TABLE statements on production and accidentally broke everything? Yeah, we've all been there.

Database schemas change constantly during development, but unlike your code changes, there's no git for your database structure. Until Alembic.

What It Actually Does

Tracks Database Changes: Alembic creates migration files that describe exactly what changed in your database schema. Each migration tracks what changed, just like git commits but for your database. Each migration has a unique UUID and knows which migration comes before and after it.

Handles the Scary Database Stuff: PostgreSQL is smart enough to support transactional DDL, so migrations run inside transactions. If your migration shits the bed halfway through, PostgreSQL rolls it all back automatically. No more manually cleaning up half-applied schema changes at 2 AM.

Autogenerates (Most) Migrations: Run alembic revision --autogenerate and it compares your SQLAlchemy models to your actual database, then generates a migration script. It catches maybe 80% of changes automatically - table creation, column additions, basic type changes. The other 20% you'll need to fix by hand, but that beats writing everything from scratch.

Why It's Better Than Alternatives

Actually Works With SQLAlchemy: Unlike generic migration tools that don't understand your ORM, Alembic gets SQLAlchemy's type system, relationships, and constraints. It knows the difference between PostgreSQL's UUID type and MySQL's CHAR(36).

Handles Branching: Multiple developers working on different features? Alembic's branching system lets you merge database changes like you merge code. No more "migration already exists" conflicts.

SQLite Doesn't Suck: SQLite's ALTER TABLE support is trash - you can't drop columns, rename them, or do much of anything. Alembic's batch migration mode works around this by recreating tables behind the scenes.

DBAs Don't Hate You: Generate SQL scripts instead of running migrations directly with --sql flag. Your DBA can review the actual SQL before it touches production.

When Alembic Will Ruin Your Day

Autogenerate is Drunk: It can't detect column renames (drops old, creates new = bye data), index renames, or complex constraint changes. I learned this the hard way when it generated DROP COLUMN user_name; ADD COLUMN username and wiped like 50k user records - maybe more? - because I was an idiot and didn't review what it generated. Always review what it generates - I can't stress this enough.

SQLite Edge Cases: Batch migrations work great until you have foreign keys pointing to the table you're modifying. Then you're debugging for hours.

Data Migrations: Alembic handles schema changes, not data changes. Need to populate that new column? Write the data migration yourself in the same script.

The Bottom Line

If you're using SQLAlchemy, use Alembic. It's not perfect, but it's the best tool for keeping database schema changes synchronized across development, staging, and production without losing your sanity or your data. Read the official tutorial to get started properly.

Installation - The Easy Part

pip install alembic

That's it. Works with Python 3.8+ and current versions handle SQLAlchemy 2.0 automatically. If you're still on SQLAlchemy 1.4, you're probably fine, but why make life harder for yourself?

Setup - Where Things Get Real

alembic init migrations

This creates your migration directory. You get:

• alembic.ini - Database connection config (edit the sqlalchemy.url line or you'll get connection errors)
• env.py - The script that actually runs migrations (you'll need to edit this to import your models)
• versions/ - Where your migration files live
• script.py.mako - Template for new migrations (probably never touch this)

Critical first step: Edit env.py to import your SQLAlchemy models. If you skip this, autogenerate will think your database is empty and try to drop everything. Don't be that person - I've seen this happen twice in the past year. Check the environment configuration docs for details.

The Migration Workflow That Actually Works

1. Change Your Models
Change your SQLAlchemy models - add columns, tables, whatever you need.

2. Generate Migration (Then Fix It)

alembic revision --autogenerate -m "Add user email field"

3. Review The Generated Bullshit
Alembic will generate a migration file in versions/. It looks something like abc123_add_user_email_field.py. Open it and fix whatever garbage it created:

• Column renames: It thinks you dropped one column and added another (data loss incoming) - see autogenerate limitations
• Index changes: Often gets the syntax wrong for complex indexes
• Foreign key constraints: Sometimes generates circular dependencies - check constraint naming conventions

4. Test Before You Wreck Production

## Run against a database copy first
alembic upgrade head

If it fails, fix the migration file and try again. Don't just YOLO this in production. Read about testing migrations properly.

When Things Go Wrong (And They Will)

SQLite Batch Migrations: SQLite can't modify columns or drop constraints normally. Alembic's batch mode recreates the table:

def upgrade():
    with op.batch_alter_table('users') as batch_op:
        batch_op.add_column(sa.Column('email', sa.String(255)))

This works until you have foreign keys pointing to that table. Then you're fucked and need to manually handle the foreign key recreation.

Migration Conflicts: Two developers create migrations simultaneously? Use branching and merging:

## Create a branch for your feature
alembic revision -m "Add user roles" --branch-label=user_roles

## Later, merge the conflicts
alembic merge -m "Merge user roles and permissions" 

DBA Approval Process: Generate SQL scripts instead of running directly using offline mode:

alembic upgrade head --sql > deploy_me.sql

Your DBA can review the actual SQL, which is usually required in enterprise environments.

Framework Integration Horror Stories

Flask-Migrate: Flask-Migrate wraps Alembic nicely with flask db migrate and flask db upgrade commands. Works great until you need advanced Alembic features, then you're back to raw Alembic commands.

FastAPI: No magic wrapper. You edit env.py manually to import your models, set your database URL, and pray. At least you have full control.

Django: Don't use Alembic with Django. Just don't. Django has its own migration system and mixing them is asking for trouble.

Production Deployment Strategy

1. Test Against Production Data Copy: Download a recent database backup and test your migration against it. You'll discover performance issues and edge cases before they bite you. Our Black Friday migration took down checkout for almost an hour because we didn't test against production data volume - adding a NOT NULL column to a massive table without a default value. Cost us... hell, probably fifty grand in lost sales? Management was pissed. Learned that lesson the expensive way. See this guide on production testing for best practices.

2. Review Every Migration: Autogenerate is a starting point, not gospel. Review every generated migration file before deployment. Check the migration operations reference for proper syntax.

3. Have a Rollback Plan: Make sure your downgrade() functions actually work. Test them. You'll thank yourself at 3 AM when you need to roll back. Read about rollback strategies in the docs.

4. Use Maintenance Windows: Large migrations can lock tables. Schedule them during low-traffic periods. Consider online schema change tools for MySQL or pg_repack for PostgreSQL.

The key is treating Alembic like the sharp tool it is - incredibly useful when used correctly, but capable of destroying everything if you're careless. Read the production deployment guide for more strategies.