joins

What it does

A collection can declare a foreign-key field with ref() — for example an invoice's clientId pointing at the clients collection. .join() is the query-builder method that resolves one of those declared references and attaches the target record onto the result under an alias you choose, so invoice.client.name is available without a second round trip. Joins run inside the hub, after decryption — the storage backend only ever sees ciphertext and never runs the query itself.

You want it for parent-record lookups that follow an existing reference: reports that need the client's name next to each invoice, multi-step navigation (invoice → client → region), or a UI pane that should re-render whenever either the invoice or its client changes. For pairing rows that have no declared reference (every worker × every pay period), use cross-join instead.

Joins are part of the core query builder today — there is no with*() factory to opt into and no bundle cost to avoid. The catalog reserves a dedicated @noy-db/hub/joins subpath and a withJoins() factory (~470 LOC) for a future extraction, but until that lands .join() is simply available on every collection.query().

Opt in

No opt-in is needed for .join() itself. Declare the reference when you create the collection, with ref():

import { createNoydb, ref } from '@noy-db/hub'
import { toMemory } from '@noy-db/to-memory'

type Client = { id: string; name: string; tier: 'standard' | 'premium' }
type Invoice = { id: string; amount: number; status: 'draft' | 'open' | 'paid'; clientId: string }

const db = await createNoydb({ store: toMemory(), user: 'alice', secret: 'a-strong-secret' })
const vault = await db.openVault('acme')

const clients = vault.collection<Client>('clients')
const invoices = vault.collection<Invoice>('invoices', {
  refs: { clientId: ref('clients') },
})

Usage

Single-FK join

query.join(field, { as }) resolves the declared reference and attaches the target record under as. toArray() is synchronous — it reads the already-decrypted in-memory cache, same as the rest of the query basics terminals:

await clients.put('cli-A', { id: 'cli-A', name: 'Acme Corp', tier: 'premium' })
await invoices.put('inv-1', { id: 'inv-1', amount: 1200, status: 'paid', clientId: 'cli-A' })

const enriched = invoices.query()
  .where('status', '==', 'paid')
  .join<'client', Client>('clientId', { as: 'client' })
  .toArray()
// → 1 row, shaped like:

[
  {
    "id": "inv-1",
    "amount": 1200,
    "status": "paid",
    "clientId": "cli-A",
    "client": { "id": "cli-A", "name": "Acme Corp", "tier": "premium" }
  }
]

The Right type parameter (Client above) is optional — omit it and the joined field types as unknown. .join() requires a query built via collection.query(); the join planner picks a nested-loop strategy when the target source supports lookupById (the normal case for a Collection target) or a hash strategy otherwise, auto-selected unless you force one with { strategy: 'hash' }.

Multi-FK chaining

Chain .join() calls to resolve more than one reference on the same left record — each call adds its own alias. A collection's refs are fixed at the point you first open it, so declare every FK you plan to join up front:

type Category = { id: string; label: string }
type FullInvoice = Invoice & { categoryId: string }

const categories = vault.collection<Category>('categories')
const fullInvoices = vault.collection<FullInvoice>('full-invoices', {
  refs: { clientId: ref('clients'), categoryId: ref('categories') },
})

await categories.put('cat-1', { id: 'cat-1', label: 'Consulting' })
await fullInvoices.put('inv-2', { id: 'inv-2', amount: 800, status: 'open', clientId: 'cli-A', categoryId: 'cat-1' })

const rows = fullInvoices.query()
  .join<'client', Client>('clientId', { as: 'client' })
  .join<'category', Category>('categoryId', { as: 'category' })
  .toArray()
// → [{ ..., client: { id: 'cli-A', name: 'Acme Corp', tier: 'premium' },
//      category: { id: 'cat-1', label: 'Consulting' } }]

Post-join filtering on either alias's fields uses the general-purpose .filter():

invoices.query()
  .join<'client', Client>('clientId', { as: 'client' })
  .filter(row => row.client?.tier === 'premium')
  .toArray()

Dangling references

ref() declares one of three enforcement modes — strict (default), warn, cascade — covered in full on Foreign refs. .join() respects whichever mode the field was declared with when a left record's FK value points at a target id that no longer exists:

• strict — throws DanglingReferenceError.
• warn — attaches client: null and emits a one-shot console.warn per unique dangling (field, target, id) triple.
• cascade — attaches client: null silently (cascade deletes are expected to clean these up; a dangling ref visible at read time is a mid-flight cascade or a pre-existing orphan, not a DSL error).

const looseInvoices = vault.collection<Invoice>('invoices-loose', {
  refs: { clientId: ref('clients', 'warn') },
})
await looseInvoices.put('inv-9', { id: 'inv-9', amount: 50, status: 'open', clientId: 'missing' })

const rows = looseInvoices.query()
  .join<'client', Client>('clientId', { as: 'client' })
  .toArray()
// → [{ id: 'inv-9', amount: 50, status: 'open', clientId: 'missing', client: null }]
// console.warn: one-shot dangling-ref warning for clientId → clients/missing

A FK field that is null or undefined is not a dangling ref — it's "no reference at all", and is always allowed regardless of mode.

Reactive joins

query.live() returns a LiveQuery that re-runs the query and updates .value whenever a source changes. For a joined query it subscribes to the left collection and every join target, deduplicated by collection name — so a client update re-fires an invoice query that joined that client:

const live = await invoices.query()
  .join<'client', Client>('clientId', { as: 'client' })
  .live()

live.subscribe(() => render(live.value))

await clients.put('cli-A', { id: 'cli-A', name: 'Acme Corporation', tier: 'premium' })
// → live.value[0].client.name is now "Acme Corporation" — the right-side put re-fired the query

live.stop()

Under strict mode, a right-side delete that leaves a dangling ref makes the re-run throw; LiveQuery catches it and stores it on .error instead of tearing down the subscription — check .error after each notification. Always call .stop() when you're done; it tears down every upstream subscription. See live for the reactive primitive itself.

API

query.join(field, opts)

join<As extends string, Right = unknown>(
  field: string,
  opts: { as: As; strategy?: 'hash' | 'nested'; maxRows?: number }
): Query<T & Record<As, Right | null>>

Resolves the ref() declared on field and attaches the target record under opts.as. Returns a new immutable Query — the original is unchanged. Throws if the query wasn't built via collection.query() (no join context), or if field has no matching refs: { [field]: ref(...) } declaration.

const rows = invoices.query().join<'client', Client>('clientId', { as: 'client' }).toArray()
// → [{ ...invoice, client: Client | null }, ...]

Chain .join() again to resolve additional FKs on the same left record — each call adds one alias to the result type.

Errors

Both are importable from @noy-db/hub:

• JoinTooLargeError (code JOIN_TOO_LARGE) — either side of a join leg exceeds the row ceiling. Carries side ('left' | 'right'), leftRows, rightRows, maxRows.
• DanglingReferenceError (code DANGLING_REFERENCE) — a strict-mode ref points at a target id that doesn't exist. Carries field, target, refId.

Limits & gotchas

• Row ceiling, checked before allocation. JoinTooLargeError fires at 50,000 rows per side by default; override per-call with { maxRows } if you've measured the query is safe larger.
• Same vault only. A join target must be reachable in the same vault; there is no cross-vault join — use db.queryAcross() for that (see query basics).
• ref() required. .join(field, ...) throws if field has no matching refs: { [field]: ref(...) } declaration on the left collection — it does not infer relationships.
• Partition-awareness is plumbed but dormant. Every join leg internally carries a partitionScope field that is always 'all' today; the executor doesn't read it yet.
• .live() on strict-mode joins can error. A right-side delete that leaves a dangling ref makes the live re-run throw; the error surfaces on LiveQuery.error rather than propagating out of the change handler.

See also

• Foreign refs — declaring ref() and the three enforcement modes
• cross-join — pairing rows with no declared reference between them
• indexing — the indexed nested-loop join strategy uses a collection's index on the join field
• aggregate — joined rows can be grouped and aggregated
• live — the reactive primitive .live() returns
• Query basics — where / filter / orderBy and db.queryAcross()
• Proven by showcase 12-with-joins.