Migrations with Alembic

Introduction

A migration is a controlled change to your database structure. When you add a table, rename a column, create an index, or change a constraint, that change should be tracked as a migration.

Why This Matters

During development, it is tempting to delete the database and recreate tables. That does not work in production because real user data already exists. Migrations let you evolve the database safely without losing data.

What Is Alembic?

Alembic is the database migration tool commonly used with SQLAlchemy. It stores migration files in your project and records which migrations have already been applied to each database.

Install it:

pip install alembic

Initialize Alembic:

alembic init alembic

This creates an alembic folder and an alembic.ini configuration file.

Basic Alembic Files

After initialization, you will see files like:

alembic.ini
alembic/
- env.py
- script.py.mako
- versions/

Migration files are stored inside alembic/versions.

Configuring the Database URL

For a small project, you can set the database URL in alembic.ini:

sqlalchemy.url = sqlite:///./app.db

For production projects, read the URL from environment variables or your app settings instead of hardcoding credentials.

Connecting Alembic to SQLAlchemy Models

Alembic needs access to your SQLAlchemy metadata. In alembic/env.py, import your Base and set target_metadata:

from app.database import Base
from app import models

target_metadata = Base.metadata

Importing models is important because it registers your model classes with Base.metadata.

Creating a Migration

If your models are configured correctly, Alembic can compare them with the database:

alembic revision --autogenerate -m "create users table"

This creates a new file inside alembic/versions.

A migration usually has two functions:

def upgrade() -> None:
    pass

def downgrade() -> None:
    pass

upgrade() applies the change. downgrade() reverses it.

Reviewing Generated Migrations

Always open the generated file before applying it. Alembic is helpful, but it cannot understand every intention.

Example generated migration:

from alembic import op
import sqlalchemy as sa

def upgrade() -> None:
    op.create_table(
        "users",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=100), nullable=False),
        sa.Column("email", sa.String(length=255), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("email"),
    )

def downgrade() -> None:
    op.drop_table("users")

Check that table names, column names, constraints, and indexes match what you expect.

Applying Migrations

Apply all pending migrations:

alembic upgrade head

head means the latest migration.

Check migration history:

alembic history

Check the current database revision:

alembic current

Rolling Back

Rollback one migration:

alembic downgrade -1

Rollback to a specific revision:

alembic downgrade revision_id_here

Be careful with downgrades in production because reversing schema changes can remove data.

Migrations vs create_all

Base.metadata.create_all() is useful while learning. It creates missing tables, but it does not manage changes well.

For example, if you add a new column to an existing table, create_all() will not reliably update the table structure for you. Alembic is the right tool for tracking schema changes over time.

Common Workflow

A normal migration workflow looks like this:

1. Update SQLAlchemy models
2. Generate an Alembic revision
3. Review the generated migration
4. Apply the migration locally
5. Run the app and tests
6. Commit the model and migration files together

The model change and migration file should travel together.

Common Mistakes

Using create_all in production

Use migrations for production schema changes. create_all() is not enough for a growing application.

Not reviewing autogenerated files

Autogenerate is a helper, not a replacement for understanding the migration.

Forgetting to import models in env.py

If Alembic cannot see your models, it may generate empty migrations.

Summary

Alembic tracks database schema changes for SQLAlchemy projects. Use it to create, review, apply, and rollback migrations. In real projects, migrations are the safe way to evolve your database while keeping existing data.