Like many other Linux/open-source software tech companies, Shutterstock makes extensive use of tried-and-true technologies like MySQL. We are always exploring different database technologies such as Riak and MongoDB, but at the core of our business is a highly available and tightly managed MySQL infrastructure. We started on MySQL with a loosely-designed schema and have been adding to it incrementally over the years.

Some of our developers who are less comfortable on the command line use GUIs to access the database but the more bearded folk tend to use the standard MySQL-client CLI tool that is the stock and trade of any LAMP stack. vim, emacs, git and MySQL are usually open in many terminals on our desktops. But, unlike the others, the MySQL shell is not the most up-to-date tool in the toolbox. With poor pagination and output handling, no color highlighting and a somewhat irritating input prompt, the MySQL client causes its fair share of frustrated “that’s not what I meant to happen” moments.

We need pagination. With our eight-year-old schema, some of our tables span 800 columns of console output. Simple queries like “select * from accounts limit 1” fill the screen with line after line of ASCII table rendering characters. Even restricting the output to one line per column (“G”) makes for an impressively difficult amount of data to parse.

In response to this, we recently undertook some improvements to the tool. With our expertise in Perl and Moose OO programming, rewriting the MySQL client in Perl seemed like a simple exercise in DBI programming (which we’re very comfortable with) and some straightforward CLI tooling. By approaching the problem iteratively, we were able to very quickly come up with a drop-in replacement to the MySQL client with the majority of features we use on a daily basis implemented. From this as a starting point, we were free to explore what we wanted to fix.

We’re very pleased with the result. We call it AltSQL, as it’s an alternative to and improvement over some of the standard command line SQL tools.

The first and simplest change to make was to add color. We’re used to seeing our prompts full of color. Our bash prompt highlights the hostname name in red, ls shows directories in blue, and vim and emacs give our coding full color syntax. Adding contextual coloring to tabular output makes sense, was a simple addition, and comes at no expense since the DBI statement handler contains a great deal of context about each result that’s delivered.

Implementing a better prompt was a simple matter of finding a suitable CPAN module, and Term::Readline::Zoid fit the bill on that measure quite well. Offering out-of-the-box multiline editing and an extensible autocomplete and key binding interface, we were able tomove quickly.

We finally had a MySQL shell prompt that could abandon the statement when you typed Ctrl-C rather than exiting the program.

Adding horizontal and vertical pagination was a simple change, but a powerful one. By checking the output width and height of the table to be printed, we are able to conditionally use the less pager. This feature finally made “select * from accounts limit 1” a command we could type without worry. No matter what the terminal size, you’ll be able to see the data in a usable format.

We hope that other people can benefit from this. Regardless of if you use Perl or not, we think this is a useful tool that could make your job easier. Install it from CPAN or Github and try it out.