API Reference

Every query method, operator, and API surface Turbine exposes. Generated clients attach db.<table> accessors with the full API below; the examples use db.users and db.posts, but any introspected table works identically.

findMany#

Returns an array of rows matching the query. Supports where, with, orderBy, limit / take, offset, cursor, distinct, select, and omit.

const users = await db.users.findMany({
  where: { role: 'admin', orgId: 1 },
  orderBy: { createdAt: 'desc' },
  limit: 20,
  offset: 0,
});

With nested relations:

const users = await db.users.findMany({
  where: { orgId: 1 },
  with: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      limit: 5,
      with: { comments: true },
    },
  },
});

Under relationLoadStrategy: 'join' the whole graph resolves in one SQL statement. Under the 'auto' default, Turbine may move an individual relation to a follow-up statement when the join plan would be slower, most often an unindexed correlation column or a to-one relation over an unbounded parent set. The rows are identical whichever plan runs, and users[0].posts[0].comments[0] is fully typed either way; see Load strategies for the rules and how to pin one.

Silencing the unlimited-query warning#

When warnOnUnlimited is enabled, a findMany with no limit / take / cursor logs a warning once per table (a where that pins the primary key or a unique column set is exempt, since that query is bounded by the constraint, and so is a client with a defaultLimit). This warning is not gated on NODE_ENV: it fires in production too, because an unbounded read is a production cost. Silence it for a query you know is bounded:

// This one is fine, a tiny lookup table
const roles = await db.roles.findMany({ warnOnUnlimited: false });

You can also set it per table on the client: warnOnUnlimited: { userProfiles: false } (accessor or snake_case keys). Precedence is per-call, then per-table, then the global boolean.

Deterministic pages: the unordered-pagination warning#

An unordered LIMIT is not stable: Postgres is free to return different rows for the same LIMIT once the heap changes underneath it, so paging with no orderBy can hand you the same row twice or skip one entirely. A findMany that paginates (limit / take / offset) with no orderBy therefore logs a one-time dev warning naming the table, the pagination shape, and the exact orderBy to add:

// warns: the page is not deterministic
const page = await db.users.findMany({ limit: 20, offset: 40 });
 
// no warning: the page is stable
const page = await db.users.findMany({ orderBy: { id: 'asc' }, limit: 20, offset: 40 });

distinct queries are exempt (DISTINCT ON imposes its own ordering). A cursor with no orderBy is not exempt, and is the worse case: the seek compares against rows the engine may return in any order, so the page skips and repeats rows, and the warning's suggested orderBy names the cursor field rather than the primary key. The warning is gated exactly like warnOnUnlimited (per-call, per-table, global) and deduped per table and shape, but unlike warnOnUnlimited it is silent under NODE_ENV=production.

To fix it globally instead of per query, set implicitPkOrdering: true on the client. Turbine then adds ORDER BY <primary key> ASC (every column of a composite key, in declaration order) to any paginating findMany that declares no orderBy, and the warning stops firing. An explicit orderBy always wins, and a table with no primary key is left alone. A single-field cursor is ordered by its own field rather than by the primary key, the only ordering coherent with the seek; a multi-field cursor with no orderBy gets nothing injected, because its key precedence has to come from orderBy, and the warning stays.

const db = turbine({ connectionString: process.env.DATABASE_URL, implicitPkOrdering: true });

It is off by default because enabling it changes SQL your application already emits: both the rows a page returns and the plan the engine picks. The prisma-compat adapter applies the same ordering unconditionally, matching Prisma.

select / omit#

Pick or drop columns at the query level. Either one, not both: passing both in the same block (each naming at least one field) throws a ValidationError (TURBINE_E003). A select that names no fields (empty, or every value false) is refused the same way rather than resolving to an empty column list.

// Only return id and email
const users = await db.users.findMany({
  select: { id: true, email: true },
});
 
// Return everything except the password_hash column
const users = await db.users.findMany({
  omit: { passwordHash: true },
});

Both take column names. A name that does not resolve throws a ValidationError (TURBINE_E003) naming the table and suggesting the closest match, at the top level and inside a relation's with block alike. Naming a relation in select or omit gets its own message pointing at with.

PII fields: includePii#

Columns tagged pii: true in defineSchema are excluded from every default projection: top-level rows, relation subqueries, batched loads, and a write's returned row. They come back in two ways: name the column explicitly in select (the explicit request is the opt-in), or pass includePii: UNSAFE to return every PII column at the top level and at every nested with level of that query.

One column is exempt: a PII-tagged column that is part of the primary key is returned anyway, on reads and writes alike. With part of its key missing, feeding a row back into an update builds a partial predicate that matches more rows than you meant. Tag sensitive data, not keys.

import { UNSAFE } from 'turbine-orm';
 
// Default: email (pii) is absent
const u = await db.users.findFirst({ where: { id: 1 } });
u.email; // undefined
 
// Explicit select returns exactly it
const withEmail = await db.users.findFirst({ where: { id: 1 }, select: { email: true } });
 
// includePii returns every PII column, including inside `with`
const full = await db.users.findMany({
  where: { active: true },
  with: { posts: true },
  includePii: UNSAFE,
});

includePii is a privilege option: the imported UNSAFE symbol is the only value that enables it, and a literal includePii: true throws a ValidationError (TURBINE_E003). A symbol cannot arrive through JSON.parse, so db.users.findMany({ ...req.body }) cannot switch it on.

It is a read option (findMany / findUnique / findFirst and their *OrThrow forms, findManyStream, and the gated groupBy / aggregate shapes below), never a write option. The line the flag draws is whether a stored PII value can reach your application, so where, orderBy, and having on a PII column are always allowed: they return no PII value of their own.

Two aggregate shapes return stored cells and are refused without includePii: UNSAFE, throwing a ValidationError (TURBINE_E003) that names the column:

• A PII-tagged column used as a groupBy by key, including a JSON-path group key. The group keys are the values.
• _min / _max over a PII column, in both groupBy and aggregate, including JSON-path targets. Each returns one row's actual stored value.

Everything else over a PII column is allowed with no opt-in:

• _count, which returns a count rather than a value.
• _sum / _avg, which return a value computed across many rows rather than a stored cell.

// Refused: the group keys would be the email values
await db.users.groupBy({ by: ['email'], _count: true });
// ValidationError [TURBINE_E003]: ... "email" is a PII column ...
 
// Refused: _max returns one row's stored email
await db.users.aggregate({ _max: { email: true } });
 
// Allowed: explicit opt-in on either args type
await db.users.groupBy({ by: ['email'], _count: true, includePii: UNSAFE });
await db.users.aggregate({ _max: { email: true }, includePii: UNSAFE });
 
// Allowed with no flag: no stored PII value is returned
await db.users.groupBy({ by: ['role'], _count: true });
await db.users.aggregate({ _count: true, where: { email: { endsWith: '@acme.com' } } });

includePii is a field on both GroupByArgs and AggregateArgs. With it set, the emitted SQL is byte-identical to an untagged schema's; the policy applies on PowDB as well as the SQL engines, and schemas with no pii tags are unaffected everywhere.

Writes enforce the same boundary at the SQL level: a write against a table with PII columns (create, createMany, update, delete, upsert, nested writes) returns an explicit non-PII projection instead of RETURNING * (RETURNING "id", "name", ... on Postgres and SQLite, a projected re-select on MySQL, per-column OUTPUT on SQL Server, a client-side strip on PowDB, whose returning takes no column list). PII values write and persist normally; they are just not returned. A PII-tagged primary key stays in the projection, and tables with no PII columns keep RETURNING * byte-for-byte.

Pagination, limit / take, offset / skip, cursor#

Offset pagination uses limit + offset. take and skip are Prisma-compatible aliases for them, folded into limit / offset before anything reads the query, so the two spellings compile to the same SQL and share one cache entry. Passing both spellings of one bound with different values throws a ValidationError (TURBINE_E003) rather than picking one: there is no reading of { limit: 10, take: 5 } that makes one of the numbers the intended answer. Equal values are accepted. offset also works alone, on every engine: SQLite and MySQL reject a bare OFFSET at the grammar level, so their dialects emit the engine's own offset-to-end idiom.

const page2 = await db.posts.findMany({
  orderBy: { id: 'asc' },
  limit: 20,   // or take: 20, same thing
  offset: 20,
});

For deep pagination, cursor is the keyset alternative: pass the column values of the last row you saw, and Turbine compiles a seek over the cursor fields (> for ascending order, < for descending, direction taken from orderBy). The cursor row itself is excluded, so the next page starts immediately after it:

const firstPage = await db.posts.findMany({
  orderBy: { id: 'asc' },
  take: 20,
});
 
const nextPage = await db.posts.findMany({
  orderBy: { id: 'asc' },
  cursor: { id: firstPage.at(-1)!.id }, // rows with id > this value
  take: 20,
});

A multi-field cursor is a keyset seek, not a conjunction: for orderBy: [{ viewCount: 'asc' }, { id: 'asc' }] and cursor: { viewCount, id } the predicate is (view_count > $1 OR (view_count = $1 AND id > $2)), with < for a desc field, so rows that tie on the leading key are not skipped. Field precedence comes from orderBy; a cursor field the orderBy does not name trails the named ones and seeks ascending, and each value is bound once however many branches reference it. End the orderBy with a unique column (or a unique combination) so the ordering is total; unlike OFFSET 100000, a keyset page never scans the skipped rows. Before 0.78 a multi-field cursor joined its comparisons with AND, which dropped every row whose leading key equalled the cursor's; if you paged over a low-cardinality leading key, the pages now contain the rows they used to miss.

distinct#

distinct de-duplicates rows by the listed fields using PostgreSQL's DISTINCT ON:

// One post per author
const sample = await db.posts.findMany({
  distinct: ['userId'],
  orderBy: { userId: 'asc' },
});

Which row survives per group is governed by orderBy (per DISTINCT ON semantics): sort by the distinct fields first, then by whatever should decide the winner within each group.

Ordering, direction and NULLS placement#

orderBy maps fields to a direction. A plain 'asc' or 'desc' works everywhere. To control where NULL values land, pass a spec object instead:

const users = await db.users.findMany({
  orderBy: { lastLoginAt: { sort: 'desc', nulls: 'last' } },
});
// ORDER BY "last_login_at" DESC NULLS LAST

Plain and spec entries mix freely, and their order is preserved:

orderBy: { name: 'asc', lastLoginAt: { sort: 'desc', nulls: 'last' } }
// ORDER BY "name" ASC, "last_login_at" DESC NULLS LAST

For multi-key ordering you can also pass a Prisma-style array of objects. The array's element order is the authoritative sort precedence, so it never depends on JS object key iteration order:

orderBy: [{ createdAt: 'desc' }, { id: 'asc' }]
// ORDER BY "created_at" DESC, "id" ASC

Both forms accept { sort, nulls } specs and produce byte-identical SQL for the same key order, and both work everywhere an orderBy is compiled: findMany, a with relation, and groupBy.

Pitfall: NULLS FIRST / NULLS LAST is a PostgreSQL and SQLite feature. On MySQL and SQL Server, explicit nulls placement throws UnsupportedFeatureError (TURBINE_E017) rather than emitting broken SQL. Plain 'asc' / 'desc' works on every engine.

Ordering by a relation#

Order a query by an aggregate of its related rows. For a to-many relation the only key is _count: Turbine adds a correlated COUNT(*) subquery:

// Users, most posts first
const users = await db.users.findMany({
  orderBy: { posts: { _count: 'desc' } },
});

For a to-one relation, order by a column on the target (a correlated scalar subquery; { sort, nulls } supported):

// Posts, ordered by their author's name
const posts = await db.posts.findMany({
  orderBy: { author: { name: 'asc' } },
});

The chain can be more than one hop long, as long as every hop is to-one:

// Versions, ordered by the name of their model's category
const versions = await db.versions.findMany({
  orderBy: { model: { category: { name: 'asc' } } },
});

Each extra hop becomes an INNER JOIN inside the same correlated subquery: one subquery, one LIMIT 1, however many hops. Every hop applies its target's global filter to the join condition, so ordering never keys off a soft-deleted or other-tenant row. On a generated client the chain is typed, too: each value is a direction, a { sort, nulls } spec, or the next hop, and the type admits the head relation plus ten further hops, which is exactly where the builder stops (one more is TURBINE_E007).

A to-many hop mid-chain is refused at runtime with TURBINE_E003 (the type keys hops by name and cannot tell a to-many relation from a to-one one): it has no single value to order by. Use a pick-row ordering ({ pick, by }) or _count instead.

Relation ordering adds no bound parameters. An unknown relation throws RelationError (TURBINE_E005); a non-_count key on a to-many relation, or an unknown column on a to-one relation, throws ValidationError (TURBINE_E003).

findUnique / findUniqueOrThrow#

Look up a row by a unique column (primary key or any unique constraint).

The where must identify a single row: the primary key, a single-column unique, every column of a compound unique, or a compound-unique selector. Extra predicates alongside the key are fine, since they can only narrow a set that already holds at most one row. Anything else throws a ValidationError (TURBINE_E003) naming the keys that would have worked.

const user = await db.users.findUnique({
  where: { id: 42 },
  with: { posts: true },
});
// user is User | null
 
const user2 = await db.users.findUniqueOrThrow({
  where: { email: 'alice@example.com' },
});
// Throws NotFoundError (TURBINE_E001) if not found

Composite primary keys are passed as an object matching the keys:

const row = await db.memberships.findUnique({
  where: { userId: 1, orgId: 2 },
});

findFirst / findFirstOrThrow#

First matching row by the given where + orderBy. Non-unique lookups.

const post = await db.posts.findFirst({
  where: { authorId: 42, published: true },
  orderBy: { createdAt: 'desc' },
});

create#

Insert a single row. Returns the full row (including generated columns).

const newUser = await db.users.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    orgId: 1,
  },
});

Inserting a row of pure defaults#

data: {} (or a data whose every field is undefined) names no column, and inserts a row in which every column takes its database default:

// INSERT INTO "events" DEFAULT VALUES RETURNING *
const event = await db.events.create({ data: {} });

A table with no usable defaults still fails with the database's own NOT NULL violation (NotNullViolationError, TURBINE_E010).

Field names and column names resolve identically#

A key in data, where, orderBy, select, distinct, cursor, groupBy's by, or an aggregate target may be spelled as the field name (lastRun) or as the underlying column name (last_run). Both spellings compile to the same SQL and go through the same value processing, including the UTC rewrite on zone-less temporal columns:

// Identical statement, and identical bound value.
await db.jobs.update({ where: { id: 1 }, data: { lastRun: new Date() } });
await db.jobs.update({ where: { id: 1 }, data: { last_run: new Date() } });

A name that resolves to no column is still refused with ValidationError (TURBINE_E003), so a typo stays a typo.

Upgrade note: before v0.53 the column spelling skipped the value pass, so a Date written through a snake_case key to a zone-less date / timestamp / time column stored local calendar fields; check columns written that way before upgrading.

Second upgrade note: before v0.72 this page overstated the rule. orderBy, groupBy's by, and the _min / _max / _sum / _avg targets tested only the field spelling and rejected the column spelling with E003, while where, select, omit, distinct and cursor accepted it. v0.72 routes every one of those through the same resolver. It also fixed four cases where the mismatch produced a wrong answer rather than an error, the most serious being a cursor whose field used the column spelling against an orderBy using the field spelling: the seek direction was resolved as ascending regardless, so a descending page silently returned the wrong rows. See the changelog.

Relation names resolve the same way#

A relation may be named by its declared name (blogPosts) or by the table spelling of that name (blog_posts), in with, _count, relation filters (some / every / none / is / isNot), and orderBy, at any nesting depth:

// Identical statement, identical rows.
await db.authors.findMany({ with: { blogPosts: true } });
await db.authors.findMany({ with: { blog_posts: true } });

The declared name always wins, so a schema that literally declares blog_posts keeps it. Relation keys in the returned rows use the declared name whichever spelling you queried with, matching how a column selected as last_run comes back as lastRun. A name that matches no relation is still refused (RelationError, TURBINE_E005).

This landed in v0.72. Before it, reading a table name off the schema and using it as a relation name failed, even though the error text already named the relation you meant.

createMany#

Batch insert with a single INSERT ... UNNEST(...), not N separate inserts. (The one exception is a batch of pure defaults, see above: with no columns to unnest, Postgres emits INSERT INTO t SELECT FROM generate_series(1, N) instead.)

const users = await db.users.createMany({
  data: [
    { email: 'a@b.com', name: 'A', orgId: 1 },
    { email: 'b@b.com', name: 'B', orgId: 1 },
    { email: 'c@b.com', name: 'C', orgId: 1 },
  ],
});

update / updateMany#

update changes one row, addressed by a unique key. updateMany changes every row matching a filter.

Since 0.78.0 that is enforced rather than assumed: a where that does not identify a single row is refused with TURBINE_E003, naming the unique keys the table actually has. It used to update every matching row and return one of them, arbitrarily, which is the same hazard findUnique has refused since 0.73. delete and deleteMany split the same way, and upsert carries the same rule for its own reason (see upsert). If a predicate really does identify one row through a constraint Turbine's metadata cannot see, allowFullTableScan: UNSAFE opts out, at the cost of the empty-where guard as well: an all-undefined where then matches every row instead of being refused. One shape that changed with the rule is update({ where: {}, optimisticLock }), which is now refused. A version column is not unique, so that call silently updated every row at that version and reported one.

await db.users.update({
  where: { id: 42 },
  data: { name: 'Alice Updated' },
});
 
await db.users.updateMany({
  where: { role: 'guest' },
  data: { role: 'member' },
});

Atomic update operators#

For race-safe counter updates, pass an operator object instead of a literal. Turbine generates col = col + $n style SQL so concurrent updates can't lose writes.

await db.posts.update({
  where: { id: 1 },
  data: {
    viewCount: { increment: 1 },
    likesCount: { decrement: 1 },
    score: { multiply: 2 },
    rank: { divide: 2 },
    title: { set: 'New title' }, // explicit set, same as a literal
  },
});

Supported operators on numeric columns: set, increment, decrement, multiply, divide.

delete / deleteMany#

await db.users.delete({ where: { id: 42 } });
 
await db.users.deleteMany({
  where: { createdAt: { lt: thirtyDaysAgo } },
});

An empty where ({}, or one whose every value is undefined) throws ValidationError: Turbine blocks accidental mass deletes. To really mean it, import the sentinel and pass allowFullTableScan: UNSAFE alongside it:

import { UNSAFE } from 'turbine-orm';
 
await db.sessions.deleteMany({ where: {}, allowFullTableScan: UNSAFE }); // every row

where itself is still required: allowFullTableScan disarms the guard on an empty where, it does not let you drop the key.

allowFullTableScan is a privilege option: the UNSAFE symbol is the only value that enables it, and true throws (TURBINE_E003). The option is typed Unsafe, so a boolean does not compile; write a conditional call site as { where, ...(flag ? { allowFullTableScan: UNSAFE } : {}) }. The same applies to updateMany.

upsert#

Insert a row, or update it if a row matching the where clause already exists, via one atomic INSERT ... ON CONFLICT ... DO UPDATE. The where keys determine the conflict target and must be unique or primary key columns: no match inserts create, a match applies update.

Since 0.78.0 that requirement is checked before any SQL is built, with the same TURBINE_E003 update and delete raise. An upsert's where is its conflict target, so a non-unique predicate emitted an ON CONFLICT clause no constraint backs and PostgreSQL answered with a bare Database error 42P10 naming nothing you wrote. A table that declares no primary key and no unique constraint gets its own message: no where can identify one row there, so use create or updateMany instead.

const user = await db.users.upsert({
  where: { email: 'alice@example.com' },
  create: { email: 'alice@example.com', name: 'Alice', orgId: 1 },
  update: { name: 'Alice Updated' },
});

Returns the full row (via RETURNING *) whether it was inserted or updated.

// Upsert with a composite key
const membership = await db.memberships.upsert({
  where: { userId: 1, orgId: 2 },
  create: { userId: 1, orgId: 2, role: 'member' },
  update: { role: 'admin' },
});

count#

const total = await db.users.count();
const admins = await db.users.count({ where: { role: 'admin' } });

aggregate#

const stats = await db.posts.aggregate({
  where: { orgId: 1 },
  _sum: { viewCount: true },
  _avg: { score: true },
  _max: { createdAt: true },
  _count: true,
});

On a PII-tagged column, _min / _max require includePii: UNSAFE; _count and where need no opt-in. See PII fields.

_sum / _avg return a number over int2 / int4 / float columns and an exact string over int8 / bigint and numeric / decimal columns, where PostgreSQL computes the result as numeric and Number() would round it; _min / _max are different, and deliberately so: they return one stored cell, read back by the same driver rule a row uses, so an int8 comes back as a number while it fits in JavaScript's safe integer range and as a string above it. The aggregate rule is decided by the column TYPE and the row rule by the VALUE's magnitude, because _sum and _avg compute a result no row holds and rounding it is a loss that no later read can undo. The same rule, with the reasoning, is under groupBy.

groupBy#

Group rows by one or more columns and compute aggregations per group. Similar to SQL GROUP BY with aggregate functions.

const postsByUser = await db.posts.groupBy({
  by: ['userId'],
  _count: true,
  _sum: { viewCount: true },
  _avg: { score: true },
});
// [{ userId: 1, _count: 12, _sum: { viewCount: 4800 }, _avg: { score: 4.2 } }, ...]
// If `score` were numeric (or bigint), _avg would be the exact string PostgreSQL
// renders, e.g. _avg: { score: '4.2000000000000000' }; see the type note below.

Grouping by a PII-tagged column, or _min / _max over one, requires includePii: UNSAFE; where / orderBy / having stay unrestricted. See PII fields.

The result row is fully typed from the args, no as const needed: each by field carries its entity field type, _count is a number, _sum / _avg fields are number | string | null, and _min / _max fields carry the field's own type. The call above infers:

// { userId: number; _count: number;
//   _sum: { viewCount: number | string | null };
//   _avg: { score: number | string | null } }[]

The string in _sum / _avg is the exact-value rule the rest of the read path already follows: over an int8 / bigint or a numeric / decimal column, PostgreSQL returns the sum and the average as numeric text, and Number() would round anything past 15 significant digits (a SUM(bigint) clears 2^53 easily), so the text is passed through verbatim. Over int2 / int4 / float columns the value is a number, and an aggregate over zero matching rows is null either way. Before 0.78 every _sum / _avg was coerced with Number(), so a bigint total came back rounded with no error.

Grouping by a JSON path yields a runtime alias that cannot be typed, so those columns are left off the row type; cast the result.

Filtering groups#

Pass a where clause to filter rows before grouping:

const activePostsByOrg = await db.posts.groupBy({
  by: ['orgId'],
  where: { published: true },
  _count: true,
  _max: { createdAt: true },
  orderBy: { _count: 'desc' },
});

Multiple group-by columns#

const breakdown = await db.posts.groupBy({
  by: ['orgId', 'published'],
  _count: true,
  _min: { createdAt: true },
  _max: { createdAt: true },
});

Supported aggregate functions: _count, _sum, _avg, _min, _max. When _count is true (or omitted), each group includes a _count field with the number of rows in that group. For Prisma parity you can also pass _count: { _all: true }, which returns _count: { _all: n } (a record) instead of the scalar number.

Grouping and aggregating over JSON paths#

Group keys and aggregate targets can drill into json/jsonb columns. A group key is { field, path, alias? }; an aggregate target is keyed by its result alias and points at { field, path, type? }:

const revenueByCategory = await db.orderItems.groupBy({
  by: [{ field: 'data', path: ['category'] }],           // result key: 'category'
  _sum: { price: { field: 'data', path: ['price'] } },    // numeric cast, result key: 'price'
});

_sum/_avg always cast numeric; _min/_max compare as text unless type: 'numeric'. having works on the alias. Result-key collisions throw upfront rather than silently overwriting.

Ordering groups#

orderBy sorts the result groups by any column the result actually contains: a plain by-column, a JSON group-key alias, or a requested aggregate. Aggregates are ordered by _count directly, or by _sum / _avg / _min / _max keyed by the aggregate field (or its JSON alias):

const topCategories = await db.orderItems.groupBy({
  by: [{ field: 'data', path: ['category'] }],           // result key: 'category'
  _count: true,
  _sum: { price: { field: 'data', path: ['price'] } },    // result key: 'price'
  orderBy: {
    _sum: { price: 'desc' },   // biggest revenue first (by the JSON aggregate)
    category: 'asc',           // tie-break by the group-key alias
  },
});

Each key re-emits the exact SQL expression it selected (the same one having uses), so aggregate and JSON-alias ordering works on every engine. Ordering by an aggregate you did not request, or by an unknown key, throws with the list of valid keys. { sort, nulls } specs apply here too (Postgres / SQLite).

Top N groups: limit and offset#

groupBy accepts optional limit and offset, applied after ORDER BY. Pair them with a deterministic orderBy for "top N groups" and paginated grouped results:

// The 10 users with the most posts
const topAuthors = await db.posts.groupBy({
  by: ['userId'],
  _count: true,
  orderBy: { _count: 'desc' },
  limit: 10,
});

limit / offset are parameterized on PostgreSQL / SQLite / SQL Server, inlined on MySQL, and native on PowDB.

Latest row per parent: distinctOn#

Aggregate over only the newest row per group of some key (a version store's "latest version per instance") with distinctOn (Postgres only):

const latestByCategory = await db.versions.groupBy({
  distinctOn: { columns: ['instanceId'], orderBy: { createdAt: 'desc' } },
  by: [{ field: 'data', path: ['category'] }],
  _sum: { price: { field: 'data', path: ['price'] } },
});

The row source becomes SELECT DISTINCT ON ("instance_id") ... ORDER BY "instance_id", "created_at" DESC before grouping; where filters rows before the pick. distinctOn.orderBy is required for determinism.

Filtering groups with HAVING#

A where clause filters rows before grouping. A having clause filters the resulting groups after. Every comparison value is parameterized.

// Users with more than one post
const prolific = await db.posts.groupBy({
  by: ['userId'],
  _count: true,
  having: { _count: { gt: 1 } },
});
 
// Groups whose summed view count clears a threshold
const popular = await db.posts.groupBy({
  by: ['published'],
  _sum: { viewCount: true },
  having: { viewCount: { _sum: { gte: 100 } } },
});

Filter on the group row count with the top-level _count, or on a column aggregate with { column: { _sum | _avg | _min | _max: { ... } } }. Aggregate operators are equals, not, gt, gte, lt, lte, in, and notIn; a bare value is shorthand for equality. Multiple having predicates combine with AND:

// Groups with > 1 row AND a summed view count <= 500
const niche = await db.posts.groupBy({
  by: ['userId'],
  _count: true,
  _sum: { viewCount: true },
  having: { _count: { gt: 1 }, viewCount: { _sum: { lte: 500 } } },
});

_min / _max operands are not numeric-only: they return a stored cell, so the operand is the column's own type:

const lateAlphabetically = await db.posts.groupBy({
  by: ['userId'],
  _min: { title: true },
  having: { title: { _min: { gt: 'm' } } },
});
// ... HAVING MIN("title") > $1

Filtering on the grouped value

A field entry also accepts a filter on the grouped value itself, not just on an aggregate of it. This is Prisma's having shape, and it covers the common "drop the NULL group" case:

const byType = await db.posts.groupBy({
  by: ['typeId'],
  _count: true,
  having: { typeId: { not: null }, _count: { gt: 1 } },
});
// ... GROUP BY "type_id" HAVING "type_id" IS NOT NULL AND COUNT(*) > $1

A bare value is equality shorthand, and both forms may appear in the same object (they are ANDed):

await db.posts.groupBy({ by: ['published'], _count: true, having: { published: true } });
// ... HAVING "published" = $1
 
await db.posts.groupBy({
  by: ['status'],
  _sum: { viewCount: true },
  having: { status: { startsWith: 'pub' }, viewCount: { _sum: { gte: 100 } } },
});
// ... HAVING "status" LIKE $1 ESCAPE '\' AND SUM("view_count") >= $2

Scalar predicates compile through the same machinery as where, so the whole WHERE operator surface is available: in / notIn, contains / startsWith / endsWith with mode: 'insensitive', JSON and array filters, LIKE escaping, and each engine's own IN form. Grouping by a JSON path works too, keyed by the group-key alias.

AND / OR / NOT

Scalar and aggregate predicates combine at any depth:

const interesting = await db.posts.groupBy({
  by: ['status'],
  _count: true,
  _sum: { viewCount: true },
  having: {
    OR: [
      { _count: { gt: 100 } },
      { AND: [{ status: 'draft' }, { viewCount: { _sum: { gt: 1000 } } }] },
    ],
  },
});
// ... HAVING (COUNT(*) > $1 OR ("status" = $2 AND SUM("view_count") > $3))

AND and NOT take either one having object or an array of them; OR takes an array. The shapes match the where combinators.

HAVING is emitted after GROUP BY and before ORDER BY, and its parameters continue the same numbering as any where params.

findManyStream#

Stream rows using a PostgreSQL server-side cursor. Constant memory, works on any number of rows, and supports nested with clauses inside the stream.

for await (const user of db.users.findManyStream({
  where: { orgId: 1 },
  orderBy: { id: 'asc' },
  batchSize: 1000,       // internal FETCH batch size (default: 1000)
  with: { posts: true }, // nested relations work inside the stream
})) {
  process.stdout.write(`${user.email}\n`);
}

Under the hood, a stream whose batchSize is at or below the default (1000) opens with a speculative LIMIT batchSize + 1 first fetch: if the whole result fits in one batch, rows are yielded directly and the four round trips of BEGIN / DECLARE / CLOSE / COMMIT are skipped entirely. Larger results escalate to a cursor. Safe to break early; the cursor and connection are cleaned up deterministically.

WHERE operators#

Every operator composes freely with AND, OR, NOT, and relation filters.

Misspelled keys are a compile error#

On a generated, typed client, an unknown key in where does not compile:

// Compile error: "emial" does not exist on the where clause.
await db.users.findMany({ where: { emial: 'a@b.com' } });

The check follows the clause everywhere it nests:

await db.users.findMany({
  where: {
    OR: [
      { emial: 'a@b.com' },              // error, inside OR
      { posts: { some: { titel: 'x' } } }, // error, inside a relation filter
    ],
  },
  with: {
    posts: { where: { titel: 'x' } },     // error, inside a with block
  },
});

No regeneration is needed: the check reuses the relation brand your generated client already emits, and it is purely type-level, so the SQL is unchanged.

Where it is still permissive. These compile, deliberately or as a known gap:

Because this is a type-level guarantee, tsx and other transpile-only runners will not catch it. Run tsc --noEmit in CI.

Equality#

Sets#

mode: 'insensitive' applies to equals, not, in and notIn on string columns as well as to the substring operators; see String. Before 0.78 it was read only by contains / startsWith / endsWith and silently ignored beside the others. On in / notIn it is PostgreSQL-only among the SQL engines: SQLite, MySQL and SQL Server refuse it with TURBINE_E017, for the reason given in the table below. PowDB supports it, because PowQL's list form takes an expression per element and can fold each one in the engine.

Comparison#

String#

LIKE wildcards in user input (%, _, \) are escaped automatically.

Array columns#

Full-text search#

The query string is bound as a parameter and uses to_tsquery syntax (&, |, !, <->). The config name is validated (alphanumeric + underscore only) before it reaches the SQL.

JSON columns (json / jsonb)#

Filter into a JSON document with a JsonFilter. path drills into nested keys before the test runs; the other keys pick the comparison.

The range operators (gt / gte / lt / lte) require path. A numeric value casts the extracted text, (col #>> path)::numeric >= $n, while a string value compares as text:

// Products rated 4 or higher, read out of a jsonb column
const topRated = await db.products.findMany({
  where: { data: { path: ['rating'], gte: 4 } },
});

Note: A bare { gt: 5 } with no path on a jsonb column is the plain column comparison, not a JSON test; reach for path whenever you mean "the value inside the document."

The three substring operators are the JSON counterpart of the scalar contains / startsWith / endsWith, and they all require path. They are deliberately not spelled contains, because on a JSON column contains already means whole-document jsonb containment (@>). The operand is LIKE-escaped, so % and _ match literally.

// Rows whose jsonb `title` contains "orm", case-insensitively
const matches = await db.docs.findMany({
  where: { data: { path: ['title'], stringContains: 'orm', mode: 'insensitive' } },
});

Column-to-column comparison#

Compare a column against another column of the same table by passing { col: 'field' } to equals, not, gt, gte, lt, or lte. The referenced column is compiled into the SQL, no value is bound:

// Instances whose published version lags behind the draft
const stale = await db.modelInstances.findMany({
  where: { currentVersionId: { not: { col: 'publishedVersionId' } } },
});
// → WHERE "current_version_id" <> "published_version_id"

An unknown referenced field throws ValidationError (TURBINE_E003), and mode: 'insensitive' cannot be combined with a column reference. On json/jsonb columns an equals object is always a JSON value (containment), never a column reference, use path filters for JSON tests.

Ordering by a JSON path#

orderBy accepts a JSON-path spec on json/jsonb columns. Pass type: 'numeric' to sort numerically; the default compares the extracted value as text:

const byWeight = await db.blocks.findMany({
  orderBy: { data: { path: ['weight'], direction: 'asc', type: 'numeric' } },
});
// → ORDER BY ("data" #>> $1::text[])::numeric ASC NULLS LAST

Works top-level and inside a with relation's orderBy. Rows whose document lacks the path sort last in both directions by default, the same on every engine; pass nulls: 'first' | 'last' to override (PostgreSQL/SQLite).

Ordering by a value from one related row#

Order parents by a column (or JSON path) taken from a single row of a to-many relation: the row is chosen by pick. The classic shape is "sort by a field inside the newest related row":

const instances = await db.modelInstances.findMany({
  orderBy: {
    versions: {
      pick: { orderBy: { createdAt: 'desc' } },   // which related row: newest
      by: { field: 'data', path: ['title'] },      // value from that row (or by: 'title' for a plain column)
      direction: 'asc',
    },
  },
});

pick.orderBy is required (it makes the choice deterministic); pick.where optionally filters first. Compiles to a correlated scalar subquery in ORDER BY, so it composes with both relation-load strategies and the SQL cache. Parents with zero related rows sort last by default (nulls overrides). Postgres-first; plain-column by also works on SQLite/MySQL/SQL Server. Not combinable with distinct, and hasMany only.

Choosing the plan (plan: 'lateral'). By default the pick compiles as a correlated scalar subquery (plan: 'subquery'). On PostgreSQL you can opt into a LEFT JOIN LATERAL (... LIMIT 1) ON true instead, which can be faster on large parent sets where the ordering subquery dominates the plan:

orderBy: {
  versions: {
    pick: { orderBy: { createdAt: 'desc' } },
    by: { field: 'data', path: ['title'] },
    direction: 'asc',
    plan: 'lateral',   // PostgreSQL only; identical results to the default
  },
}

The results are identical to the default plan; measure both to decide. The lateral plan is PostgreSQL only and throws on the other engines rather than falling back silently. It is set per ordering entry, so a query with two picks can mix plans.

Relation filters#

Filter parent rows by predicates on their child rows.

Any operator above, including JSON filters, composes inside some / every / none.

// Users who own at least one product rated 4+
const users = await db.users.findMany({
  where: { products: { some: { data: { path: ['rating'], gte: 4 } } } },
});

Combinators#

where: {
  AND: [{ orgId: 1 }, { role: 'admin' }],
  OR: [{ role: 'owner' }, { role: 'admin' }],
  NOT: { deletedAt: { not: null } },
}

An empty combinator adds no predicate: OR: [], AND: [], NOT: {} and NOT: [] each compile to nothing, so where: { OR: [] } on its own returns every row. Prisma agrees for AND and NOT but not for OR, where an empty list matches nothing. A filter list assembled from user selections therefore needs an explicit decision about zero selections:

const picked = selectedRoles.map((role) => ({ role }));
const rows = await db.users.findMany({
  // OR: [] would return every user, so say what zero selections means.
  where: picked.length > 0 ? { OR: picked } : { id: { in: [] } }, // in: [] matches nothing
});

Nested with#

Relations in a with clause accept their own where, orderBy, limit, select / omit, and nested with.

const users = await db.users.findMany({
  with: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      limit: 10,
      select: { id: true, title: true, createdAt: true },
      with: {
        comments: {
          where: { deletedAt: null },
          orderBy: { createdAt: 'asc' },
          limit: 50,
        },
      },
    },
  },
});

Nesting depth is capped at 10; beyond that, Turbine throws CircularRelationError (TURBINE_E007) with the full relation path.

Counting related rows, _count#

Add _count to a with clause to get the number of related rows without loading them, assembled into a _count object on each row:

// Count every to-many relation of the table
const users = await db.users.findMany({
  with: { _count: true },
});
users[0]._count; // { posts: 12, orgs: 3 }
 
// Count only the named relations
const authors = await db.users.findMany({
  with: { _count: { posts: true } },
});
authors[0]._count.posts; // 12

Under relationLoadStrategy: 'join' each counted relation is an inline correlated COUNT(*) scalar subquery in the same statement. Under the 'auto' default, a count whose correlation column has no covering index moves to one grouped COUNT(*) ... GROUP BY fk follow-up statement (a query bounded at a single row keeps it inline). The numbers are the same either way; the load-strategy rules carry the measured difference.

_count coexists with real relation subqueries: with: { posts: true, _count: { posts: true } } loads the posts and counts them. It applies to hasMany and manyToMany (via the junction) relations; used on a to-one relation it throws ValidationError (TURBINE_E003), and an unknown relation throws RelationError (TURBINE_E005). The result type carries _count as { [relation]: number }, so users[0]._count.posts is typed.

Transactions#

await db.$transaction(async (tx) => {
  const user = await tx.users.create({
    data: { email: 'new@example.com', name: 'New', orgId: 1 },
  });
  await tx.posts.create({
    data: { userId: user.id, orgId: 1, title: 'Hello', content: '...' },
  });
});

tx has the same typed table accessors as db. Nested $transaction calls create SAVEPOINTs automatically.

Isolation levels + timeout#

await db.$transaction(
  async (tx) => {
    // ...
  },
  {
    isolationLevel: 'Serializable',
    timeout: 5000, // ms, destroys the connection on expiry
  },
);

Supported isolation levels: 'ReadCommitted', 'RepeatableRead', 'Serializable'.

Pipeline#

Run N independent queries in a single database round-trip using the PostgreSQL extended-query pipeline protocol.

const [user, postCount, recentPosts] = await db.pipeline([
  db.users.buildFindUnique({ where: { id: 1 } }),
  db.posts.buildCount({ where: { orgId: 1 } }),
  db.posts.buildFindMany({ where: { userId: 1 }, limit: 5 }),
]);

The build* methods return DeferredQuery objects, generated SQL + params + a transform, without executing. pipeline() sends every statement in one TCP flush (parse/bind/execute/sync) and runs each transform on the result.

Non-transactional pipelines#

By default, pipelines run inside a transaction: any failure rolls back the batch. Opt out for error-isolated execution:

try {
  await db.pipeline(queries, { transactional: false });
} catch (err) {
  if (err instanceof PipelineError) {
    // err.results is a per-query [{status:'ok', value} | {status:'error', error}] array
    // err.failedIndex / err.failedTag identify the first failure
  }
}

HTTP / serverless pools that don't support the pipeline protocol fall back to sequential execution automatically. Probe at runtime with db.pipelineSupported().

The full build* set#

Every query method has a build* twin that returns a DeferredQuery ({ sql, params, transform, tag }) instead of executing. There are fifteen, and the write builders batch too:

Pair them with the array form of $transaction when you want the batch to be atomic:

// One connection, one BEGIN/COMMIT, all-or-nothing
const [order, items, user] = await db.$transaction([
  db.orders.buildCreate({ data: { userId: 1, total: 4200 } }),
  db.orderItems.buildCreateMany({
    data: [
      { orderId: 1, sku: 'A' },
      { orderId: 1, sku: 'B' },
    ],
  }),
  db.users.buildUpdate({ where: { id: 1 }, data: { orderCount: { increment: 1 } } }),
]);

Choosing between the two: db.pipeline(...) is for independent queries, one round-trip, no transaction semantics. db.$transaction([...]) is a real transaction on one connection (sequential unless the driver advertises supportsPipelining) and rolls the whole batch back on the first failure.

Client configuration#

TurbineConfig is the object you pass to turbine() / new TurbineClient(). The connection fields (connectionString, host, port, database, user, password, ssl, pool) are covered in Quick Start and Serverless. The tuning knobs:

const db = turbine({
  connectionString: process.env.DATABASE_URL,
  poolSize: 10,
  idleTimeoutMs: 30_000,
  connectionTimeoutMs: 5_000,
  preparedStatements: true,
  sqlCache: true,
  sqlCacheSize: 1000,
  // Postgres only, opt-in. Unset by default, and Turbine then sends nothing.
  // planCacheMode: 'force_custom_plan',
});

Where a pg-style alias exists, the explicit Turbine field wins when both are set.

planCacheMode is a connection parameter, so it cannot say custom here, auto there. The read arg forceCustomPlan: true covers that case: on findMany / findUnique / findFirst / count / aggregate / groupBy it sends that single statement unnamed, so it is planned with the real values every execution. The per-query lever has the mechanics and refusal rules.

Unknown options warn#

Every key on the config object is checked against the config surface, and an unrecognized one logs a one-time warning with the nearest real option:

[turbine] Unknown option "logParams" in the config passed to TurbineClient, it is ignored. Did you mean "logQueryParams"?

The suggestion covers plain typos and a guess that leaves out a whole word (logParams names the same words as logQueryParams). It is a warning, never an error, so an app that passes an option from a newer Turbine keeps running; it fires once per key name per process and is silent under NODE_ENV=production. url and schema never warn, since turbine.config.* files carry both for the CLI.

Client escape hatches#

// Query a table that isn't in the generated types
type AuditRow = { id: number; action: string; at: Date };
const audit = db.table<AuditRow>('audit_log');
const recent = await audit.findMany({ orderBy: { at: 'desc' }, limit: 20 });
 
// Check the template cache is doing its job
await db.users.findUnique({ where: { id: 1 } });
await db.users.findUnique({ where: { id: 2 } });
db.users.cacheStats();
// Two calls of the same SHAPE: the first is a miss that stores the template,
// the second is a hit. Counters are per QueryInterface instance, and `db.users`
// returns the same instance every time.

Middleware#

db.$use(fn) registers a middleware that wraps every query. It runs after SQL generation, so it can observe what's about to execute (params.model, params.action, params.args), measure timing, and transform the result returned by next(), but it cannot change the query itself.

// Query timing
db.$use(async (params, next) => {
  const start = Date.now();
  const result = await next(params);
  console.log(`${params.model}.${params.action} took ${Date.now() - start}ms`);
  return result;
});
 
// Result transformation, redact a field on the way out
db.$use(async (params, next) => {
  const result = await next(params);
  if (params.model === 'users' && Array.isArray(result)) {
    for (const row of result as { email?: string }[]) row.email = '[redacted]';
  }
  return result;
});

Warning: params.args is a read-only snapshot, mutating it does not change the executed SQL. The query is fully built and parameterized before middleware runs.

Because middleware cannot rewrite queries, cross-cutting filters like soft deletes belong in a global filter, an explicit where, or a small scoped helper:

import type { WhereClause } from 'turbine-orm';
 
// Explicit filter
const users = await db.users.findMany({ where: { deletedAt: null } });
 
// Scoped helper that always applies the filter
const activeUsers = (where: WhereClause<User> = {}) =>
  db.users.findMany({ where: { ...where, deletedAt: null } });
 
const rows = await activeUsers({ orgId: 1 });

explain#

Every table accessor has explain(args): it compiles the exact statement findMany(args) would run, executes it through the engine's plan explainer, and returns the plan as string[] lines. Use it to verify the emitted query hits the index you expect.

const plan = await db.posts.explain({
  where: { orgId: 7, isPublished: true },
  orderBy: { createdAt: 'desc' },
});
// PostgreSQL: ['Sort  (cost=…)', '  ->  Index Scan using posts_org_id_idx on posts …', …]

Engine mapping: PostgreSQL (and CockroachDB / YugabyteDB) use EXPLAIN, SQLite uses EXPLAIN QUERY PLAN, MySQL uses EXPLAIN, and PowDB uses its native explain (which since PowDB 0.14 shows the lowered, executed plan, with selectivity estimates on 0.15+). SQL Server has no in-band explain and throws a typed UnsupportedFeatureError (TURBINE_E017).

Plan text is engine-owned diagnostic output (match on node names, never on exact layout), and middleware does not run for explain.

Raw SQL#

When you need something the query builder doesn't expose (window functions, WITH RECURSIVE, lateral joins, etc.):

const stats = await db.raw<{ day: Date; count: number }>`
  SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*)::int AS count
  FROM posts WHERE org_id = ${orgId}
  GROUP BY day ORDER BY day
`;

Parameters from ${} interpolations become $1, $2, ..., never string-interpolated into the SQL text.

Typed raw SQL, db.sql<T>#

db.sql<T> is the typed escape hatch: you supply the row shape and get a thenable query with .one() and .scalar() helpers. Like db.raw, every ${value} is bound as a $N parameter.

// Awaiting the query returns T[]
const users = await db.sql<{ id: number; name: string }>`
  SELECT id, name FROM users WHERE org_id = ${orgId}
`;

.one() returns the first row or null:

const user = await db.sql<{ id: number; name: string }>`
  SELECT id, name FROM users WHERE id = ${42}
`.one();
// user is { id: number; name: string } | null

.scalar() returns the first column of the first row, or null. Pass a type argument to override the inferred value type:

const total = await db.sql<{ count: number }>`
  SELECT COUNT(*)::int AS count FROM users
`.scalar();
// total is number | null
 
const name = await db.sql<{ name: string }>`
  SELECT name FROM users LIMIT 1
`.scalar<string>();

See also#

• Typed Errors, every error code and the retry patterns.
• Schema & Migrations, code-first schemas and SQL migrations.
• Benchmarks, the numbers behind the query planner.