Schema — Relations & Referential Integrity (Revised)

Relations connect tables through foreign keys you declare in the schema DSL. Define them inline with references(...) so vibecode‑db can derive db.relations for nested reads and type‑safe projections. Physical enforcement (keys, cascades, unique) is applied by your SQLite migrations or your Postgres/Supabase DDL.

How to declare a relation (inline FK)

Use references(col.<kind>('<fk_column_name>'), () => <targetTable>.<targetColumn>).

1import { defineSchema, vibecodeTable, col, references } from '@vibecode-db/client'
2
3export const db = defineSchema({
4  users: vibecodeTable('users', {
5    id: col.integer(),
6    name: col.varchar(),
7    email: col.varchar(),
8  }),
9
10  todos: vibecodeTable('todos', {
11    id: col.uuid(),
12    title: col.varchar(),
13    // FK column and relation in one declaration:
14    user_id: references(col.integer('user_id'), () => db.tables.users.id),
15  }),
16})
17// Outputs → db.zodBundle (validators) + db.relations (relation graph)

The kind must match (integer → integer, uuid → uuid, …). The function points to the target column (usually the primary key).

Cardinality & current SQLite support

Modeling intent

• One‑to‑many: Many todos → one users row (typical FK as above).
• One‑to‑one: Model with a unique FK (enforce uniqueness in your DDL).
• Many‑to‑many: Use a join table (two FKs).

SQLite execution limits (today)

• The SQLite adapters implement buildSelect for “SELECT list + LEFT JOINs for one‑level many‑to‑one nesting.”
• This means:
  • ✅ From a child table you can project one level of its parent (M→1) via nested projections.
    Example: todos → users with select('id, title, users(name)').
  • ✅ One‑to‑one behaves like many‑to‑one for projection purposes (single parent row).
  • ❌ Deep nesting (e.g., todos → users → orgs) is not executed as chained joins yet.
  • ❌ Expanding one‑to‑many collections inline (e.g., users(todos(...))) is not handled by the current join builder.
  • ❌ Many‑to‑many expansion requires either manual SQL or multi‑step queries for now.

Supabase/Postgres may support richer nesting via its own engine, but keep your projections portable by following the constraints above when you want identical behavior across adapters.

Add to DBSpec

Include the relation graph when you build your spec:

1import type { DBSpec } from '@vibecode-db/client'
2import { db } from './schema'
3
4export const dbSpec: DBSpec<typeof db.zodBundle.shape> = {
5  schema: db.zodBundle,
6  relations: db.relations,
7  // seed?, meta?
8}

Querying with nested projections (within today’s limits)

Chain filters/modifiers first, then execute with .select(...). Use one‑level M→1 projections:

1// All todos with the owning user's name (M→1, one level)
2const rows = await db
3  .from('todos')
4  .select('id, title, users(name)')

If you need deeper or fan‑out relations (1→M or M→M), prefer two queries and compose in code, or write a custom SQL view/migration and select from it.

Referential integrity (where it’s enforced)

• SQLite (Web/Expo): express keys, indexes, and cascades in your migrations (SQL DDL).
• Supabase/Postgres: manage constraints in your DB migrations; vibecode‑db keeps the same query/types layer.

Summary

Declare FKs inline with references(...) to produce db.relations, include it in DBSpec, and project parents with one‑level M→1 LEFT JOINs on SQLite today. For deeper or collection expansions, use multi‑step queries or DB‑side constructs until broader join support lands.