Improving on the Command-Line SQL Experience

People who like staying within their terminals don’t really want to switch context into a GUI tool to work with SQL databases. Even though command-line SQL clients work just fine for most things, there are issues when:

• You need to browse around result sets
• You want more freedom when editing SQL statements

Hold tight, as we’re going to look at a few simple ways to tackle both of these problems, which you can start using immediately!

I’ll assume you have a functioning terminal environment, ideally on Linux, though a Mac would also be OK. For Windows, you’d probably be looking at WSL or cygwin with a proper terminal emulator, such as ConEmu.

We’ll be focusing on MySQL, however, everything here applies equally well to PostgreSQL and other database platforms.

Paging SQL Results

One of the primary issues with command-line SQL clients is that whenever you want to view any result set that doesn’t fit on a single screen, you’ll get unreadable output:

Scrambled SQL results

The fix for this is actually very simple: use a pager, which is a command that the result output will be sent to by the SQL client. This command is then responsible for handling the way the results are rendered, or otherwise processed.

For MySQL, you can set the pager in a variety of ways, such as by using the --pager command-line option.

Let’s set the pager to: less -SinFX

• S - Turns off line wrapping
• i - Makes searches case-insensitive
• n - Suppresses line numbers
• F - Don’t start the pager for results that fit on the screen
• X - Don’t clear the screen when the pager exits

For the same query above, this will produce:

Cleaner & easier to consume output

You can now scroll around the results just as you normally would when using less, as well as having access to other functionality, such as search, which is great!

You should get a decent amount of mileage from this solution, however, after a while a few things will start to get annoying. For example, the header rows will move off the screen when you scroll down, which means you need to remember which columns you’re looking at, or use marks. Similarly, the lack of a highlighted line makes it difficult to track your position when you scroll horizontally.

Luckily, there’s a better solution called pspg, which works with a wide range of SQL clients, even though the name might suggest PostgreSQL. This fixes the issues mentioned above, and adds other useful features, such as column search. You should be able to install it through your package manager:

Very clean results using pspg

SQL Editing

So, being able to easily browse query results is definitely a big win, and sorts out one of the main issues with using a terminal-based SQL client. You will, however, also want a lot more flexibility when it comes to editing queries, especially if you’re a developer.

Let’s start with the most basic solution, which is to set the $EDITOR environment variable to vim, micro, or whatever else you like to use. This editor will be triggered from within the MySQL client when you give it the \e command. After writing a SQL query, save your work and exit the editor. This will take you back to the SQL client, which will then run the query and display the results.

Writing SQL queries using $EDITOR

You can create a keyboard shortcut for this frequently used operation by adding something like the following to your ~/.inputrc:

$if mysql
 “\C-e”: ‘\\e;\C-m’
$endif

This will trigger the editor whenever Ctrl+E is pressed from within the MySQL client.

Let’s say you want to tweak your query; no problem, just trigger the editor again and your work will still be there, which is nice. Now let’s say you want to leave that query there and start work on a second query; this is where things start to get unstuck.

As the SQL client will execute everything in the file, your first query will also get executed when you close the editor. You could comment it out, or copy and paste queries between two separate files, however, these “solutions” will get tiresome pretty quickly for any serious work.

Using an $EDITOR Script

Instead of pointing $EDITOR to vim directly, we could instead point it to a script and get more control over how we want to handle the editing process. The script will be expected to take the path of an output file as a parameter, and should populate that file with the query to execute.

Using a script, we can automate the process of copying a single query from the SQL file we are working on to the output file expected by the SQL client. When triggered, the script would first start an editor for the main file containing all of our queries. The path of this file could be provided to the script via an environment variable.

To execute a query, we would highlight it and get the editor to save the region of selected text to a new file and then exit. This would then return control to the $EDITOR script, which would take the new file and move it to the output path expected by the SQL client.

When the script exits, the MySQL client will then pick up the output file and execute the query. If the editor is triggered again, we would land back at the file containing our queries and the process would repeat.

Old-school scripting

Such a script is actually pretty trivial to write, and the vim integration can be done with a single key binding. After a while of usage, however, some minor, but irritating, issues do present themselves. For example, you can’t view the results of the last query you executed while writing a new one, and you can end up needing to do this quite often.

You also can’t undo temporary edits that you might want to make in order to try something out. As the editor is started anew each time it is triggered, it doesn’t know anything about what you were doing before. Having to start the editor every time is also annoying, even with a keyboard shortcut, as is quickly returning to your previous position in the query file, though this last issue can be addressed in vim.

A Better Solution: qsh

Building upon the concept of an $EDITOR script is a simple and minimal solution I have written called qsh, which gives you the interface of a GUI SQL client directly within your terminal!

SQL client session using qsh

There is no re-inventing of the wheel here: tmux is used to manage the split panes, your editor of choice is used to write queries, and your database client tool is used to execute them and show the results. This gives you the full functionality of each tool, with qsh acting as the glue.

So, if you want to zoom the editor and just work on writing a query, or zoom the SQL client to understand the results or just work as you normally would in the terminal, or swap the panes around, tmux will let you do all this and more. Another benefit of using tmux is that you can run it over an SSH connection, which is typically how it is used. In this case, if you setup qsh on your remote host, everything should work as expected.

You can’t, however, open an SSH connection to connect to your database in the SQL client pane and have tmux and the editor running locally; this just wouldn’t work. You should instead either run everything locally or else remotely.

Installation

As qsh relies on tmux, you will need to have this installed and running within your terminal. Check for it in your package manager.

There are three simple steps to installing qsh, the first of which is to clone the git repository into your home directory:

$ git clone https://github.com/muhmud/qsh.git ~/.qsh

Then, you will need to setup the plugin for your editor, which can be either vim or micro at the moment. Finally, ensure that qsh is the editor used by your SQL client. This can be done using environment variables or an alias, as described here.

Now launch your SQL client (both MySQL and PostgreSQL are supported) and trigger the editor. You should see the editor pane created. To execute a query, simply highlight it and press F5, or whichever key you have bound. The results should appear in the SQL client pane below. To go back to normal, just exit the editor and everything will be cleaned up.

Going Forward

As we’ve seen, you can make a terminal environment just as usable as a graphical SQL client. The terminal option may actually be superior in some respects, as you can use an editor like vim and get the full capabilities of the native SQL client tools.

There are, however, a few nice to haves that are missing. The ability to browse metadata in a uniform way and perform related operations, such as scripting an object directly into the editor, would be great. Support for more editors, such as emacs, would also be nice, however, emacs users do have the option of using sql-mode.

Don’t worry; this stuff will hopefully make it into the next version!