Querying & Raw SQL

An absent filter should contribute no predicate at all. A constant-true pad is only a concatenation crutch, a self-comparison silently drops rows holding no value, and a parameter-driven catch-all emits a different statement whose one plan must serve every combination.

A native statement is SQL you write in the database's own language and hand to the layer unchanged, rather than an object-level query it translates. Its rows map back to entity objects, transfer models, or scalars.

An object-level query is text naming mapped classes and fields, not tables and columns; the layer parses it and emits SQL. Being text, a typo or a renamed field surfaces only at parse time - startup or first run.

A projection asks the statement for only the fields a use case needs and materialises them as a scalar, a tuple or a small transfer type, so the row is never turned into a tracked mapped object.

A tracked object stays registered with the open unit of work, so later edits to it are written at flush and unloaded parts can still be fetched. A transfer model is a plain snapshot that does neither.

Only through an UPDATE statement the code issues itself. The loaded object is a plain value with no link back to its row, so nothing detects the mutation, and forgetting the statement loses the change silently.

A materialising read buffers the whole result first, so memory scales with the result. A streaming read pulls rows in blocks from an open cursor as you consume them, keeping memory near one block — but the transaction stays open throughout.

The statement changes rows in the database, but the unit of work never sees those rows. Its tracked objects still hold the earlier values, and the layer keeps serving those stale copies — or flushes them back over the change.

Placeholders bind values only. Column names, sort direction, comparison operators and the statement shape are text your code picks from a fixed internal map keyed by the request token. Page size and offset bind as values, but are still clamped to a ceiling.

Only styles whose field names reach the compiler as symbols break the build: a generated typed DSL, or a builder taking generated field references. Query strings, derived method names and named queries fail at startup or first run.

Nothing about a projected row is registered - no snapshot, no identity-map entry, no version read - so a flush has nothing to compare or update. To write, load the mapped object by its key.

Identity, write ordering, batching into fewer round trips, cascade to related rows, the optimistic version check with its row-count test, and any cache invalidation. None of it disappears; it moves from the layer into application code.

Pending changes live in memory until the layer sends them, so a native read misses them unless the layer flushes first. After a native write, the tracked copies are stale and must be discarded or refreshed.

It can hide the statement text, join strategy, chosen columns and index or cache use. It cannot hide cost or timing: statement count, what was left unloaded, whether the result is tracked, volume, paging stability and scope duration.

It skips everything the layer runs per object: cascades to children, lifecycle hooks and the events or audit writes inside them, and the per-row version check. The database still enforces its own constraints, and the caller must supply the rest.

Positional binding matches values to placeholders by order; named binding matches labels the layer rewrites, so a value can be reused and edits stay safe. A list cannot fill one placeholder: the layer expands it into one per element.

The mapping target decides. Rows mapped to an entity type usually enter the tracked set and are reconciled with any instance already loaded for that identity; transfer models and scalars stay outside it.

The layer tokenizes the method name at wiring time, matches the tokens against mapped field names and comparison keywords, and generates the statement; arguments bind positionally. It fits one or two stable predicates and degrades fast beyond that.

A mapped-object query returns instances of one mapped class per row. A grouped row and a row combining fields from two aggregates correspond to no mapped class, so a projection is the only result the layer can build for them.

Slicing by table pushes assembly onto callers: they combine the cluster themselves, order the writes, propagate generated keys, hold the cross-object rules, and open the transaction, so the fetch plan ends up spread across call sites.

Three separate faults produce this: rows materialised into tracked objects that are never released, streaming preconditions unmet so the driver buffered anyway, and slow per-row work inside the loop stretching the transaction the cursor needs.

Joining to the many side emits one output row per matching child, so a parent matching three children appears three times even though nothing from the child is selected. Counts, page sizes and totals then measure join rows, not entities.

Eight independent optional filters give 256 where-clause shapes before ordering multiplies them. Nobody has read most of them, each is compiled separately by the engine, index coverage differs per shape, and the failing combination is usually one no test ever assembled.

Rank the evidence: regenerate typed symbols and let compilation list the call sites, parse every declared query in the build, search literal text and derived method names, then read the statements actually executed. Expand-migrate-contract keeps a miss survivable.

Projecting changes what each row is materialised into, not which rows the engine must produce. The same joins, filter, sort and row count remain, and a projection reaching across a to-many link still multiplies rows.

Both layers must run on one connection inside one transaction with a single owner of commit and rollback. Anything the mapper already loaded is stale once the other layer writes those rows, and must be discarded or re-read.

Decide on two axes: whether the new value is derivable from data the database already holds, and whether the invariants the object path enforces are load-bearing. The statement path transfers those invariants to you — explicitly, tested and documented.

Decide from the measured distribution of filter combinations, not the API surface. A composed builder suits a small orthogonal filter set; pinned statements suit a few dominant combinations; a search store suits text and facet filters, and costs a lagging copy.

Drop below the mapper only for what the object-level language cannot express, or a plan you measured, never for taste. Contain it with one statement catalog, one implementation per engine, and tests on every supported engine.