Skip to content
AyoKoding

Overview

Prerequisites

  • Prior topics: 10 · SQL Essentials -- writing joins, GROUP BY, and the Python sqlite3 DB-API this topic assumes you already know; 26 · Advanced SQL & Query Performance -- reading an EXPLAIN plan and how indexes change one, content this topic leans on throughout.
  • Tools & environment: a macOS/Linux terminal; Python 3.x, fully type-annotated (DD-39); a local PostgreSQL instance (a container is the easiest path); the DB-API driver (psycopg, PEP 249), a query builder (PyPika), a Data-Mapper ORM (SQLAlchemy 2.0.x), an Active-Record ORM (peewee), and a migration tool (Alembic) -- every version pinned and CVE-clean in this topic's own requirements.txt; Neovim/VSCode with the Python LSP (DD-17); pyright --strict for typechecking.
  • Assumed knowledge: writing joins and reading an EXPLAIN plan (topic 10); indexes and how they change a query plan (topic 26); reading a typed Python module and running a .py script (4 · Just Enough Python).

Why this exists -- the big idea

The problem before the solution: hand-writing SQL and mapping every result row to an object by hand is tedious and error-prone. Objects have identity, references, and inheritance; tables have rows, keys, and joins. That mismatch bred a generation of boilerplate mapping code, and it is exactly the gap the patterns in this topic -- Active Record, Data Mapper, Unit of Work, Identity Map -- were named to close.

Keep-this-if-you-forget-everything: an ORM is an abstraction over SQL, not a replacement for understanding it. It buys real productivity on CRUD and charges you the moment the query matters -- a report, a bulk job, a query with a shape the object model cannot express cleanly. Know which tier (raw SQL, query builder, or ORM) you are standing on for any given piece of code, and what that tier hides from you in exchange for its leverage.

Cross-cutting big ideas, taught here and then reused for the rest of this curriculum: abstraction-and-its-cost -- each tier hides more SQL for more leverage, and the hidden SQL leaks back out as the N+1 problem and the accidental full-table scan the moment you stop paying attention; coupling-vs-cohesion -- a Data-Mapper/Session layer keeps persistence concerns cohesive and decoupled from domain logic, instead of scattering hand-written SQL through the rest of the codebase.

Confirm your toolchain

Every example in this topic is a self-contained, fully type-annotated Python file colocated under learning/code/, run against a real local PostgreSQL instance -- every printed value and query count on this topic's pages is a genuine, captured transcript, never a guessed one:

$ python3 --version
Python 3.13.12
$ pip show sqlalchemy peewee pypika alembic psycopg pyright | grep -E "^(Name|Version)"
Name: SQLAlchemy
Version: 2.0.51
Name: peewee
Version: 4.2.6
Name: PyPika
Version: 0.51.1
Name: alembic
Version: 1.18.5
Name: psycopg
Version: 3.3.4
Name: pyright
Version: 1.1.411

Set up once, from this topic's own directory:

python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
docker run --name orm-by-example -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=orm_by_example -p 5432:5432 -d postgres:18.4-alpine

Every example's own defaults point at postgresql://postgres:postgres@localhost:5432/orm_by_example, so once the container above is running, python3 learning/code/ex-01-.../example.py (or any other example, run directly) works with no further configuration. Every example resets and seeds its own tables on every run, so examples are safe to run in any order, repeatedly. pyright (strict mode, via this topic's own pyrightconfig.json) and ruff format --check both run clean across every example.

How this topic's examples are organized

  • Beginner (Examples 1-28) -- the raw PEP 249 DB-API (connect, cursor, parameterized queries, typed row mapping, executemany, transactions), PyPika as a standalone query builder (SELECT, composed WHERE, JOIN, injection safety), SQLAlchemy Core's own builder (Table
    • MetaData, select()), declarative ORM mapping (DeclarativeBase + Mapped[]), the ORM's CRUD arc (insert, query, update, delete), the Active Record vs. Data Mapper contrast (peewee vs. SQLAlchemy), one-to-many and many-to-many relationships, the identity map, and the session lifecycle.
  • Intermediate (Examples 29-56) -- the four session object states and expire/refresh, the unit of work's flush ordering, dirty tracking, and autoflush, lazy loading and the DetachedInstanceError, the N+1 problem reproduced and measured, the three eager-loading strategies (selectinload, joinedload, subqueryload) contrasted, raiseload() as a guard, ORM transactions and nested savepoints, connection pooling (sizing, exhaustion, pool_pre_ping), the full Alembic workflow (init, hand-written migrations, upgrade/downgrade, autogenerate and its blind spots, reversible vs. explicitly-irreversible data migrations), and ORM cascade vs. database ON DELETE CASCADE.
  • Advanced (Examples 57-78) -- set-oriented bulk insert/update measured against a per-object ORM loop, the async ORM (create_async_engine + AsyncSession, why lazy loading is forbidden there, asyncio.gather over independent sessions), the ORM-vs-raw-SQL and query-builder-vs-ORM trade-offs made concrete (CRUD, reporting, a dynamic filter, a feature/cost table), a three-scenario tier-choosing rubric applied to a CRUD, an analytics, and a hot-path workload, a hybrid app that uses the ORM for CRUD and a raw-SQL escape hatch on the same session, the identity map dedup proven across two different query shapes, per-relationship default lazy-strategy configuration, a self-referential adjacency-list tree, an association object carrying extra columns on an M:N link, a zero-downtime expand-contract migration, connection-pool tuning measured against a concurrency target, and a capstone-preview example threading all three tiers plus an N+1 fix plus a migration into one script.

The 27 concepts this topic covers

  • co-01 · data-access-spectrum -- the raw-SQL to query-builder to ORM spectrum, and what each tier buys and hides.
  • co-02 · raw-sql-dbapi -- the PEP 249 DB-API: connection, cursor, execute, fetch, and manual row-to-object mapping.
  • co-03 · query-builder-core -- building queries as composable data structures rather than concatenated strings.
  • co-04 · query-builder-library-contrast -- how a standalone builder (PyPika) and SQLAlchemy Core differ in style and scope.
  • co-05 · parameterized-queries-and-emitted-sql -- placeholders keep values out of the SQL text; inspecting the emitted SQL and its bound parameters.
  • co-06 · declarative-orm-mapping -- SQLAlchemy 2.0's DeclarativeBase + Mapped[...] typed column mapping.
  • co-07 · active-record-vs-data-mapper -- the object persists itself (peewee) vs. a session/mapper persists it (SQLAlchemy).
  • co-08 · relationship-mapping -- one-to-many/foreign-key relationships and bidirectional back_populates.
  • co-09 · many-to-many-association -- association tables and association objects for M:N links.
  • co-10 · identity-map -- one Python object per primary key within a session, deduplicated across queries.
  • co-11 · session-object-states -- transient to pending to persistent to detached, plus expire/refresh.
  • co-12 · unit-of-work -- the session batches, orders, and flushes changes as one coordinated write.
  • co-13 · lazy-loading -- relationships fetched on first access, and the DetachedInstanceError when the session is gone.
  • co-14 · eager-loading-strategies -- selectinload, joinedload, and subqueryload, and how each shapes the emitted SQL.
  • co-15 · n-plus-1-problem -- one query fanning out into many, diagnosed by query count and fixed by eager loading.
  • co-16 · raiseload-guard -- raiseload() turns an accidental lazy load into a loud error instead of a silent query.
  • co-17 · orm-transactions -- session commit/rollback, begin() scope, and nested savepoints.
  • co-18 · connection-pooling -- pool_size/max_overflow, pool exhaustion, and pool_pre_ping recovery.
  • co-19 · migrations-alembic-workflow -- versioned schema evolution with Alembic (init, revision, upgrade).
  • co-20 · autogenerate-migrations -- deriving a migration from a model-to-schema diff, and why you still review it.
  • co-21 · migration-reversibility -- writing real downgrade paths and the expand-contract zero-downtime pattern.
  • co-22 · cascade-delete-orm -- ORM cascade="all, delete-orphan" vs. database ON DELETE CASCADE.
  • co-23 · bulk-operations -- set-oriented bulk insert/update instead of per-object round trips.
  • co-24 · async-orm-session -- create_async_engine + AsyncSession, and why lazy loading is forbidden there.
  • co-25 · orm-vs-raw-sql-tradeoff -- where the ORM's leverage helps (CRUD) and where raw SQL wins (reporting, bulk).
  • co-26 · query-builder-vs-orm-tradeoff -- composition and injection safety without the identity-map/change-tracking machinery.
  • co-27 · choosing-tier-per-workload -- matching CRUD, analytics, and hot-path workloads to the right tier.

Examples by Level

Beginner (Examples 1–28)

Intermediate (Examples 29–56)

Advanced (Examples 57–78)

← Previous: 26 · Advanced SQL & Query Performance Drilling · Next: Beginner Examples →

Last updated July 17, 2026

Command Palette

Search for a command to run...