# Relations

> Use foreign keys for eager loading and relation-aware filtering

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

## Define the relation in the schema

Mark foreign keys with the `-> table.column` syntax:

```ts
posts: {
  id: 'integer primary key',
  userId: 'integer -> users.id',
  title: 'text not null',
}
```

The ORM uses these declarations for two things: eager loading via `include()`, and relation predicates inside `where()`.

## Eager loading with `include()`

```ts
// Default: load all child rows for each parent.
const result = await db.users.include({ posts: true }).many();
```

Filter, order, limit, or narrow the children. The relation config takes the same options as a normal chain:

```ts
// Per-user, only published posts, newest 10 each, only id + title.
await db.users.include({
  posts: {
    where: { published: 1 },
    orderBy: { createdAt: 'desc' },
    limit: 10,                  // top 10 per parent (window function)
    select: ['id', 'title'],    // FK is auto-included for stitching
  },
}).many();
```

Explicit key mapping for non-conventional foreign key names:

```ts
await db.users.include({
  posts: { foreignKey: 'authorId', localKey: 'id', as: 'articles' },
}).many();
```

## Relation predicates inside `where()`

When `posts.userId -> users.id` is declared, you can filter `users` by what `posts` exist:

```ts
// Users with at least one published post.
await db.users.where({ posts: { any: { published: 1 } } }).many();
// -> WHERE EXISTS (SELECT 1 FROM posts WHERE posts.userId = users.id AND published = ?)

// Users with no posts at all.
await db.users.where({ posts: { none: {} } }).many();
// -> WHERE NOT EXISTS (SELECT 1 FROM posts WHERE posts.userId = users.id)

// Users with at least 5 posts.
await db.users.where({ posts: { gte: 5 } }).many();
// -> WHERE (SELECT count(*) FROM posts WHERE posts.userId = users.id) >= ?

// Users with exactly 0 posts (alternate spelling of `none: {}`).
await db.users.where({ posts: 0 }).many();

// Users with between 5 and 10 posts.
await db.users.where({ posts: { between: [5, 10] } }).many();
```

The relation value accepts:

| shape | meaning |
|---|---|
| <code>&#123; any: &lt;where&gt; &#125;</code> | EXISTS, at least one matching child |
| <code>&#123; none: &lt;where&gt; &#125;</code> | NOT EXISTS, no matching children |
| number | count equals N |
| <code>&#123; eq \| ne \| gt \| gte \| lt \| lte: N &#125;</code> | count comparison |
| <code>&#123; between \| notBetween: [low, high] &#125;</code> | count range |

Nested relation predicates work too:

```ts
// Users with any post that has any comment containing "great".
await db.users.where({
  posts: { any: { comments: { any: { body: { contains: 'great' } } } } }
}).many();
```

For workloads beyond what these compose into, fall back to `db.raw(sql, params)`.

## Read next

- [Querying](/docs/kunii/querying)
- [JSON Columns](/docs/kunii/json-columns)
- [Writes and Transactions](/docs/kunii/writes-and-transactions)