Database — Query Builder

A fluent, adapter‑agnostic way to read and write data from your front end.
Start with db.from('<table>'), chain filters/modifiers, then execute with .select(...).

Prerequisite: You must have created a client with a required dbSpec (schema + relations [+ optional seed, meta]) and an adapter factory (SQLite Web/Expo or Supabase).

Execution Rule

In v1.0, .select(projection) executes the query.
Always chain filters/modifiers first, then call .select(...).
Writes (insert, update, delete) execute immediately when called.

Core syntax

1const rows = await db
2  .from('posts')
3  .where({ published: true })       // or .eq('published', true)
4  .order('created_at', { ascending: false })
5  .limit(10)
6  .select('id, title')

Read data (`select`)

select('*') → all columns
select('id, name') → projection
Relational projection (SQLite today): one‑level many‑to‑one only via LEFT JOINs, e.g. todos → users(name)

1// one‑level M→1 nesting (SQLite join limit today)
2const todos = await db
3  .from('todos')
4  .where({ user_id: 1 })
5  .select('id, title, users(name)')

Deep chains (a → b → c) or inline 1→M expansions aren’t executed by the current SQLite join builder. Use two queries or DB‑side views for those cases.

Filters

Use either an object .where({...}) or operator helpers:

eq, ne, gt, gte, lt, lte, in, like

1const posts = await db
2  .from('posts')
3  .eq('published', true)
4  .like('title', '%guide%')
5  .select('id, title')

Modifiers

order(column, { ascending?, nullsFirst? })
limit(n)
range(from, to)

1const latest = await db
2  .from('posts')
3  .order('created_at', { ascending: false })
4  .limit(10)
5  .select('id, title, created_at')

Write operations

1// insert
2await db.from('users').insert({ id: 3, name: 'Grace', email: 'grace@example.com' })
3
4// update (use filters)
5await db.from('users').where({ id: 3 }).update({ name: 'Grace Hopper' })
6
7// delete (use filters)
8await db.from('users').where({ id: 3 }).delete()

Types & validation

Types are inferred from your Zod bundle (dbSpec.schema).
Inserts/updates are validated at runtime by adapters.
Filtered columns are checked against known fields.

Summary

Compose intent with a fluent chain, execute with .select(...), and project relations within current limits. The surface is identical across adapters—only storage changes, not your queries.