Relations

Relations are inferred from foreign keys. npx turbine pull reads information_schema + pg_catalog and generates a *Relations interface for each table — with the target table, cardinality ('one' vs 'many'), and the join keys baked into a phantom-branded RelationDescriptor. That brand is what powers deep with type inference.

You rarely declare a relation by hand. Define your foreign keys in defineSchema() (or let introspection read them) and the relation falls out.

One-to-many (hasMany)

The canonical case. A user has many posts because posts.user_id references users.id.

// schema.ts
export default defineSchema({
  users: {
    id: { type: 'serial', primaryKey: true },
    email: { type: 'text', unique: true, notNull: true },
  },
  posts: {
    id: { type: 'serial', primaryKey: true },
    userId: { type: 'bigint', notNull: true, references: 'users.id' },
    title: { type: 'text', notNull: true },
  },
});
// query.ts
const users = await db.users.findMany({
  with: { posts: true },
});
// users[0].posts is Post[] — never null, empty array if no rows

posts arrives as Post[] on each user, COALESCE-d to [] so you never have to null-check a collection.

One-to-one (belongsTo + hasOne)

Same foreign-key setup, different cardinality. The owning side (with the FK) gets belongsTo; the referenced side gets hasOne if the FK is UNIQUE.

users: {
  id: { type: 'serial', primaryKey: true },
  profileId: { type: 'bigint', unique: true, references: 'profiles.id' },
},
profiles: {
  id: { type: 'serial', primaryKey: true },
  bio: { type: 'text' },
},
const user = await db.users.findUnique({
  where: { id: 1 },
  with: { profile: true },
});
// user.profile is Profile | null

Without the unique: true on profileId, Turbine would infer hasMany on the reverse side.

Many-to-many (auto-detected pure junctions)

Turbine auto-detects pure junction tables during generate and gives both endpoints a flat many-to-many relation. A pure junction is a table whose primary key is exactly two single-column foreign keys and which carries no other columns — for example posts_tags(post_id, tag_id).

// schema.ts
posts: {
  id: { type: 'serial', primaryKey: true },
  title: { type: 'text', notNull: true },
},
tags: {
  id: { type: 'serial', primaryKey: true },
  name: { type: 'text', notNull: true },
},
postsTags: {
  postId: { type: 'bigint', notNull: true, references: 'posts.id' },
  tagId: { type: 'bigint', notNull: true, references: 'tags.id' },
  primaryKey: ['postId', 'tagId'],
},

Load the related rows directly — no join-table hop:

const posts = await db.posts.findMany({
  with: { tags: true }, // each post comes back with its tags array
});
// posts[0].tags is Tag[]

Nested where / orderBy / limit work on the m2m target too:

const post = await db.posts.findFirst({
  where: { id: 1 },
  with: { tags: { where: { name: 'sql' }, orderBy: { name: 'asc' }, limit: 5 } },
});

Under the hood Turbine JOINs the target through the junction table and correlates junction.sourceKey = parent.referenceKey — still one SQL statement, still json_agg.

Junctions with a payload, or non-pure junctions

A junction table that carries extra columns (a role, a timestamp, an "added by") is a first-class entity, so Turbine keeps it as an ordinary hasMany — that's by design. You query it the same way you'd query any relation: through the junction, with the join-table row available.

memberships: {
  userId: { type: 'bigint', notNull: true, references: 'users.id' },
  orgId: { type: 'bigint', notNull: true, references: 'organizations.id' },
  role: { type: 'text', notNull: true, default: "'member'" },
  primaryKey: ['userId', 'orgId'],
},
const user = await db.users.findUnique({
  where: { id: 1 },
  with: {
    memberships: {
      with: { organization: true },
    },
  },
});
// user.memberships[0].role  — the payload is right there
// user.memberships[0].organization.name
 
// Want the flat list without the join-table rows? Flatten in code:
const orgs = user.memberships.map((m) => m.organization);

Declaring a many-to-many by hand

To get the flat m2m relation on a junction that isn't pure — or to wire one up explicitly — declare it in your code-first schema with manyToMany:

import { defineSchema } from 'turbine-orm';
 
export default defineSchema({
  posts: {
    id: { type: 'serial', primaryKey: true },
    title: { type: 'text', notNull: true },
    manyToMany: [
      { name: 'tags', target: 'tags', through: 'postsTags',
        sourceKey: 'postId', targetKey: 'tagId' },
    ],
  },
  // ...tags and postsTags table definitions
});

sourceKey / targetKey are the junction columns referencing each side's primary key. Add references if the source side is keyed on something other than id.

Self-referential

A self-referencing foreign key (a column on a table that references that same table's primary key) introspects to both a belongsTo and a hasMany on the table. Categories with a parentId pointing at another category, comments threaded under a parent comment, an org chart — they all work the same way, including nested trees.

categories: {
  id: { type: 'serial', primaryKey: true },
  parentId: { type: 'bigint', references: 'categories.id' },
  name: { type: 'text', notNull: true },
},
// A category with its parent and its direct children
const category = await db.categories.findFirst({
  where: { id: 2 },
  with: { parent: true, children: true },
});
// category.parent is Category | null
// category.children is Category[]
 
// Walk a level deeper
const tree = await db.categories.findFirst({
  where: { id: 1 },
  with: { children: { with: { children: true } } },
});

When a table has a single self-referencing FK, Turbine auto-names the two relations after the table: the belongsTo takes the singular (category) and the hasMany takes the table name (categories). Rename them to parent / children in your code-first schema if you prefer — the examples above assume you have.

Back-references like posts -> user -> posts are allowed too — Turbine detects cycles by tracking the recursion path, not by refusing to revisit a table. The depth cap (10) is the guardrail.

Nested with — what's available at every level

At any level inside a with clause you can pass the same options findMany accepts (except pagination semantics differ — see below):

await db.users.findMany({
  with: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      limit: 5,
      select: { id: true, title: true, createdAt: true },
      with: {
        comments: {
          where: { flagged: false },
          orderBy: { createdAt: 'asc' },
          limit: 20,
        },
      },
    },
  },
});
  • where — applies to the relation rows, not the parents.
  • orderBy + limit — applied per parent via an inner subquery wrapping. LIMIT 5 means "5 posts per user," not "5 posts total."
  • select / omit — either one, not both. Picks/drops columns at this level.
  • Further with — recurses. Depth cap is 10; beyond that Turbine throws CircularRelationError (TURBINE_E007) with the full path trail.

Relation filters on the parent

Filter parents by their relations without loading them. some / every / none:

// Users who have at least one published post
await db.users.findMany({
  where: { posts: { some: { published: true } } },
});
 
// Users where every post is published
await db.users.findMany({
  where: { posts: { every: { published: true } } },
});
 
// Users with no posts at all
await db.users.findMany({
  where: { posts: { none: {} } },
});

These compile to EXISTS / NOT EXISTS subqueries — no relation data is returned, the join is pure filter.

Counting and ordering by relations

You don't always need the related rows — sometimes you just need how many. Add _count to a with clause to get a count per to-many relation without loading the children:

const users = await db.users.findMany({
  with: { _count: { posts: true } },
});
users[0]._count.posts; // 12

You can also order a query by a relation — { posts: { _count: 'desc' } } for a to-many count, or { author: { name: 'asc' } } for a to-one target column. Both are covered in the API reference: relation _count and ordering by a relation.

Payload size — when to prefer streaming

The json_agg strategy materializes the full object graph in Postgres memory before serializing it over the wire. That's fine for bounded queries. It's not fine for unbounded ones.

Rule of thumb: if the root limit is absent or > ~10k rows, or if a nested with has no limit on a hasMany, stop and reach for findManyStream instead.

// Bad — materializes the whole users table + all their posts + all their comments in Postgres RAM
const all = await db.users.findMany({
  with: { posts: { with: { comments: true } } },
});
 
// Good — streams parents, loads relations per batch
for await (const user of db.users.findManyStream({ batchSize: 500 })) {
  const posts = await db.posts.findMany({
    where: { userId: user.id },
    with: { comments: true },
  });
  // ...process
}

Concrete numbers on a seeded dataset (5K users, 46K posts, 432K comments): the unbounded nested findMany above builds a ~180 MB JSON payload server-side before sending a byte. Postgres happily does it. Your Lambda's 512 MB budget does not.

Nested with on the streaming API works too — Turbine opens a DECLARE CURSOR for the parent query and runs the nested subqueries per-batch:

for await (const user of db.users.findManyStream({
  with: { posts: { limit: 5 } },
  batchSize: 500,
})) {
  console.log(user.posts[0]?.title);
}

belongsTo loading without extra JOINs

Turbine emits a single correlated subquery per relation, not a JOIN. This means a belongsTo with 1,000 parents doesn't cartesian-explode against the child table:

// 1,000 posts, each with its 1 author — one SQL statement, one pass over posts, one lookup per row
await db.posts.findMany({
  limit: 1000,
  with: { user: true },
});

The generated SQL for with: { user: true } looks like:

SELECT "posts".*,
  (SELECT json_build_object('id', t0."id", 'email', t0."email")
     FROM "users" t0
     WHERE t0."id" = "posts"."user_id"
     LIMIT 1) AS "user"
FROM "posts"
LIMIT 1000

If you'd rather have a JOIN (e.g. for a reporting query with a GROUP BY), drop to db.raw — Turbine's opinion on relation loading is correlated subqueries; reporting queries have different shape.

Load strategy: join vs batched

The correlated-subquery default (relationLoadStrategy: 'join') resolves an entire with tree in one SQL statement. It's the right default: a single round-trip, and an index seek per parent when the child FK is indexed. Two situations favor the alternative:

  • A child FK column is unindexed. A correlated probe per parent becomes N-parents × full-table-scan. Run npx turbine doctor to find these — but if you can't add the index yet, batched loading pays that missing index only once.
  • Huge result sets. Nested JSON (json_build_object per row, re-serialized inside json_agg) is heavier to encode and decode than flat rows.

Opt in per query, or set a client-wide default:

// Per query
const users = await db.users.findMany({
  with: { posts: true },
  relationLoadStrategy: 'batched',
});
 
// Or as the default for every findMany/findFirst/findUnique
const db = turbine({
  connectionString: process.env.DATABASE_URL,
  relationLoadStrategy: 'batched',
});

'batched' runs the base query without json_agg subqueries, then issues one flat follow-up query per relation (WHERE fk = ANY($1), chunked at 1,000 keys) and stitches the children onto the parents in memory. D relation levels cost D extra round-trips instead of one, but each is a single indexed key-set lookup and the rows come back flat.

The result is deep-equal to the join strategy — same shape, same camelCase keys, same Date coercion — so you can flip the flag without touching the rest of your code. It honors per-relation where / select / omit / orderBy and nested with. The per-relation limit is applied client-side per parent (a LIMIT on a batched = ANY($1) would cap total children, not children-per-parent). And it's transaction-safe: the follow-up queries run on the same pinned connection, so batched loads inside $transaction see the transaction's own writes.

Note. Composite-key relations aren't supported by 'batched' — use the default 'join' strategy for those.

Lean JSON encoding

With the join strategy, each nested row is a json_build_object('id', …, 'title', …) — every key name repeats in every row of every relation. For wide relations over large result sets that repetition dominates the wire payload. jsonEncoding: 'positional' (Postgres-only) drops the keys:

const db = turbine({
  connectionString: process.env.DATABASE_URL,
  jsonEncoding: 'positional',
});

Relation subqueries then emit json_agg(json_build_array(…)) — a key-less array per row. Turbine knows the column order at build time, so it maps positions back to keys when parsing. The parsed output is byte-identical to the default 'object' encoding; only the wire format changes.

Measured on a 14-column hasMany relation: 39% fewer wire bytes and ~13% faster end-to-end findMany. The win grows with column count and result size. It composes with everything — select / omit, ordered and limited relations, hasOne / belongsTo, many-to-many, nested trees. (relationLoadStrategy: 'batched' bypasses it entirely — there's no JSON aggregation on that path.) Default is 'object', byte-unchanged.

See also