Improve your PostgreSQL Workflows with vim-tmux-runner

There are many client applications for working with databases (PostgreSQL, etc.) available in the market. Some of my colleagues use JetBrains's DataGrip, Postico, DBeaver, etc. While I do not work much directly with DBs on daily basis, I do sometimes find myself experimenting on local PostgreSQL instance via pgcli before "transferring" the queries into application code. During this process, one feature that I would really like to have from those GUI clients is the ability
execute selected/highlighted commands directly from the editor and get the result directly on screen. I use Vim as my main editor. So it would be nice if I can perform the same thing describe above
within Vim.

It turns out this can be achieve with combination of Vim and tmux and the vim-tmux-runner plugin. I've included a small demo in this article to show how that can be done with the tools mentioned before.

christoomey / vim-tmux-runner

Vim and tmux, sittin' in a tree...

VTR [Vim Tmux Runner]

A simple, vimscript only, command runner for sending commands from vim to tmux.

Usage

VTR provides a handful of commands for managing and interacting with tmux the terminal multiplexer. The main command is:

VtrSendCommandToRunner

This command will prompt for a command to run, then send it to the runner pane for execution. Subsequent calls to VtrSendCommandToRunner will reuse the provided command.

If you would like VTR to create a runner pane if one doesn't exist while issuing a command, a bang version can be used: VtrSendCommandToRunner!.

VTR provides configuration options that allow for control over the size and location of the VTR runner pane. In addition, VTR provides commands to resize, reorient, and even detach the runner pane making the interaction as painless as possible.

For a complete summary of the available commands and configuration options in VTR, check the included doc file.

…

View on GitHub

DB Preparation

For demo purposes we are going to setup a sample PostgreSQL database in a Docker container. If you already have a DB to play with, feel free to skip this part.

docker run -d --name postgres_demo -p 5432:5432 --rm postgres:11.6
echo "CREATE DATABASE pagila;" | psql -h localhost -p 5432 -U postgres
curl -s https://raw.githubusercontent.com/devrimgunduz/pagila/68b7bab066a18988694a8b698533c3c507f7b133/pagila-schema.sql | psql -h localhost -p 5432 -U postgres pagila -f -
curl -s https://raw.githubusercontent.com/devrimgunduz/pagila/68b7bab066a18988694a8b698533c3c507f7b133/pagila-data.sql | psql -h localhost -p 5432 -U postgres pagila -f -

Vim configurations

First install the christoomey/vim-tmux-runner plugin with your favorite Vim plugin manager. Then, for my setup, I've included the following in my vimrc:

let g:VtrUseVtrMaps = 1
let g:VtrStripLeadingWhitespace = 1
let g:VtrClearEmptyLines = 1

DEMO

1. This workflow requires a tmux session, so go ahead and start up one.
2. Then we launch Vim within that tmux session.
3. In Vim, create a new tmux pane (referred as "runner" in the plugin) using the :VtrOpenRunner command.

4. Run pgcli in the newly created pane and connect to the DB (pagila) created above.
   

   pgcli -h localhost -p 5432 -u postgres pagila
   

5. Turn on Multi-line Mode in pgcli by pressing the <F3> key. This means that our PostgreSQL commands in Vim need to end with semi-colon ;.

Once we have pgcli running on the runner pane, we can write down our SQL query in Vim then select the query in Visual mode.

SELECT film_id, title FROM film LIMIT 5;

Finally use the binding <Leader>sl to send the selected SQL commands to the runner. The binding (enable via g:VtrUseVtrMaps mentioned above) executes command :VtrSendLinesToRunner.

Often, we might be in a state where we already have pgcli opened but not attached to Vim as the runner. In this situation, if we do :VtrSendLinesToRunner, we will get VTR: No runner pane attached. error. For this case, we can use :VtrAttachToPane command to attach the pane as runner then continue with our workflow.

This concludes the article about me indulging myself with another "How to do X in Vim?" Of course, usage of vim-tmux-runner is not limited to PostgreSQL but you can do the same on other REPL shell e.g. IRB, IPython, etc. Hope this article gives you some ideas of how to integrate this plugin into your workflow. Thanks for reading ❤️.

Cleanup 🗑️

If you want to clean up the demo DB created above, simply do:

docker stop postgres_demo