Query safety: the fail-closed WHERE guard

A where that a dialect's builder cannot render used to be dropped: the
condition vanished from the SQL and the statement ran without it. For a
findAll that means too many rows; for an update or destroy it means every
row. Since 4.8.3 every WHERE that reaches a builder through a model call is
first rewritten into shapes the builder renders and checked, and a condition
the builder would lose throws UnsupportedWhereError before the statement is
sent. This page is what the guard does, what it refuses, and why.

[!CAUTION] The guard only sees where objects
Raw prorm.query() has no where object to check, so it is not guarded.
A hand-written DELETE FROM orders runs as written. See
Raw queries and, for a pattern-based net under raw SQL,
the query firewall.

Where it sits

The guard lives in src/query-builders/expression-render.ts (prepareWhere
and assertRenderedWhere) and is applied by Prorm to the dialect that every
model call uses, so findAll, findOne, count, update, destroy,
increment, upsert and the aggregates all pass through it. Raw
prorm.query() does not: there is no where object to check.

The flow shows the two checks: one on the condition object before building,
and one on the SQL string after building, so a builder that silently drops a
node it claimed to support is caught as well.

For each node of the condition, the first check reads the dialect's
WhereSupport profile and makes
one of three decisions:

One condition, several builders

The same where meets different builders. The SQL and the refusals below were
captured by calling prepareWhere() and then buildSelectQuery() on each
dialect, never connected.

import { Op } from 'ts-prorm-orm';

// Orders from one customer that are still pending or have failed, in a price band.
const openOrders = await Order.findAll({
  where: {
    customerId: 7,
    total: { [Op.gte]: 100, [Op.lt]: 500 },
    [Op.or]: [{ status: 'pending' }, { status: 'failed' }],
  },
});

The two shapes that differ are the price band (two operators on one column)
and the $or next to a plain column key. Each is shown on its own:

Both render directly, so the object is passed through as written.

MySQL's builder renders one operator per column and drops column keys beside
$or, so the guard rewrites both into $and lists first.

The array shorthand for IN is where the dialects split into "renders" and
"refuses":

UnsupportedWhereError: Cannot render WHERE condition at id for postgres: the array
shorthand for IN is not rendered by its builder; use { id: { [Op.in]: [...] } }.

PostgreSQL binds { id: [1, 2] } as one array value (array-column equality),
so the shorthand cannot mean IN here. With { id: { [Op.in]: [1, 2] } }:

UnsupportedWhereError: Cannot render WHERE condition at id for mysql: the array
shorthand for IN is not rendered by its builder; use { id: { [Op.in]: [...] } }.

With { id: { [Op.in]: [1, 2] } }:

[!TIP]
{ [Op.in]: [...] } renders on every builder. Use it in shared code rather
than the array shorthand.

What it rejects

Each message below is the real one, captured by calling prepareWhere with
the named dialect. Every message ends with the same sentence: The statement
was not sent, because running it without the condition would match rows it
should not.

Condition	Dialect	Refused because
`{ id: [1, 2] }`	PostgreSQL, MySQL	`the array shorthand for IN is not rendered by its builder; use { id: { [Op.in]: [...] } }.`
`{ deletedAt: { [Op.eq]: null } }`	SQL Server	`$eq: null would be sent as '= NULL', which matches nothing; use $isNull: true.`
`{ total: { [Op.gt]: literal('"discount" * 2') } }`	MySQL, SQL Server	`a literal() as an operand would be bound as a JSON string, not compiled as SQL.`
`{ [Op.exists]: literal('SELECT 1 FROM refunds …') }`	MySQL, SQL Server	`EXISTS / NOT EXISTS subquery predicates (exists(), notExists(), Op.exists) are not rendered by its WHERE builder yet.`
`{ age: { $foo: 1 } }`	any	`the operator '$foo' is not implemented by its WHERE builder.`
`{ age: { $gt: 1, foo: 2 } }`	any	`the object mixes operators ($gt) with plain keys (foo), which no builder reads as a condition.`
`{ age: { [Op.between]: 5 } }`	any	`$between needs a [low, high] array.`
`{ '$posts.status$': 'x' }` outside `findAll`	any	`'$posts.status$' refers to a column of an included association, which only findAll() resolves (via its include list).`
a non-empty `where` whose built `DELETE` has no `WHERE`	any	`a non-empty where was given, but the DELETE its builder produced has no condition ("DELETE FROM \"t\"").`

Other checks of the same kind: a literal() / fn() used as a whole condition
on a builder that would bind it as a JSON string instead of compiling it;
{ col: null } on a builder that would send = NULL; $in with a subquery on
a builder that has no subquery support; more than one operator on a column
when the builder renders only one and no rewrite applies; and column keys
beside $and / $or / $not in the same object on a builder that drops them.

The expression rows are about the operand, not the idea: on PostgreSQL the
same two conditions compile.

[!NOTE]
Oracle's builder reads $and / $or / $not at every level, so
{ [Op.or]: [{ a: 1 }, { b: 2 }] } renders there as
(("a" = :1) OR ("b" = :2)). Earlier releases refused it.

The error is exported from the package root:

import { UnsupportedWhereError } from 'ts-prorm-orm';

try {
  await User.destroy({ where: { id: [1, 2] } });
} catch (err: unknown) {
  if (err instanceof UnsupportedWhereError) {
    console.error(err.dialect, err.path, err.message); // 'postgres', 'id', 'Cannot render WHERE condition at id for postgres: …'
  }
}

path is the offending node, spelled like $and[1].age.$foo, and <root>
for the whole condition.

A purge job is where the guard earns its keep. Here the condition is written
portably, and a refusal is reported rather than swallowed:

import { Op, UnsupportedWhereError } from 'ts-prorm-orm';

export async function purgeCancelledOrders(before: Date): Promise<number> {
  try {
    return await Order.destroy({
      where: { status: 'cancelled', createdAt: { [Op.lt]: before } },
    });
  } catch (err: unknown) {
    if (err instanceof UnsupportedWhereError) {
      // Nothing was deleted: the statement was never sent.
      throw new Error(`purge refused on ${err.dialect} at ${err.path}`, { cause: err });
    }
    throw err;
  }
}

What it rewrites instead of refusing

Two shapes are rewritten, without changing meaning, for builders that render
only the rewritten form:

{ a: { $gte: 1, $lt: 9 } } becomes
{ $and: [{ a: { $gte: 1 } }, { a: { $lt: 9 } }] } for a builder that renders
one operator per column;

{ $or: [...], a: 1 } becomes { $and: [{ $or: [...] }, { a: 1 }] } for a
builder that drops column keys beside a logical key.

On PostgreSQL both render directly, so the object is passed through as
written:

await User.findAll({ where: { age: { [Op.gte]: 18, [Op.lt]: 65 } } });

On MySQL the same call is rewritten first, which is where the extra
parentheses in the MySQL tab above come
from.

How a dialect declares what it renders

The guard reads a WhereSupport profile: which logical keys and operators the
builder renders, whether it accepts the array and null shorthands, expression
operands, subqueries, several operators per column, and columns beside logical
keys. A dialect may declare its own with whereSupport(); otherwise the
profile of the builder class it inherits from applies (BUILTIN_WHERE_SUPPORT
has one for each of the PostgreSQL, MySQL, SQLite, SQL Server, Oracle,
BigQuery, Snowflake, ClickHouse and DuckDB builders, keyed by the class that
defines buildWhereClause), so every dialect derived from one of those gets
its parent's profile. A dialect with neither gets only the universal checks —
malformed operator objects, $between without an array, an included-table
column outside findAll.

The profiles of the five most used builders, as whereSupportOf() returns
them:

Flag	PostgreSQL	MySQL	SQLite	SQL Server	Oracle
`arrayShorthand`: `{ a: [1, 2] }` is `IN`	no	no	yes	yes	yes
`nullShorthand`: `{ a: null }` is `IS NULL`	yes	yes	yes	yes	yes
`nullEqOperator`: `{ $eq: null }` is `IS NULL`	yes	yes	yes	no	yes
`multipleOperatorsPerColumn`	yes	no (rewritten)	yes	yes	yes
`logicalWithSiblings`	yes	no (rewritten)	yes	yes	yes
`expressionOperands`: `literal()` / `fn()` compiled	yes	no	yes	no	no
`existsPredicates`: `$exists` / `$notExists`	yes	no	yes	no	no
`inSubquery`: `$in` with a subquery	yes	no	yes	no	no
Column operators rendered	62	26	32	15	34

Builders use the same profile from the inside: assertSupportedOperator() in
a builder's fallback branch throws instead of binding an operator object as
= ?, and renderOperand() compiles literal() / fn() / col() / cast()
operands rather than serialising them.

Why fail closed

The alternatives were tried and are worse:

Drop the node and warn. A warning in a log is not seen by the code that
just deleted a table's rows.

Fall back to = ? with the object bound as JSON. The statement runs and
matches nothing, which reads as "no rows" rather than "unsupported" and is
found weeks later.

Render TRUE. Some builders legitimately render TRUE for
{ a: { $notIn: [] } }, so it cannot double as an error marker.

Throwing at the call site, with the path and the dialect in the message, is
the only option that puts the problem in front of the code that caused it.
The cost is that a query which used to "work" by matching too much now throws;
the fix is always in the message.

Querying — the finders the guard sits behind

Query operators — the operators, and which dialects render each

Error handling — UnsupportedWhereError in the error tree

Query firewall — pattern rules for statements the guard never sees

SQL dialects — per-engine notes

Query safety: the fail-closed WHERE guard

Where it sits#

One condition, several builders#

What it rejects#

What it rewrites instead of refusing#

How a dialect declares what it renders#

Why fail closed#