Collective Idea

Collective Idea Logo

Chris Rittersdorf

A PostgreSQL Sandbox Using VIM and tmux

By Chris Rittersdorf on January 08, 2014 in postgres, postgresql, tmux, and vim

If you’re like me, you’ve let ActiveRecord hold your hand for way too long. And when it comes time to write advanced PostgreSQL queries, you need to experiment in the REPL to find what you’re looking for. Add this trick to your toolbox to make working with the PostgreSQL REPL simpler.

The PostgreSQL REPL

Working with the PostgreSQL REPL shows you how your SQL queries will behave. The workflow is simple enough: you give the REPL a query, and it shows you the results. But what happens when you need to iteratively refine a query?

If you’re trying to refine a query, you can hit the up arrow, and REPL recalls the last query you typed in. But if your query was multiple lines, refining it that way can get tedious. You’ll have to one-by-one, recall each line of that query and hit enter. Then, when you’ve finally crafted the query you’re happy with, how do you easily export the query if you want to save it for later? Things get messy very quickly.

Finding Better Tools

I’ve incorporated a few tools into my daily workflow to alleviate this pain. By using Vim, tmux, and the tslime.vim plugin, I’m able to send SQL queries directly to the PostgreSQL REPL; allowing me to tweak queries quickly, save them to files, and simply incorporate them into code.

This workflow assumes a few things, namely that you have Vim and tmux running on your system, and that you know how to work with them. Additionally, you’ll need the tslime.vim plugin, which sends commands from Vim to a running tmux session.

  1. Start a tmux session
    > tmux
  2. Split the tmux pane vertically
    Ctrl-b %
  3. Start the PostgreSQL REPL in the right window and connect to your DB
    > psql
    manlycode=# \connect database_name
  4. Start vim in the left panel
    vim
  5. Type your sql query into the Vim session
    SELECT *
        FROM users
        WHERE
            'email' LIKE '%example%';
    
  6. Place your cursor over the first line of the query and press
    <C-c> <C-c>

Vim will ask you to choose a tmux window and a pane to send the command to. Use the key to cycle through the available options, and hit enter when you’ve found the correct one.

That key sequence sends your query, line-by-line, to the Postgres REPL. If you need to tweak the query, you can then use your VIM window to edit it, then repeat the key sequence to test it out again.

Editing your queries in this manner has a few advantages. To begin, you’re able to use VIM with all of its bells and whistles. Setting your filetype to “sql” will give you syntax highlighting, and let’s not forget the comfortable keybindings. In addition, you easily copy and paste, or save your query to a file.

By Chris Rittersdorf on January 08, 2014 in postgres, postgresql, tmux, and vim

4 Comments

  1. Darin

    Darin January 08, 2014

    Good heavens, have you not discovered \e in psql?

  2. Chris Rittersdorf

    Chris Rittersdorf January 08, 2014 http://collectiveidea.com

    Darin,

    I had not. It seems though, that \e makes you switch context. When you’re editing, you only see your history. When you view the resuts, you don’t see your SQL. Using the trick in the blog post puts both the editor and the results in front of you.

  3. edkolev

    edkolev January 09, 2014 http://https://github.com/edkolev

    Nice. I’m using tbone (instead of tslime), but the workflow is pretty much the same, `:%Twrite right` instead of

    Thanks!

  4. Kevin

    Kevin April 03, 2014 http://segv.me

    Brilliant, thanks for this!

Post a Comment

Contact Us

Find us on Google Maps
Collective Idea
44 East 8th Street, Suite 410
Holland, Michigan 49423 USA 42.790334-86.105251

Follow us on the Interwebs

We are currently available for medium and long term projects. Please get in touch if we can be of service.