Overview

A clause tree is a genuine Python value -- you can read its fields, walk it, compare two trees, and compile the same tree twice, all without ever touching a database or re-parsing text. A raw string only reveals its structure by being re-parsed; nothing about it is inspectable as data (Example 1 represents a single column reference as a data node and proves rendering is deferred; Example 2 extends it to a table-qualified node).

Because the only way to guarantee a value can never change what the SQL statement DOES is to never let it become part of the SQL text at all -- the driver treats a bound parameter as pure data, so a value containing '; DROP TABLE users;-- stays an inert string, never a second statement. Interpolating it directly into the string (Kata 2's bug) reopens exactly this risk (Example 3 binds a deliberately hostile string and proves the targeted table survives; Example 4 compiles two bound values and verifies the params list stays left-to-right).

It protects a shared, partially-built base query from ever being corrupted by ONE caller's branch leaking into another's -- since nothing about the original instance can change, two callers can safely start from the same base and add their own filters independently, with no aliasing bug possible (Example 5 proves .where() never changes the original; Example 6 branches one shared base into two independent variants; Kata 1 reproduces the mutable-default-argument version of exactly this leak).

Each fragment -- .select(), .from_(), .join() -- contributes its own piece independently, so adding a JOIN never requires touching the code that renders columns, and each fragment stays independently testable and independently correct (Example 7 lists explicit columns in call order; Example 8 attaches FROM; Example 9 shows the SELECT * default; Example 10 adds a JOIN with its ON predicate).

Each And/Or combinator is a node that knows it is one, and the compiler wraps its own rendered output in parentheses on every recursive step -- a query never has to reason about implicit operator precedence, because every level of the tree renders its own explicit grouping (Example 11 compiles a single equality; Example 12/13 combine two predicates with AND/OR; Example 14 covers <, >, !=, IN; Example 15 nests a real three-leaf tree).

Treating them as ordinary bound parameters keeps the builder's "always parameterize" guarantee uniform across every clause -- carving out "this literal is safe because it's just a number" as a special case is exactly the kind of exception that's easy to get wrong later, or to forget applies somewhere else too (Example 16 appends ORDER BY; Example 17 adds DESC; Example 18 parameterizes LIMIT/OFFSET together against real seeded rows).

All three reuse the exact same Predicate protocol SELECT's WHERE already uses, so the parameterization guarantee (co-02) and the composability guarantee (co-05) apply uniformly to every write path too -- there is no separate, easier-to-get-wrong code path just for mutations (Example 19 builds a parameterized INSERT; Example 20 an UPDATE whose SET params precede its WHERE params; Example 21 a DELETE scoped by WHERE).

A hard boundary between "building a query" (pure data + pure function) and "running a query" (the driver) means the builder can be fully tested with no database required, and the same compiled (sql, params) tuple can be logged, cached, or replayed exactly and deterministically -- calling compile() twice on the same builder must never mutate the builder or share mutable state between the two returned params lists (Example 22 asserts the return value is genuinely a 2-tuple; Example 23 proves purity directly).

The builder's column list and the mapper's column list can drift apart without either side noticing -- a column renamed in one place and not the other becomes a KeyError far from its actual cause, discovered only when a query actually runs. One registry means exactly one place to update (Example 27 registers columns and a primary key; Example 28 builds a SELECT whose column order matches registration; Example 29 reads the primary key back out).

Centralizing "which column goes to which attribute" in one function means a schema change gets fixed in exactly one place, instead of every caller having to remember column order (or names) and re-derive the mapping independently -- a fragile, easy-to-desync duplication otherwise (Example 30 maps a tuple by column order; Example 31 maps a dict by column name; Example 32 maps a whole fetchall() to a list of objects).

The primary key identifies WHICH row the UPDATE targets (via WHERE) -- including it in SET as well would attempt to rewrite the very column the statement uses to find the row, which for an immutable PK column the database itself rejects (Example 33 reads an object into an INSERT-ready dict; Example 34 builds an UPDATE SET dict excluding the PK; Example 35 round-trips object-row-object and proves equality).

SQLite's storage types are only NULL, INTEGER, REAL, TEXT, BLOB -- there is no native boolean or date type at all, so a "boolean" column is actually an integer at the storage level, and without an explicit coercion step every caller would have to remember that and compare against 1 by hand, every time (Example 36 coerces 0/1 to bool on load; Example 37 an ISO string to date; Example 38 coerces both back on store; Example 39 registers a custom converter; Kata 8 reproduces the is True failure this coercion prevents).

It guarantees exactly one in-memory object per primary key within a session -- two loads of the same row return the identical instance, not two independent copies that can silently drift apart when one is mutated. That guarantee is a precondition for the unit of work's dirty tracking (co-17) to even make sense (Example 40 loads the same PK twice and asserts identity; Example 41 asserts distinct instances for distinct keys; Example 42 shows a miss then a hit issuing no second query; Example 43 keys by (table, pk); Kata 3 reproduces the two-independent-copies bug directly).

A plain dict-backed identity map keeps every object it ever loaded alive for the whole session's lifetime, even after every caller has finished with it -- a slow, silent memory leak for a long-running worker or REPL. A WeakValueDictionary lets an entry disappear the moment nothing else in the program still references its object, so the cache tracks the program's actual live working set (Example 44 proves an entry drops after GC; Example 45 loads many rows, drops references, and proves the map shrinks).

Scattering commit() calls across unrelated functions makes it impossible to reason about what's actually atomic -- two writes meant to succeed or fail together could end up split across two separate implicit transactions the moment anything outside the session commits early. A session that owns the boundary exclusively is the one place transaction scope is decided (Example 46 shows one connection shared across every query; Example 47 begin/write/commit persists; Example 48 begin/write/rollback leaves the row absent; Example 49 uses the session as a context manager).

Recording it in a set (not writing immediately) is what lets the unit of work batch every pending write into a single flush, and -- combined with flush ordering (co-19) -- insert a parent before the child that references it, all inside one transaction, regardless of what order objects were added in (Example 55 calls .add() and asserts the new-set; Example 56 flushes it and asserts exactly one INSERT).

Dirty detection works by comparing an object's current state against that snapshot -- if the snapshot is taken AFTER a mutation already happened, the snapshot matches the already-changed state and nothing is ever detected as dirty, no matter what actually changed (Example 50 takes the load-time snapshot; Example 57 mutates and detects dirty; Example 58 proves only the changed column appears in the UPDATE; Example 59 proves an unmodified object emits no UPDATE; Kata 4 reproduces the snapshot-taken-too-late bug directly).

In a garbage-collected language, releasing a reference is NOT the same operation as deleting a database row -- conflating the two is a common source of "why is this row still here" bugs. An explicit deleted-set makes deletion a first-class, trackable intent that the flush step can act on, independent of Python's own reference counting (Example 60 calls .delete() and asserts the deleted-set; Example 61 flushes it and asserts exactly one DELETE).

INSERTs of parent rows happen BEFORE INSERTs of rows that reference them by foreign key; DELETEs of child rows happen BEFORE DELETEs of the parents they reference -- flushing in an arbitrary order (say, insertion order into the tracked sets) would frequently violate the FK constraint the database itself enforces (Example 62 proves a parent's INSERT precedes its FK-dependent child's; Example 63 proves a child's DELETE precedes its parent's; Kata 5 reproduces the exact IntegrityError the wrong order produces).

A flush that commits some writes and fails partway through the rest leaves the database in a state no application logic ever intended -- half a batch persisted, the rest not. Wrapping the whole flush in one transaction makes "all or nothing" the only possible outcome, and a successful flush clears the tracking sets afterward so the next flush starts clean (Example 64 commits a mixed batch together; Example 65 injects a mid-flush error and proves a full rollback; Example 66 proves the sets are empty after success).

__set_name__ (PEP 487) lets the descriptor learn its own attribute name at class-definition time; __get__ is what actually runs the deferred query, and caches the result so a SECOND access on the same object issues no additional query (Example 67 proves the query is deferred until access; Example 68 uses __set_name__ to bind the descriptor's own name; Example 69 accesses the lazy attribute twice and proves only one query runs).

One query loads the N parents; then each parent's first access to its lazy relationship issues its own separate query -- N additional queries, N+1 total. Batching the child load into one extra query (fetching every child row for the whole current batch of parents via WHERE customer_id IN (...)) collapses N+1 down to exactly 2, regardless of how large N is (Example 70 counts N+1 real queries in a log; Example 71 adds the eager batch load and proves exactly 2; Kata 6 reproduces both counts directly).

compile()'s (sql, params) output is exactly the pair cursor.execute(sql, params) consumes -- nothing in the builder ever imports or calls into the driver directly, which is what keeps pure query construction and real I/O cleanly separable and independently testable, with the driver boundary crossed in exactly one place per query (Example 24 feeds a compiled tuple into a real cursor; Example 25 walks the full connect/cursor/execute/fetch/close lifecycle explicitly).

It records WHICH migrations already ran, so re-running the migration runner against an already-migrated database is a safe no-op instead of re-executing DDL that has already applied (and typically fails the second time, e.g. CREATE TABLE against a table that already exists) -- Kata 7 reproduces exactly that crash and its fix (Example 72 applies ordered scripts; Example 73 records applied versions and proves a re-run skips them; Example 74 applies out-of-order files and proves they still run in version order).

An ORM's whole value proposition is hiding raw SQL rows behind typed Python objects -- if the resulting API is untyped (or typed Any anywhere), that hiding buys nothing, because every caller still has to know the underlying row shape by convention rather than by the type checker catching a mismatch before the query ever runs. Generics (session.get[T](pk) -> T) and explicit return types let pyright --strict verify the whole stack, including the deliberate, explicitly # type: ignore[override]-marked exceptions where the query DSL intentionally overrides object.__eq__ (Example 26 confirms a typed fluent chain is pyright --strict clean; Example 54 types session.get[T]; Example 77 runs pyright --strict over the whole stack).

Skipping the builder throws away BOTH the clause-as-data guarantee (co-01 -- nothing here is inspectable before it runs) and parameterization (co-02 -- user_input is now literally part of the SQL text). This is exactly Kata 2's bug, reached by a different path: hand-writing an f-string instead of ever touching the builder at all.

This is the mutable-builder leak (co-03) -- if .where() genuinely always returns a NEW instance (never mutates the receiver), sharing one base object between two handlers is safe by construction, regardless of concurrency. The symptom described can only happen if .where() secretly mutates self.wheres in place, which is precisely Kata 1's bug, reproduced under load instead of two sequential calls.

This is metadata drift from having no single shared registry (co-09) -- the builder and the mapper each read their OWN idea of the table's columns instead of one shared source, so updating the column name in only one place leaves the other silently out of sync until it fails at runtime, far from the actual rename.

Object-to-row mapping (co-11) forgot to exclude the primary key from the SET dict -- the PK identifies WHICH row the UPDATE targets via WHERE; including it in SET as well tries to rewrite the very column used to find the row (Example 34 builds the SET dict correctly, excluding the PK).

This is a missing type-coercion-on-load step (co-12) -- the exact same class of bug Kata 8 reproduces for bool, here applied to date. Without an explicit coercion, the loaded attribute is whatever raw type SQLite actually stored (TEXT), not the type the domain object declared (Example 37 coerces an ISO string to date on load).

This is the case a weak-reference identity map (co-14) exists for -- a plain dict keeps every loaded object alive for the whole session's lifetime regardless of whether anything else still references it. Backing the map with a WeakValueDictionary lets an entry disappear the moment the caller's own reference to it does (Example 45 loads many rows, drops references, and proves the map shrinks under GC).

This violates the session-as-sole-transaction-owner rule (co-15) -- calling commit() directly on the connection ends the transaction boundary early, splitting what should have been one atomic unit into two separate ones. Only the session should ever call commit()/rollback() on its own connection.

This is the deferred nature of new-object tracking (co-16) working exactly as designed but never triggered -- session.add() only records the object in a "new" SET; nothing writes to the database until an actual flush() runs (or a context-managed session exits cleanly and flushes on the way out). No flush call, no INSERT, no error -- the write was simply never asked for.

Deleted tracking (co-18) is a distinct, explicit action for exactly this reason -- releasing a Python reference is invisible to the unit of work and does nothing to the underlying row. Only session.delete(obj), recorded in the deleted-set and acted on at flush time, produces a real DELETE.

This is exactly what the fully typed API (co-25) exists to prevent -- pyright --strict treats Any as compatible with everything, silently, even with strict mode on everywhere else. A single Any boundary anywhere throws away the "IDE/type-checker catches a mismatched type before the query runs" guarantee for every caller downstream of it, not just at that one call site.

Root cause: Python evaluates a def's default argument value exactly ONCE, at function-definition time -- not once per call. wheres: list[str] = [] therefore creates a single list object bound to every future call that doesn't pass its own wheres explicitly, and mutating it via .append() mutates that ONE shared object for every instance relying on the default. Switching the default to None and building a genuinely fresh [] inside __init__ (evaluated on EVERY call) gives every instance its own independent list, restoring the immutable-builder guarantee co-03 depends on.

Run: python3 kata.py

Output:

SELECT * FROM customer WHERE id = 1
SELECT * FROM orders

Root cause: f"... = '{name}'" folds name's literal characters into the SQL text itself before the driver ever sees it -- a value containing ' OR '1'='1 doesn't just fill in a string literal, it rewrites the WHERE clause's boolean logic entirely, matching every row. A bound ? parameter is never part of the SQL text; the driver passes it to SQLite as pure data, so its contents can never change what the statement's syntax means, regardless of what characters it contains.

Run: python3 kata.py

Output:

[]

Root cause: without a cache checked BEFORE constructing a new object, every load() call is independent -- there is no mechanism connecting the second call to the first, so Python has no reason to return anything but a brand-new Customer. An identity map keyed by primary key, checked first and populated before returning, guarantees the SECOND load of the same key returns the object the FIRST load already built -- a is b becomes true, and mutating one mutates the only object that exists.

Run: python3 kata.py

Output:

True Ada Renamed Ada Renamed

Root cause: dirty detection is nothing more than "does the object's current state differ from a saved snapshot" -- so the ENTIRE mechanism depends on that snapshot being taken before any mutation is allowed to happen. Calling track_clean() after the mutation captures the post-mutation state as the "baseline," making the comparison always equal (never dirty) regardless of what actually changed. Taking the snapshot at genuine load time -- before the caller has had any chance to touch the object -- restores a real "before" to compare against.

Run: python3 kata.py

Output:

[Customer(id=1, name='Ada', email='ada@newmail.com')]

Root cause: a flush that processes tracked writes in an arbitrary order (here, orders before customers) has no way to know a foreign key it's about to write hasn't been resolved to a real value yet -- the database's own FK constraint is what actually catches the mistake, as an IntegrityError rather than a silently wrong value. Ordering the flush so every parent's INSERT runs before its FK-dependent children's guarantees customer.id is always real by the time an order needs it.

Run: python3 kata.py

Output:

1 1 True

Root cause: the naive orders property has no cache and is called once per customer inside a loop -- the number of queries scales linearly with N, the number of parents, exactly the N+1 pattern co-22 names. Replacing "one query per parent, triggered lazily inside the loop" with "one query for the whole batch of children up front" (a single WHERE customer_id IN (...)) collapses the query count from N+1 down to a constant 2, no matter how many customers exist.

Run: python3 kata.py

Output:

2

Root cause: without a record of which migrations already ran, migrate() has no way to tell "the schema is already at version 1" from "the schema needs version 1 applied" -- it just re-executes the same DDL every call, and CREATE TABLE (unlike CREATE TABLE IF NOT EXISTS) fails outright against an existing table. A schema_version tracking table gives the runner exactly the bookkeeping it needs: check what's already applied, apply only what's missing, record it, and a re-run against an already-migrated database becomes a genuine no-op.

Run: python3 kata.py

Output:

[1] []

Root cause: SQLite has no native boolean storage type -- active is genuinely stored (and fetched) as a plain INTEGER, 0 or 1. Assigning that raw integer directly to a bool-typed attribute leaves the RUNTIME type mismatched with the DECLARED type: 1 == True is True (equality), but 1 is True is False (identity), because 1 and True are different objects. Calling bool() on the raw value at load time produces a genuine bool object, so the mapped attribute's runtime type finally matches what the dataclass field declares.

Run: python3 kata.py

Output:

bool True

Ties to abstraction-and-its-cost -- this topic's whole purpose is making a real ORM's abstraction cost CONCRETE and visible, not replacing a mature ORM in production. You feel exactly what an immutable fluent builder buys (co-03) and what it costs to allocate a new instance per call; you feel exactly what lazy loading buys (co-21) and exactly what it charges when it fans out into an N+1 you triggered yourself and can watch in a query log (co-22) -- neither is visible while treating an ORM as a black box.

Separating "build" (pure data and pure functions) from "run" (real I/O against a database) is what lets the entire builder be tested with no database required at all, and lets a compiled (sql, params) tuple be logged, cached, or replayed exactly and deterministically. Mixing the two would make every builder test also a database test, and would make "did compiling this query have a side effect" something you'd have to check instead of something structurally impossible.

Deferring every write is what lets the unit of work order writes correctly (co-19's flush ordering) and wrap the whole batch in one atomic transaction (co-20) -- writing immediately, one call at a time, would make "insert this parent before this child" or "roll back the WHOLE batch on any single failure" impossible to guarantee, because some writes would already be committed independently before the failure was even discovered.

The identity map's guarantee -- a is b for two loads of the same primary key -- is a PRECONDITION for dirty tracking (co-17) to mean anything at all. If the same row could load as two independent objects, mutating one wouldn't reliably get flushed, because the unit of work might be tracking the OTHER copy, or both, with only one snapshot ever matching reality -- a read-only stack never has this problem because nothing downstream depends on object identity being stable.

Ties to abstraction-and-its-cost from its other side -- lazy loading's entire benefit (skip work for a relationship you'll never touch) is the mirror image of its entire cost (that same deferral, multiplied by N the moment you touch it inside a loop). Teaching both together, and rebuilding the mechanism yourself, is what turns "my ORM feels randomly slow sometimes" into a predictable multiplication you can see in a query log and fix deliberately, by batching, whenever you already know you'll touch every relationship.

IF NOT EXISTS only protects a single CREATE TABLE statement; a real migration sequence also needs ALTER TABLE, data backfills, and multi-statement changes with no equivalent "already done" guard of their own. Kata 7 shows the CREATE-only case breaking outright on a second run; a schema_version table generalizes "already applied" to the WHOLE migration as a unit, independent of what kind of DDL or data change it contains.

Ties to abstraction-and-its-cost from the type-safety angle -- a mechanism that WORKS is not the same as a mechanism whose MISUSE gets caught before the query ever runs. An ORM's entire selling point is hiding raw rows behind typed objects, and an untyped (or Any-leaking) API throws that benefit away even when every individual method happens to behave correctly today -- the cost of skipping co-25 is invisible until a caller passes the wrong type into a boundary that should have caught it and didn't.