The query every backend developer has needed and nobody enjoys writing
In March 2016, npm removed an 11-line package called left-pad. Within minutes, builds began failing across the JavaScript ecosystem. It broke thousands of projects, including tools like Babel. Many developers didn't choose left-pad directly; it was hidden in their dependencies and went unnoticed until it vanished.
That incident points at a question you have probably asked about your own stack: what is actually in my dependency tree? Not just the 30 packages in your package.json. Everything they pull in, and everything those pull in, all the way down.
In a relational database, dependencies live in a self-referencing join table. "Everything, all the way down" means a recursive CTE. Here is that query on a snapshot of the npm registry. It finds the full runtime dependency tree of express:
WITH RECURSIVE closure(name, depth) AS (
