The Missing Manual
⌘K
All topics / SQLAlchemy From Zero

SQLAlchemy From Zero

Learn Python's premier database toolkit — the ORM under Flask-SQLAlchemy and SQLModel: Core vs ORM, the engine and connections, declarative models, the Session and unit of work, the modern select() query API, relationships, loading strategies and the N+1 trap, and Alembic migrations. The library those wrappers wrap, made plain.

  1. What SQLAlchemy Is (Core vs ORM) SQLAlchemy is Python's database toolkit, built in two layers: Core (a Pythonic SQL expression language) and the ORM (maps classes to tables) on top of it. Here's the mental model that makes everything else click.
  2. The Engine & Connecting Meet the Engine — SQLAlchemy's central source of DB connectivity and its connection pool. Connect, run raw SQL safely with text(), manage transactions with begin(), and read Result objects.
  3. Defining Models Turn plain Python classes into database tables with SQLAlchemy 2.0 declarative models: DeclarativeBase, Mapped + mapped_column, column types and options, and create_all to build the schema.
  4. The Session & Unit of Work The heart of the ORM: the Session is your handle to SQLAlchemy, an identity map and unit of work that batches changes, tracks dirty objects, and flushes them as one — with object states explaining every behavior.
  5. Querying with select() Read data the SQLAlchemy 2.0 way: build a select() statement, execute it through the Session, pull objects with scalars/scalar/get, and filter, order, limit, and join — watching the SQL the whole time.
  6. Relationships Map foreign keys to navigable attributes with SQLAlchemy 2.0 relationship(): one-to-many with back_populates, many-to-many via an association table, the both-sides sync gotcha, and cascades.
  7. Loading Strategies & the N+1 Trap Why a relationship() fires a query the moment you touch it, how that quietly becomes the N+1 disaster, the DetachedInstanceError it causes, and how selectinload and joinedload collapse the flood back to one or two queries.
  8. Migrations with Alembic Why create_all can't evolve a live schema, and how Alembic gives you version-controlled migrations: init, autogenerate, review, upgrade, downgrade — the safe loop for changing tables that already hold data.
  9. SQLAlchemy in the Real World & Where to Go Next How SQLAlchemy actually shows up: as Flask-SQLAlchemy, as SQLModel, with async drivers, and beside raw SQL when the ORM gets awkward. Plus what to build next.