In my previous article, I looked at the basics of the new, open-source SQLCMD, which is a new version of sqlcmd written in GO with modern features. This article will cover the features in this utility to help you set up and query containers, which is a really nice feature for getting a new environment set up. We'll also cover customizing these to start and removing them. You can read the previous article and check out the GitHub repo for the tool.

Getting a SQL Server Container

One of the interesting ways in which I can use go-sqlcmd is to set up an environment on the fly. The current use-case here is using Docker or Podman to set up a new container environment. The help gives some hints, but I'll run this code:

sqlcmd create mssql --accept-eula

This gives me a basic SQL Server container running, just as if I'd installed a new instance of SQL Server. I can see the port. If I connect, I can run a query and see a few things.

I can also check my Docker container list and see this container. This container is the first one, from mcr.microsoft.com. Others run my Teslamate logger ;).

Not a great container name, so let's get rid of it.

Note the delete checks if I have a user database and won't delete it if I do. You can see this demonstrated below.

Starting with a Database

Let's now create a container that has a database and data. We can do this with the --using flag, providing a URL. This is to a backup, which will be restored to the instance. I do this with the command below.

sqlcmd create mssql --accept-eula --using https://aka.ms/AdventureWorksLT.bak

This will start downloading the container if you don't have it. It should then restore the AdventureWorks database inside the container. I hadn't updated my container image lately, so this was slow. However, after the download, you can see it started restoring.

When it finished, I had a CLI again, with some hints about what to do.

I tried to run a query, and go this:

Notice I didn't enter any credentials or anything. The images above showed a few things, like the container is looking for connections on 1435. I have an instance installed on 1433, so I'm guessing that go-sqlcmd picked another port for the container. It also added Steve as a user, which is my current Windows user. When I started the container, go-sqlcmd created a context for me for this connection. I can see this with the sqlcmd current-context command.

If I start another container, I'll see this. Note you see the bottom of the container start and the new port. I now have two contexts.

I can also see two Docker containers running, with the ports I expect. At the right below, you can see 1435 and 1436 as the ports mapped into the container.

I'll connect to one container and add a new object.

Let's switch context now. I can do that with the config command. Once I do that, I'll change databases and run my proc. As expected, it fails.

One interesting thing I found is the -F switch doesn't seem to work with contexts. Very annoying. You can see this creates an error below.

However, I can run sqlcmd in the old mode (sqlcmd mode, not go-sqlcmd mode). I can do this by adding the -Q flag instead of "query". This gives me vertical results. It appears to use the current context when I don't pass in any authentication, server, or other parameters that specify a context.

To stop a container or context, I can use the stop command, as shown here.

This doesn't work with a parameter, which I think is a bug. I hate to have to cycle through multiple contexts to stop them.

I also cannot get my vertical results like this, which is very annoying.

I can also delete a context, which I do with delete. This does delete the entry in my sqlconfig file (in users/%username%/.sqlcmd). You can see here I have mssql2, but not mssql.

As shown below, this deletes the context. I assumed this also deletes the container, but after doing this I still see the container in my list. This is because the container isn't deleted if it has a user database. I can override this with the --force flag, but this is a nice safety in case I accidentally try to delete the wrong container. I can stop and delete the container with Docker commands, or use --Force. What I need to ensure I can do as well is extract my data and code, though if I'm using a good DevOps process, I should be saving my code as I go. Flyway Desktop would help here.

Once I removed both, my sqlconfig file is basically empty.

I created a new context, which ought to have my proc in it is the context is reused. It doesn't.

And you can see below I have a third container listed.

Conclusion

We can also spin up containers and get the context to the container. This mean we can easily and quickly connect with sqlcmd and query the db. What I'd like is to also ensure I get a connection string programmatically, which would be useful for automating the environment for new developers.

We can spin them down, but not remove them by default if we have a user database. We can force this if needed.