VoltroDocumentation

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 its own accumulated result. It fits organization hierarchies, reply threads, folder trees and dependency traversals. For a single parent/child level, use a join.

Query shape

A recursive CTE combines two queries with union() or unionAll():

  1. The anchor selects starting rows from an ordinary table.
  2. The recursive term joins the named CTE to find the next rows.
  3. The outer query selects from the CTE result.

Both terms must project the same columns in the same order. Selecting the original table in the outer query does not return the traversal, even if a recursive CTE is declared alongside it.

Example: descendants

Import the actual table descriptor from your schema. This example assumes id and nullable parentId fields and returns the root and all reachable descendants.

import { columnRef, eq, queryFor, union } from '@voltro/database'
import { orgs } from '../database/orgs.entity'

// orgs has id and nullable parentId columns.
const rootId = 'org_root'
const anchor = queryFor(orgs)
  .select('id', 'parentId')
  .where(eq('id', rootId))

const recursion = queryFor(orgs).as('child')
  .innerJoin('descendants', 'parent', eq('parent.id', columnRef('child.parentId')))
  .selectJoined({ id: 'child.id', parentId: 'child.parentId' })

// A local schema view for the CTE result; no table is registered or created.
const descendants = { ...orgs, tableName: 'descendants' }
const tree = await ctx.store.query(
  queryFor(descendants)
    .select('id', 'parentId')
    .recursiveCte('descendants', union(anchor, recursion).descriptor)
    .orderBy('id', 'asc')
    .descriptor,
)

For SQLite and libSQL, the essential SQL shape is:

WITH RECURSIVE descendants AS (
  SELECT id, parentId FROM orgs WHERE id = ?
  UNION
  SELECT child.id, child.parentId
  FROM orgs AS child
  INNER JOIN descendants AS parent ON parent.id = child.parentId
)
SELECT id, parentId FROM descendants ORDER BY id ASC

The recursive term is a direct SELECT. Wrapping it in SELECT * FROM (...) creates a prohibited circular reference on SQLite. Ordinary set-operation branches still retain their wrappers where branch-local ordering/pagination needs them.

Example: ancestors

Reverse the join direction to follow parent links from a leaf toward the root:

const anchor = queryFor(orgs)
  .select('id', 'parentId')
  .where(eq('id', leafId))
const recursion = queryFor(orgs).as('parent')
  .innerJoin('ancestors', 'child', eq('child.parentId', columnRef('parent.id')))
  .selectJoined({ id: 'parent.id', parentId: 'parent.parentId' })
const ancestors = { ...orgs, tableName: 'ancestors' }

const path = await ctx.store.query(
  queryFor(ancestors)
    .select('id', 'parentId')
    .recursiveCte('ancestors', union(anchor, recursion).descriptor)
    .descriptor,
)

Cycles and pagination

union() removes duplicate whole rows across iterations. The example terminates on a cycle because the same IDs and parent IDs repeat. Adding a changing depth or path column means those rows are no longer duplicates; plan a termination condition for that shape. unionAll() keeps duplicates and requires an acyclic traversal or an explicit terminating condition. Voltro does not add a default depth bound.

On SQLite/libSQL, keep branch-local ordering and pagination off the recursive term. The compiler rejects that shape explicitly. A nonrecursive anchor may have its own ordering/limit. Ordering and pagination may also belong to the compound query or to the outer query; those affect different stages. The engine's recursive_triggers setting concerns triggers, not recursive CTE traversal.

Tenant namespaces

In namespace mode, the compiler qualifies physical tables with the tenant namespace. CTE names stay local to the SQL statement: FROM, JOIN and membership/existence subqueries reference the CTE itself, not a similarly named physical tenant table. This also applies when the outer query is a UNION or another compound query.

Driver scope

The SQLite and libSQL paths execute this tree-walk shape, including UNION cycle deduplication, UNION ALL on an acyclic tree, anchor pagination, compound limits and outer pagination. Native Turso currently rejects recursive CTEs with Recursive CTEs are not yet supported; the compiler does not emulate recursion or silently return the anchor alone. A sqlite dialect label therefore does not establish this capability for every client.

Other SQL drivers have their own recursive syntax and restrictions. Do not infer runtime support from the presence of WITH RECURSIVE in generated SQL alone. Cross-driver recursive execution remains a validation gap; this guide does not claim identical behavior on all dialects.

Reactivity and limits

Recursive query dependencies include the tables read by the anchor and recursive term. A contributing-table change can reshape the entire traversal, so reactive execution reruns the query rather than updating one tree edge in isolation. Profile large or highly connected graphs with representative data.

Mutual recursion between separate CTEs is not supported. Keep one recursive relation per traversal. General graph algorithms may require a different data model rather than an unbounded recursive query.

See also