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.
Top comments (0)