How to Use Alembic with FastAPI for Database Migrations

To use alembic with fastapi, first install Alembic and initialize it in your project. Then configure Alembic to connect to your FastAPI app's database URL, create migration scripts with alembic revision --autogenerate, and apply them using alembic upgrade head to keep your database schema in sync.

Syntax

Here is the basic workflow syntax to use Alembic with FastAPI:

• alembic init alembic: Initializes Alembic in your project.
• Configure alembic.ini or env.py to use your FastAPI database URL.
• alembic revision --autogenerate -m "message": Creates a migration script by comparing models and database.
• alembic upgrade head: Applies migrations to update the database schema.

bash

alembic init alembic
# Edit alembic/env.py to set SQLALCHEMY_DATABASE_URL
alembic revision --autogenerate -m "create tables"
alembic upgrade head

Example

This example shows how to set up Alembic with FastAPI using SQLAlchemy and SQLite. It demonstrates initializing Alembic, configuring the database URL, creating a migration, and applying it.

python

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"

engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

# alembic/env.py snippet to set the URL:
# from myapp.database import SQLALCHEMY_DATABASE_URL
# config.set_main_option('sqlalchemy.url', SQLALCHEMY_DATABASE_URL)

# Commands to run in terminal:
# alembic init alembic
# (edit alembic/env.py to use SQLALCHEMY_DATABASE_URL)
# alembic revision --autogenerate -m "create users table"
# alembic upgrade head

Output

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> <revision_id>, create users table

Common Pitfalls

Common mistakes when using Alembic with FastAPI include:

• Not setting the correct database URL in alembic.ini or env.py, causing connection errors.
• Forgetting to import your models in env.py, so Alembic cannot detect schema changes.
• Running migrations without autogenerate after model changes, leading to missing updates.
• Using SQLite without connect_args={"check_same_thread": False}, causing threading errors.

Example of a wrong and right way to set the URL in env.py:

python

# Wrong way (hardcoded wrong URL)
# config.set_main_option('sqlalchemy.url', 'sqlite:///wrong.db')

# Right way (import from your app)
from myapp.database import SQLALCHEMY_DATABASE_URL
config.set_main_option('sqlalchemy.url', SQLALCHEMY_DATABASE_URL)

Quick Reference

Command	Purpose
alembic init alembic	Initialize Alembic in your project
alembic revision --autogenerate -m "msg"	Create migration script from model changes
alembic upgrade head	Apply migrations to update database schema
alembic downgrade -1	Revert last migration
Edit alembic/env.py	Set database URL and import models

Key Takeaways

Always configure Alembic's database URL to match your FastAPI app's connection string.

Import your SQLAlchemy models in Alembic's env.py to enable autogenerate migrations.

Use 'alembic revision --autogenerate' to create migration scripts after model changes.

Run 'alembic upgrade head' to apply migrations and keep your database schema updated.

Watch out for SQLite threading settings and correct URL paths to avoid connection errors.