No-Fail Guide: Getting Started with Database Migrations (FastAPI × SQLAlchemy × Alembic)

✅ Summary (Conclusion & Big Picture First)

• What you can do with this article
  Learn how to safely, reproducibly, and team-friendly apply schema changes (migrations) to the database used in your FastAPI app. Define models in SQLAlchemy and use Alembic for history tracking, automatic diff generation, and rollbacks — with concrete, production-ready implementation steps.
• Main Steps
  1. Prepare project structure → 2) Define SQLAlchemy ORM models → 3) Initialize Alembic → 4) Autogenerate revisions → 5) upgrade/downgrade → 6) Avoid pitfalls (naming conventions, merge conflicts, SQLite quirks)
• Target DB
  Start development with SQLite and later migrate to PostgreSQL or MySQL with minimal friction.
• Benefits
  • Prevent “Oops, I broke it with a manual ALTER…”
  • Keep change history so your team can reproduce the same state anytime
  • Build a stable workflow that fits easily into CI/CD

🎯 Who Will Benefit (Examples)

• Solo developer (college junior, first web project)
  Building a ToDo app in SQLite. Wants to safely add “due date” or “priority” columns later. Copy-paste runnable commands and see the changes visually (history).
• Small team (3-person contract dev)
  Specs change weekly, DB changes conflict often, and colleagues can’t reproduce each other’s local DB states. Needs naming conventions, history tracking, rollbacks for zero-accident operations.
• Startup planning to scale
  Start with SQLite, then migrate to PostgreSQL as users grow. Learn a robust flow centered on autogenerated diffs for smooth scaling.

1. Preparation: Minimal Project Layout & Installation

1.1 Directory Structure

fastapi-db/
├─ app/
│  ├─ main.py
│  ├─ db.py
│  ├─ models.py
│  └─ schemas.py
├─ alembic/           # Generated by Alembic (after init)
├─ alembic.ini        # Alembic config
└─ .env               # DATABASE_URL, etc. (optional)

1.2 Required Packages

python3 -m venv .venv && source .venv/bin/activate
pip install fastapi uvicorn sqlalchemy alembic pydantic "psycopg[binary]"  # For future PostgreSQL
# For SQLite only, psycopg is not needed

Term: Migration = A system for programmatically tracking DB schema changes (tables, columns, constraints, etc.) so you can upgrade (apply) or downgrade (revert) safely and repeatedly.

1.3 Choosing the Database URL (SQLite is fine for dev)

• Development (local): sqlite:///./app.db
• Production (PostgreSQL example):
  postgresql+psycopg://USER:PASSWORD@HOST:PORT/DBNAME

Keep it in .env for easy switching.

Key Takeaways

• Set up structure first → install dependencies
• Switch DB URL via environment variables, start with SQLite

2. Define SQLAlchemy ORM Models (2.x style for future-proofing)

2.1 db.py: Engine & Session (sync version)

Alembic works best with a sync engine (even if your app is async).

# app/db.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "sqlite:///./app.db"

engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {}
)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)

2.2 models.py: Declarative Base & Naming Conventions

Naming conventions keep autogenerated diffs stable — very important.

# app/models.py
from datetime import datetime
from sqlalchemy import String, DateTime, func, MetaData
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

naming_convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=naming_convention)

class Todo(Base):
    __tablename__ = "todos"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False, index=True)
    description: Mapped[str | None] = mapped_column(String(1000))
    is_done: Mapped[bool] = mapped_column(default=False, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )

Tip: server_default=func.now() uses DB defaults — works in SQLite and carries over naturally to PostgreSQL.

2.3 Let Alembic Handle Initial Table Creation

Do not call Base.metadata.create_all() directly.
Always create tables via Alembic upgrade so history stays consistent.

Key Takeaways

• Use SQLAlchemy 2.x type-hinted ORM for a solid future
• Naming conventions reduce diff “noise”
• From the start, let Alembic control schema creation

3. Initialize Alembic & Link App Metadata

3.1 Initialize

alembic init alembic

Generates alembic/ and alembic.ini.

3.2 Edit alembic.ini (DB URL)

Leave sqlalchemy.url commented; load from env vars to avoid accidental prod changes.

3.3 alembic/env.py: Point to App’s metadata

Pass your app’s Base.metadata to target_metadata so --autogenerate works.

# alembic/env.py (excerpt)
import os
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

config = context.config
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

from app.models import Base
target_metadata = Base.metadata

def get_url():
    url = os.getenv("DATABASE_URL")
    if url:
        return url
    return config.get_main_option("sqlalchemy.url")

def run_migrations_offline():
    context.configure(
        url=get_url(),
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
        compare_type=True,
        compare_server_default=True,
    )
    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online():
    connectable = engine_from_config(
        {"sqlalchemy.url": get_url()},
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True,
            compare_server_default=True,
        )
        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Key Takeaways

• Set target_metadata to your Base.metadata
• Enable compare_type / compare_server_default
• Prefer env var for URL

4. Create & Apply First Migration (Autogenerate)

4.1 Create Initial Revision

alembic revision --autogenerate -m "create todos table"

Check the generated file in alembic/versions/ — ensure the DDL matches your intent.

4.2 Apply to DB

alembic upgrade head

SQLite now has the todos table.
From here, every schema change is tracked in history.

4.3 Try a Rollback

alembic downgrade -1
alembic upgrade head

Always test upgrade/downgrade locally before production.

Key Takeaways

• --autogenerate is not magic — review the script
• Test round-trip migrations for safety