Database

The ctx.db API: insert, get, update, delete, and index queries with ranges, order, take, first, collect, count, and paginate.

Every call is async. Await or return each one. ctx.db has one property per table in your schema. Queries, actions, and GET/HEAD endpoints get read methods only. Mutations and writable endpoints also get insert, update, and delete.

The examples use this schema:

schema: {
  tasks: table({
    title: string(),
    done: boolean().default(false),
    priority: number().default(0),
    dueAt: number().optional(),
    ownerId: userId()
  })
    .index("by_owner", ["ownerId"])
    .index("by_owner_done", ["ownerId", "done"])
    .index("by_owner_due", ["ownerId", "dueAt"])
}

Rows

Every row has the fields you declared plus three Fold sets:

You cannot set or update these three yourself.

insert(value)

const task = await ctx.db.tasks.insert({ title: "Ship it", ownerId: userId });
// task.id, task.createdAt, task.done === false, task.priority === 0

Returns the new row with defaults filled in. Required fields must be present. Fields with .default() or .optional() may be left out. Pass null for an optional field to leave it empty. An unknown field throws Unknown field for tasks: <name>.

get(id)

const task = await ctx.db.tasks.get(taskId);
if (!task) {
  throw new Error("Task not found.");
}

Returns the row, or null.

update(id, patch)

const updated = await ctx.db.tasks.update(taskId, { done: true });
await ctx.db.tasks.update(taskId, { dueAt: null }); // clear an optional field

Changes only the fields in patch. Returns the updated row, or null if no row has that ID. Passing null clears an optional field.

delete(id)

const removed = await ctx.db.tasks.delete(taskId); // true if a row was deleted

Check ownership before writing

A row ID is not a credential. Before you update or delete a row on behalf of a user, load it and compare its owner:

mutation(async (ctx, taskId: string) => {
  const { userId } = ctx.auth.requireIdentity();
  const task = await ctx.db.tasks.get(taskId);
  if (!task || task.ownerId !== userId) {
    throw new Error("Task not found.");
  }
  await ctx.db.tasks.delete(taskId);
})

Queries: withIndex

Every list read goes through an index:

ctx.db.<table>.withIndex(<index name>, (q) => <range>).order(<"asc" | "desc">).<terminal>()

by_creation exists on every table and sorts by creation time. Use it for unfiltered reads:

const newest = await ctx.db.tasks.withIndex("by_creation").order("desc").take(20);

Filter by declaring an index on the fields you filter on, then pass a range:

const mine = await ctx.db.tasks
  .withIndex("by_owner", (q) => q.eq("ownerId", userId))
  .collect();

There is no full-table filter. where(), orderBy(), limit(), and all() throw. Filter small results in JavaScript after an index query if you need to.

Range operators

Rules:

• Clauses follow the index's field order. Start with eq on the first field, then eq on the next, and so on.
• After the eq clauses, you may add at most one lower bound (gt or gte) and one upper bound (lt or lte) on the next field.
• Range bounds only match rows where the field has a value.
• The range callback must return the builder it was given.

// Open tasks for this user
ctx.db.tasks.withIndex("by_owner_done", (q) => q.eq("ownerId", userId).eq("done", false)).collect();

// Due in the next 7 days, soonest first
const now = Date.now();
ctx.db.tasks
  .withIndex("by_owner_due", (q) => q.eq("ownerId", userId).gte("dueAt", now).lt("dueAt", now + 7 * 86_400_000))
  .order("asc")
  .collect();

Wrong order throws, for example Index tasks.by_owner_done expected field ownerId, received done.

order(direction)

"asc" (default) or "desc". Rows are sorted by the index fields, then createdAt, then id. So withIndex("by_owner", ...).order("desc") returns a user's newest rows first.

Terminals

Pagination

import { fold, query, type PaginationOptions } from "@aozumi/fold/server";

queries: {
  feed: query(async (ctx, args: { pagination: PaginationOptions }) => {
    return ctx.db.tasks.withIndex("by_creation").order("desc").paginate(args.pagination);
  })
}

Pass continueCursor back as the next cursor until isDone is true. A cursor only works for the same query shape, and stops working after a schema change (Pagination cursor belongs to a stale schema.). On the client, usePaginatedQuery handles cursors for you. See Client.

Read and write budgets

Each handler call has a budget. Hitting one throws Database <name> limit exceeded (<limit>). These apply in local dev and hosted.

A loop over paginate() inside one handler shares the same budget. It does not get around these limits.

Dashboard counts

For counts and totals, keep a summary row up to date when you write, and read the summary instead of scanning.

import { fold, mutation, number, query, string, table } from "@aozumi/fold/server";

export default fold({
  name: "clicks",
  schema: {
    events: table({ kind: string(), at: number() }).index("by_kind_at", ["kind", "at"]),
    counters: table({ key: string(), total: number().default(0) }).index("by_key", ["key"])
  },
  mutations: {
    record: mutation(async (ctx, kind: string) => {
      const at = Date.now();
      await ctx.db.events.insert({ kind, at });
      const counter = await ctx.db.counters.withIndex("by_key", (q) => q.eq("key", kind)).first();
      if (counter) {
        await ctx.db.counters.update(counter.id, { total: Number(counter.total) + 1 });
      } else {
        await ctx.db.counters.insert({ key: kind, total: 1 });
      }
    })
  },
  queries: {
    totals: query(async (ctx) => ctx.db.counters.withIndex("by_key").collect()),
    lastHour: query(async (ctx, args: { kind: string }) => {
      const since = Date.now() - 3_600_000;
      return ctx.db.events.withIndex("by_kind_at", (q) => q.eq("kind", args.kind).gte("at", since)).count();
    })
  }
});

Guidance:

• Store times as number() epoch milliseconds and index them after the fields you filter on, as in by_kind_at.
• count() is fine for a few thousand matching rows. Past 5,000 it hits the rows-read budget. Use summary rows for anything that can grow past that.
• The mutation updates the event and the counter in one transaction, so totals stay exact.
• A live useQuery("totals") on the client updates as soon as record runs.
• Stored data in an anonymous deploy is capped at 1 MB. Delete or roll up old rows if events pile up.
• To feed a dashboard from another system, have that system POST to a writable endpoint that calls the same insert-and-count logic, one small batch per request. Anonymous deploys cannot fetch out, and ctx.env is empty, so they cannot pull data or check a secret from server env. Protect an ingest endpoint with ctx.auth.requireSignedIn() or keep it to data that is safe for anyone to send.

Types

Inside fold({ schema, ... }), rows are typed from the schema: task.title is a string, task.dueAt is number | undefined. On the client, createClient<typeof app>() gives each query result the same JSON shape. The Id<"tasks"> type from @aozumi/fold/server marks an ID string by table.