[v8] .include() joins only the first column of a composite foreign key, silently returning wrong rows

[Arbitriom]

Summary

On @prisma/orm-postgres@8.0.0-rc.5, .include() builds its join predicate from
only the first column of a relation's foreign key. For relations whose FK
spans several columns, the emitted SQL correlates on that first column alone, so
the query returns every related row that shares it instead of the linked row.

Nothing is thrown. The query succeeds, in normal time, and returns well-formed,
plausible — but wrong — data.

The correct multi-column logic already exists in the same file and is used by
relation filters (.some() / .every() / .none()). Only .include() bypasses
it.

Reproduction

Minimal shape — a tenant-scoped schema where every FK carries the tenant column,
a common pattern for multi-tenant applications:

namespace public {
  model Tenants {
    id   Uuid   @id
    name String
    @@map("tenants")
  }

  model Customers {
    id       Uuid   @id
    tenantId Uuid   @map("tenant_id")
    name     String
    @@unique([tenantId, id])
    @@map("customers")
  }

  model Orders {
    id         Uuid @id
    tenantId   Uuid @map("tenant_id")
    customerId Uuid @map("customer_id")
    customers  Customers @relation(
      fields:     [tenantId, customerId],
      references: [tenantId, id]
    )
    @@unique([tenantId, id])
    @@map("orders")
  }
}

Given 11 orders, each pointing at a different customer:

const rows = await db.orm.public.Orders
  .select('id')
  .include('customers', (c) => c.select('id', 'name'))
  .all();

new Set(rows.map((r) => r.customers?.name)).size;
// expected: 11
// actual:    1   ← every order gets the same customer

The plain form .include('customers') — without the refinement callback —
behaves identically.

Emitted SQL

SELECT "orders"."id",
  (SELECT coalesce(json_agg(json_build_object('id', "customers__rows"."id", ...)), json_build_array())
     FROM (SELECT "customers"."id", "customers"."name"
             FROM "public"."customers"
            WHERE "customers"."tenant_id" = "orders"."tenant_id") AS "customers__rows") AS "customers"
FROM "public"."orders"
WHERE "orders"."tenant_id" = $1::uuid

The predicate "customers"."id" = "orders"."customer_id" is absent. The
subquery therefore returns all customers of the tenant; for an N:1 relation the
result is then unwrapped to the first element, which is why every parent row
receives the same child.

The contract is correct

contract infer emits both columns on both sides:

"customers": {
  "cardinality": "N:1",
  "on": {
    "localFields":  ["tenantId", "customerId"],
    "targetFields": ["tenantId", "id"]
  },
  "to": { "model": "Customers", "namespace": "public" }
}

Decisive evidence: the same relation, joined correctly by another path

Same contract, same relation, same session — only the API differs:

API	tenant_id correlated	customer_id correlated
.include('customers', …)	✅	❌
.where((o) => o.customers.some(…))	✅	✅

The relation-filter path emits the full predicate:

WHERE EXISTS (
  SELECT "customers"."tenant_id" AS "_exists" FROM "public"."customers"
   WHERE ("customers"."tenant_id" = "orders"."tenant_id"
      AND "customers"."id"        = "orders"."customer_id"))

So the library already resolves this relation correctly. .include() does not
use that resolution.

Root cause

In @prisma/orm-family-sql/dist/orm-client.mjs (8.0.0-rc.5), two join builders
coexist:

resolveIncludeRelation (~l. 211) — used by .include() (~l. 3627) — keeps
only index 0:

const localField  = relation.on.localFields[0];
const targetField = relation.on.targetFields[0];

and returns singular localColumn / targetColumn, which the two include join
sites (~l. 1603 and ~l. 1771) turn into a single equality:

const joinExpr = BinaryExpr.eq(ColumnRef.of(childTableRef, include.targetColumn), parentLocalRef);

buildJoinWhere (~l. 2704) — used by relation filters (~l. 2622) — handles
every column and ANDs the pairs:

const count = Math.min(localFields.length, targetFields.length);
for (let i = 0; i < count; i++) { /* … */ joinExprs.push(BinaryExpr.eq(/* … */)); }
return joinExprs.length === 1 ? joinExprs[0] : and(...joinExprs);

Note that the surrounding code is already plural-aware elsewhere: the
many-to-many path wraps in an array (include.through?.parentLocalColumns ?? [include.localColumn],
~l. 1452), and the write path iterates relation.localColumns (~l. 3146).

Suggested fix

Have resolveIncludeRelation return localColumns / targetColumns arrays, and
have the two include join sites build an AND of pairwise equalities — i.e.
reuse buildJoinWhere, or mirror it. The scope looks small: one resolver, two
call sites.

Impact

Beyond returning incorrect data, this is an authorization concern for
multi-tenant schemas that scope every relation by tenant. A parent row receives
all the tenant's related rows rather than its own — for instance a user's
"assigned records" include returns every record in the tenant. A nullable FK is
also affected: a parent whose FK column is NULL receives a fully populated
related object that corresponds to no actual link.

It is hard to notice: the types are satisfied (the object has the right shape,
it is merely the wrong row), nothing is raised, and a test asserting "the parent
has a related object" passes. Detecting it requires comparing against ground
truth in SQL.

Relations with a single-column FK are unaffected — which makes a quick
smoke-test on one of those misleading.

Documentation

Relations and joins in Prisma Next
lists three .include() limitations (implicit m-n, one-to-one field side,
refinement callback tested on PostgreSQL only). Composite foreign keys are not
mentioned, so the current behaviour is not documented as a restriction.

Minor, possibly separate: that page states "On PostgreSQL, Prisma 8 fetches
included relations with joins." The SQL above is a correlated subquery with
json_agg, not a join.

Environment

prisma (CLI)	8.0.0-rc.7
@prisma/orm-postgres	8.0.0-rc.5
@prisma/orm-family-sql	8.0.0-rc.5
@prisma/cli-engine	0.2.0
Database	PostgreSQL 17 (Supabase)
Node	24.18.0
TypeScript	5.9.3

A note on the reproduction

Observed and measured on a real 92-model schema (550 of its 728 relations are
composite), then reduced to the minimal shape above for this report. The
root-cause analysis and the two-paths comparison were performed against
8.0.0-rc.5 as published on npm.