Last modified: Dec 02, 2025 By Alexander Williams
FastAPI Database Migrations with Alembic
FastAPI is great for building APIs. But apps need data. That data lives in a database. The database schema changes as your app grows. You need a way to manage these changes safely. This is where Alembic comes in.
Alembic is a database migration tool for SQLAlchemy. It tracks schema changes over time. It lets you apply or roll back changes. This is vital for team work and production.
This guide shows you how to set it up. We will integrate Alembic with a FastAPI project. You will learn to create and run migrations. Let's build a solid data foundation.
Why You Need Database Migrations
Imagine you add a new column to a table. Your teammate has an old schema. The app breaks. Or you deploy to production. The new code expects the new column. The old database does not have it. Crash.
Migrations solve this. They are version-controlled scripts. They describe how to move from schema A to B. And how to go back to A if needed. This ensures everyone and every server is in sync.
It is a safety net for your data. Manual SQL changes are risky and error-prone. Alembic automates and documents the process. It is a best practice for any serious application.
Project Setup with FastAPI and SQLAlchemy
First, create a new project directory. Set up a virtual environment. Install the required packages. You need FastAPI, SQLAlchemy, and Alembic.
mkdir fastapi-alembic-demo
cd fastapi-alembic-demo
python -m venv venv
source venv/bin/activate # On Windows: venv\Scripts\activate
pip install fastapi sqlalchemy alembic psycopg2-binary uvicorn
We use psycopg2 for a PostgreSQL database. You can use sqlite for testing. Now, create the core application files.
Create a models.py file. This defines our SQLAlchemy models. These are Python classes that map to database tables.
# models.py
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
username = Column(String, unique=True, index=True, nullable=False)
email = Column(String, unique=True, index=True, nullable=False)
created_at = Column(DateTime(timezone=True), server_default=func.now())
Create a database.py file. This sets up the database connection. It uses SQLAlchemy's engine and session.
# database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
SQLALCHEMY_DATABASE_URL = "postgresql://user:password@localhost/dbname"
# For SQLite: "sqlite:///./test.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
Finally, create a simple main.py for your FastAPI app. It will use the database session. For robust session management, review FastAPI Dependency Injection Best Practices.
# main.py
from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from . import models, database
models.Base.metadata.create_all(bind=database.engine)
app = FastAPI()
@app.get("/")
def read_root(db: Session = Depends(database.get_db)):
# Example endpoint using the database session
return {"message": "Hello World with Database"}
Initializing Alembic for Migrations
Now, initialize Alembic in your project. This creates the migration environment and configuration files.
alembic init alembic
This creates an alembic folder and an alembic.ini file. You must configure the connection. Open alembic.ini. Find the sqlalchemy.url line. Set it to your database URL.
# alembic.ini
sqlalchemy.url = postgresql://user:password@localhost/dbname
Next, edit alembic/env.py. This file controls how Alembic runs. You need to import your models and set the target metadata. This tells Alembic about your schema.
# alembic/env.py
from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
import sys, os
# Add the project root to the Python path
sys.path.append(os.getcwd())
# Import your Base metadata and models
from models import Base
target_metadata = Base.metadata
# ... rest of the existing file remains ...
This setup links Alembic to your SQLAlchemy models. Now Alembic can detect changes in your model definitions.
Creating Your First Migration
With Alembic configured, create the initial migration. This script will create the users table defined in our model.
alembic revision --autogenerate -m "Create users table"
The --autogenerate flag tells Alembic to compare models to the current database. It generates the upgrade and downgrade scripts automatically. Find the new file in alembic/versions/.
# Example generated migration file
"""Create users table
Revision ID: abc123
Revises:
Create Date: 2023-10-27 10:00:00.000000
"""
from alembic import op
import sqlalchemy as sa
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('users',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(), nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False)
op.create_index(op.f('ix_users_username'), 'users', ['username'], unique=True)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index(op.f('ix_users_username'), table_name='users')
op.drop_index(op.f('ix_users_id'), table_name='users')
op.drop_index(op.f('ix_users_email'), table_name='users')
op.drop_table('users')
# ### end Alembic commands ###
Always review autogenerated scripts. Ensure they match your intent. Now, apply this migration to your database.
alembic upgrade head
The upgrade head command applies all pending migrations. It runs the upgrade() function in your script. Your database now has the users table.
Making Schema Changes and New Migrations
Let's modify our model. Add a new full_name column to the User model. Update models.py.
# models.py - updated
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
username = Column(String, unique=True, index=True, nullable=False)
email = Column(String, unique=True, index=True, nullable=False)
full_name = Column(String, nullable=True) # New column
created_at = Column(DateTime(timezone=True), server_default=func.now())
Generate a new migration for this change. Alembic will detect the new column.
alembic revision --autogenerate -m "Add full_name to users"
Apply the new migration.
alembic upgrade head
Your database schema is updated. The app code and database stay in sync. This process is repeatable for any change.
Rolling Back Migrations
Mistakes happen. A migration might have an error. You can roll back. Use the downgrade command. You need a target revision ID or relative step.
First, check your migration history.
alembic history
abc123 -> def456 (head), Add full_name to users
To roll back one migration, use -1. This runs the downgrade() function.
alembic downgrade -1
This removes the full_name column. The database goes back to the previous state. Rollbacks are crucial for fixing production issues quickly.
Integrating Migrations into Your Workflow
Migrations should be part of your development cycle. Follow these steps. First, create a feature branch. Make your model changes in Python code. Then generate a migration.
Test the migration locally. Apply it and run your tests. Ensure your Test FastAPI Endpoints with pytest and TestClient still pass. Commit both the model change and the migration script.
In production, run migrations before deploying new code. This is often done in a startup script. Or as part of your CI/CD pipeline. For containerized apps, see Deploy FastAPI with Docker for Production.
Never edit a migration file that has been shared or applied. Create a new migration instead. This keeps history clean and predictable for your team.
Advanced Alembic Use Cases
Migrations can handle complex operations. You can seed data. Use the op.bulk_insert() command. Or run custom SQL with op.execute().
def upgrade() -> None:
# ... schema changes ...
op.execute("UPDATE users SET status = 'active' WHERE status IS NULL")
You can manage multiple databases. Or split migrations across modules. Alembic is flexible. For async databases, use asyncpg driver. Configure the engine accordingly.
Combine migrations with background processing. For example, after adding a column, you might want to populate it via FastAPI Background Tasks: Run Async Jobs. This keeps your API responsive.
Common Pitfalls and Best Practices
Avoid long-running migrations on huge tables in a single transaction. Split them. Use batch operations for SQLite. Always have a downgrade path.
Never use Base.metadata.create_all in production. It does not track changes. Use Alembic exclusively for schema management after the first migration.
Keep your migration scripts deterministic. Avoid relying on random data. Reference models by name, not by imported Python class. The class definition might change later.
Use a consistent naming convention for migration files. Include a brief description. This helps team members understand the change at a glance.
Conclusion
Database migrations are non-optional for modern apps. Alembic provides a powerful, safe way to manage schema evolution. It integrates seamlessly with FastAPI and SQLAlchemy.
You learned how to set up Alembic. You can create, apply, and roll back migrations. This ensures your team and deployments stay synchronized.
Start using migrations from day one. It will save you from countless data-related headaches. Your future self will thank you. Now go build that scalable FastAPI application with confidence.