Alembic Migrations

Module 2 · Backend · ⏱ ~15 min

Why this matters: You can't just modify your Python models and expect the database to change automatically. alembic upgrade head is one of the first commands run after every deployment — it applies any schema changes to the live database.

The Problem Migrations Solve

Imagine you have a production database with 10,000 real bookings. You need to add a new column cancelled_reason to the bookings table. How do you do it safely?

You can't just edit your Python model — the actual database table doesn't change
You can't drop the table and recreate it — you'd lose all 10,000 bookings
You need to run ALTER TABLE bookings ADD COLUMN cancelled_reason TEXT
And you need to track whether this change has already been applied (don't run it twice)

Migrations are version-controlled SQL scripts that describe how to change a database schema from one version to the next. Alembic manages this for SQLAlchemy projects.

How Alembic Tracks What's Applied

Alembic creates a table called alembic_version in your database with a single column: the current migration version ID. When you run alembic upgrade head, it:

Reads the version from alembic_version
Finds all migration files newer than that version
Runs their upgrade() functions in order
Updates alembic_version to the latest

Migration chain (each has an ID):

  None ──► a1b2c3d4e5f6 ──► b2c3d4e5f6a1 ──► c3d4e5f6a1b2
           (initial schema)   (add index)      (add column)
                  ▲
                  │ alembic_version currently = "a1b2c3d4e5f6"
                  │
                  └─ alembic upgrade head → runs b2c... then c3d..., updates version

The Project's Migration File

There's one migration in this project — the "initial schema" that creates all tables at once. From migrations/versions/a1b2c3d4e5f6_initial_schema.py:

python — migrations/versions/a1b2c3d4e5f6_initial_schema.py (excerpt)

"""initial schema

Revision ID: a1b2c3d4e5f6
Revises:                          # None = this is the first migration
Create Date: 2026-06-21
"""
import sqlalchemy as sa
from alembic import op

revision: str = "a1b2c3d4e5f6"
down_revision: Union[str, None] = None  # what version this builds on

def upgrade() -> None:
    # Creates the shops table
    op.create_table(
        "shops",
        sa.Column("id", sa.UUID(), nullable=False),
        sa.Column("slug", sa.String(100), nullable=False),
        sa.Column("name_th", sa.String(200), nullable=False),
        sa.Column("name_en", sa.String(200), nullable=False),
        sa.Column("category", sa.String(50), nullable=False),
        sa.Column("slot_increment_min", sa.Integer(), nullable=False, server_default="30"),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("slug"),
    )
    # ... more create_table calls for resources, services, bookings, etc.

def downgrade() -> None:
    # Reverses upgrade() — drops tables in reverse order
    op.drop_table("bookings")
    op.drop_table("resource_services")
    # ... etc

Key Commands

bash — alembic commands

# Apply all pending migrations (most common command)
docker compose exec fastapi alembic upgrade head

# Roll back the last migration
alembic downgrade -1

# See current migration version
alembic current

# See migration history
alembic history

# Generate a new migration from model changes (autogenerate)
alembic revision --autogenerate -m "add cancelled_reason column"

# Apply a specific revision
alembic upgrade a1b2c3d4e5f6

How Autogenerate Works

When you run alembic revision --autogenerate, Alembic:

Imports your SQLAlchemy models (by reading env.py)
Compares the models to the current database schema
Generates an upgrade() with the differences (new columns, tables, etc.)
Generates a downgrade() that reverses them

Autogenerate is not magic: It can detect added/removed tables and columns, but it cannot detect column renames (it sees a drop + add), data migrations, or complex index changes. Always review the generated migration before running it in production.

Async Migrations

Because the project uses async SQLAlchemy, the env.py has an async migration path:

python — migrations/env.py (key part)

async def run_async_migrations():
    connectable = async_engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
    )
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)
    await connectable.dispose()

def run_migrations_online():
    asyncio.run(run_async_migrations())  # starts event loop just for migrations

🧠 Self-Check Quiz

1. You modify a Python model to add a new column. Does the database table automatically gain that column?

No. The Python model is just a Python class — it has no direct effect on the database schema. You must create a migration (either manually or with --autogenerate) and run alembic upgrade head. Without this, the database doesn't have the new column, and any attempt to insert a value into it will fail.

2. What does alembic upgrade head do and what is "head"?

"Head" means the latest migration. upgrade head applies all migrations that haven't been applied yet, in order, until the database is at the most recent version. If the database is already at head, the command does nothing. It reads alembic_version table to know where it currently is.

3. What does the downgrade() function do and when would you use it?

It reverses the migration — undoes whatever upgrade() did (drops tables, removes columns, etc.). You'd use alembic downgrade -1 if a migration was applied by mistake and you need to roll back. In production, downgrade is risky if data has already been written to the new columns — use with caution and always back up first.

4. Why does the project only have one migration file (the "initial schema") instead of many?

This is an early-stage project. All tables were designed upfront and created in a single "initial schema" migration. As the project evolves and new columns or tables are needed, each change will get its own migration file. The chain grows over time — every production schema change becomes a new migration in the history.

5. Why can't alembic revision --autogenerate detect a column rename?

Alembic compares model definitions to DB columns by name. If you rename a column in your model, Alembic sees two separate changes: the old column was deleted and a new column was added. It generates a DROP + ADD, which would lose any existing data in that column. A rename must be done manually with op.alter_column() or op.rename_column().