DEV Community

Jon Staab
Jon Staab

Posted on • Updated on


Using Kakoune as a Query Browser

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

This, so far, looks like any other code:

Queries in Kakoune

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:

Queries split into multiple Kakoune buffers

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
$ psql -d postgres -f projects/dogfish/queries.sql
  name   | length | edible
 dogfish |   22.3 | t
(1 row)
Enter fullscreen mode Exit fullscreen mode

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

psql -d postgres -e -f "$1" ${@:2}
Enter fullscreen mode Exit fullscreen mode

After a quick chmod +x ./scripts/, we can run our queries file with ./scripts/ 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/

find "$1" | entr -p -r ./scripts/ $@
Enter fullscreen mode Exit fullscreen mode

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/ projections/dogfish/queries.sql. Now every time we make a change to the file, it runs it and shows us the results!

A working feedback loop


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/

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"

Enter fullscreen mode Exit fullscreen mode

Chmod it and add it into our watch script:

find "$1" | entr -p -r bash -c "./scripts/ $1; ./scripts/ $@"
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)

Advice For Junior Developers

Advice from a career of 15+ years for new and beginner developers just getting started on their journey.