flymake-pgsanity and SQL linting beyond Emacs

Lint your SQL (and even funky templated SQL!) in Emacs with flymake. AFAIK this is the only implementation of an SQL linter for flymake (even though it's very simple). Also included here is a recipe for running such linters in CI (eg, Github Actions). And some bonus syntax highlighting tips.

This does not attempt to edit code -- just to identify (with squiggly lines) problems as you type them.

You'll need ecpg for any of this to work. It's broadly available via any package manager. And this is PostgreSQL-only.

You'll also need Captain's huglint to make this work.

Historic note: this project used to depend on pgsanity, but that dep was removed when I determined that it was such a tiny (and buggy) wrapper around ecpg, that I could replace any need for Python with one line of sed.

Emacs linting

There are already similar SQL linters available for flycheck, but I've been trying to get everything I use onto the built-in flymake.

To use with your Emacs, put flymake-pgsanity.el onto your load-path, and:

;; (add-to-list 'load-path "~/.../vendor") ; wherever you keep non-melpa additions
(require 'flymake-pgsanity)
(add-hook 'sql-mode-hook 'flymake-pgsanity-setup)

Then freshly open a .sql file and it should start highlighting any errors.

If you want to use a different linter/script, customize flymake-pgsanity-program. Eg, set it to huglint (after putting it on your path) if you use Hug.

SQL-like files (HugSQL, PugSQL, etc)

(If you are only interested in editing/checking of straight SQL files, ignore this section.)

The whole reason I started this effort was for some silly mistakes I'd been making in tweaking HugSQL .sql files. The errors would have been immediately caught by a linter (instead of at runtime!), if only there was one.

The trick is having a very simple preprocessor (sed one-liner script, included) that can convert the special :foo-bar parameters into something that a standard SQL linter can handle. I tried converting them all to basic strings like 'foo-bar-XXX' and it worked! Yes, it also supports those weird params like :v*:so-weird.

The other necessary bits to make ecpg happy involve you manually "improving" your Hug files:

• manually add semicolons (;) to the ends of each SQL statement, which ecpg needs and Hug doesn't mind

• don't end with a dangling WHERE

For that last case, here's an example:

problem:
WHERE
--~ (if ... "foo = :foo" "bar = :bar")

fix:
WHERE TRUE
--~ (if ... "AND foo = :foo" "AND bar = :bar")

In CI

Here's a recipe for running ecpg in Github Actions. This installs the dependency, ecpg, and a custom pgsanity-wrapper linter (which you'll edit to suit your needs) that will reject the build.

jobs:
  checks:

    - name: Install ecpg Postgres FE
      run: |
        sudo apt-get install libecpg-dev
        
    ...

    - name: Check for any lint warnings/errors in sql files (ecpg)
      run: ./deploy/bin/pgsanity-ci.sh

Emacs syntax highlighting

You can make your special :foo-bar params in SQL files stand out (bold blue) with this:

(defface sql-field '((t (:foreground "#528fd1" :weight ultra-bold))) "My SQL Field")
(font-lock-add-keywords 'sql-mode '((" :\\(v\\*:\\)?[-a-z0-9?]+"  0 'sql-field t)))

;; Other Hug goodies
(font-lock-add-keywords 'sql-mode '(("-- :doc .*" 0 'doc-field t)))
(font-lock-add-keywords 'sql-mode '(("-- :name [^:]+" 0 'special-comment t)))
(font-lock-add-keywords 'sql-mode '((" \\(:\\*\\|:!\\|:n\\|:\\?\\|:1\\)" 0 'boolean-true t)))

I suppose it'd be nice to color the list (:v*:...) types differently.

Hug in imenu

Neat way to see list of a hug file's functions in imenu:

(setq hug-imenu-generic-expression
      '(("SELECTS" "^-- :name \\([-a-z0-9?!]+\\) .*:\\?" 1)
        ("EXECS"   "^-- :name \\([-a-z0-9?!]+\\) .*:!" 1)
        ("INSERTS" "^-- :name \\([-a-z0-9?!]+\\) .*:i!" 1)))
(add-hook 'sql-mode-hook (lambda ()  (setq imenu-generic-expression
hug-imenu-generic-expression)))

Based simply on :? and :! as detailed here.

Shown here with [imenu-list-smart-toggle](https://github.com/bmag/imenu-list)and [consult-imenu](https://github.com/minad/consult).

This also got me realizing it's useful to organize hug files into something like those 3 sections.

Other related/interesting projects

• sqllint (awesome! but flycheck, not flymake)
• sqlfluff
• sql-lint
• flymake-diagnostic-at-point
• captain (for local git-hooking)