Joins — Quickstart

Your users sit in Postgres. Your orders stream into DynamoDB. Your live cart state lives in Redis. Your reviews are in a second MySQL instance another team owns.

That shouldn't require four backend round-trips and a service layer to stitch. It's one read:

await beam.users.query({
  where: { tier: 'pro' },
  include: {
    recentOrders:  { limit: 10, orderBy: { field: 'placed_at', dir: 'desc' } },
    activeCart:    true,
    recentReviews: { where: { rating: { $gte: 4 } }, limit: 3 },
  },
})

Each relation resolves on its own bridge, fans out in a batched { $in: [parent_ids] } read, and lands back on each parent row — typed, stitched, in one response. The planner doesn't care which database is which. It just asks each bridge for its batch.

The magic trick

// Four lenses, four sources, one query
sources: {
  'postgres':       { bridge: '@semilayer/bridge-postgres' },
  'dynamodb':       { bridge: '@semilayer/bridge-dynamodb' },
  'redis':          { bridge: '@semilayer/bridge-redis' },
  'mysql-reviews':  { bridge: '@semilayer/bridge-mysql' },
},

lenses: {
  users: {
    source: 'postgres',
    table:  'users',
    fields: { /* ... */ },
    relations: {
      recentOrders:  { lens: 'orders',      kind: 'hasMany',   on: { id: 'user_id' }, defaultIncludeLimit: 10 },
      activeCart:    { lens: 'carts',       kind: 'belongsTo', on: { id: 'user_id' } },
      recentReviews: { lens: 'reviews',     kind: 'hasMany',   on: { id: 'user_id' }, defaultIncludeLimit: 5 },
    },
    grants: { query: 'authenticated' },
  },
  orders:  { source: 'dynamodb',      table: 'orders',  grants: { query: 'authenticated' } /* ... */ },
  carts:   { source: 'redis',         table: 'carts',   grants: { query: 'authenticated' } /* ... */ },
  reviews: { source: 'mysql-reviews', table: 'reviews', grants: { query: 'public' }        /* ... */ },
}

No SQL. No N+1. No client-side stitching. No backend service that knows how to talk to all three databases. Just include.

Four moves

sources: {
  'postgres': { bridge: '@semilayer/bridge-postgres' },
  'mysql':    { bridge: '@semilayer/bridge-mysql' },
  'dynamo':   { bridge: '@semilayer/bridge-dynamodb' },
  'redis':    { bridge: '@semilayer/bridge-redis' },
}
lenses: {
  users:  { source: 'postgres', table: 'users',  /* ... */ },
  orders: { source: 'dynamo',   table: 'orders', /* ... */ },
  carts:  { source: 'redis',    table: 'carts',  /* ... */ },
  reviews:{ source: 'mysql',    table: 'reviews',/* ... */ },
}

users: {
  relations: {
    recentOrders: { lens: 'orders', kind: 'hasMany', on: { id: 'user_id' } },
  },
}

orders: {
  relations: {
    user: { lens: 'users', kind: 'belongsTo', on: { user_id: 'id' } },
  },
}

semilayer push

import { beam } from './beam'

const { rows, meta } = await beam.users.query({
  where: { tier: 'pro' },
  include: {
    recentOrders:  { limit: 10, orderBy: { field: 'placed_at', dir: 'desc' } },
    activeCart:    true,
    recentReviews: { where: { rating: { $gte: 4 } }, limit: 3 },
  },
})

if (meta.includeErrors?.length) {
  // A relation failed (access denied, bridge down, missing capability).
  // Primary rows still landed. Surface the errors however you want.
  console.warn('partial:', meta.includeErrors)
}

What just happened under the hood

For each request, the planner:

1. Runs the primary query on the owning lens's bridge (Postgres for users).
2. Collects the parent keys (every users.id).
3. For each included relation, resolves the target's bridge and calls its batchRead with { <fk>: { $in: [keys] } }. Same-bridge relations share a connection; different-bridge relations each run on their own.
4. Groups and slices the results per parent (orderBy + limit applied in-process).
5. Attaches to each parent row and responds.

Every join — same-DB or cross-DB — is a hash-join. No SQL pushdown, no federated query engine, no network-of-planners voodoo. Two round-trips per relation. Predictable latency. Works anywhere batchRead is implemented.

The rules still apply

Every lens enforces its own access rules independently. A caller who can see users but not reviews gets rows back with recentReviews: [] and a meta.includeErrors entry — primary data still lands, the denied relation fails quietly.

This means you can mix public lenses and authenticated lenses in one call, and the planner does the right thing per-relation. See Access Rules for the full composition story.

Where to go next

• Declaring Relations — hasMany / belongsTo, on semantics, composite-key limitations.
• Include Syntax — every knob on the include clause (where, select, orderBy, limit).
• Cross-Source Joins — the planner's strategy in detail, connection reuse, 1000-row ceiling.
• Streaming — stream.query / stream.search with relations attached per-batch.
• Access Rules — most-restrictive-wins composition, fail-partial semantics.
• Bridges Without Support — what happens when a target bridge opts out of batchRead.