Using Alembic with SQLAlchemy (which is often used under the hood, even with ORMs like SQLModel) is the standard approach for migrations.
Here is a step-by-step guide to installing Alembic, configuring it, defining a User model, and creating your first migration.
1. Project Setup & Installation
First, ensure you have a basic FastAPI project structure and the necessary dependencies.
Install Required Packages
You’ll need fastapi, a database driver (like psycopg2-binary for PostgreSQL or sqlite support with SQLAlchemy), SQLAlchemy, and alembic.
pip install fastapi "uvicorn[standard]" sqlalchemy alembic psycopg2-binaryProject Structure (Example)
A typical structure might look like this:
fastapi-project/
├── app/
│ ├── init.py
│ ├── database.py # SQLAlchemy Base/Engine/Session
│ └── models/
│ ├── init.py
│ └── user.py # Your User Model
├── alembic/ # Created by alembic init
├── alembic.ini # Created by alembic init
└── main.py2. Define the SQLAlchemy Base and User Model
You need a central Base object for SQLAlchemy’s declarative models and then define your User model inheriting from it.
app/database.py (Setup Base)
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
# Replace with your actual database URL
# Example for PostgreSQL:
SQLALCHEMY_DATABASE_URL = "postgresql://user:password@localhost/dbname"
# Example for SQLite:
# SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# This is the Base that all your SQLAlchemy models will inherit from
Base = declarative_base()
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()app/models/user.py (User Model)
from sqlalchemy import Column, Integer, String, Boolean, DateTime
from sqlalchemy.sql import func
from app.database import Base # Import the Base from your database file
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
email = Column(String, unique=True, index=True, nullable=False)
# Note: Password should be hashed and not stored as plain text!
hashed_password = Column(String, nullable=False)
is_active = Column(Boolean, default=True)
created_at = Column(DateTime, server_default=func.now())app/models/__init__.py (Register Models for Alembic)
Alembic needs to know about all your models to automatically generate migrations. Importing them into your __init__.py file often serves this purpose.
# Import all models here so Alembic can discover them
from . import user
# ... and any other models (e.g., post, item)3. Initialize & Configure Alembic
Initialize Alembic
Run the init command in your project’s root directory:
alembic init alembicThis creates the alembic directory and alembic.ini file.
Configure alembic/env.py
This is the most crucial step. You need to tell Alembic three things:
- How to get your database URL.
- Where to find your SQLAlchemy
Base.metadata. - How to connect to the database (online mode).
Find the following lines in alembic/env.py and modify them:
- Import your
Baseand database URL:
# ... other imports
from app.database import Base, SQLALCHEMY_DATABASE_URL
# Also ensure your model files are imported, e.g.,
from app.models import * ```
2. Set target_metadata: Uncomment and set this to your Base’s metadata. This is what Alembic uses to compare against the current database schema.
# ... other context settings
# target_metadata = None # Replace this line
target_metadata = Base.metadata # Set to your Base.metadata3. Set sqlalchemy.url in online mode: Find run_migrations_online() and set the database URL using your imported variable.
def run_migrations_online():
"""Run migrations in 'online' mode."""
# ...
# Add these two lines to set the URL dynamically
config.set_main_option("sqlalchemy.url", SQLALCHEMY_DATABASE_URL)
connectable = engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
# ... rest of the function remains the same
4. Create and Apply the User Migration
Now that Alembic is configured and knows about your User model, you can create the migration script.
Generate the Migration
Use the –autogenerate flag to create a migration script based on the difference between your current models (User) and the database schema (which is currently empty).
alembic revision --autogenerate -m "Create users table"A new file will be created in alembic/versions/ (e.g., ..._create_users_table.py). Open this file and ensure the upgrade() function looks correct:
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('users',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('hashed_password', sa.String(), nullable=False),
sa.Column('is_active', sa.Boolean(), nullable=True),
sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False)
# ### end Alembic commands ###Apply the Migration
Execute the upgrade command to apply the changes to your database, creating the users table.
alembic upgrade headYour users table is now created in your database, ready to be used by your FastAPI application!
If any case, If you would like to rollback the migrations follow this step:
1. Rolling Back to the Previous Revision
The most frequent rollback operation is to revert the last applied migration.
Command: alembic downgrade -1This command tells Alembic to go back by one revision from the current state.
2. Rolling Back to a Specific Revision
If you need to revert to a particular migration revision, you can use its unique ID.
Command: alembic downgrade <target_revision_id>For example, if your current head is e8b7c5d3d4b6 and you want to roll back to the state defined by revision 1a2b3c4d5e6f:
alembic downgrade 1a2b3c4d5e6f
3. Rolling Back All Migrations (to Base)
To revert all applied migrations and return the database schema to its initial, un-migrated state (the “base” revision).
Command: alembic downgrade baseThese are the use of alembic to create and destroy migrations file which help us to keep state with database transaction.