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.

Writing links: connect, disconnect, set#

Since 0.50 a many-to-many relation also accepts nested writes for the three operations that only ever touch the junction table. Turbine writes the junction rows in the same transaction as the parent write, on every engine including PowDB.

// Link on create
await db.posts.create({
  data: { title: 'Single-query relations', tags: { connect: [{ slug: 'sql' }] } },
});
 
// Link, unlink, or replace on update
await db.posts.update({
  where: { id: 1 },
  data: { tags: { connect: { slug: 'indexes' }, disconnect: { slug: 'sql' } } },
});
 
await db.posts.update({
  where: { id: 1 },
  data: { tags: { set: [{ slug: 'postgres' }] } }, // becomes the post's only tag
});

connect is idempotent (an already-linked target is a no-op, not a duplicate and not a constraint error), disconnect is scoped by both the parent key and the named targets, and set: [] clears every link. disconnect and set are update-only, as on every other relation type.

The operations that would also have to write the target row (create, connectOrCreate, update, upsert, delete) still throw ValidationError (E003): there is no safe default for a junction's extra payload columns. Composite junction keys are refused for the same reason. See Nested writes for the full rules and the escape hatch.

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-array order is not guaranteed. Without an explicit orderBy, a with relation comes back in whatever order the loader produces, json_agg injects no ORDER BY, and the 'auto' / 'batched' load strategies can order the same rows differently again. If any code depends on child order, add orderBy to that with block, or opt into the client-wide stableRelationOrder flag (a per-relation orderBy always wins over it).

That is about the order of rows inside a relation array, which is a different axis from the order of keys on the returned object. Key order is deterministic on its own, with or without stableRelationOrder: the 'batched' and 'auto' strategies run their follow-up statements concurrently, so completion order is a race, and every relation key and every _count entry is therefore seeded up front in the order the join plan emits it, before any load runs. The same query serializes to the same JSON under every strategy.

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: auto, join, batched, flatten#

When you don't set relationLoadStrategy, Turbine uses 'auto' (since 0.41.0): it compiles the single-statement correlated-subquery plan ('join') and moves individual relations to the batched loader when one of two rules fires. Everything else stays in the join, byte-identical. An explicit 'join', 'batched' or 'flatten' (client-wide or per query) always wins over 'auto', and the results are deep-equal whichever plan runs.

The strategies also agree about whether a query is valid, independent of data (0.65). The batched loader used to skip validating the with tree when the base query matched no rows, so a misspelled relation field could throw in production and pass in an empty test database; now the whole tree is validated on every strategy even for zero-row results, with the same error codes. A seeded differential fuzz suite runs the same random queries through both plans on every release and nightly, asserting row equality and accept/reject agreement.

"Potentially large" means the query has no limit / take (and no client defaultLimit), or a limit above autoToOneJoinMaxRows (default 1000). findUnique and findFirst never trip the cardinality rule: their parent set is one row.

The unindexed rule needs index metadata that came from the database, so a defineSchema-only client can never prove a probe unindexed and that rule simply never fires. The cardinality rule does not depend on index metadata and applies either way.

Both rules only ever move a relation that the batched loader can actually handle. Composite-key relations, and relations whose subtree the loader cannot express, always stay in the join. Engagement is visible: a once-per-relation dev note naming the reason, and query events carry strategy: 'auto-batched'.

Relation _count follows the unindexed rule only, but on its own size rule, and that rule is far more aggressive than the two above.

The reason is the shape of an inline _count. It is not a grouped scan. It compiles to a correlated scalar COUNT(*) subquery that the database re-evaluates once per parent row, so with no covering index on the child's correlation column you pay one full child-table scan per parent. The batched follow-up is the grouped form (COUNT(*) ... GROUP BY fk) and pays that scan once for the whole page. That is the difference, and it compounds linearly with the parent count:

Measured on a 200,000-row child table with an unindexed FK. EXPLAIN (ANALYZE, BUFFERS) at 30 parents reads 50,013 buffers inline against 1,727 batched, with loops=30 on the child scan and Rows Removed by Filter: 199980 on each one.

So auto moves an unindexed _count to the follow-up statement from two parent rows upward. Only a parent set provably bounded at one row keeps it inline: findUnique, findFirst, and any query carrying limit: 1 / take: 1. autoToOneJoinMaxRows does not influence _count at all.

Two ways to keep _count in the single statement: add the covering index the correlation needs (npx turbine doctor names it, which is the fix worth making either way), or pin relationLoadStrategy: 'join' on that query.

Tuning note. The two cardinality/indexing rules above are heuristics: the real parent count is unknown until the base query runs, and limit is the only bound available at plan time. If profiling disagrees with the choice, raise or lower autoToOneJoinMaxRows (which governs the to-one rule only), or pin relationLoadStrategy on that query. On endpoints that fan out to very large child sets (megabyte-scale JSON per request), the batched loader's flat rows can beat the join plan even with healthy indexes and a small parent set, so 'batched' is still worth pinning there.

The correlated-subquery strategy (relationLoadStrategy: 'join') resolves an entire with tree in one SQL statement: 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 32,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 'join' strategy for those ('auto' never batch-falls-back on a composite-key relation). A _count nested inside a with is refused on every strategy for the same reason it always was on 'join': the two must not disagree about whether a query is valid, since under 'auto' which one runs is decided by a cost heuristic.

Because the stitching happens in memory, the loader needs each level's correlation key present in the rows it was handed. It adds those keys to the projection itself and strips them again afterwards, so a select or omit that removes an FK is fine at any depth (fixed in 0.63 for keys below the first level, where the relation previously came back empty). If a correlation key is ever missing from every parent row, the loader now throws UnsupportedFeatureError (TURBINE_E017) naming the relation rather than returning an empty one, since an empty relation is indistinguishable from a true absence at the call site.

'flatten': to-one relations as a LEFT JOIN#

relationLoadStrategy: 'flatten' compiles an eligible to-one relation into a LEFT JOIN in the same statement, instead of a correlated subquery. One round-trip, no per-parent re-evaluation, and no client-side stitching.

const posts = await db.posts.findMany({
  with: { author: { with: { org: true } } },
  relationLoadStrategy: 'flatten',
});

The whole to-one subtree becomes one derived table, joined once:

SELECT "posts".*, f0."f0__id", f0."f0__name", f0."f1__code"
FROM "posts"
LEFT JOIN (
  SELECT 1 AS "f0__$k", f0s."id" AS "f0__$c0",
         f0s."id" AS "f0__id", f0s."name" AS "f0__name",
         (f1s."id" IS NOT NULL) AS "f1__$k", f1s."code" AS "f1__code"
  FROM "users" f0s
  LEFT JOIN "orgs" f1s ON f1s."id" = f0s."org_id"
  WHERE f0s."deleted" = $1
) f0 ON f0."f0__$c0" = "posts"."author_id"

Every column the join exposes is prefixed (f0__, f1__, …), so a child column can never collide with a parent column of the same name. The $k columns are match discriminators (deliberately value-free, so a PII-tagged key column never reaches the wire), and the $c correlation columns are used only in the outer ON and are never handed to you. Turbine reassembles the nested object client-side, and the result is deep-equal to the join strategy: same shape, same camelCase keys, same Date coercion.

Eligibility, and what silently falls back#

This matters more than usual, because an ineligible relation falls back to a correlated subquery without an error. You get correct results either way; you just may not get the plan you asked for.

A relation is eligible only when all of these hold:

• It is belongsTo or hasOne. Every to-many relation falls back.
• The target-side correlation columns are provably unique. That means an exact match against the target's primary key, a declared unique constraint, or a full, non-partial, non-expression unique index. Partial and expression indexes are refused, and a unique index on (a, b) does not prove (a): the proof is exact set equality, so a subset never qualifies.
• The relation has no limit and no orderBy (a to-one row has nothing to order or limit anyway).
• It contains no nested _count. A _count at the top level of the with is fine and stays a correlated COUNT(*).
• It is under the depth cap of 10, the same cap the subquery path uses.

Inside an eligible relation, all of this is supported: a relation where, target global filters, select / omit, to-one chains of arbitrary depth (each becomes a further inner join in the same derived table), self-relations, and a nested to-many, which stays a correlated subquery hanging off the joined node.

Fallback is per relation for the rules above: one ineligible relation in a with clause does not stop the others from flattening. Four conditions instead disable flattening for the whole query:

'flatten' works on PostgreSQL, MySQL and SQLite. PowDB has its own relation path and is unaffected.

Performance: better than 'join', not the fastest#

Measured on 9,200 parent rows against local PostgreSQL, as a speedup over the 'join' plan:

'flatten' beats 'join' and loses to 'batched'. The reason is structural, not a defect. With 2,000 distinct targets sitting behind 9,200 parents, the join transmits the target's columns 9,200 times, while the batched loader transmits 2,000 rows exactly once. As the cardinality approaches 1:1 that gap closes. Local round-trip time is also about 0.1 ms here, which structurally favors the batched loader's extra round-trip more than a real network would.

So the honest pitch for 'flatten' is: one round-trip, transaction-trivial, no client-side stitching, and strictly better than 'join' on large to-one parent sets. It is not the fastest plan. If raw throughput on a wide to-one fan-in is what you want and a second round-trip is acceptable, 'batched' is still faster. That is also why 'flatten' is deliberately not wired into 'auto'.

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.

SQL template cache size#

Repeated queries of the same shape reuse cached SQL text instead of rebuilding it. sqlCacheSize bounds the per-table LRU template cache:

const db = turbine({
  connectionString: process.env.DATABASE_URL,
  sqlCacheSize: 2000,
});

Values are parameterized ($1, $2, …) and never fragment the cache, so this bounds distinct query shapes. The default is 1000: raise it for apps with a very large surface of query shapes to lift the hit rate at the cost of memory, or lower it to cap memory. sqlCacheSize: 0 disables caching entirely (identical to sqlCache: false).

The generic-plan cliff: planCacheMode#

This one is not about relations, but it has the same shape as the strategy rules above: a condition you can state, a mechanism that explains it, a fix, and an escape hatch. It is also the tuning knob people reach for last, because the symptom looks like nothing you did.

The condition#

A query is fast the first few times you run it and then, with no deploy, no data change and no different arguments, becomes orders of magnitude slower and stays that way for the life of the connection. Restarting the process fixes it until it happens again. It typically shows up on one tenant, one customer or one account, while the same endpoint stays fast for everyone else.

You are exposed when all of these hold:

• You are on PostgreSQL 12 or newer with named prepared statements on. Turbine defaults preparedStatements: true on a pool it owns.
• The statement carries a predicate whose selectivity swings by orders of magnitude across the bound values. The canonical case is a shared multi-tenant table with a tenant_id equality, where one tenant owns a few hundred rows and another owns most of the table.
• The statement runs at least six times on the same connection.

The mechanism#

PostgreSQL caches the plan for a named prepared statement. For the first five executions it builds a custom plan: it re-plans using the actual bound values, so a sparse tenant_id gets an index seek and a dense one gets a scan. From the sixth execution the backend may switch to a generic plan, planned once with no knowledge of the values, and compare its cost against the average custom-plan cost. If the generic plan wins that comparison it is kept, and the statement never reverts, for as long as the connection lives.

A generic plan is not wrong on average, which is the trap. It is wrong for the values that are not average. If the planner assumes the "typical" tenant, the sparse tenant inherits a plan built for a dense one.

The measured shape, on a 358,000-row table with skewed tenants, one connection, ordered-with-limit lookup for the sparse tenant. Execution times in milliseconds:

The cliff lands exactly on execution six, and 'force_generic_plan' reproduces it from execution one, which is what confirms the mechanism rather than a cache-warming coincidence. The same query against a dense tenant is flat at ~0.2 ms in every row of that table: the generic plan is the right plan for that one.

How to diagnose it#

Three checks, in increasing order of effort.

1. The one-line test. On a session that reproduces the slowness, run the statement six or more times, then:

SET plan_cache_mode = force_custom_plan;

and run it again on that same session. If the query snaps back to its first-execution speed, you have found it. SET plan_cache_mode = auto; puts it back.

2. The backend's own counters. pg_prepared_statements tracks the split per statement, so you do not have to infer it from timings:

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

In the run above, the default gave custom_plans = 5 with generic_plans climbing after that; force_custom_plan gave custom_plans = 9, generic_plans = 0. A statement with a growing generic_plans and a stalled custom_plans is one that has been promoted.

3. EXPLAIN, on the prepared statement, not on the literal SQL. EXPLAIN SELECT … with your values written in gives you a custom plan by construction and will look fine. Prepare the statement and EXPLAIN (ANALYZE, BUFFERS) EXECUTE … it instead, six times, and compare the plan before and after promotion. What you are looking for is the selective predicate moving out of the index condition and into a filter. In the run above:

• Custom plan: index-only scan on the tenant index with a top-N sort, estimated cost 25.34, actual 0.14 ms.
• Generic plan: a backward index scan on the ordering column with tenant_id demoted to a filter, estimated cost 3.32 (cheaper on paper, which is why it won), actual 461 ms, with Rows Removed by Filter: 357550.

A large Rows Removed by Filter on a column you have an index for is the signature.

The fix#

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

'force_custom_plan' tells the backend to re-plan on every execution, so the plan always sees the values. You pay the planning cost each time (microseconds for a simple statement, and the parse is still cached), and you get rid of the cliff.

Turbine applies it as a connection parameter (options=-c plan_cache_mode=…) as the pool opens a connection, not as a SET issued after checkout. It is therefore in force for that connection's very first statement, for every later checkout, $transaction, stream and pipeline on it, and it cannot race your first query. An existing PGOPTIONS or a ?options=… already on the connection string is appended to, never replaced. Unset (the default) Turbine sends nothing at all, and behaviour is byte-identical to previous versions.

The other two values are diagnostic more than prescriptive: 'auto' states the backend default explicitly, and 'force_generic_plan' is how you prove the pathology exists (or measure the opposite case, a statement where planning cost dominates and the generic plan is genuinely the one you want).

What this actually reaches in Turbine#

The numbers above come from a named prepared statement over the client's own pool, and the option demonstrably reaches every connection the client hands out.

What is actually true, measured on PostgreSQL 16 with synchronize_seqscans off and parallelism disabled. Both fixtures are described here so you can rebuild them.

The substituted defaults. 400,000 rows, k = id % 104 so n_distinct is exactly 104, a btree on k, under force_generic_plan:

A generic plan substitutes a default for every value it cannot see, and each unknown alone can flip the plan shape. Two conditions on the 10% are easy to miss: it clamps at one row, so it is not always an overestimate, and an unknown OFFSET triggers the same substitution on its own even when the limit is a constant. LIMIT 20 OFFSET $2 estimated its 20 rows correctly but costed a 385-row prefix as startup, and picked a different plan shape from the same query with no offset. Turbine binds both (… LIMIT $2 OFFSET $3), so a paginated read has no constant-limit escape, and in that fixture the constant-limit form was the one that chose a seq scan anyway.

What actually goes wrong, and when. Two 200,000-row tables joined on an indexed key, with a predicate matching 190,000 rows for one value and one row for the rest (n_distinct sampled at roughly 1,600, an ANALYZE estimate that varies on rebuild), for SELECT count(*) FROM j JOIN jc ON jc.j_id = j.id WHERE j.k2 = $1. No LIMIT, no OFFSET, no ORDER BY anywhere:

• custom plan: hash join, 1,770 shared buffers
• generic plan: nested loop (the ~1,600-way estimate makes 190,000 inner lookups look cheap), 761,002 shared buffers, a 430x difference
• and it is promoted under the default plan_cache_mode = auto: pg_prepared_statements reports generic_plans = 2, custom_plans = 5 after seven executions

That inverts the ranking the old text gave, and it is the practical takeaway:

• The sixth execution is a ceiling, not a trigger. auto promotes only when the generic plan's estimated cost is not worse than the average custom cost. Check pg_prepared_statements.generic_plans to see whether a statement is actually on a generic plan; plenty never are.
• The shape that gets promoted unprompted has no limit. In the same session, the limited form of that predicate was never promoted at all (generic_plans = 0, custom_plans = 8), because its substituted row count made the generic plan look more expensive. count() and an unlimited findMany both compile to the pure parameterized-predicate shape that promotes on its own, so look at those first.
• A limited findMany gives the planner two unknowns instead of one, the predicate value and the limit count, and either alone can flip the plan shape. More unknowns is not the same as more damage.
• Neither an ORDER BY nor any limit is required for a generic plan to be catastrophically worse than a custom one. The 430x case above has none of them, and neither does the inverted case further down this page.
• But in practice, ORDER BY is still the strongest single predictor. That is not a contradiction, and 0.55.0's wording overcorrected by giving only the refutation. In a table-by-table sweep of a multi-tenant schema, every divergent shape measured was WHERE tenant = $1 ORDER BY id ASC LIMIT $2, and every shape without an ordering measured 1.00x. The reason is mechanical: an ORDER BY on a different indexed column hands the planner a second plan it can run away with, and a generic estimate on the wrong side of that boundary is what makes it take it. So: not necessary in general (do not conclude your unordered reads are safe), and the first place to look in practice. doctor's divergence check models exactly this shape for that reason, and says so.
• implicitPkOrdering is off by default in core, so a default findMany({ where, limit }) emits SELECT … WHERE … LIMIT $2 with no ORDER BY at all. turbine-orm/prisma-compat defaults it on. Switching it on adds an ordering a generic plan can walk the whole table in, which is worth knowing, but it is your choice to make and nothing about it changes here.

So treat planCacheMode as a targeted remedy for a statement you have measured getting slower after its fifth execution, not as a general speed-up, and measure with plan_cache_mode = force_generic_plan against force_custom_plan rather than reasoning about which query shapes ought to be safe. Setting it globally on a workload with no skew buys you nothing and costs you a re-plan per execution. One measurement hazard: synchronize_seqscans is on by default and makes a repeated seq scan resume where the last one stopped, which reported an 8,000-buffer scan as 4 buffers until it was turned off.

A per-query lever: forceCustomPlan#

planCacheMode is a connection parameter, so it cannot express custom here, auto there. Since 0.56.0 the read args carry a per-query opt-in that can:

const rows = await db.orders.findMany({
  where: { tenantId },
  orderBy: { id: 'asc' },
  limit: 20,
  forceCustomPlan: true,
});

It is available on findMany, findUnique, findFirst (and the OrThrow forms), count, aggregate, groupBy, the streaming read, and the batched strategy's relation follow-ups. It is a read arg: writes do not take it.

How it works, stated as the mechanism really is. true sends that one statement unnamed. It is not true that PostgreSQL treats an unnamed statement as a one-shot plan that never enters the plan cache; the backend builds and saves a CachedPlanSource for it too. It works one level up, in the driver: node-postgres only skips Parse for a statement it has already parsed by name, so an unnamed statement is re-parsed on every execution, each Parse replaces the unnamed cached plan source with a fresh one whose custom-plan counter is zero, and the five-execution threshold that precedes promotion is never reached. No GUC, no SET LOCAL, no transaction, no extra round trip.

Precedence, because one of these is the opposite of what the mechanism suggests.

The refusal is not pedantry. That setting governs the unnamed statement as well as a named one, measured: five executions of the same unnamed statement read 19,107 buffers with it in force and 55 with the same connection set back to auto. Withholding the name buys nothing against it, so Turbine names both settings and asks you to change one rather than reporting a guarantee the next execution breaks. Turbine can only see the setting it applied: a plan_cache_mode installed by your own SET, by ALTER ROLE, or by a pooler is invisible here and is not refused.

Omitting it, or false, is byte-identical to previous versions and does not opt back out of a client-level setting. With preparedStatements: false every statement is already unnamed, so it is a no-op for plan choice. On SQLite, MySQL, SQL Server and PowDB it throws UnsupportedFeatureError (TURBINE_E017): an engine with no PostgreSQL plan cache cannot make this guarantee.

What it costs. Planning happens on every execution instead of once. On a flat read that is in the noise, and an unnamed statement also skips the extra round trip a named one needs on its first execution. It grows with the size of the statement: a deep with tree is a much larger plan to rebuild each time. Turn it on where a flip is the risk, not everywhere.

A custom plan is not automatically the better plan#

This is the counterexample, and it is here so nobody reads force_custom_plan as strictly safe. Reproduced on PostgreSQL 16.14 with synchronize_seqscans off and parallelism disabled:

CREATE TABLE ev (id bigserial PRIMARY KEY, tenant_id int NOT NULL, pad text);
 
-- head of the heap: 320,000 rows over 799 small tenants, in RANDOM physical order
INSERT INTO ev (tenant_id, pad)
  SELECT t, repeat('x', 60)
  FROM (SELECT ((g % 800) + 1) AS t FROM generate_series(1, 320000) g
        ORDER BY random()) s
  WHERE t <> 400;
 
-- tail of the heap: the dense tenant's 80,000 rows, inserted LAST
INSERT INTO ev (tenant_id, pad)
  SELECT 400, repeat('x', 60) FROM generate_series(1, 80000) g;
 
CREATE INDEX ev_tenant_idx ON ev (tenant_id);
ANALYZE ev;   -- relpages 5334, n_distinct 800, correlation 0.004
 
PREPARE q(int, int) AS SELECT * FROM ev WHERE tenant_id = $1 LIMIT $2;

60x, with no ORDER BY anywhere. The custom planner knows tenant 400 is 20% of the table, so with LIMIT 20 it prices a sequential scan as nearly free on the assumption it will stop almost immediately. It is right about how many rows match and wrong about where they are: they are all at the end of the heap, so it reads 319,600 non-matching rows first. The generic plan, unable to see the value, estimates 500 rows, takes the bitmap path, and touches one heap block. Re-insert the identical rows in random physical order and the effect vanishes and reverses (custom 2 buffers, generic in the seventies, the exact figure moving with the index leaf-page count on each rebuild): the variable is physical clustering, not selectivity.

The practical consequence is the same either way: this is why forceCustomPlan is per query and why planCacheMode: 'force_custom_plan' is not a blanket recommendation.

When to reach for this#

Recommended, now that five divergent cells across four tables have been measured on one real schema: a multi-tenant reader on a shared table with a skewed tenant column, paginating with ORDER BY <pk> LIMIT $n. That is the shape that diverges, that doctor detects, and that forceCustomPlan fixes without touching anything else. Run turbine doctor, confirm with pg_prepared_statements.generic_plans and the EXPLAIN pair it prints, and scope the option to the reads it named.

Not recommended as a global setting. Setting planCacheMode client-wide on a workload with no skew buys nothing, costs a re-plan per execution, and on the shape above it is the wrong direction outright.

The blunter alternative: preparedStatements: false#

const db = turbine({
  connectionString: process.env.DATABASE_URL,
  preparedStatements: false,
});

With prepared statements off, Turbine submits { text, values } instead of { name, text, values }. There is no named statement, so there is no cached plan to promote, so the cliff cannot happen. It is also the setting you already need behind a transaction-pooling proxy.

Be clear about what it costs. The plan cache is not the only thing you give up: the backend re-parses and re-plans every execution of every statement, for the whole application, not just the skewed one. planCacheMode: 'force_custom_plan' gives up the plan half and keeps the parse half, and only where you asked for it. Reach for preparedStatements: false when you want it for the pooler reason anyway, or as a fast global mitigation while you find the statement, and prefer planCacheMode as the durable fix.

Three scope limits#

• External pools. Turbine never opens those connections, so the option is a no-op there, with a dev-mode warning. Set the GUC in the driver's own connection setup instead. Turbine-owned string replicas on that same client are Turbine's connections and do get it, which splits the policy between reads and writes, so the warning names them when they exist.
• Postgres wire-compatible engines. The capability flag speaks for the dialect, not for the server. CockroachDB, YugabyteDB and pre-12 PostgreSQL run through the default Postgres dialect and have no plan_cache_mode, so they reject the connection parameter itself with unrecognized configuration parameter rather than raising TURBINE_E017. Leave it unset there. A non-Postgres engine (SQLite, MySQL, SQL Server, PowDB) does throw UnsupportedFeatureError (TURBINE_E017) at construction, and any value outside the three throws ValidationError (TURBINE_E003).
• Connection poolers. The GUC travels as a connection-time startup parameter, and a pooler may refuse to pass it through (PgBouncer's ignore_startup_parameters). Set it on the role there instead: ALTER ROLE app_user SET plan_cache_mode = 'force_custom_plan';.

See also#

• API Reference, every operator and option in where, with, select.
• Transactions & Pipelines, batching and atomic groups.
• Serverless, why with depth matters more on the edge.
• Typed Errors, RelationError (E005) and CircularRelationError (E007).