A PostgreSQL Sandbox Using VIM and tmux
Finding better tools to iteratively refine queries
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
# Split the tmux pane vertically
# Start the PostgreSQL REPL in the right window and connect to your DB
> psql manlycode=# \connect database_name
# Start vim in the left panel
# 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
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
Good heavens, have you not discovered \e in psql?
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.
Nice. I’m using tbone (instead of tslime), but the workflow is pretty much the same, `:%Twrite right` instead of
Brilliant, thanks for this!
I’m using dbext for working with sql queries. https://mutelight.org/dbext-the-last-sql-client-youll-ever-need
”# Place your cursor over the first line of the query and press” what? Is something missing here?