The ORM Question

An object-relational mapper turns rows into objects and objects into SQL, and the service pays for the convenience in two currencies: the queries it did not know it was issuing, and the SQL it cannot express. Stagedoor's list-orders endpoint issued 51 statements to return 50 orders, one for the list and one per order for its tickets, and nothing in the handler's twelve lines said so. The endpoint that lists an organizer's 500 orders issued 501 and timed out, and the mapper had done exactly what it was written to do.

The honest position is not "ORM or not." It is "which parts of data access does the mapper help with, and where does the service write SQL," decided per query and written down in the storage layer. Stagedoor uses SQLAlchemy Core, a query builder without an object tracker, for the queries it composes, and raw parameterized SQL for the queries it tunes, and this topic is the line between the two.

What a Mapper Buys

A mapper of any kind buys four things: rows that arrive as typed values instead of tuples, queries that compose without string concatenation, the schema written in one place from which Topic 35's migrations are generated, and the boring 80 percent of data access, the lists and lookups and inserts, written once instead of once per table. Those are real, and a service that writes every query by hand pays for them in a thousand lines of near-identical SQL and the bugs that hide in the differences between them.

q = (
    select(orders.c.public_id, orders.c.status, orders.c.total_cents, orders.c.created_at)
    .where(orders.c.user_id == buyer.id)
)
if status is not None:
    q = q.where(orders.c.status == status)              # a parameter, never a formatted string
if cursor is not None:
    q = q.where(tuple_(orders.c.created_at, orders.c.id) < (cursor.created_at, cursor.id))
q = q.order_by(orders.c.created_at.desc(), orders.c.id.desc()).limit(page_size + 1)

rows = await svc.db.fetch_all(conn, q)               # one statement, whatever the filters

The composition is the point. The list endpoint of Chapter 3 accepts an optional status filter and an optional cursor, and each one is a clause added to the same query object; the four combinations are one piece of code, not four, and no combination is built by pasting strings together. Every value in every clause becomes a bound parameter, because the builder cannot do anything else with it. The cursor comparison on (created_at, id) is the keyset pagination Chapter 3 chose, and it composes like any other clause. The result is one statement, and the builder prints it if asked, so what runs is never a mystery.

N+1

The list-orders handler was written against the mapped model: fetch the orders, then for each order read order.tickets to count them for the response. The first line is one query. The attribute access on each order is a lazy load, and a lazy load is a query. Fifty orders is 51 statements; 500 is 501. The code does not change shape as the page grows, so the cost is invisible in the code and obvious in the query log, where 51 nearly identical SELECT … FROM tickets WHERE order_id = $1 lines follow one SELECT … FROM orders. Each is a round trip of about a millisecond on the private network; 501 of them is half a second of pure latency for work the database could do in one.

Two fixes, and both make the statement count a constant. A join fetches the orders and their tickets in one statement, at the cost of repeating each order's columns once per ticket in the result set. A second query fetches every ticket for the page at once, with WHERE order_id = ANY($1) and the 50 ids as one array parameter, and the storage layer groups the rows by order in memory. Stagedoor uses the second, because the order row is wider than the ticket row and the join would send it four times per order. The rule that both fixes serve is the one Marek now asserts in a test: a page's query count is fixed, and never proportional to the number of rows it returns.

What a Mapper Hides

The lazy load is the most famous hidden query, and it has company. A mapper that selects every column of the row fetches the seat map's 2-megabyte jsonb layout to read one status, and does it on every request that touches an event. An identity map, which a full ORM keeps so that the same row loaded twice is the same object, returns the object it already has, which is the row as it was when first loaded in this session, not as it is now. A flush that the ORM schedules for "before the next query" runs an UPDATE at a moment nobody chose, sometimes inside a transaction the developer thought was read-only. In an async handler the lazy load has one more failure: the attribute access runs a query on the event loop, and if it happens after the connection was returned to the pool, it fails with an error about a detached instance that has nothing to do with the line that caused it.

Each of these is a query the reader did not write, and the place they show up is the same: the statement log with timing that Chapter 13 turns on, where a handler that should issue two statements is seen issuing fifty, or issuing an update nobody asked for. The discipline is not to distrust the mapper. It is to read the log for every new endpoint once, and to assert the count in a test so that it cannot drift.

Where SQL Wins

Some queries are the service. The cursor-paginated list with its composite key comparison can be built, but the seat-row lock of Topic 34, SELECT status FROM seats WHERE id = $1 FOR UPDATE, is one line of SQL that says exactly what it does, and a builder's with_for_update() is a translation of it that the reader must translate back. The idempotency insert of Chapter 7 is an INSERT … ON CONFLICT (key) DO NOTHING with a RETURNING clause, and the seat map's sections are a recursive query that no mapper composes well. These are written by hand, parameterized, kept in the storage layer next to the composed ones, and tested against a real Postgres in Chapter 12, because a hand-written query that is tested only against a mock is tested against nothing.

Parameterization Is Not Optional

Every value that reaches a query is a parameter, sent separately from the SQL text, so that the database never parses it as SQL. The builder does this by construction: there is no way to hand it a value that it will splice into the statement. Hand-written SQL does it by discipline, with $1 placeholders and the values passed beside the string, and the discipline has to hold in every query, because a single f-string with a request value in it is an injection point regardless of how the other 300 queries look. "Just this one dynamic filter" is how it happens: a status from the query string, formatted into a WHERE clause because the builder felt heavy for one line, and the organizer who types ' OR 1=1 -- into the filter box reads every organizer's orders. Chapter 5's row-level security is the last line against that, and it is a last line, not a reason to skip the first.

The Model Is Not the API

The mapped class mirrors the table: orders has id, public_id, user_id, total_cents, exactly as the canon lists them. The response shape mirrors the contract of Chapter 3: a public id and never the internal one, a total as a money object, the tickets nested. The two drift on purpose, and a service that returns the mapped model as the response has coupled its schema to every client it has. The day total_cents is renamed for a migration, or a column is added that should never leave the service, is the day the coupling is found, and Chapter 4's translation at the boundary is where the two shapes are kept apart. The storage layer returns domain types, the transport layer serializes response models, and the table's column names appear in neither.