Querying

Querying Every read goes through a finder on the model, and every finder compiles the same FindOptions object into SQL for the active dialect. This guide covers the finders, each option that shapes the statement, and the SQL that comes out the other end. All the SQL below is the real output for SQLite; the shapes are the same elsewhere apart from quoting ("col" vs col vs [col]) and the placeholder style (? vs $1 vs :1). How a read becomes SQL Every finder funnels into the same pipeline, which is why where, include, order and the rest behave identically whichever one you call. The dialect is the only stage that differs per engine: identifier quoting, pagination syntax, and which clauses are legal all resolve there. That is why the same FindOptions produces LIMIT ... OFFSET on PostgreSQL and OFFSET ... FETCH NEXT on MSSQL without your code changing. The finders Method | Returns | Notes | --- | --- | --- | findAll(options) | Model[] | The workhorse. Everything else is built on it. | findOne(options) | Model \| null | Adds LIMIT 1. | findByPk(pk, options) | Model \| null | Looks up by primary key. | findOneOrFail(options) | Model | Throws EmptyResultError instead of returning null. | findByPkOrFail(pk, options) | Model | Same, by primary key. | findLast(options) | Model \| null | Orders by primary key descending, LIMIT 1. | findAndCountAll(options) | { rows, count } | Rows for the page, count for the whole set. | findOrCreate(options) | [Model, created] | Finds by where, inserts defaults if missing. | findOrBuild(options) | [Model, built] | Same, but does not insert. | count(options) | number | SELECT COUNT(). | exists(where) | boolean | A count under the hood. | min / max / sum / avg | number | Real SQL aggregates. | findInBatches(options, handler, { batchSize }) | number | Keyset pagination over the whole table. | findEach(options, handler, { batchSize }) | number | Same, one row at a time. | where Conditions are plain objects. Keys at the same level are AND-ed; operators live under the column as symbol keys. Op.or / Op.and nest: The complete operator set — comparison, pattern, range, array, JSON, spatial, full-text — is in Query operators. Filtering on an included table $alias.column$ inside where refers to a column on an eagerly-loaded association. It is rewritten onto that include so it restricts the parent rows, before LIMIT is applied: Referring to an alias you did not include raises an AssociationError rather than silently dropping the condition. See Eager loading. attributes Select a subset of columns, or exclude a few: exclude is expanded into an explicit list rather than emitted as SELECT EXCEPT(...), because most dialects have no such syntax. Aliased expressions use the [expression, alias] form together with prorm.fn() / prorm.col(): order order must be an array. The accepted entry shapes, all verified against findAll: Form | Result | --- | --- | [['name','DESC'], ['id','ASC']] | ORDER BY "name" DESC, "id" ASC — the form to prefer | ['name DESC'] | ORDER BY name DESC — raw, passed through unquoted | [asc('a'), desc('b')], random() | see the helpers below | prorm.fn(...) expressions | rendered as the function call | Two shapes that look reasonable and are not supported: Form | What actually happens | --- | --- | order: 'name DESC' (a bare string, not in an array) | silently ignored — no ORDER BY is emitted at all and rows come back unordered | order: ['name', 'DESC'] (a flat pair) | read as two column names, so it fails with no such column: DESC | order: [{ name: 'DESC' }] (map form) | throws itemArr is not iterable on most dialects; only MSSQL, Snowflake and Oracle accept it | The bare-string case is the one to watch, because it fails silently. Always wrap in an array, and prefer the nested-array form. The asc() / desc() / random() helpers are exported at the package root: Field names are quoted with the dialect's own identifier rules ("a", a , [a]). random() resolves to whatever each engine actually uses for a shuffling sort key: SQL | Dialects | --- | --- | RANDOM() | SQLite, Turso, PostgreSQL, TimescaleDB, Greenplum, YugabyteDB, CrateDB, CockroachDB, Redshift, Vertica, Exasol, DuckDB, Snowflake, Trino | RAND() | MySQL, MariaDB, TiDB, SingleStore, Databricks, Firebird, SAP HANA, Db2, Spanner | rand() | ClickHouse | NEWID() | SQL Server | DBMSRANDOM.VALUE | Oracle | SQL Server is the one worth knowing: T-SQL's RAND() is evaluated once per query, so ordering by it does not shuffle at all — NEWID() is the correct idiom and is what you get. A dialect with no usable random sort key (QuestDB) throws a message naming the dialect rather than guessing. limit, offset and pagination findAndCountAll drops limit/offset from the count query but keeps everything else that narrows the set, so count describes the full result and rows describes the page. For walking a large table, prefer keyset iteration over OFFSET: Each round issues ... WHERE id > <last seen> ORDER BY id ASC LIMIT 500, so the cost does not grow as you get deeper into the table. findEach is the same loop with a per-row handler. group and having having accepts the same operators as where, including Op. symbol keys. groupType: 'rollup' | 'cube' | 'grouping' and groupingSets emit ROLLUP(...), CUBE(...) and GROUPING SETS (...) on dialects that have them. distinct Aggregates They are real SQL aggregates — nothing is loaded into memory to be summed — and they honour where, scopes and the paranoid deletedAt IS NULL filter. Writes Method | SQL | --- | --- | create(values) | INSERT INTO … VALUES (…) | bulkCreate(rows) | one INSERT per row (a loop, not a multi-row VALUES list); individualHooks: true routes each row through create() so per-row hooks fire | update(values, { where }) | UPDATE … SET … WHERE … | destroy({ where }) | DELETE FROM … WHERE … (or a deletedAt update on a paranoid model) | truncate() | TRUNCATE / DELETE FROM | upsert(values) | insert with the dialect's conflict clause | increment / decrement | arithmetic in SQL | bulkUpdate(rows, { key }) | one UPDATE … CASE statement for many rows | increment / decrement The arithmetic happens in the database, so two concurrent increments do not lose each other: bulkUpdate Different values for many rows in a single round trip: Rows the WHERE does not match are untouched, and the ELSE keeps columns that a given row did not supply. See Bulk operations. upsert The conflict clause is per dialect — ON CONFLICT … DO UPDATE on SQLite/PostgreSQL, ON DUPLICATE KEY UPDATE on MySQL/MariaDB, MERGE on SQL Server and Oracle. findOrCreate Common table expressions tableName overrides the FROM target so the query reads from the CTE instead of the model's own table. Recursive CTEs are built the same way with recursive: true on the CTE entry. Related-row counts without loading rows This issues one extra query per association (not per parent row) — SELECT FROM "posts" WHERE "userId" IN (…) — and tallies the rows in memory. It avoids the N+1 problem, but it does still transfer the child rows, so for very large children prefer an explicit grouped count(). Raw rows raw: true skips instance construction and hands back plain objects. Use it for grouped aggregates that map to no model row, and for hot read paths. Logging and benchmarking a single query logging: false silences one query on an otherwise-logging connection. See Logging. Choosing a connection using: 'analytics' routes one query to another connection registered on the same ConnectionManager, leaving the model's other queries where they are. Things to know Row locking. Every documented lock shape compiles — true, a level string, { of: Model }, and the { level, nowait, skipLocked } object form: | lock | PostgreSQL | MySQL | Oracle | SQL Server | | --- | --- | --- | --- | --- | | true / 'UPDATE' | FOR UPDATE | FOR UPDATE | FOR UPDATE | WITH (UPDLOCK, ROWLOCK) | | 'SHARE' | FOR SHARE | LOCK IN SHARE MODE | throws | WITH (HOLDLOCK, ROWLOCK) | | 'KEY SHARE' / 'NO KEY UPDATE' | FOR KEY SHARE / FOR NO KEY UPDATE | throws | throws | throws | | { level: 'UPDATE', nowait: true } | FOR UPDATE NOWAIT | FOR UPDATE NOWAIT | FOR UPDATE NOWAIT | WITH (UPDLOCK, ROWLOCK, NOWAIT) | | { level: 'UPDATE', skipLocked: true } | FOR UPDATE SKIP LOCKED | FOR UPDATE SKIP LOCKED | FOR UPDATE SKIP LOCKED | WITH (UPDLOCK, ROWLOCK, READPAST) | | { skipLocked: true } (no level) | FOR UPDATE SKIP LOCKED | FOR UPDATE SKIP LOCKED | FOR UPDATE SKIP LOCKED | WITH (UPDLOCK, ROWLOCK, READPAST) | | { of: Model } | FOR UPDATE OF "users" | FOR UPDATE OF users | FOR UPDATE | WITH (UPDLOCK, ROWLOCK) | SQL Server expresses locking as a table hint, not a trailing clause, which is why it looks different — and why scoping a lock to a different table throws there rather than silently locking the wrong one. A level a dialect lacks throws naming the supported levels; nowait and skipLocked together throw as mutually exclusive. Lock-less dialects (SQLite) drop lock silently — the transaction it would protect is already serialised by the engine. union. Forwarded, with the branch values bound correctly: EXCEPT and INTERSECT work the same way, and findAndCountAll /count() / exists() count over the combined query, so count and rows.length agree: Because the set operator dedupes inside the derived table, UNION and UNION ALL count correctly without special-casing. The outer limit/offset and order are excluded from the count — none can change it, and several dialects reject ORDER BY in a derived table. One caveat: combining union with a top-level limit is broken on SQLite — the dialect emits LIMIT before the UNION keyword and SQLite rejects it (LIMIT clause should come after UNION ALL not before). Put the limit on each branch, or use prorm.query(). transaction. Thread transaction: t through every call that should be part of the transaction — see Transactions. Next: Query operators — comparisons, ranges, pattern matching and JSON access. Related reading Query operators — every Op. Eager loading — include, nested and filtering includes Scopes — reusable FindOptions fragments Raw queries — prorm.query(), replacements, query types Bulk operations Query optimization — explain plans, hints, caching