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 },
    },
  },
});

The entire object graph resolves in one SQL statement under relationLoadStrategy: 'join', and users[0].posts[0].comments[0] is fully typed either way. On the 'auto' default (since 0.41) Turbine may move an individual relation to a follow-up statement when the join plan would be the slower one, most often an unindexed correlation column or a to-one relation over an unbounded parent set. The rows are identical whichever plan runs; 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 one-time warning. Silence it for a query you know is bounded by passing warnOnUnlimited: false on the call:

// 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), leaving the warning on everywhere else. 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 through a table with no orderBy can hand you the same row twice, or skip one entirely. It is usually the slower plan too: an unordered LIMIT can discard heap rows that an index scan on the key would have skipped.

So a findMany that paginates (limit / take / offset) with no orderBy 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 });

cursor and distinct queries are exempt (both already impose their own ordering semantics). The warning is gated exactly like warnOnUnlimited: per-call, then per-table, then the global boolean, deduped process-wide per table and shape, and 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.

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

It is off by default in core, deliberately: turning it on adds an ORDER BY to SQL your application already emits, which changes both the rows a given page returns and the plan the engine picks. With it off, the emitted SQL is byte-identical to before. The prisma-compat adapter applies the same ordering unconditionally, because that is what Prisma does and reproducing Prisma is that layer's contract.

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), since 0.65 enforced rather than implied. Before that, the omit half was silently ignored next to a select on the SQL engines, without even validating its names. 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. Before 0.64 a relation-level typo was silently filtered out instead, so select returned {} rows and omit returned the column it was asked to hide; see the errors page for the relation-name case, which has its own message.

PII fields: includePii#

Columns tagged pii: true in defineSchema are excluded from every default projection: the 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 (since 0.63). A row that cannot address itself is a correctness hazard, not a privacy win: with part of its key missing, feeding that 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 UNSAFE symbol is the only value that enables it, and a literal includePii: true throws a ValidationError (TURBINE_E003). The reason is that includePii sits on the same options object as where, so a handler written as db.users.findMany({ ...req.body }) used to let a request body ask for the PII columns; JSON.parse cannot produce a symbol. Read Privilege options for the full rationale and the upgrade table.

includePii is a read option (findMany / findUnique / findFirst and their *OrThrow forms, findManyStream, and the gated groupBy / aggregate shapes below); it is never a write option. Schemas with no PII-tagged columns are unaffected: the emitted SQL is byte-identical either way.

The line the flag draws is whether a stored PII value can reach your application. where, orderBy, and having on a PII column are therefore always allowed, with or without the flag: they narrow, sort, or filter rows and return no PII value of their own.

Aggregates are where this needs care, because some of them return stored cells. Two shapes 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. (A group of exactly one row is a known theoretical edge; the gate is deliberately narrow rather than blanket.)

// 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 what an untagged schema would produce. The whole policy applies on PowDB as well as the SQL engines, and it only affects schemas that opted into pii: true: untagged schemas are completely unaffected.

Writes enforce the same boundary at the SQL level. A write against a table with PII columns (create, createMany, update, delete, upsert, and nested writes) returns an explicit non-PII projection instead of RETURNING *: RETURNING "id", "name", ... on Postgres and SQLite, the follow-up SELECT on MySQL, and OUTPUT INSERTED.… / OUTPUT DELETED.… on SQL Server all list only the non-PII columns. You may still write PII values freely (they are persisted); they simply never cross the wire back to your application unrequested. A PII-tagged primary key is kept in the projection anyway, so the returned row stays addressable. Tables with no PII columns keep RETURNING * byte-for-byte. (PowDB is the one exception: its returning keyword accepts no column list, so PII is stripped client-side from the returned row there; the upsert path reselects by PK through the non-PII read projection.)

Pagination, limit / take, offset, cursor#

Offset pagination uses limit + offset. take is a Prisma-compatible alias for limit, when both are passed, take wins (take ?? limit). 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 (fixed in 0.65; it was a driver syntax error before).

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 adds a comparison per cursor field, column > value for ascending order, column < value for descending (taken from orderBy; defaults to ascending). 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,
});

Multiple cursor fields combine with AND. Use a unique (or unique-in-combination) column in cursor + orderBy so the ordering is total. That's what makes keyset pagination skip-free and index-friendly at any depth, where OFFSET 100000 still scans the skipped rows.

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' },
});

Per DISTINCT ON semantics, which row survives per group is governed by orderBy, 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. The array form works everywhere an orderBy is accepted: findMany, a with relation, and groupBy.

{ sort, nulls } applies everywhere an orderBy is compiled, top-level queries, groupBy, and the inner subquery of a with relation.

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' } },
});

Since v0.51 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, so an N-hop chain still costs one subquery with one LIMIT 1, not N levels of nesting. Every hop applies its target's global filter to the join condition, so ordering never keys off a soft-deleted or other-tenant row.

A to-many hop mid-chain is refused rather than silently picking an arbitrary row: 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).

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: {} });

This is easy to reach honestly: a handler that assembles its payload from optional request fields produces {} when the request supplied none of them. Turbine inserts the defaults row rather than failing, which is what it used to do (a 42601 syntax error from INSERT INTO t () VALUES ()).

A table with no usable defaults still fails, correctly, with the database's own NOT NULL violation (NotNullViolationError, TURBINE_E010). Nothing pre-empts that.

Field names and column names resolve identically#

A key in data, where, orderBy, or select may be spelled as the field name (lastRun) or as the underlying column name (last_run). Both spellings have always compiled to the same SQL, and since v0.53 both are guaranteed to be processed the same way as well:

// 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() } });

That guarantee used to hold for the statement only. The SQL builders accepted both spellings, but the per-column value pass resolved keys through the field map alone, so a column-name key skipped it: { last_run: date } produced correct SQL carrying an unprocessed value. On a zone-less date / timestamp / time column that meant the UTC rewrite never ran and the process's local calendar fields were stored, with no error anywhere. Both resolvers are now the same function.

The column spelling is the natural one on an introspected schema, so if you have been writing it, check any zone-less temporal column written that way before upgrading; the value a fresh write stores changes to the correct one.

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 a single row by unique key, or every row matching a where.

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: true throws (TURBINE_E003), because on an options object built from a request body a plain boolean turns "delete the rows I named" into "delete the table". The option is typed Unsafe, so a boolean does not compile at all, false included; write a conditional call site as { where, ...(flag ? { allowFullTableScan: UNSAFE } : {}) }. (false / null / undefined are still accepted at runtime and mean "guard on", which keeps untyped callers working.) The same applies to updateMany.

upsert#

Insert a row or update it if a row matching the where clause already exists. Uses PostgreSQL's INSERT ... ON CONFLICT ... DO UPDATE under the hood, atomic, no race conditions.

The where keys determine the conflict target (must be unique or primary key columns). If no matching row exists, create is inserted. If a row matches, update is applied.

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 return stored values, so they require includePii: UNSAFE and otherwise throw a ValidationError (TURBINE_E003). _count needs no opt-in (it is a count, not a value), and neither does where. See PII fields.

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 } }, ...]

Grouping by a PII-tagged column returns its stored values as the group keys, so that also requires includePii: UNSAFE and otherwise throws TURBINE_E003. The same holds for _min / _max over a PII column, including JSON-path targets. where / orderBy / having on PII columns stay unrestricted, because none of them return the value. See PII fields.

The result row is fully typed from the args (as in Prisma and Drizzle), no as const needed. Each by field carries its entity field type, _count is a number, _sum / _avg fields are number | null (an aggregate over zero matching rows is null), and _min / _max fields carry the field's own type. The call above infers:

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

Grouping by a JSON path yields a runtime alias that can't be typed, so those columns are left off the row type (cast the result when grouping by a JSON path).

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 (new in v0.32). 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, new in v0.32):

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. Those two return a stored cell, so comparing one against the column's own type is as valid as comparing a sum against a number:

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 (new in v0.53)

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 is what you want for 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, so the two clauses read the same way.

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 Turbine issues a speculative LIMIT batchSize + 1 first fetch, if the result fits in one batch, rows are yielded directly without DECLARE CURSOR overhead. Larger result sets 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#

A typo used to be silently accepted and silently ignored, which returned the wrong rows with no error anywhere:

// Before 0.50: compiled, matched every user, returned the whole table.
// From 0.50: 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. It reuses the relation brand your generated client already emits, so an existing generated client picks this up on upgrade. It is also purely a type-level change: the SQL is byte-identical.

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#

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, new in v0.30) 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 (new in v0.51) 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 (@>) and means something entirely different. 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 (new in v0.31). 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 (new in v0.31). 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, so ordering behaves the same across 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 (new in v0.32). 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 (for example, a flagged row). 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, in this release.

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 significantly faster on large parent sets where the ordering subquery dominates the query 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, since the faster plan depends on your data and indexes). The lateral plan is PostgreSQL only: it throws on the other engines rather than falling back silently. Set it per ordering entry, so a query with two picks can use a lateral for the large relation and the default subquery for a small, indexed one.

Relation filters#

Filter parent rows by predicates on their child rows.

Any operator above, including JSON filters, composes inside some / every / none. As of v0.30, a JSON filter nested in a relation filter (or in a with relation's where) compiles correctly; earlier versions silently degraded it to a broken equality that matched nothing.

// 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 } },
}

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

There are two plans, and the load strategy picks between them. Under relationLoadStrategy: 'join' each counted relation is an inline correlated COUNT(*) scalar subquery in the same statement, re-evaluated once per parent row. Under the 'auto' default, a count whose correlation column has no covering index moves to one grouped COUNT(*) ... GROUP BY fk follow-up statement from two parent rows upward, because the inline form would otherwise scan the child table once per parent. Only a query bounded at a single row (findUnique, findFirst, limit: 1) keeps an unindexed count 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 of them, and they are not read-only: the write builders batch too, which is the part most people miss.

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 the extended-query protocol and is for independent queries where you want one round-trip and 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. These are the tuning knobs that are easy to miss.

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 one case: on findMany / findUnique / findFirst / count / aggregate / groupBy it sends that single statement unnamed, so the driver re-parses it every execution and it is always planned with the real values. It is refused with ValidationError on a client pinned to 'force_generic_plan', which governs unnamed statements too, and throws UnsupportedFeatureError (TURBINE_E017) on a non-Postgres engine. See the per-query lever.

Unknown options warn (new in v0.53)#

A JavaScript object has no schema, so a key that is not a config field used to be silently ignored. That makes a wrong guess indistinguishable from a broken feature: you set logParams, nothing happens, and you conclude query parameters cannot be logged rather than that the option is spelled logQueryParams.

Every key on the config object is now 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 the case a typo check alone misses: a guess that leaves out a whole word (logParams is five edits from logQueryParams, but it names the same words in the same order).

It is deliberately a warning, never an error, so an app compiled against a newer Turbine that passes an option this version has not heard of keeps running. It fires once per key name per process, is silent under NODE_ENV=production, and the whole check is wrapped so that no config object can turn a diagnostic into a failed constructor. url and schema never warn: turbine.config.* files carry both for the CLI, and that object is routinely spread into the client factory.

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 can't rewrite queries, cross-cutting filters like soft deletes belong in the query itself, either explicitly or via 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) (since v0.35): 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 that the query the ORM actually emits hits the index you expect, no dropping to raw needed.

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).

Two caveats: plan text is engine-owned diagnostic output, match on node names if you must parse it, never on exact layout, and middleware does not run for explain (the rows are plan text, not entities).

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, injection is impossible even with hostile input. Unlike db.raw, you choose the result shape and get the convenience helpers.

// 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>();

Reach for db.sql<T> when you want a hand-written query with a known return type and the .one() / .scalar() helpers; use db.raw when you don't need either.

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.