perf(orm): memoize implicit m2m join-table and model lookups (#2715)

[abetss]

Fixes #2715.

Summary

PolicyHandler.resolveManyToManyJoinTable(tableName) performed an O(models × fields) scan of the entire schema on every call — and it's called once per table per (sub)query while building policy filters (plus on the mutation/validation path). For a regular non-m2m table the scan runs to completion and returns undefined, so every query pays a full schema scan per referenced table. On a CPU profile of a deep, policy-filtered read this (getModelPolicyFilterForManyToManyJoinTable) was ~29% self-time, with getModel (16%) close behind; the database itself was 0.3%.

The resolution is a pure function of (schema, tableName), and the schema is immutable for a client's lifetime. This PR memoizes it.

What changed

Rather than caching inside the policy plugin, the structural lookups are memoized once in @zenstackhq/orm's QueryUtils, keyed by the immutable schema, so every consumer (ORM query building + all plugins) shares them:

• query-utils.ts: a WeakMap<SchemaDef, …> index memoizing getModel and getManyToManyRelation, plus a new O(1) getManyToManyJoinTable(schema, tableName) that builds a Map<joinTable, endpoints> in a single pass on first use.
• policy-handler.ts: resolveManyToManyJoinTable is now a thin O(1) lookup into that index (the per-call scan is gone). The descriptor it returns is unchanged.

Typed throughout (no any/as).

Why it's access-control-safe

resolveManyToManyJoinTable reads only schema.models + the table name and returns a structural descriptor ({firstModel, firstField, firstIdField, …}). It takes no $auth/per-request state and mutates nothing. The per-user policy filter is still built fresh, per call, downstream in getModelPolicyFilterForManyToManyJoinTable from that descriptor + auth(). So the cache cannot change any access-control outcome — it only skips recomputing a deterministic lookup.

What this deliberately does not cache (to stay forward-compatible):

• the compiled policy filter node — left per-call, so the newly-created-side skip from Many-to-many update check failing with connect #2531 and future field-level relation policies ([Feature Request] Consider using field-level access policies to control permissions for implicit many-to-many relation #856, [Feature Request] Field-level access overrides for implicit relations #2382) keep working;
• anything auth()/value-dependent.

The WeakMap is keyed by the schema object, so the cache dies with the schema (no leak, no cross-schema collision), and the negative (undefined) result is cached too — which matters because non-m2m tables are the dominant call pattern.

Benchmarks

Deep, policy-filtered read on a ~114-model schema (Bun 1.3.2 / JavaScriptCore; medians). Bun is the worst case for this JS-bound path; Node/V8 shows the same ranking at ~¼ the magnitude.

scenario	before	after	Δ
warm deep read (reused client)	92.7 ms	10.5 ms	−89%
cold deep read (chain rebuilt per call)	136.8 ms	31.6 ms	−77%
warm PK lookup	6.9 ms	0.65 ms	−91%
cold PK lookup	42.7 ms	14.9 ms	−65%
concurrency c=8 (wall)	698 ms	46.5 ms	−93%
concurrency c=16 (wall)	1380 ms	87 ms	−94%

The PK-lookup wins (which touch no m2m code at all) come from memoizing getModel — that's why the index lives in QueryUtils rather than the policy plugin.

A sanitized CPU profile (.cpuprofile) backing the original 29%/16% attribution is available — happy to attach or gist it.

Tests

• New tests/regression/test/issue-2715.test.ts guards the memoization (resolution is correct for two-model, self-relation, explicit @relation name, and multiple relations; repeat resolution returns the same cached descriptor; negative results are cached). Verified it fails if the memoization is removed.
• Existing m2m + policy e2e suites (relation/many-to-many, policy/migrated/relation-many-to-many-filter, connect-disconnect, self-relation) pass unchanged — the behavior-preservation gate.

Questions for maintainers

1. Where should the index live? I put it as a module-level WeakMap<SchemaDef, …> inside QueryUtils. If you'd prefer no module-level state, it could hang off the long-lived client/schema object instead — happy to move it.
2. Scope: I included getModel/getManyToManyRelation memoization alongside the join-table fix because the profile and the PK-lookup numbers show getModel is the larger lever. I can split it out if you'd rather keep this PR to resolveManyToManyJoinTable only.

Summary by CodeRabbit

• Performance Improvements

  • Model resolution queries now execute faster
  • Many-to-many relationship handling optimized for improved performance

• New Features

  • Enhanced support for implicit many-to-many join table discovery and resolution

• Tests

  • Added regression test for many-to-many join table functionality

[coderabbitai]

No actionable comments were generated in the recent review. 🎉

ℹ️ Recent review info

⚙️ Run configuration

Configuration used: Path: .coderabbit.yaml

Review profile: CHILL

Plan: Pro

Run ID: c5051926-8d6d-4681-83ca-f5226cb86f6d

📥 Commits

Reviewing files that changed from the base of the PR and between 897df21 and 16a05ab.

📒 Files selected for processing (3)

• packages/orm/src/client/query-utils.ts
• packages/plugins/policy/src/policy-handler.ts
• tests/regression/test/issue-2715.test.ts

---

📝 Walkthrough

Walkthrough

Adds WeakMap-based per-schema memoization in query-utils.ts for model name resolution and many-to-many relation metadata. Introduces a new ManyToManyJoinTableEndpoints interface and getManyToManyJoinTable function that precomputes a join-table-name → endpoints map in one schema pass. Refactors resolveManyToManyJoinTable in policy-handler.ts to use this O(1) index. Adds a regression test for issue #2715.

Changes

M2M Join Table Memoization

Layer / File(s)	Summary
Schema cache infrastructure and getManyToManyJoinTable index packages/orm/src/client/query-utils.ts	Introduces a WeakMap-keyed per-schema cache for getModel (case-insensitive) and M2M relation metadata. Adds the ManyToManyJoinTableEndpoints interface and getManyToManyJoinTable, which builds a join-table-name → endpoints map by scanning all model fields once per schema and caches it for O(1) subsequent lookups.
Policy handler resolveManyToManyJoinTable refactor packages/plugins/policy/src/policy-handler.ts	Replaces the nested O(models × fields) scan with a single QueryUtils.getManyToManyJoinTable call. Adds early-return on undefined, sorts the two endpoints with manyToManySorter, validates each side has exactly one ID field via invariant, and returns the same { firstModel, firstField, firstIdField, secondModel, secondField, secondIdField } shape.
Regression test tests/regression/test/issue-2715.test.ts	Vitest test covering plain two-model M2M resolution, self-relation with explicit @relation name, memoization reference equality on repeated calls, distinct cached results per relation, and undefined for non-M2M or unknown table names.

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~20 minutes

Poem

🐇 Hop hop, no more loops to dread,
A WeakMap cache was built instead!
Each join table found in just one pass,
The CPU scan becomes the past.
O(1) lookups, swift as spring —
This bunny fixed the bottleneck thing! 🌸

🚥 Pre-merge checks | ✅ 5

✅ Passed checks (5 passed)

Check name	Status	Explanation
Description Check	✅ Passed	Check skipped - CodeRabbit’s high-level summary is enabled.
Title check	✅ Passed	The title 'perf(orm): memoize implicit m2m join-table and model lookups (#2715)' accurately reflects the main changes: adding memoization for many-to-many join-table and model lookups in the ORM layer, addressing issue #2715.
Linked Issues check	✅ Passed	The PR comprehensively addresses all objectives from issue #2715: implements memoization for getModel and getManyToManyRelation (eliminating repeated full-schema scans), adds O(1) getManyToManyJoinTable for join-table lookups, refactors resolveManyToManyJoinTable to use the new index, maintains backward compatibility, and includes regression tests.
Out of Scope Changes check	✅ Passed	All changes are directly aligned with issue #2715's objectives: query-utils.ts adds memoization infrastructure, policy-handler.ts refactors to use the new lookup, and the test file validates the new functionality with no unrelated modifications.
Docstring Coverage	✅ Passed	No functions found in the changed files to evaluate docstring coverage. Skipping docstring coverage check.

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

✨ Finishing Touches

🧪 Generate unit tests (beta)

• Create PR with unit tests

Warning

There were issues while running some tools. Please review the errors and either fix the tool's configuration or disable the tool if it's a critical failure.

🔧 ESLint

If the error stems from missing dependencies, add them to the package.json file. For unrecoverable errors (e.g., due to private dependencies), disable the tool in the CodeRabbit configuration.

ESLint install failed: private package registry requires authentication. Disable ESLint in CodeRabbit settings or use public packages.

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}