Query Like It’s 1986: Exploring mssql-cli

Back in my day…

The first time I touched a keyboard, it was back in 1983. I was 4 years old, and I was watching an older cousin play snake on a TRS-80 Model III in my grandfather’s family room. It was old, and it wasn’t pretty, and sometimes it would just turn off randomly. I wanted a turn. Turns out, when you’re four years old, you don’t quite have the fine motor skills for a game like snake. But I was hooked: I was just as interested in what it took to launch the game of snake as it was to play it. I was also interested in how the game was made. Everything was keystrokes: you had to type in commands to launch a game. You had to press keys to interact with the snake on the screen. And someone had to type (a lot) of code to make the snake game even possible.

That was the day I fell in love with computers.

My parents were both teachers, and growing up in the shadow of former steel mills, outside of a few Commodore 64’s, Atari 2600’s, and some Texas Instruments products, a real, honest-to-goodness PC wasn’t something a lot of me my friends had (my parents though, had various Apple computers in the house since they got an educator discount on different models). To get my PC fix I had to hang out at my grandparents’ house. I spent every visit and a lot of sleepovers messing around on his computers and just soaking up what I could. As the years went on, the hardware got better and better: next, had some 286s and 8088s that he built himself. Those were fun, but what really sent me over the edge was his first 386: A Zenith Data Systems 386, with the math co-processor. I have so many good memories of that machine growing up. From Sierra text-based games, to making cool logos in Harvard Graphics, to the first time he installed Windows 3.0, up until he finally retired it for his first Pentium… that machine was a workhorse.

Each time he built a got a new computer, though, he just didn’t throw the old ones out. No, see, he co-owned a local pharmacy in town. These were the days before we had a Walgreens or CVS on every other corner, so my grandpap’s little corner pharmacy was a staple in the surrounding community.

He offered free delivery and paid high school kids to drive medicine to people who were not able to come and pick it up. He sponsored a little league team (that I wasn’t on; I ended up on a different team). You could find him back behind the tall pharmacy counter, filling prescriptions for long hours of the day, while his wife worked the front of the store (and always gave the grandkids Chiclets and tootsie rolls). They even used to have a soda fountain, but that was before my time.

I digress; the old computers, the ones that he would “retire” would end up at the pharmacy. And there, using programs like Lotus 1-2-3 and the database Q&A, he managed all his customer data, inventory, and finances. Weekly, month, quarterly, and yearly reports were filed off in drawers of 5.25″ and then 3.5″ disks. I’m not saying my grandfather was some sort of visionary who was the first person to do this, but I can probably safely assume he was one of the few in Washington County who was doing it at that particular point in time. And that’s where my real education with computers began. Sure, I loved (and still love) building computers and playing games on them, but it was the first time I had seen a computer used as a business tool. Q&A was the first database I ever touched.

We take keyboards and mice for granted today but grandpap was a maestro with those keyboard shortcuts, and could just as quickly add a customer as he could look one up on the interface.

That was then… this is now

Of course we have way better tools available to us now than we did then; my grandfather’s 25Mhz machine wouldn’t even require 1% of the usage on the 3.4Ghz monsters we put in personal computers today. RAM? Storage? People forget (or may never know) just how much of a luxury 2 MB (with an M) was back then. Human computer interaction is way different now too: we almost all use keyboard and mice now, and monstrous 4K displays; we can enjoy computer audio without knowing what and interrupt request is why it matters. Things change, and our computing habits change with them.

So it was with great curiosity, then, that I started looking into a bit of a throwback: mssql-cli. For those who haven’t seen it, it’s essentially a command-line based tool for querying SQL Server databases. Here’s a gif of me goofing around on it by querying my local AdventureWorks database:

After using it for a few weeks at home, I came up with a mock “Q&A” in honor of the first database I ever used, to talk about this project.

What is mssql-cli?

The Microsoft SQL Server Command Line Interface (mssql-cli) tool is exactly what it sounds like: a database client for the command line. The project was built to follow the dbcli project of bringing such tools to the community for a handful of database providers (and is growing).

Who’s this for?

Well, anyone who wants to query a database on a system without a UI. With features like syntax highlighting, code completion, and nice output formatting, if you’ve ever wanted a text-based command line tool for querying your data, this is it.

Command line? So this will this replace tools like SQLCMD.EXE, BCP.EXE, or Invoke-SQLCmd?

I don’t think so. Tools like SQLCMD and BCP are great for one-time, fire-and-forget queries or for loading data to SQL Server, and Invoke-SQLCmd can do that as well as return query results for additional processing in PowerShell, but they aren’t interactive. This tool is basically like having a query window open in your terminal at all times. It’s got great code completion, syntax highlighting, and object completion too.

Okay, fine, who’s this really for?

Anyone, I suppose, but in reality? If you’re stuck on a remote system that only allows terminal logins, this is a super handy tool to use. If you’re used to text-based editors, like Emacs, Nano, or even vi or vim, you’ll feel right at home in here. You can type in commands and then run queries. There’s even multi-line support for more complex stuff. There’s another side to this too: if you’re the kind of DBA that gets really mad if someone installs management studio on a server, then this might be a solution: it’s got a very small installation footprint, and you don’t get a full UI experience so unless you know all the big T-SQL commands for heavy administration (like for, say, dealing with an availability group or adding permissions, or taking or restoring backups), you won’t get much use out of it. But in a pinch, on a server, in a crisis where you can’t tell if SQL is up and serving queries? It just might work out well for you.

When you launch the tool you get a nice, clean terminal interface to type your queries in. Since you’re not going to have use of a mouse, everything is a keyboard shortcut, from clearing a line, wiping the terminal screen, navigating results, and even gracefully quitting. The tool supports two editing methods: Emacs mode and Vi mode. Right now, there only appears to be support for SQL-based logins on Linux, but it IS supported on Windows (via the -E or –integrated flag). It’s also got some strange bugs that I encountered, but otherwise, the experience is solid.

Wait: Emacs? Nano? Vi? Is this is a Linux based tool? No Windows support?

Nah, of course there’s Windows support: the goal of the project is to be cross-platform, remember? There IS one catch though: the entire project depends on Python being installed where you plan to run this. Doesn’t matter what version as long is it’s 2.7 or 3.6, and it’s installed with the help of the Python package installer, pip. So while most (but not all) Linux machines where you’re going to run SQL Server might have Python installed, getting it running on Windows is a separate install. So while mssql-cli is lightweight, you’ll still have a dependency to install Python. Which, again, some people might not be comfortable with. There’s nice, concise installation instructions over on the github project page.

I don’t know Emacs or vi, how much of a pain will be this be?

Then chances are you’re going to hate it. If you’re not used to keyboard-only interaction with a terminal, you’re going to have to get up to speed on how to navigate between different parts of the editor. And it’s a learning curve!

I’m an Emacs superstar and I can recite every vi shortcut by heart. Will I like it?

Each mode will use whatever keyboard shortcuts you’re used to, so you’re going to feel right at home and be writing and editing queries in no time.

How might you end up using it, Drew?

That’s a tricky one: I like the idea of having a simple tool installed on a Linux-based SQL Server for testing or troubleshooting purposes… but given that I need to install Python on a Windows machine to then install this, when I already have nice PowerShell integration, is a bit of a hard sell for me. That said, I do thing it has a unique purpose: as I continue to explore and learn Python, I’ve been using a dedicated Linux VM to do that on. It’s kind of nice to have a quick, easy, and efficient way to write queries against SQL Servers to verify data changes or code that is executing and doing things.

Still, though, I doubt I’ll be switching to something like this as a DBA or developer anytime soon. While you can do all your administration via T-SQL, sometimes, having an easy to navigate UI with multiple tabs is just nicer to work with, to say nothing of having an object explorer in SQL Server Management Studio, or source control integration like in SQL Operations Studio (which, by the way, is also cross-platform).

So this is a gimmick, then. Got it.

Come on Drew, no, that’s not a fair assessment. I think it’s a unique project that, like every other tool in your toolbox, has a time and a place, even if you just bought it to finish one project and never used it again. Command-line tools existing well before our user interfaces of today, and they’ll probably continue to be around for quite a bit longer, so as with anything else you just need to know about it, know how to install it, and hopefully know how to use it if the situation arises. With a simple installation procedure it’s easy to get off the ground and playing around with some of your testing or local instances. And remember: just because something isn’t a fit for you doesn’t mean it isn’t a fit for someone else, either. Should the question come up, you can at least say “yeah, I’ve played around with it.” Remember, the key here is that this is an interactive session. You wouldn’t use this like existing tools because it gives you a window to continue to work with your databases, not just issue commands. And some people will want that. Maybe you will too. If nothing else, it scratches a nice, nostalgic itch for me. I’ll keep it around to poke around in and keep an eye on the project as it (and other CLI tools for other databases) continue to develop.