Query Builders
Composing SQL structurally in the host language: dynamic filters without string concatenation, and no mapping layer to explain.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
How do I build a query whose shape depends on runtime input without concatenating strings?
A search endpoint takes optional filters — status, date range, assignee, free-text — plus a sort column and a direction. Any combination is legal, and the SQL differs for each.
Build the SQL as a string. Start with a base statement and append AND ... clauses for whichever filters are present, then append the ORDER BY.
The first WHERE/AND seam needs a flag or a WHERE 1=1, and every branch is a place to get spacing or precedence wrong.
- The first
WHERE/ANDseam needs a flag or aWHERE 1=1, and every branch is a place to get spacing or precedence wrong. - Any value that reaches the string instead of the parameter list is an injection, and under time pressure exactly one will (SQL Injection).
- The sort column cannot be a bind parameter, so it gets interpolated — and that is the injection that actually happens in the wild.
- The resulting statement is invisible until runtime, and the combinatorial explosion of filter permutations is untestable by inspection.
What is actually happening
- A query builder is a structural representation of a statement: a list of selected expressions, a from clause, a tree of predicates, ordering, limit. Calling
.where(...)adds a node; nothing executes. - At execution it walks the tree and emits SQL plus a parameter array, with placeholders in the correct dialect. Values are never concatenated.
- Because the query is data until it runs, it is composable: a function can take a builder, add a tenant predicate, and return it. That is what makes conditional filters clean.
- Identifiers — table names, column names, sort keys — are quoted, not parameterized, because SQL has no placeholder for them. Quoting is not validation: the builder will happily quote whatever string you hand it.
- A builder does not map rows to objects, track changes or manage relations. That is the whole difference from an ORM: it decides the syntax, you decide the query (What an ORM Actually Does).
Conditional filters without a string seam
This is the pattern the whole lesson exists for. Each optional filter is an if that adds a predicate. There is no first-clause special case, no spacing, no precedence, and no value ever touches the statement text.
let sql = 'SELECT * FROM tickets WHERE 1=1'
if (f.status) sql += ` AND status = '${f.status}'`
if (f.assignee) sql += ` AND assignee_id = '${f.assignee}'`
if (f.since) sql += ` AND created_at >= '${f.since}'`
sql += ` ORDER BY ${f.sort} ${f.dir} LIMIT ${f.limit}`
const rows = await db.raw(sql)const SORTABLE = { created: 'created_at', updated: 'updated_at' } as const
const col = SORTABLE[f.sort] ?? 'created_at'
const dir = f.dir === 'asc' ? 'asc' : 'desc'
let q = db('tickets').where('tenant_id', tenantId)
if (f.status) q = q.where('status', f.status)
if (f.assignee) q = q.where('assignee_id', f.assignee)
if (f.since) q = q.where('created_at', '>=', f.since)
const rows = await q.orderBy(col, dir).limit(Math.min(f.limit ?? 50, 200))Every value in the second version is bound, so no filter can alter the statement's structure. The sort column — which SQL cannot parameterize — goes through a fixed map, so an unknown value becomes a default rather than an injection point. The limit is clamped server-side because a client-supplied bound is a request, not an instruction.
Values are bound, identifiers are not
The distinction that matters is not "builder versus string". It is that SQL placeholders exist for values and not for identifiers. A column name, a table name, a sort direction and an index hint all have to be part of the statement text.
Every builder therefore has an identifier path, and every identifier path is an allow-list problem. Quoting helps with reserved words and case; it is not an authorization or validation mechanism.
| Position in the statement | Can it be a parameter? | What to do instead |
|---|---|---|
Value in WHERE col = ? | Yes | Bind it. Always. |
IN (...) list | Yes, as an array or expanded placeholders | Bind, and chunk long lists |
LIMIT / OFFSET | Yes in most drivers | Bind, and clamp server-side |
Column in ORDER BY | No | Map a public name to a real column via a fixed object |
| Sort direction | No | Ternary to asc/desc — never pass through |
| Table name | No | Fixed in code, or an allow-list for multi-table search |
LIKE pattern | Yes, but wildcards are data | Bind the pattern and escape % and _ in user text |
Where a builder stops being the right tool
Builders are for queries whose *shape* varies. They are not, in general, better than SQL for queries that are merely complicated. A recursive CTE, a window function over a grouped set or an INSERT ... ON CONFLICT DO UPDATE with an excluded-row reference is usually clearer written out, even in a codebase that uses a builder everywhere else.
The practical rule: if you find yourself reading the builder call and mentally reconstructing the SQL to check it, write the SQL (Raw SQL in Application Code).
What varies about this query?
when The predicate set, the sort or the joins vary at runtime from user input or feature flags.
cost A second dialect to read, and generated SQL you must log to see.
when The query is fixed but complex — CTEs, window functions, upserts, set operations.
cost Schema changes are found by grep; parameterization is a discipline rather than a default.
when A fixed complex core with a dynamic filter around it — common for reports with a date range.
cost The seam between them is where injection sneaks back in; keep the raw part free of interpolation.
How to build it
Most important first.
- Compose conditionally by adding predicates to the builder, never by assembling text.
if (filters.status) q = q.where('status', filters.status)is the whole pattern. - Put the non-negotiable predicates in one place — a helper that every query for that table must go through — so tenant scoping is structural rather than remembered (Tenant Isolation).
- Validate sort and filter columns against an explicit allow-list mapping public names to real columns. Never pass a client string into an identifier position, quoted or not.
- Log the generated SQL in development. A builder is only better than concatenation if you can still see the statement.
- Keep the builder in the data layer. A builder object travelling into a handler is the same coupling as an ORM query object escaping (When the Repository Is Just Indirection).
What can go wrong
- A raw fragment escape (
whereRaw,orderByRaw,literal) fed with request data — the injection the builder was adopted to prevent. - Predicates added in a loop capturing the loop variable incorrectly, producing a query with the last value repeated.
- An
ORgroup without parentheses changing the meaning of the wholeWHEREclause; builders group only if you ask them to. - Reusing a mutable builder across requests, so predicates accumulate. Whether
.where()mutates or returns a new builder is library-specific and a real source of bugs. - A dynamic query whose plan differs wildly by filter combination — one permutation is indexed and another scans the table (Should I Add an Index?).
- A dynamic read composed of several statements — count plus page — can see different snapshots, so a total of 100 with only 97 rows returned is normal, not a bug (Isolation Levels).
- Offset pagination over concurrently-modified data skips and repeats rows regardless of the builder, because the offset is positional (Pagination That Survives a Large Table).
- Values are safe by construction; identifiers are not. The allow-list for sortable and filterable columns is the security control in this lesson.
- Any
*Rawmethod is an injection site by definition. Grep for them in review, and require a comment explaining why the argument cannot contain user input. - A
LIMITtaken from the client without a server-side maximum lets a caller ask for the whole table (Resource Limits). - Free-text search that interpolates into a
LIKEpattern needs the wildcard characters escaped, or a caller can turn a prefix match into a full scan.
- "A query builder is a lightweight ORM." It maps nothing and tracks nothing. It is a SQL syntax tree with a fluent interface.
- "Using a builder means I cannot be injected." Values, yes. Identifiers and raw fragments, no — and those are where injection actually lives.
- "Builders are slower than raw SQL." They emit the same statement. Construction cost is host-language work and is not the thing that decides query time.
- "I need a builder because my query is complex." Complexity is an argument for raw SQL. Builders earn their place when the query is dynamic, which is a different property.
Operating it
- Log the emitted SQL with the parameter count. Statements that vary by filter combination should be grouped by normalised text in the slow query log.
- Track which filter combinations are actually used — the long tail usually justifies an index for one or two shapes and nothing for the rest.
- Alert on statements without a
LIMITreaching the database from search endpoints; that is a scan waiting to happen.
- Nothing about the builder changes at scale. What changes is that the permutations you never indexed start scanning, and search endpoints are where that surfaces first.
- At larger data volumes, a general-purpose dynamic query is often replaced by a small number of specialised statements plus a search index (Keeping a Search Index in Sync).
- Keyset pagination composes well with builders and offset pagination does not, once offsets get deep (Pagination That Survives a Large Table).
- You learn a second dialect. The builder's API is not SQL, and expressing an unusual construct in it can be harder than writing the statement.
- You give up the ORM's mapping, change tracking and migrations, so those become your problem or another library's.
- Composability invites queries assembled from fragments across several files, which is exactly as hard to read as it sounds. The generated SQL is the only ground truth.
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- GENERALStructural composition with parameterized values and quoted identifiers is common to Knex, jOOQ, SQLAlchemy Core, Kysely and Drizzle.
- LANGUAGE-SPECIFICIn statically-typed languages a builder can carry the result type through composition (jOOQ, Kysely, Drizzle), so a renamed column is a compile error; in dynamically-typed ones it cannot, and the same mistake surfaces at runtime.
- FRAMEWORK-SPECIFICWhether
.where()mutates the builder or returns a new one differs by library — Knex mutates and returnsthis, Kysely and SQLAlchemy Core are immutable. Reusing a builder is therefore safe in one and a bug in the other.
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.