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.

The examples use one small shop schema throughout — users, orders and
products — and every SQL string on this page is what the dialect's builder
produced for that call, captured with the driver stubbed out. Where the engines
differ, the SQL sits in dialect tabs; where they only differ in identifier
quoting ("col" vs `col` vs [col]), one engine is shown.

Models built with prorm.define() send the statement with the bound values
already escaped into it (through the dialect's own escape()), so that is the
form you see here and in the query log. A dialect called directly —
dialect.buildSelectQuery() — returns placeholders plus a separate values
array instead; pipe() shows that form.

import { Prorm, DataTypes } from 'ts-prorm-orm';

export const prorm = new Prorm({ dialect: 'postgres', host: 'localhost', database: 'shop' });

export const User = prorm.define('User', {
  id:          { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  name:        { type: DataTypes.STRING(100) },
  email:       { type: DataTypes.STRING(255) },
  age:         { type: DataTypes.INTEGER() },
  status:      { type: DataTypes.STRING(20) },
  preferences: { type: DataTypes.JSON() },
}, { tableName: 'users', timestamps: false });

export const Order = prorm.define('Order', {
  id:        { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  userId:    { type: DataTypes.INTEGER() },
  status:    { type: DataTypes.STRING(20) },
  total:     { type: DataTypes.DECIMAL(10, 2) },
  createdAt: { type: DataTypes.DATE() },
}, { tableName: 'orders', timestamps: false });

export const Product = prorm.define('Product', {
  id:    { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  sku:   { type: DataTypes.STRING(32) },
  name:  { type: DataTypes.STRING(200) },
  price: { type: DataTypes.DECIMAL(10, 2) },
  stock: { type: DataTypes.INTEGER() },
}, { tableName: 'products', timestamps: false });

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:

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

const page = await Order.findAll({
  where: { status: 'paid', total: { [Op.gte]: 100 } },
  order: [['createdAt', 'DESC']],
  limit: 20,
  offset: 40,
});

In order, one findAll call does this:

The sequence shows where the fail-closed WHERE guard
sits — before the builder, so a condition the dialect cannot render throws
instead of being dropped — and that the connection is the transaction's own
when one is passed (or ambient under transactionContext: true), the pool's
otherwise. To build the options object from optional parts rather than by
hand, see pipe() and Q.

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.

The single-row finders are where engines first diverge — SQL Server has no
LIMIT, so "one row" becomes TOP(1):

const ada   = await User.findOne({ where: { email: 'ada@example.com' } });
const byKey = await User.findByPk(42);

`where`

Conditions are plain objects. Keys at the same level are AND-ed; operators
live under the column as symbol keys.

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

await User.findAll({
  where: {
    name:  { [Op.in]: ['a', 'b'] },
    age:   { [Op.between]: [10, 50] },
    email: { [Op.like]: '%@example.com' },
  },
});

Op.or / Op.and nest:

await User.findAll({
  where: { [Op.or]: [{ age: { [Op.lt]: 25 } }, { status: 'off' }] },
});

A realistic filter mixes both — "paid or shipped orders between 50 and 500":

await Order.findAll({
  where: {
    status: { [Op.in]: ['paid', 'shipped'] },
    total:  { [Op.between]: [50, 500] },
  },
});

The complete operator set — comparison, pattern, range, array, JSON, spatial,
full-text — is in Query operators. A condition the
active dialect's builder cannot render throws UnsupportedWhereError before
the statement is sent, rather than running without it; what is refused, and
why, is in Query safety.

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:

await User.findAll({
  include: [{ model: Post, as: 'posts' }],
  where: { '$posts.status$': 'published' },
});

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:

await User.findAll({ attributes: ['id', 'name'] });
await User.findAll({ attributes: { exclude: ['email'] } });

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():

await User.findAll({
  attributes: ['status', [prorm.fn('COUNT', prorm.col('id')), 'total']],
  group: ['status'],
});

`order`

await User.findAll({ order: [['age', 'DESC'], ['name', 'ASC']] });

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

[!WARNING] A bare string is silently ignored
order: 'name DESC' compiles to no ORDER BY at all, and nothing throws.
Always wrap in an array, and prefer the nested-array form.

The asc() / desc() / random() helpers are exported at the package root:

import { asc, desc, random } from 'ts-prorm-orm';

await User.findAll({ order: [desc('name')] });        // ORDER BY "name" DESC
await User.findAll({ order: [asc('a'), desc('b')] }); // ORDER BY "a" ASC, "b" DESC
await User.findAll({ order: random() });              // see below

"Three random products" shows both the quoting and the sort key changing per
engine:

await Product.findAll({ order: [desc('price'), asc('name')] });
await Product.findAll({ order: random(), limit: 3 });

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
`DBMS_RANDOM.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

const page = 3, perPage = 20;
const { rows, count } = await Order.findAndCountAll({
  where: { status: 'paid' },
  order: [['id', 'ASC']],
  limit: perPage,
  offset: (page - 1) * perPage,
});
const pages = Math.ceil(count / perPage);

[!TIP]
SQL Server's OFFSET … FETCH is only legal after an ORDER BY, so give every
paged query an explicit order — which you want anyway, since without one
no engine promises a stable page.

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:

await Order.findInBatches({ where: { status: 'paid' } }, async (batch) => {
  await ship(batch);
}, { batchSize: 500 });

The first round is

(SELECT TOP(500) … ORDER BY [id] ASC on SQL Server), and each later round
adds "id" > <last seen>, so the cost does not grow as you get deeper into the
table. findEach is the same loop with a per-row handler.

[!TIP]
OFFSET 100000 still reads and discards 100,000 rows. Keyset iteration seeks
straight to id > last, so use findInBatches for exports and backfills and
keep offset for user-facing page numbers.

`group` and `having`

const byStatus = await Order.findAll({
  attributes: [
    'status',
    [prorm.fn('COUNT', prorm.col('id')), 'orders'],
    [prorm.fn('SUM', prorm.col('total')), 'revenue'],
  ],
  group: ['status'],
  having: { status: { [Op.ne]: 'cancelled' } },
  raw: true,
});
// [{ status: 'paid', orders: 42, revenue: 5120.5 }, …]

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
(PostgreSQL, SQL Server; MySQL has WITH ROLLUP only). The same goes for
distinctOn and sample (TABLESAMPLE, PostgreSQL). A dialect that cannot
render one of these refuses the query rather than running it without it.

`distinct`

await User.findAll({ attributes: ['status'], distinct: true });

Aggregates

const paidOrders = await Order.count({ where: { status: 'paid' } });
const revenue    = await Order.sum('total', { where: { status: 'paid' } });
const biggest    = await Order.max('total');
const smallest   = await Order.min('total');
const average    = await Order.avg('total');

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

update and destroy need a where that selects rows: with no where, or
an empty one (where: {}), they throw Missing where option in update /
… in destroy before anything is sent. Pass all: true to write the whole
table on purpose (Order.destroy({ all: true })). Before 4.9.2, update
without a where and destroy({ where: {} }) changed every row. See
Bulk operations.

`increment` / `decrement`

The arithmetic happens in the database, so two concurrent increments do not
lose each other:

await Product.increment('stock', { where: { id: 7 }, by: 5 });
await Product.decrement({ stock: 2 }, { where: { id: 7 } });

decrement is increment by the negated amount, which is why it renders as
+ -2.

`bulkUpdate`

Different values for many rows in a single round trip:

await User.bulkUpdate([{ id: 1, status: 'x' }, { id: 2, status: 'y' }], { key: 'id' });

Rows the WHERE does not match are untouched, and the ELSE keeps columns
that a given row did not supply. See Bulk operations.

`upsert`

await Product.upsert({ id: 1, sku: 'KB-01', name: 'Keyboard', price: 49 });

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:

The MERGE statement

On a define() model the upsert is bracketed by a primary-key SELECT before
and after it (SELECT * FROM "products" WHERE "id" = 1 LIMIT 1), which is how
it reports whether the row was created and returns the stored row.

`findOrCreate`

const [user, created] = await User.findOrCreate({
  where: { email: 'ada@example.com' },
  defaults: { name: 'Ada', age: 36 },
});

[!CAUTION]
The select and the insert are two statements. Two requests racing on the same
where can both see "missing" and both insert — back the where columns with
a unique index, and run the call in a transaction, so
the loser fails instead of duplicating.

Common table expressions

await Order.findAll({
  cte: [{ name: 'big_orders', query: 'SELECT * FROM orders WHERE total >= 500' }],
  tableName: 'big_orders',
  order: [['total', 'DESC']],
});

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.

const users = await User.findAll({ withCount: ['posts'] });
users[0].postsCount; // number

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.

const rows = await User.findAll({ raw: true });
rows[0].save; // undefined — these are plain objects

Logging and benchmarking a single query

await User.findAll({
  where: { status: 'active' },
  logging: (sql, ms) => console.log(sql, ms),
  benchmark: true,
});

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.
A job queue that claims one unlocked row at a time:

await prorm.transaction(async (t) => {
  const [next] = await Product.findAll({
    where: { id: 7 },
    lock: { level: 'UPDATE', skipLocked: true },
    transaction: t,
  });
});

The full matrix:

`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:

await Active.findAll({ where: { status: 'a' }, unionType: 'UNION ALL',
                       union: [{ model: Archived, where: { status: 'x' } }] });

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.

[!WARNING]
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.

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

How a read becomes SQL#

The finders#

where#

Filtering on an included table#

attributes#

order#

limit, offset and pagination#

group and having#

distinct#

Aggregates#

Writes#

increment / decrement#

bulkUpdate#

upsert#

findOrCreate#

Common table expressions#

Related-row counts without loading rows#

Raw rows#

Logging and benchmarking a single query#

Choosing a connection#

Things to know#

Related reading#