Using Kakoune as a Query Browser
Tags: kakoune, postgres, unix, entr
Published: 2019-05-27 20:58:25

---
title: Using Kakoune as a Query Browser
published: true
description: Unixing together a query browser with Kakoune and Postgres
tags: kakoune, postgresql, unix, entr
series: Kakoune Scripting
---

For my day-to-day work, I use [TablePlus](https://tableplus.io/) 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:

![Queries in Kakoune](https://thepracticaldev.s3.amazonaws.com/i/e1fkly2m5z71msr6wldb.png)

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](https://thepracticaldev.s3.amazonaws.com/i/a730k7m11ibfzrslyfyo.png)

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`](http://eradman.com/entrproject/) 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](https://github.com/mawww/kakoune/blob/master/doc/pages/commands.asciidoc#declaring-new-commands) 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!


![A working feedback loop](https://thepracticaldev.s3.amazonaws.com/i/4rv16g58u1x0aj6k6f98.png)

# 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](https://github.com/darold/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:

![nnn_0S](https://i.makeagif.com/media/5-27-2019/nnn_0S.gif)

# 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.