Eager loading with .with()

.with(spec) is the framework's eager-load chain. The query builder reads the relations registry to produce a single SQL query that returns the parent rows + every related table the spec touches as nested JSON.

const result = await store.query(
  database.users.where(eq('tenantId', tenantId)).with({
    profile: true,
    posts:   { limit: 5, orderBy: [{ column: 'createdAt', direction: 'desc' }] },
    organizations: true,
  }).descriptor,
)

// TypeScript infers:
// result: Array<User & {
//   profile: Profile | null,
//   posts: Post[],
//   organizations: Org[],
// }>

One SQL round-trip. The framework's JSON-aggregation compiler renders the whole tree as a single SELECT.

#Nesting

.with() nests arbitrarily deep:

database.users.with({
  posts: {
    with: {
      author: true,
      comments: {
        with: { user: true },
        limit: 10,
      },
    },
  },
})

That's users → posts → (author + comments → user). Still one SQL roundtrip. The JSON shape comes back nested; the framework decodes it to typed JS objects.

#Per-dialect SQL shapes

Each dialect emits its native JSON-aggregation idiom. You write the same code; the framework dispatches.

#Postgres

SELECT jsonb_build_object(
  'id', users.id,
  'email', users.email,
  'profile', (SELECT to_jsonb(p) FROM profiles p WHERE p.user_id = users.id LIMIT 1),
  'posts', COALESCE((SELECT jsonb_agg(t) FROM (
              SELECT * FROM posts WHERE author_id = users.id LIMIT 5
            ) t), '[]'::jsonb)
) AS __row
FROM users;

jsonb_build_object + jsonb_agg are postgres-native; the eager compiler is best-in-class here.

#MySQL 8+

SELECT JSON_OBJECT(
  'id', users.id,
  'email', users.email,
  'profile', (SELECT JSON_OBJECT(...) FROM profiles p WHERE p.user_id = users.id LIMIT 1),
  'posts', COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(...))
                     FROM (SELECT * FROM posts WHERE author_id = users.id LIMIT 5) t),
                    JSON_ARRAY())
) AS __row
FROM users;

Functionally equivalent to postgres; the FROM-derived-table wrapper handles per-parent LIMIT.

#MariaDB 10.6+

MariaDB rejects correlated references inside non-LATERAL derived tables. The framework switches to a ROW_NUMBER window-function pattern:

SELECT JSON_OBJECT(
  'id', users.id,
  'posts', COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(...) ORDER BY ranked.rn)
                     FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY author_id
                                                         ORDER BY created_at DESC) AS rn
                           FROM posts) ranked
                     WHERE ranked.author_id = users.id AND ranked.rn <= 5),
                    JSON_ARRAY())
) AS __row
FROM users;

For the no-pagination case, MariaDB uses JSON_ARRAYAGG(... ORDER BY ...) — a MariaDB-only extension that MySQL rejects — for cleaner SQL.

#MSSQL 2019+

SELECT (SELECT TOP 1 * FROM users WHERE id = u.id
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS __row
FROM users u;

MSSQL's FOR JSON PATH builds nested objects from the SELECT's column list; sub-aggregations become correlated (SELECT ... FOR JSON PATH) blocks inside CASE expressions.

#SQLite 3.38+

SELECT json_object(
  'id', users.id,
  'profile', (SELECT json_object(...) FROM profiles WHERE user_id = users.id LIMIT 1),
  'posts', COALESCE((SELECT json_group_array(json_object(...))
                     FROM (SELECT * FROM posts WHERE author_id = users.id LIMIT 5)
                    ), json('[]'))
) AS __row
FROM users;

json_object + json_group_array are SQLite's JSON1 extension; available since 3.38.

#Walker fallback

When the JSON-agg compiler can't express a particular spec — for example a relation that hasn't been registered, or a per-dialect edge case the emitter doesn't cover — the framework transparently falls back to a portable per-relation N+1 walker.

Nothing in the eager spec routes here deliberately. Junction columns ({ junction: [...] } on a manyToMany branch) compile natively on every dialect, so asking for junction data does not reroute the query — let alone the rest of the .with() tree.

The walker:

1. Runs the parent query without any eager loading.
2. For each declared relation in the spec, issues a follow-up query against the related table.
3. Attaches the related rows by FK in JavaScript.
4. Recurses through nested with.

Result is identical to the JSON-agg path. Only difference is the round-trip count: N+M+K+… vs 1.

The framework emits a warning when this fires:

mariadb JSON-agg eager-load failed; falling back to walker

If you see this regularly, the spec is hitting a code path the compiler doesn't cover. File an issue with the descriptor + dialect.

#When NOT to eager-load

Three patterns where .with() is the wrong tool:

#1. Children sets are large + unbounded

// User with 1M events — RAM explosion.
database.users.with({ events: true })

The JSON document per parent grows linearly with child count. At 1M events per user × 200 bytes per event = 200 MB per parent row. Use limit: always when the child set could grow.

#2. You need pagination UI for the children

// User has 10k posts; UI shows 20 at a time with pagination.
// Don't eager-load — query the children separately with paginateById.
const [user] = await store.query(database.users.where(eq('id', uid)).descriptor)
const rows = await ctx.store.query(
  paginateById(database.posts.where(eq('userId', uid)).descriptor, input.cursor, 20),
)
const nextCursor = rows.at(-1)?.id

Eager-loading 10k posts to render 20 is wasteful. Cursor pagination handles it cleanly.

#3. You want aggregations, not rows

// Want: { user, postCount, lastPostAt } — NOT { user, posts: [...] }
const rows = await ctx.store.transactional(async (txn) => {
  const users = await txn.query(database.users.where(...))
  const stats = await txn.unsafe(`
    SELECT user_id, COUNT(*) as post_count, MAX(created_at) as last_post_at
    FROM posts WHERE user_id = ANY($1) GROUP BY user_id
  `, [users.map(u => u.id)])
  return users.map(u => ({
    ...u,
    postCount:  stats[u.id]?.post_count  ?? 0,
    lastPostAt: stats[u.id]?.last_post_at ?? null,
  }))
})

Aggregations are a different query shape; .with() returns rows, not summaries.

#Type inference

Schema-registered relations propagate through .with() at the type level:

const rows = await store.query(
  database.users.with({
    profile: true,
    ownedOrgs: { with: { projects: true } },
  }).descriptor,
)
// rows: Array<User & {
//   profile: Profile | null,
//   ownedOrgs: Array<Org & { projects: Project[] }>,
// }>

Branded TypeID columns flow through — rows[0].ownedOrgs[0].id is OrgId, not just string.

#Performance

The JSON-agg path is fast for typical app workloads (parents × ~100 KB JSON per parent). At larger scales the per-row JSON serialization becomes the dominant cost, and at that point the walker (which streams rows individually) may actually outperform JSON-agg.

Benchmarks (postgres on Apple M2, app-tier instance type):

Pattern	JSON-agg	Walker (N+1)	Note
100 users × 5 posts each	4ms	35ms	JSON-agg wins decisively
1000 users × 50 posts each	90ms	280ms	JSON-agg still wins
1000 users × 5000 posts each (no limit)	4.5s	6s	Both pay; project narrowly
100 users × 100k posts each (no limit)	OOM	12s	JSON-agg blows memory; walker survives

The framework defaults to JSON-agg because the common case wins. If you hit the OOM cliff, add limit: to the branch.

#Reactive subscriptions

Reactive subscriptions on .with() queries register against EVERY table the spec touches. See reactive for the dependency-graph + per-field pre-filter details.

#Where it lives

• voltro/packages/database/src/queryBuilder.ts — .with(spec) chain
• voltro/packages/database/src/jsonEagerCompiler.ts — per-dialect JSON-aggregation compilers
• voltro/packages/database/src/joinCompiler.ts — walker fallback (attachEagerLoads)
• voltro/packages/sql-postgres/src/store.ts, etc. — each store's runWithEager() decides JSON-agg vs walker