For my day-to-day work, I use TablePlus to develop/maintain various postgres and mysql databases, which I can honestly recommend as a pretty great query browser.

Like any other piece of software though, it has its warts, among which are sub-par query formatting and odd text-editing keybindings. This isn't helped by the fact that I use Kakoune (a text editor that's built around doing vim "the unix way") for most of my text-editing activities. I also use tmux for a window manager, since Kakoune doesn't come with one.

So I asked myself: instead of wishing there were Kakoune-like editing abilities in TablePlus, what if I put sql into Kakoune? It turned out to be easier than I imagined because, well, unix.

Editing queries

To start with, I created a directory to put all my scripts and sql-related projects:

$ mkdir query-browser
$ cd query-browser
$ mkdir scripts projects

Creating a place where I can edit queries was as simple as creating a sql file, opening it in Kakoune, and copying my query-in-progress into it.

$ mkdir projects/my-project
$ kak projects/my-project/queries.sql

This, so far, looks like any other code:

Thinking ahead a bit, that looks problematic. If I'm developing that last query, I don't want to run the CREATE TABLE query every time! Easily solved:

Now I can run the setup query once on the right, close that tmux pane, and get to work on the query I'm developing.

Running queries

Now that I have some sql files, I can run them using the psql command line tool:

$ psql -d postgres -f projects/dogfish/setup.sql
INSERT 0 3
$ psql -d postgres -f projects/dogfish/queries.sql
  name   | length | edible
--------------+--------+--------
 dogfish |   22.3 | t
(1 row)

Let's generalize this by saving it to a script called run.sh.

psql -d postgres -e -f "$1" ${@:2}

After a quick chmod +x ./scripts/run.sh, we can run our queries file with ./scripts/run.sh projects/dogfish/queries.sql. Notice that I'm passing the rest of the arguments directly to psql so we still have access to all of its command line options.

Developing with a feedback loop

I love quick feedback when I'm working. I almost always have some tests running in another window, or hot reload turned on for my app. We can do the same thing here, too.

Let's make another script called ./scripts/watch.sh.

find "$1" | entr -p -r ./scripts/run.sh $@

This one uses find along with entr to watch the given query file and run it when it changes.

Note that I don't recommend this if you're going to be using this technique in production. It's much better to create a custom command that explicitly runs your query. For me, I want a fast feedback loop in development, so file watching fits the bill.

We can start our watcher (don't forget to chmod +x it) with ./scripts/watch.sh projections/dogfish/queries.sql. Now every time we make a change to the file, it runs it and shows us the results!

Auto-formatting

The possibilities with this technique are endless, but let's cap it off here with adding auto-formatting to our query browser. If you're following along, install pgFormatter for your OS (brew install pgformatter works on OSX). Now, save a new script to ./scripts/format.sh:

current="$(cat $1)"
formatted="$(pg_format $1 -s 2 ${@:2}"

# Only write to the file if it changed to avoid an infinite loop
if [[ "$current" != "$formatted" ]]; then
  echo "$formatted" > "$1"
fi

Chmod it and add it into our watch script:

find "$1" | entr -p -r bash -c "./scripts/format.sh $1; ./scripts/run.sh $@"

And now we have automatic query formatting:

Final Words

Obviously this isn't a full-featured query browser, but for me, the longer I develop software, the fewer features I want in the software that I use. Kakoune is a great foundation for building custom, minimal tools for day-to-day use. And, of course, this technique isn't limited to Kakoune — it's just a matter of building small tools that do one thing well, and composing them together.

I do encourage you to try Kakoune out if you haven't! Its learning curve is not beginner friendly, but it does pay off for the dedicated student.