How I use :dbconnection in org files

In the post "Followup on secrets in my work notes" Magnus Therning mentions a feature in Org Babel I contributed to (in ob-sql.el, to be more precise).

You can see my old post for details about that feature, but basically, the patch allows one to use a :dbconnection header argument in a source block to reference a connection defined in sql-connection-alist.

The question in Magnus' post is a signal somebody is actually using this feature, so I am pleased. Perhaps I should also follow up, describing how I use this feature in my workflow. This will constitute another example of how to manage secrets.

I use Org, among other things, to keep work "lab notes" that usually contain SQL queries on different databases.

At work, pretty much all databases are Postgresql or Redshift, and I keep connection details in ~/.pgpass, following this format:

In other words, every DB definition is made of two lines: the first is a comment, with the name of the database (with no spaces); the second contains the actual connection details.

In my Emacs configuration, then, I have this function:

(defun get-connection-alist (filename)
  "Gets PG connections details from ~/.pgpass file (FILENAME)."
  (with-current-buffer (find-file-noselect filename)
        (let ((lines (split-string (buffer-string) "\n" t)))
          (when lines
            (cl-loop for (k v) in (seq-partition lines 2)
                  collect (cl-destructuring-bind (host port db user password)
                              (split-string v ":" nil)
                            `(,(replace-regexp-in-string "^#\s+" "" k)
                              (sql-product 'postgres)
                              (sql-port ,(string-to-number port))
                              (sql-server ,host)
                              (sql-user ,user)
                              (sql-database ,db)
                              (sql-password ,password))))))))

and

(setq sql-connection-alist (get-connection-alist "~/.pgpass"))

I use Emacs in daemon mode and it's not unusual Emacs to stay up for weeks, so I also have an automatic way to incorporate .pgpass changes, using filenotify.

(file-notify-add-watch
 "~/.pgpass" '(change)
 (lambda (evt)
   (setq sql-connection-alist
         (get-connection-alist "~/.pgpass"))))