Query builder

The full ctx.store API — select, where, orderBy, limit, aggregates, raw SQL escape hatches.

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

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)`	`R`	throws `NoRowFound`	throws `NoRowFound`
`.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.

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', 'awb_')        // case-SENSITIVE prefix (LIKE 'awb\_%')
.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: awb_ and AWB_ 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) 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 builder

#Select

#Single-row terminals on the typed builder

#where

#Operators

#OR / NOT

#JSON path filters

#orderBy

#limit / offset

#Aggregates

#Distinct

#Raw SQL escape hatch

#Tenant scoping (implicit)

#Where to find more

#CTEs (Common Table Expressions)