Overview

Raw SQL over the PEP 249 DB-API is the floor -- full control, zero abstraction, every join and column typed by hand. A query builder (PyPika, or SQLAlchemy Core) composes the SAME SQL as data structures instead of concatenated text, buying safety and reuse while still emitting SQL you can read directly. A full ORM (SQLAlchemy's declarative mapping, or peewee) maps rows to objects with identity, relationship navigation, and change tracking, buying real productivity on CRUD at the cost of hiding the SQL it emits -- hidden SQL that leaks back out as the N+1 problem the moment you stop paying attention (Example 1 answers the SAME query all three ways; Example 78 and the capstone's report_three_tiers.py both prove the three tiers agree on one correct answer).

Connect (opens a connection), cursor (a handle for issuing statements against that connection), execute (sends one SQL statement, optionally with bound parameters), and fetch (reads the result rows back). .fetchone() reads exactly one row (or None if there are no more), while .fetchall() materializes every remaining row into a list at once -- the right choice depends on whether the result set is small enough to hold entirely in memory (Example 2's connect+cursor; Example 4's manual row-to-dataclass mapping, which is exactly the boilerplate an ORM automates away).

Each clause -- .select(), .join(), .on(), .where(), .groupby() -- returns a Query VALUE that the next method call extends, so the whole statement is a tree of Python objects until you finally ask for its rendered SQL text. Because every value composed into that tree passes through the builder's own escaping/parameter machinery rather than raw string interpolation, a builder-composed query is immune to SQL injection by construction, the same way a parameterized raw query is (Example 7's SELECT; Example 8's composed WHERE; Example 11 directly contrasts builder composition against string-concatenated SQL).

PyPika is a lightweight, standalone query-composition library with no ORM, no engine, and no connection management of its own -- it renders SQL text and something else (plain DB-API, in this topic) executes it. SQLAlchemy Core's builder is one layer of the SAME library that also ships the ORM -- it shares Table/MetaData/Engine objects with SQLAlchemy's declarative mapping, so a codebase that also uses the SQLAlchemy ORM elsewhere gets tighter integration for free, while a codebase with no ORM at all carries a smaller dependency with PyPika (Example 9's PyPika JOIN; Example 12's SQLAlchemy Core Table; Example 13's SQLAlchemy Core select()).

A placeholder sends the SQL TEXT and the VALUES as two separate pieces of data over the wire -- the database parses the SQL text once, into a fixed structure, and only then binds the values into that already-parsed structure, so a value containing a quote or a keyword can never be interpreted as SQL syntax. String interpolation instead builds ONE combined text blob before the database ever sees it, so a crafted value can break out of its intended position and add real SQL the query never intended (Example 3's parameterized query; Drilling Kata 3 reproduces the exact injection a %s placeholder prevents).

Mapped[int]/Mapped[str]/etc. tells SQLAlchemy's own mapper machinery "this attribute is a real mapped column, backed by a database column with this Python type" -- a bare int/str annotation is just a type hint with no persistence meaning at all. DeclarativeBase is the shared registry every mapped class in a program inherits from; it is what lets relationship() calls on DIFFERENT classes resolve each other by name, and what Base.metadata.create_all() reads to know which tables exist at all (Example 14's basic model; Example 15's fully typed columns).

In Active Record (peewee), the model class itself knows how to save and delete -- calling .save() directly on an instance is the API. In Data Mapper (SQLAlchemy), the model class carries no persistence methods of its own at all -- a separate Session object (the mapper) is what tracks, flushes, and commits changes, keeping the domain object itself free of any database-specific method (Example 20's peewee .save(); Example 21 contrasts the same CRUD operation written both ways side by side).

back_populates tells SQLAlchemy that two relationship() declarations -- one on each side of a foreign key -- are the SAME logical link, viewed from opposite directions. Appending to the "many" side's collection (or setting the "one" side's reference) then automatically updates the OTHER side's in-memory attribute too, in the SAME Python process, before any flush -- without it, each side would only reflect changes made directly to itself until the object is reloaded from the database (Example 22's one-to-many FK; Example 23 names back_populates directly and shows both directions staying in sync).

A plain association table (just two foreign keys) has no natural place for a per-link attribute -- an enrollment grade, an assignment's logged hours, an approval date. An association OBJECT is a full mapped class sitting on that SAME link table, so it can carry arbitrary extra columns and be queried, updated, and navigated like any other entity. The cost is one extra hop when navigating: student.courses (a plain M:N) reads the OTHER side directly, while student.enrollments[i].course (an association object) reads through the link object first (Example 25's plain association table; Example 75's association object with a grade column; the capstone's Assignment carries hours_logged the same way).

Within ONE session, fetching the same primary key twice -- by any query shape -- returns the literal SAME Python object both times, is, not merely ==. That guarantee is scoped strictly to a single session's own identity map; two DIFFERENT sessions each fetching the same row get two DIFFERENT Python objects with equal but independently-tracked data (Example 27 proves the SAME-session guarantee directly; Drilling Kata 6 reproduces the cross-session case where the assumption breaks).

Transient (constructed, no session has ever seen it) becomes pending the moment session.add() (or a cascading add) registers it. Pending becomes persistent the moment a flush actually sends its INSERT and it gets a real primary key. Persistent becomes detached the moment its owning session closes (or expunge()s it) -- the object still exists in Python memory, still has its id, but no session's identity map or unit of work tracks it anymore (Example 28's session lifecycle; Example 29 walks all four states in order with inspect(); the capstone's Step 3f repeats this walk on a throwaway team).

Rather than sending each add()/attribute-mutation as its own immediate statement, the session accumulates pending inserts, updates, and deletes and only sends them at flush() (explicit, or implicit before a query/commit) -- and it orders the sent statements to respect foreign-key dependencies automatically: a parent's INSERT before its children's, a child's DELETE before its parent's. session.new/session.dirty expose exactly what a pending flush will send, before it sends it (Example 31's flush ordering; Example 32's dirty tracking; Example 33's autoflush; Drilling Kata 5 reproduces what happens when autoflush is disabled).

By default, a relationship attribute (customer.orders) issues NO query when the parent object is first loaded -- the SELECT fires the FIRST time code actually touches .orders, not before. If that first touch happens AFTER the owning session has already closed, there is no open session left to run that lazy query against, and SQLAlchemy raises DetachedInstanceError instead of silently failing (Example 34's default lazy load; Example 35 reproduces the detached error directly; Drilling Kata 2 reproduces the exact same failure and fixes it by eager-loading before the session closes).

selectinload issues a SECOND, separate SELECT ... WHERE parent_id IN (...) batching every parent's children into one additional round trip -- the strategy this topic recommends by default. joinedload issues ONE combined query with a LEFT OUTER JOIN, fetching parents and children together at the cost of a wider, potentially duplicated result set. subqueryload also issues a second query, but shaped as a correlated subquery rather than an IN list -- SQLAlchemy's own docs now describe it as "mostly legacy," superseded by selectinload for most cases (Example 37's selectinload; Example 38's joinedload; Example 39's subqueryload; Example 40 contrasts all three directly).

N+1 is issuing ONE query to fetch N parent rows, then, inside application code, one ADDITIONAL query PER PARENT to fetch that parent's related rows -- N+1 total round trips, growing linearly with N. Fixing it with eager loading changes ONLY the query count, never the returned data -- the SAME rows come back either way, just fetched in a constant 2 queries instead of N+1 (Example 36 reproduces N+1 with real query counting; Example 42 asserts the exact before/after counts; the capstone's Step 3 measures the identical pattern at 6 queries before, 2 after, on its own team/member/assignment data).

raiseload() marks a relationship so that touching it NEVER silently issues a lazy query -- instead it raises InvalidRequestError immediately, at the exact call site that would otherwise have fired an accidental N+1. A silent extra query is invisible in a code review and often invisible in a quick manual test too, surfacing only under real production load; a loud, immediate exception is caught by ANY test that exercises the guarded code path, turning "forgot to eager-load" from a runtime performance bug into a development-time crash (Example 41's raiseload() guard; the capstone's Step 3d re-guards the EXACT access pattern Step 3b's N+1 measurement reproduced, on the same data).

session.rollback() both rolls back the underlying database transaction AND expires every object the session was tracking -- their in-memory attribute values are marked stale, so the NEXT access to any attribute on any of them triggers a fresh SELECT reflecting the database's true, rolled-back state. This is why reading an attribute right after a rollback shows the ORIGINAL value, not the uncommitted change that was just discarded (Example 43's commit/rollback; Example 44's nested savepoint; the capstone's Step 3e renames Ada, rolls back, and reads ada.name again in the SAME session, confirming the expiry-triggered reload shows the original value).

pool_size sets how many connections the pool keeps OPEN and ready for reuse; max_overflow sets how many EXTRA connections the pool may open temporarily above pool_size under a burst of concurrent demand, before new requests start waiting. pool_pre_ping issues a cheap validation query on a pooled connection before handing it out, so a connection the DATABASE silently closed (an idle timeout, a network blip) gets discarded and replaced automatically instead of surfacing as a confusing error on the caller's real query (Example 45's pool basics; Example 46's exhaustion under load; Example 47's pool_pre_ping recovery; Example 77's tuning against a concurrency target).

alembic init scaffolds a fresh migration project (env.py, script.py.mako, a versions/ folder). alembic revision creates a new, timestamped migration file with empty upgrade()/downgrade() functions to fill in by hand (or via autogenerate). alembic upgrade/downgrade actually RUN a migration's upgrade() or downgrade() function against a live database connection, moving the schema forward or backward one (or more) revisions (Example 48's init; Example 49's hand-written revision; Example 50's upgrade/downgrade; the capstone's Step 4 runs all three against its own live shared schema, not a throwaway one).

Autogenerate connects to the LIVE database, reflects its actual current schema, compares that reflection against what the SQLAlchemy models currently declare, and writes a migration file containing the DDL operations it computes would reconcile the difference. It is a PROPOSAL, not a guarantee -- it can miss changes it cannot detect (a column rename it sees as a drop-plus-add, a check constraint, certain index changes) or propose noisy false-positive diffs, so every autogenerated migration still needs a human read-through before it ships (Example 51's autogenerate against a genuinely new table; Example 52 shows a case autogenerate gets subtly wrong).

A schema-only migration (add a column, drop a column) is reversible if downgrade() undoes the exact structural change. A DATA migration -- one that also TRANSFORMS row values, like splitting a full_name column into first_name/last_name -- is only truly reversible if downgrade() merges the values back together too, not just drops the new columns; dropping without merging silently LOSES real data the split had preserved. The expand-contract pattern (add-nullable, backfill, THEN tighten to NOT NULL) is what keeps both directions of that round trip safe (Example 53's real data-migration round trip; Example 54's explicitly-irreversible guard; Example 76's zero-downtime expand-contract; the capstone's Step 4a backfills member.active before tightening, then reverses cleanly).

cascade="all, delete-orphan" is Python-side relationship configuration -- the ORM itself computes and issues the extra DELETE statements for child rows BEFORE deleting the parent, entirely independent of whatever the database schema itself declares. ON DELETE CASCADE is a database-level FOREIGN KEY constraint option -- it fires for ANY delete that reaches the database, regardless of which tier (raw SQL, a query builder, or the ORM) issued it. With NEITHER configured, deleting a parent with a required (NOT NULL) child FK still pointing at it fails with an IntegrityError, because the ORM's own default behavior (try to null out the child FK) collides with the column's own NOT NULL constraint (Example 55's ORM cascade; Example 56 contrasts it against the database's own ON DELETE CASCADE; the capstone's Step 3g exercises BOTH mechanisms on two different relationships in the same script; Drilling Kata 4 reproduces the IntegrityError neither mechanism being configured produces).

A per-object session.add() loop constructs N tracked Python objects, each participating in the identity map and change tracking, and flush() issues (up to) N separate INSERT statements for them -- useful overhead when the code ALSO needs each object's identity, relationships, or further mutation, but pure cost when it does not. A single Core insert(table) executed with a LIST of plain dicts never constructs any ORM object at all -- the driver batches the whole list into ONE statement's worth of network traffic, at the cost of skipping the ORM's own per-object machinery entirely (Example 57's bulk insert; Example 58's set-based update(); Example 59 measures the throughput gap directly; the capstone's Step 4b/4c bulk-inserts 50 rows and re-triages 53 in exactly 2 statements total).

A lazy load is, underneath, a synchronous, blocking network call -- under asyncio, that would block the entire event loop, defeating the whole point of using async in the first place. AsyncSession therefore forbids implicit lazy loading structurally: touching an unloaded relationship raises an error rather than silently blocking the event loop, forcing every relationship access to be either eager-loaded up front or awaited explicitly via the AsyncAttrs mixin's awaitable_attrs escape hatch (Example 60's async engine/session; Example 61's async eager loading; Example 62 reproduces the forbidden lazy access; Example 63's concurrent sessions via asyncio.gather).

CRUD and object-graph navigation are where the ORM earns its keep -- the identity map, change tracking, and relationship navigation directly replace real boilerplate (manual row-to-object mapping, manual dirty-checking) that raw SQL would otherwise require by hand. A reporting query or a bulk job usually favors raw SQL or a query builder instead -- the ORM's per-object machinery adds overhead the query never needed, and a hand-written GROUP BY or a set-based UPDATE is often SHORTER, not longer, than the equivalent expressed through mapped entities (Example 64's ORM-vs-raw CRUD; Example 65's ORM-vs-raw reporting; the capstone's report_three_tiers.py shows all three tiers expressing the SAME report at roughly equal complexity, while Step 4's bulk writes show raw Core clearly winning on throughput).

A query builder composes clauses as VALUES that can be conditionally added based on runtime input (an optional filter, a dynamic sort column) without string concatenation, while carrying none of the ORM's identity-map/change-tracking machinery -- machinery that a purely dynamic, read-only filter query never needed in the first place. The ORM's select() can ALSO be built dynamically (it is Core underneath), but a query builder used standalone (PyPika, with no ORM involved at all) stays a leaner dependency for a codebase that never needs object identity or persistence (Example 66's dynamic filter built with a query builder; Example 67 contrasts the same dynamic filter against the ORM's own select()).

CRUD (create/read/update/delete on individual records, with real object-graph navigation) tends to favor the full ORM -- the identity map and relationship machinery directly pay for themselves. Analytics (wide aggregations, reporting, joins across many rows with no need for object identity) tends to favor raw SQL or a query builder -- the ORM's per-object overhead buys nothing a GROUP BY doesn't already express directly. A latency-sensitive hot path (a request handled thousands of times per second, where every microsecond of ORM overhead compounds) tends to favor raw SQL or a query builder too, for the SAME reason analytics does -- minimal overhead between the code and the wire (Example 68's CRUD scenario; Example 69's analytics scenario; Example 70's hot-path scenario; Example 71's hybrid app using the ORM for CRUD and a raw-SQL escape hatch on the SAME session).

This is the N+1 problem (co-13, co-15) -- one query lists the customers, then one ADDITIONAL query per customer fetches that customer's most recent order, so total round trips grow linearly with the customer count. selectinload (or a batched IN (...)) collapses this to a constant 2 queries regardless of how many customers exist (Example 36; Example 37; the capstone's Step 3 measures the identical shape).

This is the ORM's OWN cascade (co-22) -- cascade="all, delete-orphan" on the Team.members relationship is entirely Python-side configuration; SQLAlchemy computes and issues the child DELETE statements itself, before the parent's, regardless of whatever (or however little) the database schema itself declares (Example 55; the capstone's Step 3g deletes its own scratch team this exact way).

This is the string-interpolation injection risk parameterized queries exist to prevent (co-02, co-05) -- a crafted input containing a quote can break out of its intended string literal and add arbitrary SQL the query never intended. A %s placeholder sends the value on a separate channel from the SQL text, so it can never be interpreted as SQL syntax regardless of its contents (Example 3; Example 11; Drilling Kata 3 reproduces the exact injection and its fix).

This is a migration that reverses the SCHEMA but not the DATA (co-21) -- true reversibility for a data migration means downgrade() must MERGE the split values back into the original column before dropping the new ones, not just discard them. A naive drop-only downgrade() looks reversible (the schema shape does round-trip) while silently destroying real user data (Example 53 shows the correct merge-back pattern; Example 54 shows a migration the topic explicitly marks irreversible rather than pretending otherwise).

This is the bulk-operations tradeoff (co-23) -- the per-object ORM loop constructs 50,000 tracked Python objects and issues up to 50,000 individual INSERTs at flush, machinery that pure bulk throughput never needed. Core's insert() executed with a list of dicts never constructs an ORM object at all, batching the whole write into far fewer statements (Example 57; Example 59 measures the gap directly; the capstone's Step 4b bulk-inserts 50 rows in exactly 1 statement).

This is pool exhaustion (co-18) -- pool_size plus max_overflow caps how many connections the application's OWN pool will hand out at once, independent of whatever capacity the database server itself has left; once every pooled (and overflow) connection is checked out, the NEXT request waits (or fails) regardless of server headroom. Raising pool_size/max_overflow (or shortening how long requests hold a connection) relieves this, without touching the database server at all (Example 45; Example 46 reproduces exhaustion directly; Example 77 tunes against a concurrency target).

This is the forbidden implicit lazy load under AsyncSession (co-24) -- a lazy load is a blocking network call underneath, and blocking calls are structurally disallowed inside asyncio's event loop, so SQLAlchemy raises an error immediately rather than silently blocking everything else the event loop is doing. The fix is eager-loading the relationship up front, or awaiting it explicitly through the AsyncAttrs mixin's awaitable_attrs escape hatch (Example 61; Example 62 reproduces the exact crash).

This is the query-builder-vs-ORM style choice for a dynamic query (co-26) -- both a standalone builder and the ORM's own select() (itself Core underneath) can compose clauses conditionally at runtime without string concatenation; the choice between them is about which one already integrates with the REST of the codebase (an ORM-heavy codebase likely already imports SQLAlchemy; a codebase with no ORM at all keeps a leaner footprint with a standalone builder) rather than a hard capability difference (Example 66; Example 67 contrasts both directly on the SAME dynamic filter).

This is exactly why autogenerate's output needs a human review before it ships (co-20) -- it is correctly reporting a real DIFFERENCE between the live database and the models, but "the model doesn't mention this index" does not automatically mean "drop the index" is the right migration; it might instead mean the MODEL should be updated to declare an index that already exists and is doing real work in production. Autogenerate proposes; a human decides which side of the diff is actually wrong (Example 51; Example 52 shows a different case autogenerate gets subtly wrong for a related reason).

This is choosing the WRONG tier for the workload (co-27) -- CRUD (the API) and analytics (the dashboard) are different workload shapes with different needs: CRUD benefits from the ORM's identity map and relationship navigation, while analytics benefits from raw SQL or a query builder's minimal overhead over a wide aggregation. Using ONE tier everywhere for consistency's sake trades away exactly the tradeoff this topic's rubric exists to make deliberately (Example 68's CRUD scenario; Example 69's analytics scenario; Example 70's hot-path scenario, the SAME lesson applied a third way).

Root cause: a @property LOOKS like a self-contained, pure computation from every call site, but it still executes the SAME self.orders attribute access underneath -- and a lazy relationship fires its own SELECT the first time it is touched, regardless of whether that touch happens directly or through a property. The fix never changes the property's own code at all -- it eager-loads .orders for every customer in the SAME initial query, so by the time the property runs, the data is already in memory.

Run: python3 kata.py

Output:

grand_total=49.95 queries=2

Root cause: a relationship's default (lazy) loading strategy defers its SELECT until first touch, and a DETACHED object has no session left to run that deferred query against. The fix does not avoid detachment -- ada is still detached at the exact same point -- it instead ensures .orders is already LOADED before detachment happens, via selectinload, so nothing needs to query anything afterward.

Run: python3 kata.py

Output:

order_count=0
no error raised

Root cause: f"... = '{value}'" builds ONE combined SQL text before the database ever sees it -- a quote inside value breaks out of the intended string literal and adds real SQL. A %s placeholder sends the SQL text and the value as two SEPARATE pieces of data; the database parses the text once into a fixed structure and only then binds the value into it, so a quote inside the value can never change the query's structure.

Run: python3 kata.py

Output:

rows_returned=0
emails=[]

Root cause: with no cascade= option, SQLAlchemy's default relationship behavior tries to detach the child from the parent by setting its foreign key to NULL before deleting the parent -- a strategy that only works when the FK column is nullable. Because customer_id is NOT NULL, that attempted UPDATE collides with the column's own constraint, and the database rejects it as an IntegrityError. cascade="all, delete-orphan" tells the ORM to delete the child rows outright instead of trying to detach them, which respects the NOT NULL constraint by removing the row entirely rather than nulling one of its required columns.

Run: python3 kata.py

Output:

remaining_customers=0 remaining_orders=0

Root cause: the unit of work normally flushes every pending change BEFORE running a new query, specifically so a read inside the SAME session never misses its own uncommitted writes. autoflush=False disables exactly that safeguard, so the count query runs against whatever the database already durably contained, ignoring the still-pending INSERT. The default (autoflush=True, no argument needed) is almost always the right choice; disabling it is a narrow, deliberate performance optimization, not a safe default.

Run: python3 kata.py

Output:

count_with_default_autoflush=1

Root cause: the identity map is an attribute of ONE Session object's own internal state -- it was never designed to be a cross-session or process-wide cache, and two independently constructed sessions have two independently empty identity maps to start with. The "fix" is really a correction of expectation, not a code change to the identity map itself: fetch through the SAME session whenever code depends on object identity holding, and treat cross-session equality as VALUE equality only.

Run: python3 kata.py

Output:

same_object_within_one_session=True