Querying

Use the chainable table API for inserts, filters, selection, and safe writes

Query result shape

Most ORM operations return:

type QueryResult<T> = {
  success: boolean;
  data?: T;
  results?: T;
  error?: string;
  meta?: {
    rowsRead?: number;
    rowsWritten?: number;
    duration?: number;
  };
}

The chain is the API

Every operation in kunii is a chain. db.todos is an empty query that means "all rows". Each method either continues the chain (returns the same Table<Row> shape) or terminates it (returns a Promise). There are no shortcuts and no second API. Cardinality always comes from the chain, not the verb:

db.todos.where({ id: 1 }).first();               // one row
db.todos.where({ id: 1 }).update({ done: 1 });   // updates one row (id is unique)
db.todos.where({ done: 0 }).update({ done: 1 }); // updates many rows (broad where)
db.todos.where({ id: 1 }).delete();              // deletes one row
db.todos.where({ done: 1 }).delete();            // deletes many rows

update and delete always require a preceding where to prevent accidental table-wide writes. To affect a single row when the where is broad, narrow it (typically by primary key) or chain .limit(1).

Inserts

await db.todos.insert({ title: 'Hello', done: 0 });

await db.todos.insert([
  { title: 'First' },
  { title: 'Second' },
]);

Fields not present in the schema are skipped when using a schema-aware client.

Reads

const all = await db.todos.many();
const first = await db.todos.first();
const exact = await db.todos.where({ id: 1 }).one(); // errors if not exactly 1
const exists = await db.todos.where({ id: 1 }).exists();
const total = await db.todos.count();
const proCount = await db.todos.where({ tier: 'pro' }).count();
const titles = await db.todos.where({ done: 0 }).distinct('title');

Filtering

The value side of where({ ... }) carries intent in two ways: by shape for the common cases (a primitive means equality, null means IS NULL, an array means IN), and by operator object for everything else.

Value-shape shortcuts

db.todos.where({ done: 0 })                       // done = 0
db.todos.where({ deleted_at: null })              // deleted_at IS NULL
db.todos.where({ tier: ['pro', 'enterprise'] })   // tier IN (...)

Bare strings are always equality. There is no auto-LIKE magic. Use { contains }, { like }, or { notLike } to opt into pattern matching.

Operator object

// Comparisons
db.todos.where({ id: { gt: 5 } })
db.todos.where({ age: { gte: 18, lt: 65 } })
db.todos.where({ id: { ne: 3 } })

// Substring / pattern
db.todos.where({ title: { contains: 'urgent' } })   // LIKE %urgent% with %/_ escaped
db.todos.where({ title: { like: '%hello%' } })      // raw LIKE pattern (escape hatch)
db.todos.where({ title: { notLike: '%spam%' } })

// Sets
db.todos.where({ id: { in: [1, 2, 3] } })
db.todos.where({ id: { notIn: [4, 5] } })

// Ranges
db.todos.where({ id: { between: [1, 100] } })
db.todos.where({ priority: { notBetween: [1, 3] } })

// Null
db.todos.where({ deleted_at: { isNull: true } })   // IS NULL
db.todos.where({ verified_at: { isNull: false } }) // IS NOT NULL

Multiple operators on the same column AND together:

db.todos.where({ priority: { gte: 5, lt: 10 } })
// -> priority >= ? AND priority < ?

contains vs like

Use contains when you have a literal needle. The ORM wraps it in %...% and escapes any %/_ in the input so a search for 'discount_50%' matches the literal substring instead of being interpreted as a wildcard. Use like when you want raw LIKE pattern syntax (for example _ single-character wildcards or pre-shaped patterns).

IN scales without limits

{ in: [...] } (and the array value-shape shortcut) compile to a single bound parameter using SQLite's json_each:

SELECT * FROM todos WHERE id IN (SELECT value FROM json_each(?))

This avoids D1's per-statement placeholder limit (around 100), so you can pass arrays of any size.

Empty arrays are predictable

db.todos.where({ id: [] })             // -> WHERE 0 (matches nothing)
db.todos.where({ id: { notIn: [] } })  // -> WHERE 1 (matches everything)

Pass dynamic arrays without a length guard. The result is always sensible.

OR conditions

db.todos.whereAny([
  { done: 0, priority: { gte: 5 } },
  { title: { contains: 'urgent' } },
])
// -> WHERE (done = ? AND priority >= ?) OR (title LIKE ? ESCAPE '\')

whereAny is the canonical OR. It accepts an array of full filters. Each entry is a where-style object whose keys AND together, and the entries OR with each other.

Raw SQL conditions

You can add raw predicates without giving up parameter binding:

db.todos.sql({
  query: 'title LIKE ? OR done = ?',
  params: ['%hello%', 1],
})

The query builder rejects obvious interpolation and multi-statement patterns. Use ? placeholders and a params array.

Ordering and selection

db.todos.orderBy({ created_at: 'desc' })
db.todos.orderBy([{ done: 'asc' }, { created_at: 'desc' }])
db.todos.orderBy('created_at DESC')

db.todos.select(['id', 'title'])
db.todos.limit(10).offset(20)

Updates and deletes

Both terminals require a preceding where for safety. Cardinality follows the chain:

// Single row (id is unique).
await db.todos.where({ id: 1 }).update({ done: 1 });
await db.todos.where({ id: 1 }).delete();

// Many rows.
await db.todos.where({ done: 0 }).update({ banner_seen: 1 });
await db.todos.where({ done: 1 }).delete();

To affect exactly one row when the where could match many, chain .limit(1):

await db.todos
  .where({ done: 0 })
  .orderBy({ created_at: 'asc' })
  .limit(1)
  .update({ in_progress: 1 });

Soft delete

If a table has a deleted_at column, the ORM filters soft-deleted rows by default. Use withDeleted() on the query builder when you need everything:

const allTodos = await db.todos.withDeleted().many();