Filters, Sorting & Pagination

Filter rows with where(), sort with orderBy(), and paginate with limit/offset.

Filters

Use where(...) to filter rows. Pass the columns you want to match on — all conditions are combined with AND. OR filters are not supported in queries; use multiple queries if you need disjoint result sets.

Operator support by column type (TypeScript):

Column kindOperators / shape
ideq, ne, in
s.string()eq, ne, contains, in
s.boolean()eq, ne, in
s.int() / s.float()eq, ne, gt, gte, lt, lte, in
s.timestamp()eq, ne, gt, gte, lt, lte, in
s.bytes()eq, ne, in
s.uuid() (required)eq, ne, in
s.uuid().optional()eq, ne, in, isNull
s.enum(...)eq, ne, in
s.array(...)eq, contains, in
s.json() / s.json(schema)eq, ne, in

Nullable columns with ordinary filter operators also support isNull. Required columns, id, and payload enums do not expose it.

export async function readTodosWithFilters(db: Db) {
  return db.all(app.todos.where({ done: false, title: { contains: "docs" } }));
}
export async function readTodosWithWhereOperators(db: Db) {
  await db.all(app.todos.where({ done: false }));
  await db.all(app.todos.where({ title: { contains: "milk" } }));
  await db.all(app.todos.where({ projectId: { ne: EXAMPLE_PROJECT_ID } }));
}

Sorting

Sort results with orderBy(...). Pass a column name and optionally "asc" or "desc".

export async function readTodosSortedByTitle(db: Db) {
  return db.all(app.todos.where({ done: false }).orderBy("title", "asc"));
}

Pagination

limit(n) caps the number of rows returned; offset(n) skips that many rows. Combine them with a deterministic order. If the first sort column is not unique, add orderBy("id") as the final tie-breaker so page boundaries are stable.

export async function readTodoPage(db: Db, page: number, pageSize = 20) {
  const offset = Math.max(0, (page - 1) * pageSize);
  return db.all(
    app.todos
      .where({ done: false })
      .orderBy("title", "asc")
      .orderBy("id", "asc")
      .limit(pageSize)
      .offset(offset),
  );
}