Querying
Filters, sorting, pagination, relations, counts and aggregates
Read rows
const todos = await db.orm.todo.all();all() returns every matching row. You can await it for an array, call .toArray(), or iterate it:
for await (const todo of db.orm.todo.where({ done: false }).all()) {
console.log(todo.title);
}first() returns the first matching row, or null:
const next = await db.orm.todo.where({ done: false }).orderBy({ title: "asc" }).first();findUnique() reads one row by primary key and returns null when there's none. For a compound key, pass the values as an array, in the order the key declares them:
const user = await db.orm.user.findUnique("user_1");
const member = await db.orm.membership.findUnique(["org_1", "user_1"]); // @@id([orgId, userId])Filtering
Pass an object to match fields by equality. Every field must match:
const mine = await db.orm.todo.where({ done: false, userId: "user_1" }).all();
const noEmail = await db.orm.user.where({ email: null }).all(); // email is nullPass a function to use operators. It receives each field of the model:
const releases = await db.orm.todo.where((todo) => todo.title.contains("release")).all();| Operator | Matches when the field… |
|---|---|
eq(v), neq(v) | equals, or doesn't equal, v. |
gt(v), gte(v), lt(v), lte(v) | is greater or less than v. |
in(values), notIn(values) | is, or isn't, one of values. |
contains(s), startsWith(s), endsWith(s) | is a string containing, starting or ending with s. Case-sensitive. |
isNull(), isNotNull() | is, or isn't, null. |
Combine conditions with and, or and not from @prisma-idb/client-idb/orm:
import { and, not, or } from "@prisma-idb/client-idb/orm";
const todos = await db.orm.todo
.where((todo) => or(todo.title.contains("release"), and(todo.done.eq(false), not(todo.userId.eq("user_1")))))
.all();Calling where() more than once adds conditions: a row must match all of them.
Nullable comparisons follow SQL's three-valued logic. A comparison against a null or absent field is unknown. not(unknown) stays unknown. and returns false if any condition is false; otherwise, an unknown condition makes the result unknown. or returns true if any condition is true; otherwise, an unknown condition makes the result unknown. A filter keeps only rows where the complete condition is true.
For example, email.neq("alice@example.com"), email.notIn(["alice@example.com"]), and not(email.eq("alice@example.com")) exclude null emails. To include them, use or(email.neq("alice@example.com"), email.isNull()).
Explicit null checks remain true or false: eq(null) and isNull() match null and absent fields. neq(null) and isNotNull() match present values. The shorthand { email: null } is also a null check.
Membership lists preserve SQL null semantics:
in(["Alice", null])matches"Alice". For other values, including null, the result is unknown.notIn(["Alice", null])matches no rows. Negating it matches only"Alice".in([])is false andnotIn([])is true for every row, including null fields.
DateTime and Bytes values compare by value, so eq(new Date("2026-01-01")) matches a stored date at that moment. Comparing values of different types follows IndexedDB's key order: numbers, then dates, then strings, then binary values.
Decimal fields use exact numeric equality, membership, ranges and sorting. For example, eq("1e1") matches "10.00", and gt("9") matches "10". Numeric comparisons preserve all significant digits, including values outside JavaScript's number range. String operators such as startsWith still inspect the stored text. See Decimal storage and valid inputs.
The query planner uses primary keys and indexes for supported equality, range, membership, and compound filters. It reapplies the full filter to candidate rows. When no index can cover every possible match, it scans the store. Decimal predicates use residual numeric filters rather than native string key ranges. Decimal sorting completes before skip and take.
Sorting and pagination
const page = await db.orm.todo.orderBy({ title: "asc" }).skip(20).take(10).all();orderBytakes one or more fields, each"asc"or"desc". Later fields break ties. CallingorderByagain replaces the sort.nullsorts after every other value, or before them when descending.- Without
orderBy, row order is unspecified and can change when the planner chooses another access path. Add a unique field toorderBywhen order matters.
Select
Return only some scalar fields. The result type narrows to match:
const titles = await db.orm.todo.select("id", "title").all();
// { id: string; title: string }[]Include relations
include() adds a related model to each row. A to-one relation becomes a row or null; a to-many relation becomes an array.
const todos = await db.orm.todo.include("user").all(); // todo.user: User | null
const users = await db.orm.user.include("todos").all(); // user.todos: Todo[]Pass a function to filter, sort or limit a to-many relation. These apply per parent, so take(5) gives each user up to five todos:
const users = await db.orm.user.include("todos", (todos) => todos.where({ done: false }).take(5)).all();Return count() from the function to get the number of related rows instead:
const users = await db.orm.user.include("todos", (todos) => todos.count()).all(); // user.todos: numberEach included relation is loaded with one batched read, however many parent rows there are.
Count
const open = await db.orm.todo.where({ done: false }).count();count() applies skip and take, unlike SQL's COUNT. To get the total for a paginated list, count before adding them.
Aggregates
const stats = await db.orm.task.aggregate((agg) => ({
total: agg.count(),
avgPriority: agg.avg("priority"),
maxPriority: agg.max("priority"),
}));count() counts every matching row, including rows with null or unsupported field values. It returns 0 for an empty set.
sum, avg, min and max accept scalar Int and Float fields and return number | null. They skip null and absent values. They return null when no value remains. Field types are checked at runtime, including contracts without TypeScript type maps. String, DateTime, Decimal, BigInt, collection and unknown fields are rejected, even for an empty set. Numeric strings and dates are never converted to numbers.
Non-null inputs must be finite numbers. Int inputs must also be integers in the signed 32-bit range. sum and avg throw a RangeError if an intermediate total becomes nonfinite. For Int, they also throw if an intermediate total exceeds the JavaScript safe-integer range, ±(2^53 − 1). This can reject an average even when its final value would fit. Float arithmetic uses JavaScript floating-point precision. Exact BigInt and Decimal aggregation is not supported.
Group by
const perUser = await db.orm.todo.groupBy("userId").aggregate((agg) => ({ total: agg.count() }));
// [{ userId: "user_1", total: 3 }, { userId: "user_2", total: 1 }]groupBy() returns one row per distinct combination of the grouping fields, with the aggregates you ask for. Grouping keys can be BigInt fields. The aggregate selectors follow the same input and overflow rules as ordinary aggregates. An empty set produces no groups. Equal Date, binary, array and JSON object values share a group. A Date and its ISO string form separate groups. Invalid Dates share a separate group. Null and undefined share a group.