The Database: Raw SQL, No ORM

Website Rebuild with Express

Chapter 6 · The Database: Raw SQL, No ORM

Five times now, a different ORM in this series caught the same deliberately-planted N+1 breadcrumb pattern and fixed it with a nested eager-load call — and five times, that fix admitted the identical limitation: a fixed depth, hard-coded into the nesting itself, with no way to express "however deep this chain actually goes." Every one of those chapters named the theoretical fix: a raw recursive SQL query. This chapter finally builds it.

The Recursive Query

WITH RECURSIVE ancestors AS (
  SELECT *, 0 AS depth FROM pages WHERE id = ?
  UNION ALL
  SELECT p.*, a.depth + 1 FROM pages p
  INNER JOIN ancestors a ON p.id = a.parent_id
)
SELECT * FROM ancestors ORDER BY depth DESC;

The base case starts at the target page (depth 0). Each recursive step joins back to pages to find the current row's own parent, incrementing depth as it climbs — continuing until a row's parent_id no longer matches anything (the root has been reached). ORDER BY depth DESC puts the highest depth — the furthest-back root ancestor — first, and the target page itself (depth 0) last: exactly root-to-leaf order, ready for a breadcrumb.

// lib/pages.js
async function getBreadcrumb(pool, pageId) {
  const [rows] = await pool.query(`
    WITH RECURSIVE ancestors AS (
      SELECT *, 0 AS depth FROM pages WHERE id = ?
      UNION ALL
      SELECT p.*, a.depth + 1 FROM pages p
      INNER JOIN ancestors a ON p.id = a.parent_id
    )
    SELECT * FROM ancestors ORDER BY depth DESC;
  `, [pageId]);
  return rows;
}

Verified: genuinely unbounded, not just a deeper fixed limit

Every prior ORM's own nested eager-load call had to state its own depth explicitly — four levels, five levels, however many the developer chose to write. This query states no depth at all. A page three levels deep and a page thirty levels deep are both resolved by the exact same unchanged SQL, in one query, with zero code changes between them. This is the concrete answer to a limitation five separate courses in this series named but never actually solved.

Stated honestly: an unbounded-depth win, not a universal performance win

This isn't "free" or automatically faster than every prior approach at the shallow depths this site realistically has — a four-level nested with() call and this recursive query both resolve a four-level chain in roughly comparable cost. The genuine advantage is specifically that this query's own correctness doesn't degrade or silently break as depth grows, where every sibling's own fixed-depth call would. MySQL's own recursive CTE support also has a real, configurable safety limit (cte_max_recursion_depth, defaulting to 1000) — far beyond anything a real content hierarchy would ever need, but worth knowing it exists.

The Whole Series' Own Shared Limitation, Finally Closed

Framework	Fixed-Depth Limitation?
Next.js (Prisma)	Yes — nested `include`
Django	Yes — chained `select_related`
Laravel (Eloquent)	Yes — dotted `with()`
Rails (ActiveRecord)	Yes — nested `includes()`
Astro (Drizzle)	Yes — nested `with`
Express (raw SQL)	No — genuinely unbounded, via `WITH RECURSIVE`

Hands-On Exercises

Exercise 1

Build getBreadcrumb() using the recursive CTE, and confirm it correctly returns the full, correctly-ordered ancestor chain for a page three levels deep.

📄 View solution

Exercise 2

Store a page six levels deep — deeper than every prior course's own fixed nested-call limit — and confirm the identical, unchanged query correctly returns the full six-level chain with zero code changes.

📄 View solution

Exercise 3

Explain why ORDER BY depth DESC produces root-to-leaf order, and confirm removing the depth column and ORDER BY clause entirely produces an unpredictable row order instead.

📄 View solution

Chapter 6 Quick Reference

WITH RECURSIVE ancestors AS (...) — a real MySQL 8+ recursive common table expression
Base case + recursive case — starts at the target page, climbs via parent_id until nothing more matches
depth column + ORDER BY depth DESC — produces correct root-to-leaf order
Genuinely unbounded — the same unchanged query resolves any depth, closing a limitation five sibling ORMs each admitted but never solved
Honestly stated — an unbounded-depth win specifically, not a universal performance win at shallow depths
Next chapter: Rendering Content & the Kanji Edge Case