Query Builder

The query builder is the same on both engines. Everything on this page works identically inside a mutator (against IDB in the browser, D1 on the Worker), inside a useLiveQuery(...) factory, and against a raw executor via createServerDb.

`select` — column projection

// All columns
const rows = await db.select().from(todos)

// Explicit projection
const rows = await db.select({ id: todos.id, title: todos.title }).from(todos)

// Computed projection with aggregates
const stats = await db
  .select({ userId: todos.userId, n: count() })
  .from(todos)
  .groupBy(todos.userId)

The row shape flows through the projection. No projection = full row. Explicit projection = the shape you named.

`where` — filtering

import { and, eq, gt, gte, inArray, isNull, isNotNull, like, lt, ne, not, or } from "@sh1n4ps/plasma-core"

// Comparison
db.select().from(todos).where(eq(todos.done, 0))
db.select().from(todos).where(gt(todos.updatedAt, cutoff))
db.select().from(todos).where(ne(todos.userId, banned))

// Boolean logic
db.select().from(todos).where(and(eq(todos.done, 0), gt(todos.updatedAt, cutoff)))
db.select().from(todos).where(or(eq(todos.priority, 1), eq(todos.pinned, 1)))
db.select().from(todos).where(not(eq(todos.done, 1)))

// Set membership
db.select().from(todos).where(inArray(todos.userId, ["u1", "u2", "u3"]))

// Null checks
db.select().from(todos).where(isNull(todos.assignee))
db.select().from(todos).where(isNotNull(todos.assignee))

// Pattern
db.select().from(todos).where(like(todos.title, "% urgent %"))

and / or accept 2+ predicates. Nesting is allowed: and(or(a, b), c).

`orderBy` — sorting

import { asc, desc } from "@sh1n4ps/plasma-core"

db.select().from(todos).orderBy(asc(todos.updatedAt))
db.select().from(todos).orderBy(desc(todos.priority), asc(todos.updatedAt))

Multiple orderings are stable: primary → secondary → …

`limit` and `offset` — pagination

// First 20
db.select().from(todos).limit(20)

// Page 2 (rows 21-40)
db.select().from(todos).limit(20).offset(20)

// Sortable pagination — always pair with orderBy for deterministic pages
db.select()
  .from(todos)
  .orderBy(desc(todos.updatedAt))
  .limit(20)
  .offset((page - 1) * 20)

Cursor-based pagination (by a monotonic column, no offset) is usually more performant for infinite scroll — offset(N) still walks N rows internally. For plasma reads that’s fine at typical per-user data sizes, but at 10k+ rows in a window, prefer:

db.select().from(todos)
  .where(lt(todos.updatedAt, lastSeenCursor))
  .orderBy(desc(todos.updatedAt))
  .limit(20)

`innerJoin` / `leftJoin` — joins

db.select()
  .from(todos)
  .innerJoin(users, eq(todos.userId, users.id))

// Left join — users.* is nullable
db.select()
  .from(todos)
  .leftJoin(users, eq(todos.userId, users.id))

Joined row shape:

{ todos: { id, title, ... }, users: { id, name, ... } | null }

Multiple joins:

db.select()
  .from(todos)
  .innerJoin(users, eq(todos.userId, users.id))
  .leftJoin(comments, eq(comments.todoId, todos.id))
  .where(eq(users.id, ctx.userId))

`groupBy` + aggregates

import { avg, count, max, min, sum } from "@sh1n4ps/plasma-core"

// How many todos per user
db.select({ userId: todos.userId, n: count() })
  .from(todos)
  .groupBy(todos.userId)

// Average per priority
db.select({ priority: todos.priority, avg: avg(todos.score) })
  .from(todos)
  .groupBy(todos.priority)

Aggregates without groupBy produce a single-row result.

`having` — post-aggregate filter

where filters rows before grouping. having filters groups after aggregation.

// Only users with more than 3 todos
db.select({ userId: todos.userId, n: count() })
  .from(todos)
  .groupBy(todos.userId)
  .having(gt(count(), 3))

// Combine — recent completed todos, only counting users with 5+
db.select({ userId: todos.userId, n: count() })
  .from(todos)
  .where(and(eq(todos.done, 1), gt(todos.updatedAt, cutoff)))
  .groupBy(todos.userId)
  .having(gte(count(), 5))

`fromSubquery` + `colRef` — subqueries as sources

Use a subquery’s result as the FROM clause of an outer query. Useful for “compute aggregates, then filter by them” patterns where a single WHERE + HAVING wouldn’t work.

import { colRef, count, gt } from "@sh1n4ps/plasma-core"

// Inner: count of active todos per user
const active = db
  .select({ userId: todos.userId, cnt: count() })
  .from(todos)
  .where(eq(todos.done, 0))
  .groupBy(todos.userId)

// Outer: only users with > 3 active todos, then join their name
const busy = await db
  .select()
  .fromSubquery(active, "active")
  .innerJoin(users, eq(colRef("active", "userId"), users.id))
  .where(gt(colRef("active", "cnt"), 3))

colRef(tableAlias, column) references a column from the subquery’s projection. The alias ("active" above) matches the second argument to .fromSubquery(...).

Live vs one-shot

Every builder shape above accepts .live() to become a LiveQuery<T>:

const live = db.select().from(todos).where(eq(todos.done, 0)).live()
live.subscribe((rows) => setRows(rows))

Or as a useLiveQuery factory in React:

const rows = useLiveQuery(
  () => plasma.db.select().from(todos).where(eq(todos.done, 0)),
  [],
)

See Live queries for reactivity mechanics.

Building predicates dynamically

Predicates compose like values — build them ahead of time and pass them into where:

function todoFilter(criteria: { done?: number; userId?: string }) {
  const parts = []
  if (criteria.done !== undefined) parts.push(eq(todos.done, criteria.done))
  if (criteria.userId !== undefined) parts.push(eq(todos.userId, criteria.userId))
  if (parts.length === 0) return undefined
  return parts.length === 1 ? parts[0] : and(...parts)
}

const predicate = todoFilter({ done: 0, userId: currentUser })
const rows = await db.select().from(todos).where(predicate!)

Query Builder

select — column projection

where — filtering

orderBy — sorting

limit and offset — pagination

innerJoin / leftJoin — joins

groupBy + aggregates

having — post-aggregate filter

fromSubquery + colRef — subqueries as sources