Query all reachable nix store paths

query_reachable.sh

#!/usr/bin/env bash
roots_file="$(mktemp)"
find /nix/var/nix/gcroots -type l | xargs realpath 2>/dev/null >"$roots_file"

sqlite3 --readonly --batch db.sqlite <<EOF
CREATE TEMPORARY TABLE roots (path TEXT);
.mode csv
.import $roots_file roots

WITH RECURSIVE closure(id) AS MATERIALIZED (
  SELECT id
    FROM roots
    JOIN ValidPaths USING (path)
  UNION
  SELECT ValidPaths.id
    FROM Refs
    JOIN ValidPaths ON (reference = ValidPaths.id)
    WHERE referrer = id
)
SELECT path
  FROM closure
  JOIN ValidPaths USING (id);
EOF