Database

The Database layer gives you a unified, adapter‑agnostic API to read and write data from your front end. It’s schema‑driven (via the Zod bundle from your schema), type‑safe, and works the same whether you’re using SQLite adapters (Web/Expo) or the Supabase adapter—your queries don’t change, only where the data lives.

What this section covers

• Query Builder — the fluent chain (from() … where() … select())
• Core CRUD — Select, Insert, Update, Delete
• Filters — object .where({...}) and operator helpers (eq, ne, gt, gte, lt, lte, in, like)
• Modifiers — order, limit, range
• Execution model — how and when queries run
• Relational reads — nested projections (see note on SQLite join limits)

All pages here assume you created a client with a required dbSpec (schema + relations [+ optional seed, meta]) and an adapter factory.
In v1.0, .select() executes the query, so chain filters/modifiers before select().

Design principles

• Adapter‑agnostic: one API across SQLite (Web/Expo) and Supabase.
• Schema‑driven: known tables/columns are validated from your dbSpec.schema.
• Predictable execution: build intent → then call select().
• Typed results: return shapes follow your projection string and relations.

Quick taste

1// Read: incomplete todos for a user, newest first, first 10
2const todos = await db
3  .from('todos')
4  .where({ user_id: 1, completed: false })   // or .eq('user_id', 1).eq('completed', false)
5  .order('created_at', { ascending: false })
6  .limit(10)
7  .select('id, title, created_at, users(name)') // one‑level M→1 nesting

1// Create
2await db.from('users').insert({
3  id: 3,
4  name: 'Grace',
5  email: 'grace@example.com',
6})

1// Update
2await db
3  .from('users')
4  .where({ id: 3 })
5  .update({ name: 'Grace Hopper' })

1// Delete
2await db
3  .from('users')
4  .where({ id: 3 })
5  .delete()

Operators & modifiers (at a glance)

• Filters: where({ ... }), eq, ne, gt, gte, lt, lte, in, like
• Modifiers: order(column, { ascending?, nullsFirst? }), limit(n), range(from, to)

Chain any number of filters/modifiers, then call select() to execute.

Relational reads (projection strings)

Use nested projection strings to pull parent fields (e.g., todos → users(name)).
SQLite adapters today: support “SELECT list + LEFT JOINs for one‑level many‑to‑one nesting.”
Deep chains (todos → users → orgs) or expanding one‑to‑many collections inline aren’t executed by the current join builder—use two queries or DB‑side views for those cases.

Adapter behavior (high level)

• SQLite (Web / Expo): runs locally; you supply migrations (SQL DDL); optional persistence (Web via OPFS/IDB, Expo on‑device by default). Seed handling is adapter‑specific (commonly insert‑if‑empty on first run).
• Supabase (bundled in @vibecode-db/client): remote Postgres with RLS/auth; same query surface. Manage DDL in your Postgres migrations/tooling.

What’s next

• Start with Query Builder
• Perform reads: Select Data
• Write data: Insert, Update, Delete
• Refine results: Using Filters, Using Modifiers

One fluent API, portable across runtimes—the adapter decides where your data lives, not how you query it.