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.

# Start a tmux session

> tmux

# Split the tmux pane vertically

Ctrl-b %

# Start the PostgreSQL REPL in the right window and connect to your DB

> psql
manlycode=# \connect database_name

# Start vim in the left panel

vim

# Type your sql query into the Vim session

SELECT *
    FROM users
    WHERE
        'email' LIKE '%example%';

# Place your cursor over the first line of the query and press

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.

---

Sandbox with Toys is licensed under CC BY 3.0