When a layer runs a native statement, how do named and positional bindings differ, and how is a list bound?

## Why a placeholder exists at all A parameterized statement separates two things that string concatenation fuses: the **statement text**, which is structure, and the **values**, which are data. The text goes to the database to be parsed; the values arrive alongside it, typed, and are never parsed as SQL. That separation is what lets a data-access layer send caller data safely and hand the engine a stable text, and it is why a placeholder is a **scalar slot** — a slot for one value of one type, not for a fragment of SQL. No amount of binding will let you parameterize a table name, a column name, or a sort direction; those are structure, and they have to be chosen from a fixed set in your own code. ## Positional binding The text carries anonymous markers and the caller supplies values in order. ```sql SELECT id, total FROM orders WHERE customer_id = ? AND status = ? AND created_at >= ? ``` Three values, bound in that order. What to know about it: - **Edits are unsafe by construction.** Add a predicate in the middle and every subsequent index moves; nothing in the type system notices, and the statement still executes if the types happen to line up. - **Ordinal base differs between layers.** Some count parameters from `1`, some from `0`. An off-by-one here is a runtime error at best and a wrong-parameter bind at worst. - **Repetition costs an extra bind.** If the same value is compared twice, it occupies two positions and must be supplied twice. ## Named binding The text carries labels instead of anonymous markers, and the caller supplies a map from label to value. - The layer **rewrites** labels into the placeholder form the engine actually accepts and keeps the label-to-position map internally. Named binding is therefore a layer feature built on top of positional binding, not a separate database facility everywhere. - **A label can appear many times** and is supplied once — useful for a range compared against two columns, or a flag consulted in several branches of a predicate. - **Refactoring is safe**: adding a predicate adds a label; nothing renumbers. - The cost is a rewriting step and one more thing that can go wrong: a label in the text with no value supplied, or a value supplied for a label that no longer appears. | | Positional | Named | |---|---|---| | Match rule | order of supply | label in the text | | Effect of inserting a predicate | later indexes shift | nothing shifts | | Reusing one value | bind per occurrence | bind once | | Failure mode | wrong value in the right slot | missing or unused label | | Where it lives | usually the driver's own form | usually rewritten by the layer | ## Expanding a variable-length list A membership test against a caller-supplied collection is the one binding case that cannot be solved by the placeholder alone. `WHERE id IN (?)` binds **one** value, not a list. So the layer builds the text to fit the collection: 1. Count the elements. 2. Emit that many placeholders separated by commas inside the parentheses. 3. Bind the elements to those placeholders in order. The practical consequences: - **The statement text now varies with the input.** Two calls with different list sizes are two different texts. That has database-side effects on how statements are prepared and reused, which is the database engine's story rather than the mapper's. - **The empty list has no valid expansion.** `IN ()` is not legal SQL. A layer must either refuse the call or substitute a predicate that is constantly false, and you must know which yours does — the two behaviours differ by *everything*, since an unhandled empty list can otherwise turn into a predicate that matches every row. - **Very large lists hit limits.** Engines cap the number of parameters a statement may carry, and long placeholder lists are unpleasant to parse. Past a few hundred elements, feeding the values in as a temporary set, a joined table, or an array-typed single parameter where the engine offers one is the better shape. - **Chunking changes semantics.** Splitting a big list into several statements produces several result sets to merge, and for writes it means several statements rather than one — worth naming out loud before you do it. ## The discipline in one line Bind every value, choose names over positions in anything longer than a couple of predicates, and treat list expansion as a code path with three cases — empty, normal, and too large — rather than as string building that happens to work today.