Recursive CTEs

WITH RECURSIVE for org hierarchies, comment threads, file-folder trees, category graphs — single query, no app-side loops.

WITH RECURSIVE lets a query reference itself. Used for tree-walks and graph traversals that would otherwise need an app-side loop with N round-trips. Voltro's .recursiveCte() ships the SQL standard form on every supported dialect (postgres, mysql 8+, mariadb 10.2+, mssql, sqlite 3.8+).

When to use

Org hierarchy: "find every descendant of org X" / "find every parent up to the root"
Comment threads: "fetch a comment + every reply, recursively"
File-folder tree: "list everything inside this folder, any depth"
Category graphs: "products in this category OR any sub-category"
Dependency graphs: "what migrations does plan X transitively depend on?"

When you don't have a recursive structure, plain withCte() is enough.

Shape

Every recursive CTE has two arms joined by UNION (or UNION ALL):

Anchor — the seed query. Picks the starting rows non-recursively. Typically WHERE id = <root>.
Recursion — references the CTE by name, joining itself to the parent table to walk one level. Composed via .innerJoin().

Compose with union(anchor, recursion) or unionAll(...) and pass the combined descriptor to .recursiveCte(name, ...).

Example: org hierarchy

import { eq, queryFor, union } from '@voltro/database'
// `database` is YOUR project's handle — `export const database =
// databaseHandle({ ...tables })` in `database/index.ts`.
import { database } from '../database/index'

const rootId = 'org_root'

// Anchor: the root org itself
const anchor = queryFor(database.orgs).where(eq('id', rootId))

// Recursion: every org whose parentId is in the running set
const recursion = queryFor(database.orgs).as('child')
  .innerJoin('descendants', 'parent', eq('parent.id', 'child.parentId'))

const tree = await ctx.store.query(
  queryFor(database.orgs)
    .recursiveCte('descendants', union(anchor, recursion).descriptor)
    .where(eq('id', rootId))
    .descriptor,
)
// tree: every org reachable from rootId, transitively

Compiles to:

WITH RECURSIVE "descendants" AS (
  -- anchor
  (SELECT * FROM "orgs" WHERE "id" = $1)
  UNION
  -- recursion
  (SELECT * FROM "orgs" AS "child"
   INNER JOIN "descendants" AS "parent" ON "parent"."id" = "child"."parentId")
)
SELECT * FROM "orgs" WHERE "id" = $1

Example: walk UP a tree

The recursion direction is yours to choose — join child.parentId to walk up, or parent.id to walk down.

const node = queryFor(folders).where(eq('id', leafFolderId))
const ancestors = queryFor(folders).as('parent')
  .innerJoin('chain', 'child', eq('child.parentId', 'parent.id'))

const path = await ctx.store.query(
  queryFor(folders)
    .recursiveCte('chain', union(node, ancestors).descriptor)
    .descriptor,
)

Cycle handling

Standard UNION semantics dedupe across iterations — if your graph contains cycles, the recursion stops naturally when no new rows appear in a step. For very large graphs with cycles, prefer unionAll only when you've verified the graph is acyclic OR you have a WHERE predicate in the recursion that prevents infinite loops (e.g. a depth limit).

Cross-dialect notes

Dialect	Supported	Notes
postgres	✓ native	Best optimizer for recursive CTEs
mysql 8+	✓ native	Recursion depth limited by `cte_max_recursion_depth` (default 1000) — set per-session for deeper trees
mariadb 10.2+	✓ native	Same as mysql
mssql	✓ native	`OPTION (MAXRECURSION N)` hint NOT auto-emitted — set if you need >100-level recursion
sqlite 3.8+	✓ native	Smaller default recursion limit; check `PRAGMA recursive_triggers`

Voltro's compiler emits identical syntax across dialects; only the runtime defaults differ.

Reactivity

Recursive CTE queries are reactive — coarsely. The engine registers the subscription against every table the recursion reads (the anchor + the recursive arm), so a write to any of them re-runs the tree-walk. It can't pre-filter per column — a change to one ancestor can reshape the whole result — so it re-queries on any contributing-table change. Correct, but it re-runs the full recursion each time: fine for bounded trees (org hierarchies, comment threads). For very hot or very large trees, model the relationship via Relations and eager-load with .with({...}) to get the per-field pre-filter.

Limitations

No mutual recursion between two CTEs in the same block. Each recursive CTE references only itself.
Cycle detection without UNION dedup: if you use unionAll, ensure your recursion has a termination predicate. Voltro doesn't inject a default depth limit.
No reactivity: see above.

When NOT to use

Single-level parent / child — use a regular self-join. A recursive CTE is overkill at depth 1.
Performance-critical hot path with a large result set — recursive queries can explode on wide trees. Profile with EXPLAIN ANALYZE against realistic data. If the iteration count runs into the thousands, consider materializing the computed hierarchy into a separate table instead.
Arbitrary graph algorithms (shortest path, connected components) — a recursive CTE can be bent into these but it gets ugly fast. A graph database (Neo4j, the AGE extension) is the better fit.

Recursive CTEs

#When to use

#Shape

#Example: org hierarchy

#Example: walk UP a tree

#Cycle handling

#Cross-dialect notes

#Reactivity

#Limitations

#When NOT to use

#See also