Querying in JavaScript

Every finder takes one options object. The same shape works on all 27 dialects;
the ORM writes the SQL each one needs.

Finders

Filtering

A plain object is an equality match, and several keys are AND:

Anything else is an operator, written by name:

A null test can be a plain null (deletedAt: null) or { is: null }; both
render IS NULL on every engine. Before 4.9.2, { is: null } threw
UnsupportedWhereError on MySQL and SQL Server.

Operator	Means
`eq` `ne`	`=` `<>`
`gt` `gte` `lt` `lte`	the comparisons
`between` `notBetween`	`BETWEEN a AND b`
`in` `notIn`	`IN (…)`
`like` `notLike` `iLike`	pattern matching
`is` `not`	`IS` / `IS NOT`, for null
`and` `or` `not`	combine conditions

The full set is in Query operators. The $-prefixed
spellings ($gte) and the Op symbols work too — they are the same operators.

Combining conditions

Choosing columns, ordering, paging

Paging is where the engines disagree most:

[!NOTE]
attributes: { exclude: [...] } expands to the columns you declared, minus
the excluded ones — so the implicit createdAt / updatedAt are left out
too: { exclude: ['password'] } selects
"id", "name", "email", "age", "role", "active", "verified", "deletedAt". List
the timestamps in attributes explicitly when you need them.

A typical list endpoint puts filtering, ordering and paging together:

Aggregates

Updating and deleting in bulk

[!CAUTION]
A bulk update or destroy with no where, or an empty one (where: {}),
throws Missing where option in destroy instead of touching every row. To
write the whole table on purpose, say so with all: true —
Post.destroy({ all: true }) sends DELETE FROM "posts";.

Raw SQL, when you want it

Everything else on this page compiles to SQL for you; query() is the escape
hatch, not the starting point. See Raw queries.

Relations — querying across tables

Query operators — the complete list

Bulk operations — many rows at once

Querying in JavaScript

Finders#

Filtering#

Combining conditions#

Choosing columns, ordering, paging#

Aggregates#

Updating and deleting in bulk#

Raw SQL, when you want it#

Next#