Offline SQL Formatting with sqlformat

sql

python

emacs

Published

January 29, 2021

It’s polite to format your SQL before you share it around. You want to be able to do it in context, and not upload your private SQL to some random website. The sqlformat command of the Python package sqlparse is a great tool for the job.

You can install sqlformat in Debian derivatives such as Ubuntu with sudo apt install sqlformat. Alternatively with any system with Python you can install it via pip install sqlparse, just make sure you have the binary in your path (e.g. if using pip install --user then ~/.local/bin/ should be in your path).

The formatting is provided by the Python library sqlparse, and you can see an example of how it works at sqlformat.org. The instructions are at sqlformat --help and there’s lots of arguments to choose how to format keywords, identifiers, alignment, comma placement and wrapping. Here’s a simple command that takes the contents of query.sql, converts the keywords to upper case, reindents the statements and puts space around operators, and outputs to formatted.sql:

sqlformat -k upper -r -s -o formatted.sql query.sql

The command line is useful for batch changing SQL files, but often I want to change an SQL query inside a Python or R file. This works great, even if you’re using parameters, f-strings in Python or glue in R, it seems to do a good job. It’s straightforward to run on a region of SQL in Vim (or Emacs Evil) using a filter command, or in Emacs by executing a shell command, just make sure you pass - as the file so it reads from stdin.

For Emacs I’ve written a small function to SQL format a region; being careful not to clobber the final newline of the region. A nice improvement would be to customise the arguments.

(defun remove-trailing-newline (point)
  (if (= (char-before point) ?\n)
      (- point 1)
    point))

(defun sql-format (start end)
  "Formats the selected sql `sqlformat'"
  (interactive "r")
  (shell-command-on-region
   ;; beginning and end of buffer
   start
   (remove-trailing-newline end)
   ;; command and parameters
   "sqlformat -k upper -r -s -"
   ;; output buffer
   (current-buffer)
   ;; replace?
   t
   ;; name of the error buffer
   "*Sqlformat Error Buffer*"
   ;; show error buffer?
   t))