Drizzle Query Builder III

Build the in-memory equivalent of a SQL JOIN: stitch two related tables together on a matching condition. You have a users table and a posts table, where each post carries a userId pointing back at its author. A join answers questions that need both tables at once — "every post alongside the user who wrote it." This question extends the chainable, lazy builder from Drizzle ORM with two join flavours: innerJoin, which keeps only rows that have a match on both sides, and leftJoin, which keeps every left row and fills in null when nothing on the right matches. A predicate here is a function that takes a row (or, for the join condition, two rows) and returns true or false.

Signature

// query(leftRows) returns a lazy builder over the left table.
query(leftRows: object[]): Builder

interface Builder {
  // `on(leftRow, rightRow)` decides whether a pair matches.
  innerJoin(rightRows: object[], on: (l, r) => boolean): Builder;
  leftJoin(rightRows: object[], on: (l, r) => boolean): Builder;
  // `pred` runs on the COMBINED row, not on a bare left or right row.
  where(pred: (row: Combined) => boolean): Builder;
  all(): Combined[]; // terminal — runs the plan, returns a new array
}

// A combined row is namespaced so a shared key (like `id`) never collides:
type Combined = { left: object; right: object | null };
//   innerJoin → right is always a matched row.
//   leftJoin  → right is a matched row, or null when the left row had no match.

query is also exported under the alias drizzleQueryBuilderIii.

Examples

const users = [
  { id: 1, name: 'Ada' },
  { id: 2, name: 'Bo' },
  { id: 3, name: 'Cy' },
];
const posts = [
  { id: 10, userId: 1, title: 'Hello' },
  { id: 11, userId: 1, title: 'Again' },
  { id: 12, userId: 2, title: 'Hi' },
];
const onUserPost = (u, p) => u.id === p.userId;

// innerJoin: one combined row per matching pair. Ada (id 1) wrote two posts,
// so she appears twice. Cy (id 3) wrote none, so Cy is dropped entirely.
query(users).innerJoin(posts, onUserPost).all();
// → [
//   { left: { id: 1, name: 'Ada' }, right: { id: 10, userId: 1, title: 'Hello' } },
//   { left: { id: 1, name: 'Ada' }, right: { id: 11, userId: 1, title: 'Again' } },
//   { left: { id: 2, name: 'Bo'  }, right: { id: 12, userId: 2, title: 'Hi'    } },
// ]

// leftJoin: every left row survives. Cy has no posts, so Cy is kept with
// right === null. where() then runs on the combined rows.
query(users)
  .leftJoin(posts, onUserPost)
  .where((row) => row.right === null) // users who have written nothing
  .all();
// → [ { left: { id: 3, name: 'Cy' }, right: null } ]

Notes

• innerJoin keeps matches only; leftJoin keeps every left row. An inner join is the intersection: a left row with no matching right row produces nothing. A left join guarantees one output row per left row at minimum — unmatched left rows pair with right: null.
• Combined rows are namespaced as { left, right }. Both tables here have an id column. A flat merge ({ ...left, ...right }) would let the right row's id overwrite the left's, silently losing data. Keeping left and right as separate sub-objects sidesteps every key collision.
• One left row can match many right rows. Each match is its own combined row, so a one-to-many relationship multiplies rows — Ada with two posts yields two output rows. This is exactly how SQL joins behave.
• where runs on the combined row. Its predicate receives { left, right }, not a bare row. After a leftJoin, right may be null, so a predicate reading row.right.title must guard against null first or it throws.
• The builder is reusable. Each step returns a fresh builder over an immutable spec, so calling .all() twice gives the same result and branching one builder into two .where()s must not let them interfere. The join itself runs when you call .innerJoin/.leftJoin; the .where predicates are what .all() defers and applies.
• Don't mutate the sources. Neither leftRows nor rightRows may be reordered or resized. .all() returns a new array.
• Don't worry about rightJoin/fullJoin, multi-table chains, column projection, or hash-join performance tuning — those are listed under Going further in the solution.