Every time I model categories, comments, or org charts I end up writing the same recursive CTE. I save it as a template now. It walks a (id, parent_id, name) table from a given root and returns depth plus a slash-delimited path, which is handy for rendering a tree.

WITH RECURSIVE tree AS (
    SELECT
        id,
        parent_id,
        name,
        0           AS depth,
        name        AS path
    FROM category
    WHERE id = :root_id

    UNION ALL

    SELECT
        c.id,
        c.parent_id,
        c.name,
        t.depth + 1,
        t.path || '/' || c.name
    FROM category c
    JOIN tree t ON c.parent_id = t.id
)
SELECT id, depth, path
FROM tree
ORDER BY path;

Two things I always forget:

  1. You need UNION ALL, not UNION. UNION does a dedupe per step, which is slow and rarely what you want.
  2. Guard against cycles. SQLite 3.41+ supports WITH RECURSIVE tree(... ) AS ( ... ) CYCLE path SET is_cycle USING .... Older versions need you to track visited ids yourself.

The same pattern works in Postgres with the same syntax. See also /posts/sqlite-strict-tables-migration/.