Running Queries in Kakoune with a Custom Command

Last Time, I wrote about building a minimal query browser using bash and Kakoune. In it, I set up a file watcher using entr, and auto-ran my queries on file change.

An arguably better approach would have been to register a custom command to do that, since my queries might not always be safe in a production environment. Kakoune's documentation on the topic is dense to say the least, so let's put together an example to go with it!

Project Structure

For this project, we're going to need two components: Kakoune commands, and connection configuration. Commands should be reusable between projects, while connections will be project-specific.

Best practice when writing Kakoune plugins is to put them in a file on their own, somewhere in one of Kakoune's autoload directories. I'll be putting my plugin into ~/.config/kak/autoload/jstaab/eval-query.kak.

Connection configuration, on the other hand, will go in my project root directory, in .eval-query.json. For now, I'll just use a simple json object describing arguments to pass to psql:

  "connections": {
    "local": "-d my_project",
    "production": "-h 192.168.0.1 -d my_project"
  }

Adding our Command

For this plugin, we're only going to need one command, which I'll name after my plugin: eval-query. Commands are defined using the def keyword with some optional switches. We of course want a docstring and we'll add the -override switch so we can redefine this command as we work on it (we'll want to remove that when we're done).

def eval-query \
  -override \
  -docstring "Evaluate current selection using given connection." \
%{
    echo "I don't do anything yet"
}

That %{} syntax is kakoune-speak for a multi-line string. The bodies of commands abide by the rules described in the command parsing docs. When the command is invoked, the contents of this string are what will be evaluated.

Let's go ahead and invoke our no-op command, by typing :eval-query in normal mode. This will print our message, "I don't do anything yet" down on the bottom-left of the screen.

Asking for Input

The first thing we'll need to do is add a way to ask the user which connection we want to use. There are a lot of ways to do this, but we'll be using -shell-script-candidates. This switch takes a shell script that supplies a newline-delimited list of possible completions, which Kakoune will then fuzzy-match over. We'll also have to tell Kakoune how many parameters are expected, using -params. We'll also just have the command echo back at us what we provide:

def eval-query \
  -override \
  -docstring "Evaluate current selection using given connection." \
  -params 1 \
  -shell-script-candidates %{ echo something } \
%{
    echo %arg{@}
}

This gives us only one auto-complete option, "something". When we invoke this command with :eval-query, it prompts us with "something", and echoes it back to us.

Let's fill in the completion script by reading from our config file and grabbing the keys using jq.

def eval-query \
  -override \
  -docstring "Evaluate current selection using given connection." \
  -params 1 \
  -shell-script-candidates %{
    cat .eval-query.json \
      | jq -r '.connections|keys|@sh' \
      | sed s/\'//g \
      | tr -s " " "\n"
  } \
%{
    echo %arg{@}
}

This looks at the project's config file, pulls out the keys, and provides them as hints to the command's autocomplete. Now we know which connection to use!

Evaluating the Query

The last piece of the puzzle is finding the query we want and passing it to our database for execution. Kakoune makes this simple, by providing a $kak_selection variable to our shell expansions. Getting the string quoting correct on the other hand can be tricky.

def eval-query \
  -override \
  -docstring "Evaluate current selection using given connection." \
  -params 1 \
  -shell-script-candidates %{
    cat .eval-query.json \
      | jq -r '.connections|keys|@sh' \
      | sed s/\'//g \
      | tr -s " " "\n"
  } \
%{
  info -title "Query output" %sh{
    echo "${kak_selection}"
  }
}

I've changed the echo command to an info command, which opens up an info box filled with the stdout of the %sh expansion. Inside the shell expansion, we're echoing the contents of the current selection.

To pass it to postgres, we grab the options from the specified connection, and pass them, along with the current selection to postgres:

def eval-query \
  -override \
  -docstring "Evaluate current selection using given connection." \
  -params 1 \
  -shell-script-candidates %{
    cat .eval-query.json \
      | jq -r '.connections|keys|@sh' \
      | sed s/\'//g \
      | tr -s " " "\n"
  } \
%{
  info -title "Query output" %sh{
    psql `cat .eval-query.json | jq .connections.$1 | cut -d\" -f 2` \
      -c "${kak_selection}" 2>&1
  }
}

Notice that I'm redirecting stderr to stdout so we can see any errors that might arise (like a nonexistent database, for example). Here's what this looks like:

Binding to a Hotkey

You'll notice that this isn't exactly ergonomic. Do I have to type out the full command, complete with my connection every time? The answer is no. We can bind our command — along with any useful variations — to keyboard shortcuts, using map.

map global user \" \
  -docstring "Evaluate current selection in local database" \
  ':eval-query local<ret>'

map global user \' \
  -docstring "Evaluate current paragraph in local database" \
  '<a-i>p:eval-query local<ret>'

I've created two bindings here; the first one just calls our command with the local connection filled in. The second one does the same thing, but selects the current paragraph.

This has the effect of letting us run a query with two keystrokes, even if we don't have it already selected. If we need more control, for example to run just part of a query, we can use the more basic double quote version. You can see that in action below:

Note that the keys I chose to bind are completely arbitrary; change them to whatever you prefer!

Addenda

This "plugin" is far from done. Like every software project, it has the potential to be a business/all-consuming obsession, so I'm going to stop here, for now. Partly as a disclaimer, and partly as an exercise for the reader, here's a non-exhaustive list of improvements that could be made:

• Large result sets will not be shown; I'm not sure what's going on internally, but Kakoune appears to have an upward limit on the amount of text that can fit in the info block. One way to handle this would be to put the results in a scratch buffer instead.
• Plugins really shouldn't put keyboard shortcuts in the user scope. A better approach might be to create a custom user mode to namespace the shortcuts defined by the plugin, then declare an option that a user can override in her config file. To get that into the mappings, you'd likely have to listen for a GlobalSetOption hook to re-bind the mapping when the user sets the module option.
• Errors and edge cases could be handled much more reliably. I mostly ignored it for the purposes of this post.
• In order to share this code (and take on the accompanying burden of maintaining an open source project), we should wrap it up in an installable package. This might be as simple as making the file downloadable somewhere.

Maybe I'll do further posts on how to accomplish these things 😉

At any rate, my point here isn't primarily to demonstrate how to create a Kakoune plugin; it's to showcase the flexibility and ease with which you can customize Kakoune to suit your workflow. An ad-hoc architecture that only concerns one person's use case can be much less robust than a plugin meant for general use. Don't be afraid to push JetBrains out of your way and make some of your own tools!