SQLServerCentral Article

Using the New Open Source SQLCMD to Create Containers


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.

create container and query it

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 ;).

Container list with mssql container

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

Deleting the container

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.

starting a container

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

container started and restore complete

I tried to run a query, and go this:

query run from current context

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.

go-sqlcmd current context

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.

Two go-sqlcmd 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.

docker container list

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

create an object in go-sqlcmd

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.

Switching contexts

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.

Error with -F switch

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.

vertical results

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

stopping a container

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.

error trying to stop a particular context

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

go-sqlcmd error with -F flag

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.

one context in the sqlconfig file

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.

containers still listed

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

Empty sqlconfig file with keys and empty values

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

Error trying to run stored proc

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

Three containers listed


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.


5 (1)

You rated this post out of 5. Change rating




5 (1)

You rated this post out of 5. Change rating