FastAPI Using Alembic for Database Migrations

When your app is live and you need to change the database structure — add a column, rename a table, add an index — you cannot simply drop and recreate the tables. Real data sits in those tables. Alembic is a migration tool that applies changes safely, step by step, without losing existing data.

Why Not Just Use create_all Every Time

create_all() behavior:
  ✓ Creates tables that do not exist
  ✗ Does NOT add new columns to existing tables
  ✗ Does NOT rename columns
  ✗ Does NOT drop removed columns

Production database with 10,000 users:
  You add an "age" column to the User model.
  create_all() → does nothing to the existing users table.
  Your app crashes because "age" column doesn't exist.

Alembic detects what changed and generates SQL to apply only those changes.

Install Alembic

pip install alembic

Initialize Alembic in Your Project

alembic init alembic

This creates an alembic/ folder and an alembic.ini config file:

project/
├── alembic/
│   ├── env.py           ← configuration, points to your models
│   ├── versions/        ← migration scripts live here
│   └── script.py.mako   ← template for new migrations
├── alembic.ini          ← main config file
├── database.py
└── models.py

Configure alembic.ini

Open alembic.ini and set your database URL:

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

Configure env.py to Find Your Models

Open alembic/env.py and edit the target metadata line:

# alembic/env.py

from database import Base    ← import your Base
import models                ← import all your models

target_metadata = Base.metadata   ← tell Alembic about them

Alembic compares Base.metadata (your Python models) with the live database to detect differences.

Creating Your First Migration

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

Alembic compares your models to the database and generates a migration script in alembic/versions/:

# alembic/versions/abc123_create_users_table.py

def upgrade():
    op.create_table(
        "users",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("name", sa.String(), nullable=False),
        sa.Column("email", sa.String(), unique=True),
    )

def downgrade():
    op.drop_table("users")

Every migration has an upgrade() (apply the change) and a downgrade() (reverse it).

Applying Migrations to the Database

alembic upgrade head

head means "apply all pending migrations." After running this, the database matches your models.

The Migration Version Chain

Base (empty db)
    │
    ▼ upgrade
Migration 001: create users table
    │
    ▼ upgrade
Migration 002: add age column to users
    │
    ▼ upgrade
Migration 003: add posts table
    │
   head (current state)

alembic upgrade head     → apply all migrations
alembic downgrade -1     → undo the last migration
alembic current          → show which migration is applied
alembic history          → list all migrations

Adding a New Column — Full Workflow

Step 1: Edit your model in models.py
  class User(Base):
      ...
      age = Column(Integer, nullable=True)   ← new column

Step 2: Generate migration
  alembic revision --autogenerate -m "add age to users"

Step 3: Review the generated script (check it looks right)

Step 4: Apply it
  alembic upgrade head

Step 5: Your database now has the age column.
        Existing rows get NULL for age (since nullable=True).

Key Points

• Alembic applies database changes incrementally without losing existing data.
• Run alembic init alembic once to set up the migration environment.
• Point env.py to your Base.metadata so Alembic can detect changes.
• Use --autogenerate to let Alembic write the migration script for you.
• Run alembic upgrade head to apply all pending migrations.