Read and write data

Import the generated db in a server route, action, job or script. A query handler uses the same read builders through ctx.db; a mutation can also write. The examples extend the tasks table from the quick start.

import { db } from '../db/_generated/server'

const tasks = await db
  .select('tasks')
  .where({ completed: false })
  .orderBy('title')
  .limit(20)

The result is an array of plain rows with IDs. Builders are lazy: the operation executes when awaited. Awaiting the same builder again reuses its result; create another builder for another read.

Use an object for equality, or put the field, operator and value in order:

const unfinished = await db.select('tasks').where({ completed: false })
const matches = await db.select('tasks').where('title', 'contains', 'guide')

Repeated where calls combine with AND. Use the callback helpers for an explicit compound expression:

const tasks = await db
  .select('tasks')
  .where({ completed: false })
  .where(({ or, startsWith }) =>
    or(startsWith('title', 'Write'), startsWith('title', 'Review'))
  )

Text matching is case-sensitive by default. Use { caseSensitive: false } with literal helpers when needed:

.where('title', 'contains', 'guide', { caseSensitive: false })

contains, startsWith, and endsWith match literal text. like and ilike interpret SQL % and _ wildcards. Negate in the operator position, as in .where('title', 'not contains', 'draft'), or use callback not(...).

Equality with null becomes IS NULL; inequality becomes IS NOT NULL. Undefined filter values throw instead of silently dropping a condition.

Use generated references when you want table-based field access:

import { tasks } from '../db/_generated/tables'

const rows = await db.select(tasks).where(tasks.completed, '=', false)

Both forms are typed. Field-name strings are relative to the selected table: use title, not tasks.title. Generated references come from _generated/tables, not the authored schema declaration. Strings in the value position are literals; a generated field reference there compares compatible columns of the same table.

Pass an array or inclusion map as the second argument to select:

const titles = await db.select('tasks', ['id', 'title']).limit(20)
const titlesAgain = await db
  .select('tasks', { id: true, title: true })
  .limit(20)

You can filter and sort by unselected fields. Conditional inclusion produces optional result properties. An empty effective selection is an error.

limit(1) returns an array with at most one row. unique() returns one row or null and rejects multiple matches. paginate({ limit, cursor }) returns a page. These are terminal alternatives; do not combine them. See live pagination for browser lists and stable ranges.

const task = await db.insert('tasks', { title: 'Write the guide' })
const ids = await db
  .insert('tasks', [
    { title: 'Write the guide' },
    { title: 'Review the guide' },
  ])
  .returning(['id'])

A single insert returns one row. Bulk inserts return an array. Defaults and generated IDs come from the schema. returning narrows the result with the same array or inclusion-map syntax as select.

Updates take their patch as the second argument. Both operations require where:

const completed = await db
  .update('tasks', { completed: true })
  .where({ title: 'Write the guide' })
  .returning(['id', 'completed'])

const deleted = await db.delete('tasks').where({ completed: true })

Both return arrays of rows actually changed; no match returns []. An update that leaves a stored row unchanged does not invent a changed-row result. Add .expectRows(1) when a missing row should fail before commit. A synchronous predicate such as .expectRows(count => count >= 1 && count <= 10) checks a range. The expectation checks the write; it does not limit how many rows it attempts to change.

An explicit .where(true) permits a whole-table operation. Undefined patch values leave known fields unchanged; null requires a nullable field. Unknown properties throw. JSON fields are replaced as complete values, without implicit deep patches.

Separate direct operations commit independently. Group dependent changes in one transaction and use the provided tx throughout:

await db.transaction(async (tx) => {
  await tx.insert('tasks', { title: 'Write the guide' })
  await tx.insert('tasks', { title: 'Review the guide' })
})

If a write or the callback fails, the group rolls back. Calling the outer db inside this callback throws; it cannot accidentally start a separate transaction. Await every operation before returning.

Direct transaction callbacks run once by default. Opt into conflict retries with { retries: 3 } only when the whole callback is safe to replay. Keep external effects outside it. A declared mutation already has its transaction and bounded conflict retries, so use its ctx.db without opening another transaction.

An onConflict policy targets a real primary or unique key declared in the schema. For example, configure uniqueness on the task title in your schema:

export const tasks = defineTable(
  { title: v.string(), completed: v.boolean().default(false) },
  { unique: { task_title: { fields: ['title'] } } }
)

Keep this table registered in defineSchema, regenerate, and explicitly provision the matching storage. Then an insert can skip an existing title:

const task = await db
  .insert('tasks', { title: 'Write the guide' })
  .onConflict(({ on }) => on('title').skip())

The result is the inserted row or null for a skip. To write the incoming completion state into an existing task instead:

const task = await db
  .insert('tasks', { title: 'Write the guide', completed: true })
  .onConflict(({ on }) =>
    on('title').update(({ incoming }) => ({ completed: incoming.completed }))
  )

The callback can also read current fields. A composite unique constraint needs all its fields in on(...). The policy never bypasses other database constraints. Bulk results contain only written rows and have no promised correspondence to input positions.

For a counter that several callers can update, use a numeric column and a write expression instead of reading a value into JavaScript and writing it back:

export const counts = defineTable(
  { name: v.string(), value: v.int64().default(0) },
  { primaryKey: 'name' }
)

Register counts in defineSchema, regenerate and provision it. Insert a named counter once, then update that row with an expression:

await db.insert('counts', { name: 'visits' })
await db
  .update('counts', (current) => ({ value: current.value.add(1) }))
  .where({ name: 'visits' })
  .expectRows(1)

The addition runs in PostgreSQL against the stored value, avoiding a separate application read/replace race. The callback constructs an expression; it is not called once per fetched row. Column constraints still apply. For a codec, SQL arithmetic follows its storage type, as explained in data types and codecs.

Named aggregates return a compact result instead of loading every row:

const total = await db
  .select('tasks')
  .where({ completed: false })
  .aggregate(({ count }) => ({ unfinished: count() }))

Use groupBy before aggregate for one summary per group:

const totals = await db
  .select('tasks')
  .groupBy('completed')
  .aggregate(({ count }) => ({ total: count() }))
  .having(({ gte }) => gte('total', 2))
  .orderBy('total', 'desc')

where filters rows; having filters aggregate groups and is available only after groupBy(...).aggregate(...). Numeric fields support sum and avg; orderable fields support min and max. Null values are skipped; empty sums/averages/extrema return null, while count() returns zero. Grouped pagination is not supported. Representation details are in data types and codecs.

When this code runs in your application server, enforce authorization before the operation. A live browser declaration needs its own guard; see queries and mutations and authentication.