Introduction

Database migrations are a crucial part of maintaining a structured and evolving database schema in any web application. Alembic is a lightweight database migration tool for SQLAlchemy. It helps you track changes in your database schema over time.

For example, if you create a User table and later decide to add a new column (e.g., age), you don't have to manually update the database. Alembic will generate a migration file that updates your database structure automatically.

How Alembic Works

Alembic works in two main steps:

1. Create a migration script – This is a file that records the changes in your database schema.

2. Apply the migration – This applies the changes to your actual database.

Think of it like a version control system (like Git) but for your database schema.

Setting Up Alembic in FastAPI

1. Install Alembic

pip install alembic

2. Initialize Alembic

alembic init alembic

This will create a new alembic directory with configuration files.

3. Configure Alembic

Go to alembic.ini file and find this line:

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

sqlalchemy.url = postgresql+asyncpg://user:password@localhost/mydatabase

sqlalchemy.url = mysql+pymysql://user:password@localhost/mydatabase

Folder Structure for Alembic in a FastAPI Application

fastapi_project/
│── app/
│   ├── main.py       # FastAPI entry point
│   ├── models.py     # Database models
│   ├── database.py   # Database connection
│
│── alembic/
│   ├── versions/     # Migration scripts
│   ├── env.py        # Alembic config
│
│── alembic.ini       # Alembic settings
│── requirements.txt  # Dependencies

Define Your Models

In FastAPI, models are usually defined using SQLAlchemy ORM. For example:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    email = Column(String, unique=True, index=True)

Now, tell Alembic where to find your models

Open alembic/env.py and find this line:

target_metadata = None

Change it to:

from myapp.models import Base  # Import your models
target_metadata = Base.metadata

Replace .models with the actual location of your models

Generate the First Migration

Now that everything is set up, create your first migration:

alembic revision --autogenerate -m "Initial migration"

This will create a file inside the alembic/versions folder.

Apply the Migration (Upgrade Database)

To apply the migration and create tables in the database, run:

alembic upgrade head

Now your database has the users table!

Making Changes to the Database Schema

Let's say you want to add a new column age to the User table.

1. Modify the Model

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    email = Column(String, unique=True, index=True)
    age = Column(Integer)  # New column added

2. Generate a New Migration

alembic revision --autogenerate -m "Added age column"

Alembic will detect the changes and create a new migration script.

3. Apply the Migration

alembic upgrade head

Now, the users table will have an age column.

Rollback (Undo a Migration)

If you make a mistake, you can roll back the last migration:

alembic downgrade -1

To rollback to a specific version, find the migration ID (1234567890_abcd.py) and run:

alembic downgrade 1234567890_abcd

Deleting All Migrations (Start Fresh)

If you want to delete all migrations and start fresh:

1. Delete everything inside the alembic/versions/ folder.

2. Drop your database tables manually.

alembic revision --autogenerate -m "Initial migration"
alembic upgrade head

Viewing Migration History in Alembic

To check the migration history in your FastAPI application, use the following command:

alembic history

This command lists all applied and available migrations in sequential order. If you want a more detailed view, use:

alembic history --verbose

Best Practices for Database Migrations

• Use --autogenerate for simple changes, but review the generated code before applying.

• Manually write migrations for complex schema updates to avoid incorrect auto-detections.

• Check for orphaned migrations and keep track of applied versions using alembic history and alembic current.

• Always back up your database before major migrations.

Alembic Migration Tracking Mechanism

Alembic keeps track of applied migrations using the alembic_version table in your database. This table stores the latest migration version applied, helping Alembic determine which migrations need to run.

How Alembic Tracks Migrations

• Each migration is assigned a unique version identifier (a hash).

• When you run alembic upgrade head, the latest migration version is recorded in alembic_version.

• If the migration is successful, Alembic assumes all previous migrations have been applied.

What Happens When Migrations Are Applied/Rolled Back?

When you apply a migration (alembic upgrade head)

• Alembic checks the alembic_version table to see the last applied migration.

• If new migrations exist, they are applied in order.

• The alembic_version table is updated with the latest migration ID.

When you roll back a migration (alembic downgrade -1)

• Alembic reverses the last migration.

• The alembic_version table is updated to reflect the previous migration state.

If the database file is replaced (in SQLite) or migrations are missing

• Alembic may assume migrations are already applied, leading to missing tables.

• You may need to reset Alembic with alembic stamp base and reapply migrations.

My Experience with Alembic: Migration Applied but Table is Missing

While working on my project, I faced an issue where Alembic successfully created a migration, but the table was missing in the database. After researching and troubleshooting, I discovered several possible reasons for this problem and their fixes. Here’s what I learned

Migration Ran But Failed Silently

Alembic may update the alembic_version table even if the migration script encounters an issue (e.g., missing permissions, constraints, or errors in the migration file).

How to Check

Run the following SQL command to verify the latest migration applied:

SELECT * FROM alembic_version;

If the migration appears here but the table is missing, the migration likely failed silently.

Fix: Reapply the Migration

alembic downgrade -1  # Roll back the last migration
alembic upgrade head  # Apply the migration again

Migration Script is Empty or Incorrect

Sometimes, Alembic generates an empty migration without the necessary op.create_table(...) statement.

How to Check

Open your migration file inside alembic/versions/ and check if op.create_table(...) is present.

Fix: Manually Add Table Creation Code

If missing, manually add:

def upgrade():
    op.create_table(
        "feed",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("title", sa.String(), index=True),
        sa.Column("content", sa.String()),
    )

Then apply the migration:

alembic upgrade head

Conclusion

Alembic is a powerful tool for managing database migrations, but like any automation, it requires careful handling. Issues such as missing tables despite a successful migration can often be traced back to version mismatches, incorrect metadata configurations, or mistakenly applied migration stamps.

The key takeaway? Never assume a migration has worked just because Alembic says so. Always check your database, review migration files, and don't hesitate to rollback and reapply when things seem off. Debugging is part of the process, and every issue you solve makes you a better developer.

Pro Tips for Smooth Migrations

Always check the alembic_version table after running migrations.
If something seems off, rollback (alembic downgrade -1) and try again.
Before applying a migration, inspect the generated file for missing op.create_table(...).
Use Git to track migrations and avoid unnecessary stamp commands.
Keep an eye on database changes, especially if using SQLite or multiple environments.

With the right approach, Alembic makes database migrations seamless. Stay consistent, verify migrations, and always keep backups! Happy coding and smooth migrations!