Python for JS Devs · FastAPI

Database access (SQLAlchemy 2.0)

Prisma/TypeORM vibes — but learn the modern typed 2.0 style, not the legacy patterns the AI code used.

Lesson 18Time ~15 min Win Define a model & run a typed queryPrereq Lessons 06, 14

SQLAlchemy is the standard Python ORM. Version 2.0 introduced a typed style that plays beautifully with the type hints you learned in Lesson 06. The AI-generated project you had used the old 1.x style — this lesson is the modern replacement.

1 · Modern models: Mapped[], not Column

Legacy 1.x (avoid)
Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer,
        primary_key=True)
    email = Column(String)
Modern 2.0 (use)
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column)

class Base(DeclarativeBase): pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True)
    name: Mapped[str | None]   # nullable via | None

The column type comes from the Mapped[...] hint — no more __allow_unmapped__ workarounds. Nullability is expressed with | None, exactly like your other type hints.

2 · Engine, session, and the DI hook

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session

engine = create_engine(settings.database_url)
SessionLocal = sessionmaker(engine)

def get_db():                 # the yield-dependency from Lesson 14
    with SessionLocal() as db:
        yield db

3 · Queries: select(), not db.query()

Legacy 1.x (avoid)
db.query(User).filter(
    User.email == e
).first()
Modern 2.0 (use)
from sqlalchemy import select

stmt = select(User).where(User.email == e)
user = db.scalars(stmt).first()   # typed result

Writes are unchanged and intuitive: db.add(obj), then db.commit(), and db.refresh(obj) to reload DB-generated fields (like an autoincrement id).

new = User(email="a@b.co", name="kd")
db.add(new)
db.commit()
db.refresh(new)      # now new.id is populated
Async + FastAPI note (Lesson 17 payoff) The code above is sync — so use it in plain def routes (FastAPI threadpools them safely). For async def routes, use the async stack: create_async_engine, AsyncSession, and await db.execute(stmt). Pick one lane and stay in it. Migrations are handled by Alembic (uv add alembic) — the "django migrate"/Prisma-migrate equivalent.
Don't leak ORM objects Return a Pydantic response_model (Lesson 13), not the raw ORM object. Add model_config = {"from_attributes": True} to the output model so it can read attributes off the ORM instance.

🎯 Your win

Using SQLite for speed (create_engine("sqlite:///./app.db")), define the modern User model, create the table with Base.metadata.create_all(engine), then in a plain def route: insert a user (add/commit/refresh) and read it back with select(User).where(...). A round-trip through a real DB in the 2.0 style is the win.

Check yourself

Q1. A column type in SQLAlchemy 2.0 is declared via:
2.0 uses id: Mapped[int] = mapped_column(...) — the hint carries the type.
Q2. The modern way to build a read query is:
select(User).where(...) executed via db.scalars()/db.execute() is the 2.0 style.
Q3. Sync SQLAlchemy calls belong in which kind of route?
Sync DB calls go in def routes (threadpooled). Async DB needs async def + AsyncSession.
Primary source SQLAlchemy 2.0 — ORM Quick Start and FastAPI's SQL Databases tutorial.
💬 Ask your teacher: should I use SQLModel (tiangolo's Pydantic+SQLAlchemy combo) instead? Worth a conversation — there are real tradeoffs.
← Lesson 17Lesson 19 →