Reading and writing

A generated client has one accessor per table, db.<table>, with the methods on this page. The examples use db.users and db.posts.

Reading#

findMany#

Returns every row matching the query.

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

A findMany with no limit, take or cursor logs unlimited-find-many once per table, in production too. A where that pins a unique key, or a client defaultLimit, skips the warning.

// A tiny lookup table: no warning wanted
const roles = await db.roles.findMany({ warnOnUnlimited: false });

On the client, set warnOnUnlimited: false, or a per-table map such as { userProfiles: false }. Per call beats per table, which beats global.

findUnique / findUniqueOrThrow#

Returns the row a unique key identifies, or null. findUniqueOrThrow throws NotFoundError (E001) instead.

const user = await db.users.findUnique({ where: { id: 42 }, with: { posts: true } });
 
// Composite key: name every column
const row = await db.memberships.findUnique({ where: { userId: 1, orgId: 2 } });
 
// E003: an insensitive match can hit "Ada@x.com" and "ada@x.com". Use findFirst.
await db.users.findUnique({ where: { email: { equals: input, mode: 'insensitive' } } });

where must name the primary key, a unique column, every column of a compound unique, or a compound-unique selector. Each key takes a plain value or { equals: value }, and extra predicates are fine.

A key compared with null, in, a range, mode or a column reference does not identify a row. A where with no identifying key throws ValidationError (E003) naming the keys that work. update, delete and upsert follow the same rule.

findFirst / findFirstOrThrow#

Returns the first row in orderBy order, or null. findFirstOrThrow throws NotFoundError instead.

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

A where whose every key is undefined throws E003, so a missing token cannot match an arbitrary row. Omit where, or pass {}, to ask for any row.

Selecting fields#

select / omit#

const users = await db.users.findMany({ select: { id: true, email: true } });
const safe = await db.users.findMany({ omit: { passwordHash: true } });

At any level, these throw E003: select and omit both naming fields; a select naming no field; a name that is not a column. A relation name gets its own message pointing at with.

Keys may use the field name (lastRun) or the column name (last_run), and relations their declared name (blogPosts) or table spelling (blog_posts). Both compile to the same SQL, and results use the field and declared names.

PII fields: includePii#

Columns tagged pii: true in defineSchema are left out of every default projection, write results included. A read returns them, at every with level, with includePii: UNSAFE.

import { UNSAFE } from 'turbine-orm';
 
const u = await db.users.findFirst({ where: { id: 1 } });
u.email; // undefined: email is tagged pii
 
// E003: select alone is not an opt-in
await db.users.findFirst({ where: { id: 1 }, select: { email: true } });
 
await db.users.findFirst({ where: { id: 1 }, select: { email: true }, includePii: UNSAFE });

• Reads, streams, groupBy and aggregate take it; writes do not.
• where, orderBy and having on a PII column need no opt-in: they return no value.
• A PII column in the primary key is always returned.
• Grouping by a PII column, or _min, _max or _percentile over one, needs the opt-in. _count, _sum and _avg do not.
• A write select naming a PII column throws E003. Read the row back with includePii: UNSAFE.

Relations in reads#

with loads relations into each row. Each takes its own where, orderBy, limit, offset, select, omit and with:

const users = await db.users.findMany({
  with: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      limit: 5, // per user
      with: { comments: true },
    },
  },
});
users[0].posts[0].comments[0]; // fully typed

Nesting stops at 10 levels (E007). Relations covers the options and load strategies.

Counting related rows, _count#

const users = await db.users.findMany({ with: { _count: true } });
users[0]._count; // { posts: 12, orgs: 3 }
 
const authors = await db.users.findMany({ with: { posts: true, _count: { posts: true } } });
authors[0]._count.posts; // 12

It counts hasMany and manyToMany relations; a to-one relation throws E003. Under 'auto', a count over an unindexed foreign key runs as one grouped follow-up query, with the same numbers.

Row locks#

findMany, findFirst, findUnique and their *OrThrow forms take lock, held until the transaction ends:

await db.$transaction(async (tx) => {
  const job = await tx.jobs.findFirst({
    where: { status: 'queued' },
    orderBy: { id: 'asc' },
    lock: { mode: 'update', skipLocked: true }, // FOR UPDATE OF "jobs" SKIP LOCKED
  });
  if (job) await tx.jobs.update({ where: { id: job.id }, data: { status: 'running' } });
});

• Only the queried table is locked, not rows loaded by with.
• Outside a transaction, on a stream, or with distinct, it throws E003.
• MySQL takes 'update' and 'share'; SQLite, SQL Server and PowDB throw E017.

A lock that cannot be taken throws LockNotAvailableError, which is retryable. On PostgreSQL it aborts the transaction, so try uncertain locks in a nested block:

import { LockNotAvailableError } from 'turbine-orm';
 
await db.$transaction(async (tx) => {
  try {
    await tx.$transaction((inner) =>
      inner.accounts.findUnique({ where: { id }, lock: { mode: 'update', noWait: true } }),
    );
  } catch (err) {
    if (!(err instanceof LockNotAvailableError)) throw err;
    // Only the savepoint rolled back.
  }
});

Writing#

create#

Inserts one row and returns it, generated columns included.

const user = await db.users.create({ data: { email: 'alice@example.com', name: 'Alice', orgId: 1 } });
 
const event = await db.events.create({ data: {} }); // INSERT ... DEFAULT VALUES

Takes data, select / omit and timeout.

Returning fewer columns#

create, createMany, update, delete and upsert take select / omit with the read rules, checked before anything is written. Only the chosen columns cross the wire.

const { id } = await db.events.create({ data: { kind: 'click', payload }, select: { id: true } });

Writing JSON columns#

import { jsonValue } from 'turbine-orm';
 
await db.docs.create({ data: { data: jsonValue('hello') } }); // the JSON string "hello"

On PostgreSQL, an object or array written to a built-in scalar column, such as text, a number or a date, throws E003. Pass JSON.stringify(value) to store it as text.

createMany#

Inserts many rows in one statement and returns them. A plain MySQL createMany returns [] (Engines).

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

• A large batch is split into chunks sized to the engine's parameter and row limits, inside one transaction, so all rows land or none. Inside $transaction, one failed chunk rolls back every chunk, even if you catch the error.
• An oversized batch throws E017 on a pool without connect(), such as an HTTP driver. buildCreateMany is one statement, so it throws E003.
• Several all-defaults rows throw E017 on SQLite and SQL Server; call create({ data: {} }) per row.

Counting instead of returning rows: returning: false#

const { count } = await db.events.createMany({ data: batch, returning: false });

A plain insert then sends no RETURNING. count is rows inserted or updated, never skipped ones. select or omit beside it throws E003; MySQL refuses it with skipDuplicates (E017).

To fill several tables together, pipeline one buildCreateMany per table. They share a transaction, but none sees ids another generates:

const [orders, lines] = await db.pipeline(
  db.orders.buildCreateMany({ data: orderRows, returning: false }),
  db.orderLines.buildCreateMany({ data: lineRows, returning: false }),
);

Multi-row upsert: onConflict#

import { col, expr } from 'turbine-orm';
 
await db.users.createMany({
  data: [{ email: 'a@b.com', name: 'A' }, { email: 'b@b.com', name: 'B' }],
  onConflict: { target: ['email'], update: ['name'] },
});
// INSERT ... ON CONFLICT ("email") DO UPDATE SET "name" = EXCLUDED."name" RETURNING *
 
await db.pageViews.createMany({
  data: batch,
  onConflict: {
    target: ['path'],
    update: { views: expr.add(col('views'), expr.excluded('views')) },
    where: { archived: false },
  },
});

Two rows with one key, even keys equal only under the column's collation, throw E003 and write nothing. Global filters apply to the update, updatedAt is stamped unless update names it, and rows return in no set order.

update / updateMany#

update changes one row by unique key and returns it. updateMany changes every match and returns { count }.

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

updatedAt columns are stamped unless named. limit, take, offset and skip on updateMany or deleteMany throw E003; select the ids first:

const batch = await db.jobs.findMany({ where: { status: 'queued' }, select: { id: true }, orderBy: { id: 'asc' }, limit: 100 });
await db.jobs.updateMany({ where: { id: { in: batch.map((j) => j.id) } }, data: { status: 'running' } });

Atomic update operators#

data: {
  viewCount: { increment: 1 }, // "view_count" = "view_count" + $1
  likesCount: { decrement: 1 },
  score: { multiply: 2 },
  rank: { divide: 2 },          // truncates on an integer column
  title: { set: 'New title' },
}

Concurrent updates cannot lose writes. On a JSON column only set is an operator; in create data an operator object is a plain value.

Update expressions: expr#

Build a value from the row with expr and col(). It runs inside the UPDATE:

import { col, expr } from 'turbine-orm';
 
await db.posts.update({
  where: { id: 1 },
  data: {
    viewCount: expr.add(col('viewCount'), 1),
    peakViews: expr.greatest(col('peakViews'), col('viewCount')),
    tier: expr.case([{ when: { viewCount: { gt: 1000 } }, then: 'hot' }], 'cold'),
    seenAt: expr.now(),
  },
});

• Operands are values, col() references or expressions, so they nest. A non-integer result into an integer column throws E003.
• Expressions throw E003 in create and createMany data, inside { set }, and on JSON or array columns. PowDB and prisma-compat throw E017.
• Only expr builds one: a look-alike object is a value, so request data cannot become SQL.
• If your generated update types reject expr, regenerate the client. Lint rules banning a then property flag case arms.

upsert#

Updates the row where names, or inserts create, and returns the row. where must identify one row, as in findUnique.

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

When every where key has the same value in create, it is one atomic INSERT ... ON CONFLICT. Otherwise Turbine looks the row up, then updates or inserts, in one transaction. These take the lookup:

• A where value that create lacks or contradicts.
• update: {}, or nested writes.
• A global filter on MySQL or SQL Server.
• On MySQL, a create that sets another unique key.

Like Prisma's, the lookup can race a concurrent insert, which then fails with UniqueConstraintError (E008). A batched buildUpsert refuses these shapes.

delete / deleteMany#

delete removes one row by unique key and returns it. deleteMany returns { count }.

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

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

allowFullTableScan: true throws; write a conditional as ...(flag ? { allowFullTableScan: UNSAFE } : {}). A list that may be empty (notIn: ids) needs that decision. An always-false where is allowed and matches nothing.

Streaming#

findManyStream#

Yields rows through an async iterator. On PostgreSQL it reads a server-side cursor in constant memory. SQLite, MySQL and SQL Server load the result, then yield it in batches.

for await (const user of db.users.findManyStream({ where: { orgId: 1 }, batchSize: 1000, with: { posts: true } })) {
  process.stdout.write(`${user.email}\n`);
}
 
// One array per fetch, without a promise per row
for await (const batch of db.users.findManyStreamBatches({ where: { orgId: 1 }, batchSize: 500 })) {
  await sink.writeAll(batch);
}

Both take findMany's options except lock, plus batchSize (default 1000) and idleTimeoutMs. A result that fits one batch skips the cursor. Breaking out releases the connection.

Abandoned streams close themselves#

A stream holding a cursor closes after five minutes without a pull. It logs stream-idle-timeout, and the next pull throws TimeoutError (E002).

// Per call: a consumer that pauses for up to 30 minutes
for await (const row of db.events.findManyStream({ idleTimeoutMs: 30 * 60_000 })) {
  await slowSink.write(row);
}
 
// Per client. 0 disables it.
const db = turbine({ connectionString: process.env.DATABASE_URL, streamIdleTimeoutMs: 0 });

A stream inside $transaction is bounded by the transaction's timeout. On PostgreSQL the server also ends the stream's transaction if the process freezes.

See also#

• Filtering and sorting: where operators, ordering and pagination.
• Relations: with, counts and load strategies.
• Nested writes: create and update related rows in one call.
• Transactions: savepoints, isolation and timeouts.