Database Migrations with Alembic
Scenario
You add an 'age' column to your User model and deploy to production. The app immediately crashes with `column 'age' does not exist`. You forgot that changing Python code doesn't magically alter the Postgres tables.
Mental model
Migrations are 'git for your database schema'. Just as git tracks changes to your code over time, migrations track changes to your tables over time, ensuring every environment (local, staging, prod) has the exact same structure.
Alembic provides a linear history of Python scripts, where each script defines how to upgrade the database schema (e.g., add a column) and how to downgrade it (e.g., drop the column).
Deep dive
When you first start a project, you can use SQLModel.metadata.create_all() to build tables. But in production, you can't just drop and recreate tables when a schema changes—you would lose all user data.
create_all() is for prototypes; migrations are for production.
Instead, we use Alembic. Alembic generates migration scripts. A migration script contains two functions: upgrade() (how to apply the change, like adding a column) and downgrade() (how to reverse it, like dropping the column).
A migration script contains upgrade and downgrade instructions.
Alembic can 'autogenerate' these scripts by comparing your Python models against the actual database schema. It sees that User.age exists in Python but not in Postgres, and writes the ALTER TABLE command for you.
Autogenerate compares your code to the DB to write the migration.
However, autogenerate is not magic. It often misinterprets renames. If you rename first_name to given_name, Alembic usually thinks you dropped first_name (deleting the data!) and added a new, empty given_name column. You MUST read the generated script before applying it.
Always manually review autogenerated scripts to prevent data loss.
Code examples
An Alembic migration script
def upgrade():
# Alembic autogenerated this:
op.add_column("user", sa.Column("age", sa.Integer(), nullable=True))
def downgrade():
# Reverses the upgrade
op.drop_column("user", "age")This script is checked into version control. When deployed, Alembic runs upgrade() to safely alter the production table without destroying existing rows.
Fixing a renamed column in Alembic
def upgrade():
# BAD: Autogenerate did this (data loss!)
# op.drop_column('user', 'first_name')
# op.add_column('user', sa.Column('given_name', sa.String()))
# GOOD: You manually edit it to rename instead
op.alter_column('user', 'first_name', new_column_name='given_name')Never blindly trust `alembic revision --autogenerate`. Always open the file and verify it didn't choose a destructive DROP/ADD when you meant RENAME.
Key points
- Never use create_all() in prod: Once a database has real data, schema changes must be applied via incremental migration scripts.
- Autogenerate is a draft: It detects additions and deletions well, but misses renames and complex type changes.
- Migrations go in git: Migration scripts are part of your source code. They ensure other developers and CI/CD pipelines can reproduce the database state.
Common mistakes
- Blindly running autogenerated migrations: If you rename a column, Alembic sees a dropped column and a new column. If you don't catch this in the generated script, `alembic upgrade head` will delete that column's data in production.
Recall questions
- What is the purpose of Alembic in a SQLAlchemy project?
- What two functions does an Alembic migration script contain?
- Why must you manually review migrations created with --autogenerate?
Questions & answers
A developer renamed the `billing_address` field to `invoice_address` in the SQLAlchemy model, ran Alembic autogenerate, and pushed. In production, all users lost their address data. What happened?
Alembic's autogenerate feature cannot reliably detect renames. It saw that billing_address disappeared (so it generated a DROP statement) and invoice_address appeared (so it generated an ADD statement). The developer failed to read the generated script and manually change it to an ALTER COLUMN ... RENAME statement.
Why do we check migration scripts into version control instead of just running a SQL script on the prod DB directly?
Migrations ensure that the schema evolves deterministically across all environments. Local, staging, and production databases all run the exact same sequence of changes, preventing schema drift.
Continue learning
Previous: The N+1 Queries Problem