# Querying

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

Package: Kunii
Canonical: https://kuratchi.dev/docs/kunii/querying
Markdown: https://kuratchi.dev/docs/kunii/querying.md

## Query result shape

Most ORM operations return:

```ts
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:

```ts
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

```ts
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

```ts
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

```ts
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

```ts
// 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:

```ts
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`:

```sql
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

```ts
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

```ts
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:

```ts
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

```ts
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:

```ts
// 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)`:

```ts
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:

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

## Read next

- [Relations](/docs/kunii/relations)
- [JSON Columns](/docs/kunii/json-columns)
- [Writes and Transactions](/docs/kunii/writes-and-transactions)
- [Aggregates and Reporting](/docs/kunii/aggregates-and-reporting)
- [Type Generation](/docs/kunii/type-generation)