Chapter 12: Saving Data with SQLAlchemy and Alembic

EF Core is integrated into the .NET ecosystem. SQLAlchemy is powerful, mature, and more explicit. Alembic handles migrations.

SQLAlchemy 2.x declarative model:

from sqlalchemy import String, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Recipe(Base):
    __tablename__ = "recipes"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120), nullable=False)
    instructions: Mapped[str] = mapped_column(Text, nullable=False)
    servings: Mapped[int] = mapped_column(nullable=False)

Repository-style function:

from sqlalchemy import select
from sqlalchemy.orm import Session


def get_recipe(session: Session, recipe_id: int) -> Recipe | None:
    return session.get(Recipe, recipe_id)


def list_recipes(session: Session) -> list[Recipe]:
    statement = select(Recipe).order_by(Recipe.title)
    return list(session.scalars(statement))

Important differences from EF Core:

• SQLAlchemy sessions are units of work and identity maps, but they are not thread-safe.
• You must decide sync vs async database access. Do not mix casually.
• Explicit transactions are your friend.
• Lazy loading under async can surprise you. Prefer eager loading for relationship-heavy queries.
• Migrations are generated by Alembic, reviewed by humans, and applied in deployment.

For a newcomer, start with synchronous SQLAlchemy inside FastAPI unless you have a strong async database requirement. Sync database calls are commonly deployed behind multiple worker processes or threads. Async is powerful, but it adds constraints and does not automatically make database-bound apps faster.