Relations

Define the relation in the schema

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

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()

// 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:

// 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:

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:

// 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
{ any: <where> }	EXISTS, at least one matching child
{ none: <where> }	NOT EXISTS, no matching children
number	count equals N
{ eq | ne | gt | gte | lt | lte: N }	count comparison
{ between | notBetween: [low, high] }	count range

Nested relation predicates work too:

// 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
• JSON Columns
• Writes and Transactions