Core Concepts — Relations

Relations connect tables through foreign keys declared in the vibecode‑db schema DSL. You define the FK inline on the column with references(...), and vibecode‑db derives a relation graph (db.relations) that adapters use for nested reads and integrity hints.

What a relation is (at a glance)

• Declared via references(col.<kind>(...), () => <targetTable>.<targetColumn>)
• Supports one‑to‑many (e.g., users → todos) and one‑to‑one (e.g., users → profiles) modeling
• Powers nested projections in queries: e.g., 'id, title, users(name)'
• Included in your DBSpec as relations alongside schema

Declare foreign keys (inline)

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    user_id: col.integer(),
14    // one-to-many: many todos belong to one user
15    user_fk: references(col.integer('user_id'), () => users.id),
16  }),
17
18  profiles: vibecodeTable('profiles', {
19    id: col.uuid(),
20    user_id: col.integer(),
21    bio: col.varchar(),
22    // one-to-one: app-level intent (enforced in migrations if needed)
23    user_fk: references(col.integer('user_id'), () => users.id),
24  }),
25})
26
27// Outputs -> db.zodBundle (validators) + db.relations (relation graph)

Note: The kind of the FK source column (e.g., integer for user_id) should match the target column’s kind.

Add to DBSpec

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, // include the relation graph
7  // seed?, meta?
8}

Query with nested projections

• Chain filters/modifiers first, then call .select(...) to execute.
• Use string projections to include related rows.

1// all todos with the owning user's name
2const rows = await db
3  .from('todos')
4  .where({ /* e.g., user_id: 1 */ })
5  .select('id, title, users(name)')

For a one‑to‑one like profiles → users, you can project user fields similarly:

1const profiles = await db
2  .from('profiles')
3  .select('id, bio, users(name, email)')

Notes

• Cardinality (1‑to‑1 vs 1‑to‑many) is a modeling intent here; enforce uniqueness or cascades in SQLite migrations if needed.
• Keep FK and target kinds aligned (e.g., integer → integer, uuid → uuid).

Summary

Declare FKs with references(...) on the FK column, export db.relations, and include it in your DBSpec. You’ll get nested, type‑safe reads with the same fluent API across all adapters.