At a few different conferences in early 2023, I've heard Microsoft employees talking about the version of sqlcmd, called go-sqlcmd. I'm not sure why the language it's written in matters, and I suspect that Microsoft developers are proud of their work, as most developers would be. However, this new version of sqlcmd is supposed to have new features, so I decided to investigate it.
This article looks at installing it, the basics of the new command line tool, and a few new features that exist.
For the sake of clarity, we will refer to the tool as go-sqlcmd, even though the command line call is simple to sqlcmd.
The go-sqlcmd utility is designed to be a more modern command line utility. It is designed to do everything the old sqlcmd did, but include additional options for Azure Active Directory authentication, more environment configurations, more logging/tracing, and get results in a vertical format that is easier to read. This also uses the go-mssqldb driver instead of the older sqlcmd ODBC driver. Overall, the goal here is to modernize the application while keeping backwards compatibility for existing scripts. Maybe the biggest win here is this is cross platform, so not only on Windows, but also MacOS and Linux. Good for pipelines where agents might not run Windows.
There is a Microsoft Learn article on this new go-sqlcmd tool, which is fairly sparse. This describes the install and then some of the changes from the previous sqlcmd utility. There are not any examples of how to use it, though there is a link to the sqlcmd utility, and it appears most of the older switches still work. The article does cover some of the enhancements, primarily around authentication.
The MS Learn article does link to the GitHub repo for this tool, with more information there. There are a few examples, but the information and explanations are still light.
The instructions for Windows note that you can get this from Winget. I checked to see if I had Winget installed by running it at the command line. Surprise, surprise, it was installed on my Windows 10 desktop. Not sure if I did this or it was there by default.
Following along, I ran this code:
winget install sqlcmd
This started the download, as you can see below. This is a fairly small download.
After this completed, I had to agree to the terms. I did that and a second download started. Together these are small downloads, but it was interesting to see two things happen.
When this completed, a Windows installer ran. I didn't need to do anything, but it looked like an MSI install ran. This finally completed and I had a command line.
Running Basic Queries
I ran a couple basic queries from the same command line. These worked fine, and I used the older sqlcmd syntax.
I wasn't sure which tool I was running, so I tried to get help. The old sqlcmd uses DOS style parameters, all single dash and a letter. The go-sqlcmd uses newer Unix style parameters with two dashes for named parameters. Apparently I was running the old version. 🙁
I stopped here one day and the next day opened a new command prompt, at which this appeared to run. You can see help working below:
Most of the existing functionality in sqlcmd is ported over, so I won't test or go over the ways in which you might use sqlcmd. However there are two things I find interesting.
With sqlcmd, when I get results from a table, it can be hard to read things at the command line, especially when you have a lot of columns. As an example, here is a set of data from a test table that I have in a database. Notice below the results roll over to a second line.
This is especially cumbersome when I'm using sqlcmd with the admin connection. There I'm usually stressed and in a hurry and I can't easily decipher information.
The go-sqlcmd gives a new formatting option with the -F parameter and the vertical option. This produces results that are easier to read. Of course, if I return 100 rows, this is still hard to read, but I can more easily scroll through this in a command line window.
Much easier to read.
I am not a big fan of colors most of the time. I started in computers with green screens and text only, so I got used to working with lots of text and not WYSIWYG and colors. Most of the time I often write in text editors that don't use formatting. However, with go-sqlcmd, you have some options here. These are very, very lightly described in the GitHub page, so I'll give you a few samples. You can set an environment variable, SQLCMDCOLORSCHEME, but I'll do this interactively for now.
I can open go-sqlcmd interactively by typing this code:
sqlcmd -S . -d sandbox
This will give me the familiar 1> prompt, indicating that I am connected to a database.
If I now enter this code,
I get a list of colors supported with the theme name. These are a key of a name and a value that shows the colorization. There are a lot of these, as you can see. This overran my screen, so there are more than you see below.
I can pick one of these, for example, I'll choose emacs. I can set this with this code:
:setvar SQLCMDCOLORSCHEME emacs
If I run a query, I see this:
If I want to get these results vertically, I'll add that switch when I run go-sqlcmd. Get out and set the same environment variable, and I see this:
I don't know how useful this is, but it is a nice little enhancement. I also tested this as ":setvar sqlcmdcolorscheme emacs" and that works, so not case sensitive.
One of the things I often do with SQLCMD is make a quick connection to a local database. I was wondering if I could do this easily with go-sqlcmd since I liked the idea of a context for running queries and not needing to constantly enter the credentials or use up arrow.
I can, but it's not simple. There is a two step process here to create an endpoint and then make a connection. First the endpoint. I use the config command here with add-endpoint. I can give this an address for the endpoint. The port is optional.
sqlcmd config add-endpoint --name ep1 --address localhost --port 1433
I actually created it without the port and it worked:
I can now set the context with a name and credentials.
sqlcmd config add-context --name defaultinstall --endpoint ep1
From here, I can see my context with get-contexts. In this case, I have a few.
Now I can run a query. My local default install is Aristotle, and I see this:
I can also get rid of contexts with uninstall.
I can clean up the endpoints as well. There is a delete-endpoint, but the uninstall removed this for me.
I didn't check the AAD authentication, mainly because I don't have an AAD account I use for stuff. I tried setting one up in a free account, but it didn't quite work, I'm not domain joined at home, and this feels like more of a hassle than sticking with MS accounts or name/password access for resources. I assume this work, but would love to see someone test this (and write an article). If you want to do this, please test MFA and various AAD types of connections.
I'll cover this in another article.
In this article, we've installed go-sqlcmd and used it like sqlcmd, as well as taken advantage of a few new features. We can colorize results (somewhat) and also get vertical formatted results. I didn't cover containers, but will look at that in a future article.
Overall, this is an interesting new tool that I think will be helpful and useful over time, but it has a long way to go. As of April 2023, it is still in preview, which is where it needs to be. There are a number of open issues, including a few I've filed. Please feel free to test things and file more.
Hopefully you've seen something here that either tells you to experiment and invest in this, or ignore it for now. Feedback and comments are always welcome.