Query builder

ctx.store is the framework's typed data layer. It's available on every executor's AppContext. Reads are dependency-tracked for subscriptions; writes fire CDC events that invalidate subscribers.

This page covers selects + filters + aggregates. For relations see Joins; for writes see Transactions.

#Select

For declared timestamp() and date() columns, temporal predicate values accept Date, epoch milliseconds and ISO text. Millisecond instants are normalized to Date; strings with more than three fractional digits retain their original precision for the database comparison. Guarded migration rewrites use this same binding path, so an imported microsecond timestamp is compared without rounding its guard to milliseconds.

ctx.store.select('notes')                  // SELECT * FROM notes
  .where('tenantId', tenantId)             // WHERE tenantId = $1
  .orderBy('createdAt', 'desc')
  .limit(20)
  .all()                                   // → ReadonlyArray<Note>

Terminal operations:

Method	Returns	When to use
.all()	ReadonlyArray<T>	Multi-row result.
.one()	T (throws if missing)	"I know this exists" — primary-key lookups.
.maybeOne()	T | null	Lookup that may fail (login by email).
.first()	T | null	First row; equivalent to .limit(1).maybeOne().
.count()	number	Counts.
.exists()	boolean	EXISTS check; cheap.

Column projection — pick only the fields you need:

ctx.store.select('notes').select('id', 'title')   // SELECT id, title

The return type narrows automatically — { id: string; title: string }[].

#Single-row terminals on the typed builder

ctx.store.query(...) takes the typed database.<table> builder and returns typed rows. ctx.store.one / .first / .maybeOne are the single-row terminals of that same path — so "I want one row" and "I want typed rows" compose, instead of forcing you to pick one:

import { eq } from '@voltro/database'

// ONE row, typed. No cast, no explicit type argument — the row type
// rides in on the builder.
const user = await ctx.store.one(database.users.where(eq('id', id)))
user.email        // string — not `row['email'] as string`

They accept the builder itself or its .descriptor, so a call site never reaches for .descriptor just to use a terminal:

await ctx.store.one(database.users.where(eq('id', id)))
await ctx.store.one(database.users.where(eq('id', id)).descriptor)   // same thing

Terminal	Returns	On zero rows	On 2+ rows
.one(query, onMissing?)	R	throws NoRowFound — or what onMissing returns	throws NoRowFound — or what onMissing returns
.first(query)	R | null	null	returns the first
.maybeOne(query)	R | null	null	returns the first

one() probes with LIMIT 2, not LIMIT 1. A one-row probe cannot tell "the row you meant" from "the first of several", so a filter that silently stopped being unique would keep handing back an arbitrary row. One extra row buys a loud failure at the moment the assumption breaks.

onMissing turns the refusal into your typed error. It receives the same { table, found } that NoRowFound carries, and whatever it returns is thrown instead — so a contract that already declares NotFoundError({ resource, id }) keeps declaring it, without a maybeOne + if (row === null) hop at every read:

const user = await ctx.store.one(
  database.users.where(eq('id', id)),
  () => new NotFoundError({ resource: 'user', id }),
)

The same argument exists on the string-keyed builder: ctx.store.select('users').where('id', id).one(onMissing).

Scoping is identical to ctx.store.query() — the tenant filter and the soft-delete filter both apply — because the terminals are query() underneath rather than a second read path. (A second scoping path is exactly the drift that makes one of them quietly leak across tenants.)

NoRowFound is typed, so the hand-written not-found branch goes away:

// instead of: const rows = await ctx.store.query(...); if (!rows[0]) throw new NotFound()
const note = await ctx.store.one(database.notes.where(eq('id', input.id)))

voltro doctor flags the hand-written version — see the hand-roll detector.

#where

Filters chain (AND-merged):

ctx.store.select('notes')
  .where('authorId', myId)
  .where('createdAt', '>', cutoff)
  .where('archived', false)

#Operators

.where('col', value)                       // =  (default)
.where('col', '=',  value)
.where('col', '!=', value)                 // also '<>'
.where('col', '<',  value)
.where('col', '<=', value)
.where('col', '>',  value)
.where('col', '>=', value)
.where('col', 'in',  [a, b, c])
.where('col', 'contains',   'needle')      // case-INsensitive substring (ILIKE '%…%')
.where('col', 'startsWith', 'acme_')        // case-SENSITIVE prefix (LIKE 'acme\_%')
.where('col', 'fts', 'query string')       // full-text fallback (LIKE-based here)

contains folds case because it is a search primitive — a human typing into a box means hello to find Hello. startsWith does not, because a prefix is a namespace: acme_ and ACME_ are two different key spaces, and quietly merging them is a bug. startsWith is also the only one of the two a database can answer from an index — LIKE 'literal%' is a btree range scan, %…% is not. % and _ inside either value are escaped, so they match literally.

There is no 'like'. It used to be here, and it was a lie: it mapped to contains, so .where('path', 'like', '/api/%') matched only rows literally containing the characters /api/% and the wildcard you wrote did nothing. An operator named after SQL's must honour your wildcards or not exist.

Writing an in-memory store for tests? Do not reimplement these. evaluatePredicate is exported from @voltro/database and is the same function the memory store runs, so a test double built on it agrees with a real database by construction. Hand-rolling the switch is where a stub quietly diverges — a case 'contains' written as String(a).includes(b) is case-SENSITIVE where the real one folds case, and coerces a number where the real one returns false. That stub passes queries a live dialect fails, which is the worst direction for a test double to be wrong in.

These are the only operators the ergonomic .where(col, op, value) form accepts. For IS NULL / NOT IN / IS NOT NULL, pass a predicate built with the @voltro/database helpers:

import { isNull, isNotNull, notInSet } from '@voltro/database'

ctx.store.select('notes').where(isNull('deletedAt'))
ctx.store.select('notes').where(isNotNull('publishedAt'))
ctx.store.select('notes').where(notInSet('status', ['archived', 'spam']))

For index-backed full-text search use the .matching('indexName', 'query') builder (see Full-text search); the 'fts' operator above is a plain substring fallback.

#OR / NOT

The predicate helpers compose into the single-argument .where(predicate) form:

import { or, not, and, eq, contains } from '@voltro/database'

ctx.store.select('notes').where(or(
  eq('authorId', myId),
  contains('sharedWith', myId),
))

ctx.store.select('notes').where(not(eq('archived', true)))

and(...) is rarely needed because chained .where() calls are already AND'd; useful inside or(...) to nest.

eq(col, val) (and the other predicate helpers) is callable without a row-type generic — it defaults to a loose row shape — so in generic handler code you write eq('teamId', id) directly. There's no need for a const ef = (c, v) => eq<Row, string>(c, v) wrapper.

#JSON path filters

For json<T>() columns:

ctx.store.select('notes')
  .where('prefs.fontSize', 'md')           // prefs->>'fontSize' = 'md'
  .where('prefs.collapsed', 'contains', 'inbox')  // prefs->'collapsed' @> '["inbox"]'

See JSON columns for indexing + path semantics.

#orderBy

Each call adds ONE column + direction; chain for multi-column ordering:

.orderBy('createdAt', 'desc')
.orderBy('priority', 'desc').orderBy('createdAt', 'asc')

#limit / offset

.limit(20)
.limit(20).offset(40)

For cursor pagination that avoids OFFSET's O(n) scan, use paginateBy over ctx.store.query(...):

import { paginateBy } from '@voltro/database'

const rows = await ctx.store.query(
  paginateBy(database.notes.descriptor, 'createdAt', req.cursor, 20, 'desc'),
)
const nextCursor = rows.at(-1)?.createdAt ?? null
return { rows, nextCursor }

paginateBy(descriptor, column, cursor, limit, direction?) adds the keyset predicate, sets the ORDER BY, and preserves any existing where.

The direction argument controls both the comparison and the sort — a desc feed pages with <, not >. That pairing is the classic keyset bug: an ascending comparison under a descending sort returns the same first page forever.

The cursor column must be unique, or monotonic enough that ties don't straddle a page boundary. For a timestamp with collisions, order by a tie-breaker and paginate on that:

paginateBy(database.notes.orderBy('createdAt', 'desc').descriptor, 'id', req.cursor, 20)

paginateById(descriptor, cursor, limit) is the id-column shorthand — literally paginateBy(descriptor, 'id', cursor, limit). It works for any sortable id scheme (TypeID, ULID, Snowflake, Numeric). Note it sets the order to id asc, so passing a descriptor that already carries .orderBy('createdAt', 'desc') does not page by createdAt — use paginateBy when the sort column is the thing you want to page on.

#Aggregates

The aggregate terminals live on the database.<table> builder, run via ctx.store.query(...):

import { count, sum, max, eq } from '@voltro/database'

// COUNT(*) — one row, { count: number }
const rows = await ctx.store.query(
  database.notes.where(eq('archived', false)).count().descriptor,
)
const open = rows[0]!.count

// Bundle multiple aggregates into one row
await ctx.store.query(
  database.orders.where(eq('orgId', oid)).aggregate({
    total: sum('amount'),
    peak:  max('createdAt'),
    cnt:   count(),
  }).descriptor,
)

Helpers: count(), countDistinct(col), sum(col), avg(col), min(col), max(col). See Aggregations for .groupBy() / .having() + window functions.

#Distinct

distinct lives on the database.<table> builder, run via ctx.store.query(...):

await ctx.store.query(database.notes.select('authorId').distinct().descriptor)

For DISTINCT ON and one-row-per-group, see DISTINCT + DISTINCT ON.

#Raw SQL escape hatch

When the DSL doesn't model what you need:

import { sql } from '@voltro/database/sql'

const rows = await ctx.store.raw!<{ id: string; n: number }>(sql`
  SELECT id, count(*) AS n
  FROM events
  WHERE occurred_at > ${cutoff}
  GROUP BY id
  ORDER BY n DESC
  LIMIT 10
`)

The sql tag is imported from the server-only @voltro/database/sql subpath — it never reaches the browser bundle. It parameterises values automatically: every ${value} interpolation is bound as a parameter by the store's dialect-specific driver, never spliced into the SQL text. A value containing '; DROP TABLE round-trips as data, not SQL. The generic parameter (<{ id: string; n: number }>) names the row shape.

store.raw is an optional method on the store — it exists on every SQL store (postgres / mysql / mariadb / mssql / sqlite / turso), including the transactional view a mutation's ctx.store is (there it runs on the transaction's own connection, so a SELECT … FOR UPDATE through it locks for the rest of that transaction), but NOT on the in-memory store (there's no SQL engine to run raw text against). The ! non-null assertion above is appropriate on SQL-backed apps; for code that must run on the memory store too, guard with if (ctx.store.raw).

You own dialect-portability of the static text. Only the interpolated values are auto-parameterised — the rest of the fragment is emitted verbatim. Postgres-only syntax (->>, plainto_tsquery) breaks on mysql. Keep the static SQL portable, or branch on the dialect.

Raw queries aren't tracked by the reactive engine — the planner can't infer which tables an arbitrary SQL string touches. If you want a subscription to invalidate on a raw read's tables, declare them explicitly:

ctx.store.raw!<{ n: number }>(sql`SELECT count(*) AS n FROM events`, { dependsOn: ['events'] })

The tables can also live on the fragment itself ({ ...frag, dependsOn: ['events'] }), which is handy when the fragment is built somewhere else. They are taken at face value — the framework records what you declare and never checks it against the SQL.

If you forget, the framework tells you. A raw read taken while a live query's handler runs, with nothing declared, warns once at subscribe time and names both the query and the SQL:

[voltro] reports.summary: a raw SQL read in this live query declares no
dependsOn, so no write can invalidate it — every subscriber keeps its first
result until it reconnects. Declare the tables it reads:
ctx.store.raw(fragment, { dependsOn: ['orders'] }) — or on the fragment itself.
The read: SELECT sum(total) FROM orders WHERE tenant = ?

Only the STATIC text is logged; the bound values never are.

The diagnostic keeps a bounded record: one request remembers up to 32 distinct raw reads (deduplicated by SQL text), tunable with VOLTRO_RAW_READ_TRACKING_LIMIT. Reads past the bound are dropped rather than remembered — a handler that raw-reads in a loop must not turn a warning into a memory leak. Raise it only if a handler issues many distinct raw reads and you want the warning to name a later one.

Where dependsOn works, and where it can only warn. It drives recomputation for a query whose handler returns a computed value — the shape whose handler is genuinely re-run on a change (see Subscriptions). The declared tables join the query's own source:; they never replace it. A handler that returns a query descriptor is different: a change re-runs the descriptor's query, not your handler, so the raw result is not refreshed. There the warning is the whole answer — return a computed value if the raw read has to stay live.

#Tenant scoping (implicit)

If the table has the tenant() mixin, every select auto-merges WHERE tenantId = ctx.subject.tenantId. You don't write it; the runtime injects it. To opt out (admin queries crossing tenants), use .unscoped():

ctx.store.select('notes').unscoped().all()

.unscoped() drops the automatic tenant filter for cross-tenant staff reads — gate it yourself in the handler (e.g. requireScope(ctx.subject, 'admin:full')) before exposing it. Soft-delete reads have the parallel .withDeleted() opt-out.

#Where to find more

The query builder has dedicated pages for the deeper topics:

• Aggregations — .count() / .aggregate({...}) / .groupBy() / .having() + window functions (rowNumber / rank / lag / lead / sumOver).
• Sub-queries — inSubquery / notInSubquery / exists / notExists predicates that reference other queries.
• Set operations — union / unionAll / intersect / except combine multiple queries with the same column shape.
• DISTINCT + DISTINCT ON — dedup row sets, pick one row per group on postgres.
• Self-joins — .as(alias) + .innerJoin(table, alias, on) + .selectJoined({...}) for parent/ child trees and CTE references.
• CTEs — .withCte(name, sub) for named sub-queries reusable inside the outer SELECT.
• Recursive CTEs — .recursiveCte for tree walks (org hierarchy, comment threads, file folders).
• Bulk writes — .updateMany / .upsert / .insertIgnore for one-statement bulk operations.

#CTEs (Common Table Expressions)

For complex queries with reusable sub-queries, declare named CTEs:

import { eq, notInSubquery, queryFor } from '@voltro/database'

const blocked = queryFor(database.blockedUsers).select('userId').descriptor

const visibleUsers = await ctx.store.query(
  queryFor(database.users)
    .withCte('blocked', blocked)
    .where(notInSubquery('id', { table: 'blocked', projection: ['userId'], /* ... */ }))
    .descriptor,
)

Emits WITH blocked AS (SELECT "userId" FROM "blockedUsers") SELECT ....

For recursive CTEs (WITH RECURSIVE) see the Recursive CTE page.

#Query-only stores and pagination probes

EffectStore has one, maybeOne and first, including the store passed to its transactional callback. At boot or inside a query-only tuple source, use readMaybeOne(store, queryOrBuilder): it needs only query, limits the read to one row and returns first-or-null. It uses the supplied store path; it does not add request scoping to a raw driver.

import { readMaybeOne } from '@voltro/runtime'
import type { DataStore, QueryDescriptor } from '@voltro/database'

export const findBootRow = (store: Pick<DataStore, 'query'>, query: QueryDescriptor) =>
  readMaybeOne(store, query)

paginateBy and paginateById return a QueryDescriptor, not a page envelope. Request pageSize + 1, derive hasMore from the extra row, trim it, and derive the next cursor from the last retained row. Doctor accepts that probe when the keyset helper is present. Page-number navigation remains a valid reason to retain OFFSET; record that decision in the Doctor review file.