Emacs and SQL

In the last year I had the pleasure of rewriting a data calculation software originally written in Java in Vertica SQL. As you can imagine I am not talking about simple select statements. A lot of try and error was involved. For debugging you would run those queries manually in an interactive SQL session. That way you can dive into the content of your temporary tables. My usual go to tool Emacs Orgmode was not the right tool for this task. Instead I was using the sqli-mode. For a long time I have a small function to create those sessions buried in a gpg encrypted .el file. Emacs can open those directly if your configuration contains something like:

;; Do not use gpg agent when runing in terminal
(if (>= emacs-major-version 25)
(defadvice epg--start (around advice-epg-disable-agent activate)
  (let ((agent (getenv "GPG_AGENT_INFO")))
    (when (not (display-graphic-p))
      (setenv "GPG_AGENT_INFO" nil))
    ad-do-it
    (when (not (display-graphic-p))
      (setenv "GPG_AGENT_INFO" agent)))))


(defun my-sql-connect ()
  (interactive)
  (require 'my-password "~/.emacs_secrets.el.gpg")
  (setq sql-product 'vertica)
  (let ((connection (helm-comp-read
                     "Select server: "
                     my-sql-servers-alist
                     )))
    (sql-connect connection))
  (sql-rename-buffer)
  )

in your gpg file you can store the credentials:

(setq my-sql-servers-alist
      '("bbaprd" "bbatst"))

(setq sql-connection-alist
      '((bbaprd (sql-product 'vertica)
                (sql-server "host42.foo.com")
                (sql-user "dbadmin")
                (sql-password "foo")
                (sql-database "bbaprd"))
        (bbatst (sql-product 'vertica)
                (sql-server "host42.foo.com")
                (sql-user "dbadmin")
                (sql-password "foo")
                (sql-database "bbatst"))
        ))

(provide 'my-password)

Per default sqli brings you already some addons to your plain vsql command. Editing statements, command history, scrolling up. But wouldn’t it be nice to have Statement history from previous sessions as well?

(defun my-sql-save-history-hook ()
  (let ((lval 'sql-input-ring-file-name)
        (rval 'sql-product))
    (if (symbol-value rval)
        (let ((filename
                 (concat (if (equal "work" (getenv "SYSENV"))
                             "~/workorg/sql/"
                           "~/org/sql/")
                         (symbol-name (symbol-value rval))
                         "-history.sql")))
            (set (make-local-variable lval) filename))
        (error
         (format "SQL history will not be saved because %s is nil"
                 (symbol-name rval))))))

  (add-hook 'sql-interactive-mode-hook 'my-sql-save-history-hook)

If you already use helm comint is what you want to have for your sql sessions:

(require 'sql)
(require 'helm-comint)
(defun helm-comint-sqli-input-ring ()
  "Preconfigured `helm' that provide completion of `comint' history."
  (interactive) ;; seq-contains helm-comint-mode-list major-mode
  (when (or (member major-mode helm-comint-mode-list)
            (derived-mode-p 'comint-mode))
    (helm :sources 'helm-source-comint-input-ring
          :input (buffer-substring-no-properties (comint-line-beginning-position)
                                                 (point-at-eol))
          :buffer "*helm comint history*")))

(define-key sql-interactive-mode-map (kbd "M-p") 'helm-comint-sqli-input-ring)

With sqli you can connect other buffers to the sqli buffer via sql-set-sqli-buffer. Now you can select queries and send them over just like you do in other environments like DBVisualizer or SquirrelSQL. You might say they have completion for tables and columns as well.

(define-key global-map (kbd "S-SPC") 'dabbrev-expand)

Dabbrev is far from perfect for this task but it brings you almost to the same level.

I don’t know about you but I like properly indented SQL statements.

sql-indent does exactly that for you:

(add-to-list 'load-path "~/.emacs.d/elpa/sql-indent-1.4")
(require 'sql-indent)
(add-hook 'sql-mode-hook 'sqlind-minor-mode)

Sometimes you get a query from somebody else. Everything in one line.

That’s the moment when you need sqlformat.el

(load-file (expand-file-name "~/.emacs.d/lisp/sqlformat.el"))

sqlup-mode is another great add on to upcase reserved words like SELECT:

(add-to-list 'load-path "~/.emacs.d/elpa/sqlup-mode-20170610.1537")
(require 'sqlup-mode)
(add-hook 'sql-mode-hook 'sqlup-mode)
(add-hook 'sql-interactive-mode-hook 'sqlup-mode)

With all this Emacs is still not as eye candy as those other tools. But it brings you your precious notes for almost free. And thrust me: without Emacs orgmode I wouldn’t have been able to complete the task I was talking of in the beginning. It involved a lot of going back to those notes my younger me has taken over the course of that year.