Psql + Vim = Happy Face

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!
psqlvim
In conclusion, psql is awesome, but psql with Vim integration is awesome-er.

Running WordPress 3.8 on a Nexus 7

I had the good fortune recently to come into possession of an Asus Nexus 7 Android tablet. After a couple of months playing games on it, a task it excels at I might add, I decided it might be neat to see if I could setup a super-portable self-contained mini-development environment for WordPress. Turns out this is surprisingly easy to put together. With all the pieces in place I can both run and hack WordPress code on the device, even without an active network connection. There are surely many ways to accomplish this, but here is what worked for me.

  • Keyboard
    Trying to code with a onscreen keyboard is a show stopper for me, so in order to make this work I need an external keyboard. It just so happens that my employer, Automattic, included an exceptional Logitech Bluetooth keyboard in our very generous holiday gift packages. It pairs with the device out of the box, but unfortunately the media keys are not supported. To fix that, and to create some useful application shortcuts, I’m using External Keyboard Helper Pro (https://play.google.com/store/apps/details?id=com.apedroid.hwkeyboardhelper&hl=en). It’s not the most intuitive interface (quite possibly one of the worst) but it is powerful with a lot of advanced options.
  • PHP enabled web and DB server
    I expected this to be the most pain in the ass part of the process, but it turned out to be really easy. I chose the Bit Web Server (https://play.google.com/store/apps/details?id=com.andi.serverweb&hl=en) app which is amazingly simple to use and does not require root privileges. It had a small problem with some default paths on first install, but that only took a minute to fix. Web and DB services are accessible via the localhost interface so you can use them without any external network connection. It even comes with phpmyadmin for database management.
  • Vim
    I use Vim for software development and there is a very nice port for Android called Vim Touch (https://play.google.com/store/apps/details?id=net.momodalo.app.vimtouch&hl=en). It’s really a full featured build, and for the most part compatible with my standard configuration.
  • WordPress
    Download the latest WordPress zip file, move it to the document root of the web server, unzip it, run the browser based install. I expected this to be easy, and I was not disappointed: It worked flawlessly.
Vim FTW!

Vim FTW!

I'm blogging!

I’m blogging!

So far it seems to all fit together nicely. I can switch between code and a browser with just the keyboard, and the fact that it’s entirely self contained over localhost is handy for traveling. I have some LONG flights coming up in the next month or two, so I’m hoping I can take advantage of the time and work on some plugins. I have to admit I’m strangely disappointed at how easy this was to setup. I expected a long drawn out battle with obtuse error conditions and undocumented issues. Maybe I should see if I can dual boot Debian and Android …