Alembic: Version Control for Your Database Schema

WHY IT EXISTS Managing database schema changes manually is error-prone and chaotic. As an application evolves, developers need a reliable, repeatable way to apply and roll back changes like adding a column or table across different environments (dev, test, prod) without losing data or causing inconsistencies.

THE MENTAL MODEL Alembic is version control for your database schema, working with SQLAlchemy. Instead of writing raw SQL ALTER TABLE statements and running them by hand, you define your schema in Python using SQLAlchemy models. Alembic compares your models to the current database state and generates a Python script that represents the difference. This script is a "migration" – a single, versioned step in your database's history that you can apply (upgrade) or revert (downgrade).

HOW IT WORKS You initialize Alembic in your project, creating a versions directory. When you change your SQLAlchemy models (e.g., add a User.email field), you run alembic revision --autogenerate. Alembic inspects the database, compares it to your models, and generates a new file in the versions directory. This file contains two Python functions: upgrade() and downgrade(). The upgrade() function has the code to apply the change (e.g., op.add_column(...)), and downgrade() has the code to revert it. You then run alembic upgrade head to apply the migration to your database.

WHEN TO USE IT Use Alembic in any Python application that uses SQLAlchemy for database interaction. It is essential for team projects to coordinate schema changes and for CI/CD pipelines to automate database updates during deployment. Its "offline mode" is also useful for generating raw SQL scripts for environments with restricted direct execution access.

WHEN NOT TO USE IT For very simple projects with a static schema that never changes, Alembic might be overkill. It is also not a data migration tool; it manages the schema, not the data itself. For large-scale data backfills or transformations, you would use a separate script, though you might trigger it from within an Alembic migration.

ONE CANONICAL EXAMPLE The most common workflow is autogenerating a migration. After adding is_verified = Column(Boolean, server_default="false") to your User model, you run alembic revision --autogenerate -m "Add verified flag to user". Alembic creates a new version script. The footgun here is that autogeneration might not detect the server_default correctly on all database backends or might miss a custom index name. You must open the generated file and verify the op.add_column() call is correct before running alembic upgrade head.