org-roam Orphans

I use org-roam for taking notes, but I also like to have indexes (just org roam nodes that link their contents) that link everything together. This means that if I want nodes properly linked when I make them, I first have to find the index they belong to insert them into, which really ruins the flow. I would rather go through the graph regularly and clean things up later, but aside from squinting at org-roam-ui finding those orphan nodes is actually quite annoying from within Emacs.

Thankfully I stumbled upon this note by Justin Abrahms, which gives a nice SQL query for finding orphans, and I've written a bit of Elisp around it to prompt for the resulting nodes with completing read. Hopefully someone else finds this useful!

(defun org-roam-find-orphan ()
  "Find all orphaned nodes and then provide their paths to open with completing-read."
  (interactive)
  (let* ((org-roam--db (sqlite-open org-roam-db-location))
       (org-roam-orphans
        (sqlite-select
         org-roam--db
         "select * from nodes n
where not exists ( select null from links l where n.id = l.source )
and not exists ( select null from links l where n.id = l.dest );")))
    (sqlite-close org-roam--db)
    (find-file
     (completing-read
      "Orphan node: "
      (mapcar
       (lambda (rec)
       (string-replace "\"" "" (cadr rec)))
       org-roam-orphans)))))