How I do development on PostgreSQL over Emacs by Eduardo Bellani

These days I’m doing quite a lot of work in PostgreSql. Given that my tool of choice is Emacs, I had to learn how to make do. This post’s goal is to document that.

First, I set up a connection

(add-to-list 'sql-connection-alist
             `(production-read-only (sql-product 'postgres)
                                    (sql-user    "prod_user")
                                    (sql-server "data-aurora.cluster-ro.us-east-1.rds.amazonaws.com")
                                    (sql-database   "ProdDB")))

Since this uses psql under the covers and I want to not to have to type passwords all the time, I store the passwords in ~/.pgpass.

# hostname:port:database:username:password
data-aurora.cluster-ro.us-east-1.rds.amazonaws.com:5432:ProdDB:prod_user:the_password

In order to make life a bit better over at psql’s prompt, I have a .psqlrc file with the following:

\set QUIET 1

\set PROMPT1 '(%n@%m) [%/] > '
\set PROMPT2 ''

\pset null '[null]'
\set COMP_KEYWORD_CASE upper
\set HISTSIZE 2000
\set VERBOSITY verbose
\pset linestyle unicode
\pset border 2
\pset format wrapped

\set QUIET 0

Finally, I’d like to keep the history of commands. Here is how I enable that on comint-mode:

(use-package comint
  ;; This is based on
  ;; https://oleksandrmanzyuk.wordpress.com/2011/10/23/a-persistent-command-history-in-emacs/
  ;; The idea is to store sessions of comint based modes. For example, to enable
  ;; reading/writing of command history in, say, inferior-haskell-mode buffers,
  ;; simply add turn-on-comint-history to inferior-haskell-mode-hook by adding
  ;; it to the :hook directive
  :config
  (defun comint-write-history-on-exit (process event)
    (comint-write-input-ring)
    (let ((buf (process-buffer process)))
      (when (buffer-live-p buf)
        (with-current-buffer buf
          (insert (format "\nProcess %s %s" process event))))))

  (defun turn-on-comint-history ()
    (let ((process (get-buffer-process (current-buffer))))
      (when process
        (setq comint-input-ring-file-name
              (format "~/.emacs.d/inferior-%s-history"
                      (process-name process)))
        (comint-read-input-ring)
        (set-process-sentinel process
                              #'comint-write-history-on-exit))))

  (defun mapc-buffers (fn)
    (mapc (lambda (buffer)
            (with-current-buffer buffer
              (funcall fn)))
          (buffer-list)))

  (defun comint-write-input-ring-all-buffers ()
    (mapc-buffers 'comint-write-input-ring))

  (add-hook 'kill-emacs-hook 'comint-write-input-ring-all-buffers)
  (add-hook 'kill-buffer-hook 'comint-write-input-ring))

(use-package sql
  :after comint
  :config
  (add-hook 'sql-interactive-mode-hook 'turn-on-comint-history)
  (setq sql-password-wallet (list "~/.authinfo.gpg")))