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 to move quickly.
We finally had a mysql shell prompt that could abandon the statement when you typed Ctrl-C rather than exiting the program. 
Improving the table rendering was next. By dropping in Text::ASCIITable we quickly had a better table renderer that properly wrapped output on newline characters, but why stop there? All of our terminal emulators have full Unicode support, so we spent some time developing a simple but powerful low-level Unicode box formatter (Text::UnicodeBox) to make terminal table drawings more intuitive and less obtrusive.
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.
This is just the beginning. By choosing Moose, all the features of the tool are extendable by other modules. We’ve written it from the ground up to be pluggable. In fact, most of the features mentioned above aren’t a part of the core code, but instead written as modules to modify the behavior.
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.
This is a very nice tool.
Thanks for taking some of the pain out of using MySQL on the command line!
This looks great, I wonder, could the DB specific bits be abstracted so this could work with postgresql too ?
Yes, that’s the hope. I made some progress on that this weekend and hope to have it configurable as a psql drop in replacement in the next two weeks. I’m thinking that the user could indicate both which backend to use as well as which frontend behavior to mostly emulate. That way, a user could run
show databasesand have it do the right thing with a Pg backend or\lor\dtwith a MySQL backend.That sounds great… mostly I’m just up for the pretty tables and scrolling.
It would be nice if it could see what symlink it runs from, then accept all the mysql or psql commandline options (I guess just pass them through).
It might also be worth displaying a psql> or mysql> prompt too, as it could get confusing in places that have both.
So far I haven’t attempted to replace all the features of
mysql, most notably the ability to load data via stdin redirection. As a result, it’s not quite appropriate to symlinkaltsqltomysql.I’m currently playing with the idea of having
--mysqlcommand line option, so if you wanted a convenient alias you can define one in your~/.bashrc(or similar) such as:As for the prompt, that’s completely customizable via the config file. It’s a good idea to have some sort of delineation between modes in the prompt; I’ll see what I can do there.
Looks cool–if you add postgres or sqlite support, I’d definitely use it
Just a note: the mysql CLI (while definitely outdated) does have pager support built-in: \P sets the pager to whatever you want (I usually do “less -XF” or similar), and you can set that in your my.cnf to be permanent.
That’s a good point. My original complaint with using
\Pwas that I didn’t want it going into full screen mode if the output was shorter then my terminal, but as I was working on AltSQL I discovered that that’s not the case with-X. In AltSQL we useless -FRXS(F: quit if one screen, R: support color, X: don’t go full screen, S: don’t wrap long lines). Using this inmysqldoes certainly address the pagination issue.