Joins

Stitch related rows from one lens onto another without caring which database each lens lives in. A single include clause on search, similar, query, or their streaming cousins returns parent rows with their relations already attached.

queryrecipes

// recipes lens — declares the relations once, in sl.config.ts
recipes: {
  source: 'postgres',
  table: 'recipes',
  fields: {
    id:      { type: 'number', primaryKey: true },
    title:   { type: 'text',   searchable: { weight: 2 } },
    cuisine: { type: 'enum',   values: ['italian', 'thai', 'japanese'] },
  },
  relations: {
    reviews:        { lens: 'reviews',     kind: 'hasMany', on: { id: 'recipe_id' }, defaultIncludeLimit: 10 },
    ingredientRows: { lens: 'ingredients', kind: 'hasMany', on: { id: 'recipe_id' }, defaultIncludeLimit: 20 },
  },
  grants: { query: 'public' },
}

// reviews may live on a different source — the planner doesn't care
reviews: {
  source: 'postgres-reviews',
  table: 'reviews',
  fields: { /* ... */ },
  grants: { query: 'public' },
}

No SQL JOIN written, no FK picked, no second round-trip. The join planner resolves the relation you declared once, fans out a batchRead to each target bridge, and stitches the results.

ℹ️

Joins are a first-class feature, not a last-mile hack. The same primitive works whether both sides live in one Postgres or whether one side is Postgres and the other is MySQL, Mongo, or DynamoDB. You change the source in your config — the join keeps working.

What you get

Declarative relations in sl.config.ts — hasMany / belongsTo with a single key mapping. No SQL in your config file.
Cross-source planning — same-DB relations stay cheap, different-DB relations use an in-memory hash-join via each bridge's batchRead capability.
Typed return shapes — generated Beam clients expose recipe.reviews[0].rating as a fully-typed path.
Per-parent caps (defaultIncludeLimit on the relation, overridable per-call) with a hard system ceiling of 1000 so a chatty caller can't blow up the server.
Fail-partial semantics — if one relation's bridge is down or an access rule denies, that relation comes back empty for this request and meta.includeErrors surfaces why. Primary rows still land.

Where joins attach

Same include shape, different response locations — the data lives where the data naturally lives:

Op	Relations land at
`query()`	`rows[i][relationName]` — flat on the row
`search()`	`results[i].metadata[relationName]` — on the metadata object
`similar()`	`results[i].metadata[relationName]` — same as search
`feed()`	`items[i].metadata[relationName]` — same as search
`stream.query()` / `stream.search()`	Per-batch, attached to each row before the row yields

The three things to learn

Declaring relations — the relations key on LensConfig, the two kinds, how on works.
Include syntax — the include param on every read op (HTTP, streaming, typed + untyped clients, CLI).
Cross-source joins — how the planner picks a strategy and what happens if a bridge doesn't support batchRead.

When to use joins vs. separate queries

Reach for include when:

You'd otherwise fetch the primary row, then fire off a second round-trip to look up related rows by FK. The planner batches these across all parents in one call — far cheaper than N per-row lookups.
You're rendering parent + related in the same view (dashboard cards, detail pages, feed items with their author / reviews / tags).
The relationship is declared and stable. include doesn't support ad-hoc joins on arbitrary columns — those live in regular query with a SQL-capable bridge.

Stick with separate query calls when:

You need an aggregate across the joined side (avg, sum, count) — aggregations are a separate primitive, not a flavour of include.
The "join" is really a child-side filter: "recipes where any ingredient name contains 'chocolate'". Query the child lens directly, collect parent ids, pass them via where: { id: { $in: [...] } } on the parent.