I love Vim. I use it to develop software, write blog posts, analyze and manipulate raw data, even compose E-mail. If I’m typing something and it requires more than 4 words, I do it in Vim. I was going to write a post about all the Vim tips and tricks I have consumed over the years, but that information lives almost exclusively in muscle memory, which requires too much effort to extract. Instead, I’m writing about how you can spice up your psql environment with Vim, since I just did that last week, and kind of sort of remember how I did it.

Psql is the command line client for Postgres. It’s a powerful, if somewhat boring, interface to the DB. Type an SQL statement, get an ASCII formatted result. In this way it’s similar to the mysql command line interface for that other RDMS you may have heard of. Psql has some interesting options we can use to bring the Vim goodness. That other client might have similar features, but thankfully I don’t work with that anymore and have all but forgotten it existed at all.

Vi Style Key Bindings

This is like “set -o vi” in bash, but for psql. Support for vi key-binding is provided by the readline or libedit library, so enabling this in psql can be done using the .inputrc file in your home directory. Just create the file if it does not exist, and add the following:

set editing-mode vi
set keymap vi-command

Now you can navigate the Psql command line with some Vim-like shortcuts via a limited command mode accessed with ESC. So cool!

Editing Queries With Vim

Being able to jump around the CLI is nice, but when you are building a complicated query, it’s better to have a full featured editing environment. Psql provides a command, “\e”, that will start an editor with the last query pre-loaded. When exiting the editor, the updated query will be re-run. You can control the editor with the EDITOR environment variable. Just set that to “vim” before starting psql. I define this in my .bashrc file so ANY program respecting the EDITOR variable will use Vim by default, because of course I do. The only other trick here is to make sure Vim knows that you are editing a query so we get fancy-pants syntax highlighting. Adding this to your .vimrc file should do the trick (presuming psql is using /tmp like it does on Debian):

syntax on
au BufRead /tmp/psql.edit.* set syntax=sql

Now I can seamlessly use Vim from psql to develop complex SQL statements. SO GOOD.

Query Results in Vim

In the course of developing said complex SQL statements, I’m running oodles of queries and analyzing the results. This is yet another place we can dollop Vim awesome-sauce into psql. Like the EDITOR environment variable, psql respects the PAGER variable. This defines what program will be used to display results that don’t fit in the available terminal screen real-estate. There are 3 steps to gluing this all together: 1. Setup a custom Vim syntax file to make the results all pretty; 2. create a custom Vim config to be used when displaying results; and 3. Set the correct PAGER value before starting psql.

First up, the syntax highlighting. Create a file called “dbout.vim” and put it in ~/.vim/syntax. If those folders don’t exist in your home directory, create them. Put the following super hacky syntax highlight definitions in that file:

" Vim syntax file
" " Language: db output
" " Maintainer: Jason Munro

syn region Heading start=/^ \l/ end=/[-+]\+$/
syn match Border "|"
syn match IntVal " \d\+\(\n\| \)"
syn match NullVal " NULL\(\n\| \)"
syn match NegVal " -\d\+\(\n\| \)"
syn match FloatVal " \d\+\.\d\+\(\n\| \)"
syn match NegFloatVal " -\d\+\.\d\+\(\\n\| \)"
syn match DateTime "\d\{4}-\d\{2}-\d\{2} \d\{2}:\d\{2}:\d\{2}\(\.\d\{1,}\|\)"
syn match TrueVal " t\(\n\| \)"
syn match FalseVal " f\(\n\| \)"

hi def Heading ctermfg=246
hi def IntVal ctermfg=229
hi def FalseVal ctermfg=88
hi def NullVal ctermfg=242
hi def Border ctermfg=240
hi def NegFloatVal ctermfg=160
hi def FloatVal ctermfg=230
hi def NegVal ctermfg=160
hi def DateTime ctermfg=111
hi def TrueVal ctermfg=64

The color definitions I’m using here expect a 256 color capable terminal, those with less will have to fiddle with the ctermfg values. Next, create a custom Vim configuration for the results that uses the syntax highlighting. Just copy your existing .vimrc to .vimrcpg, and add the following lines:

syntax on
set syntax=dbout

Finally, set the PAGER environment variable before running psql:

export PAGER='vim -R -u ~/.vimrcpg -'

And, voila! Check out the super cool color highlighting from the obviously very useful “test” table I just created!

In conclusion, psql is awesome, but psql with Vim integration is awesome-er.