SQL in a whale container🐋

Have you ever wished to play with SQL but don’t had the energy to install lots of apps that are also hard to remove when you are done?

Architecture Diagram

Head note: this article is more about Docker than SQL or whales.

Instead of installing SQL server or XAMPP or tools to visualize databases like SQL Server Management Studio (SSMS) / MySQL Workbench you could just run a SQL server isolated in a Docker container. In this way all you need to have installed is Docker (assuming you have already the VS Code installed), which is available on any operating system and its also not as hard as you’d imagine to use.

And not installing crap that I would pretty soon delete is a big reason to use this way, but if you’re not convinced yet here is a list of other benefits:

• Isolation, of anything else from you’re system that may influence, and this also improves security
• Versioning, you could choose very easy what version do you want or need for your project
• Compatibility, if you work from Windows with someone who is using MacOS you won’t have any problems of compatibility

And I am sure there are more benefits but I am too lazy to think of others.

Before we begin I want to let you know that we are going to achieve that through two methods, one will be from the CLI using docker run and for the second one we will use a configuration file named docker-compose.yml.

Option 1: using docker CLI

So, let’s start with searching for an image, and my choice was mcr.microsoft.com/mssql/server. To my disappointed it didn’t have a logo image but at least the instructions where pretty clear and also it had like 50M+ pulls. So I guess this one will do the trick. Next we can just run a single command and the container will be up:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=asdf1234!" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest

Also it would be nice to understand what that does before. Here we use docker run to run a container. We have some environment variables noted with -e, port configuration noted with -p, detach mode with -d, which basically doesn’t block the terminal after the container is build. If you don’t specify it you will receive stuff in the terminal until the container is stopped (this can be useful for debugging). And the image name at the end (which it will also download).

About the port configuration -p 1433:1433, we want to use outside the container the SQL server on port 1433 (the part left to :). SQL runs by default on port 1433, so we can just use 1433:1433 to make things as simple as possible (the part right to the : is the port from inside the container).

And about the variables first we have ACCEPT_EULA and we have to accept the end user license agreement. Here I was just playing and testing to see how the image works but in a real case scenario be sure to comply with the licensing (which also, I did) to make sure you can use that piece of software. On the docker hub page of this image you can scroll down at the end of the page and you’ll see a licensing section. Read that!

All the time check the LICENSE !

After you ensure that you comply with the license you will encounter the second variable MSSQL_SA_PASSWORD and I think you can figure out by yourself what is it (in case you didn’t is the password for admin). The default username will be sa which stands for system administrator. When you set a password keep in mind that you have to use a strong one. It must have at least 8 characters and use both letters and numbers. If the password is too weak you will get an error when running the container.

And that’s all you need (for now at least). After you press enter and run the command (and drink a glass of mango juice until the container is built) you can connect to the database. The connection string is localhost, the user is sa and the password is what you set to the password variable.

If somehow it doesn’t work check the terminal for any errors (you could also try to run the container without -d to be able to see more output in the terminal). Also using docker ps in any terminal will show you all the running containers. If you still can’t figure out why it doesn’t work try deleting everything and run it again (you can delete a container with docker rm [container]), and if you can’t run it even now try with another image, instead of 2022-latest maybe you can try to use the 2019-latest or a specific version instead of just reling on the latest version.

Also keep in mind that at some point you will need to stop the container from running or also to delete it or the image. You could achieve that from the Docker desktop interface or by using docker stop [container]. By default Docker will set a name for each container, you can see it in the table output of docker ps (its the last column in the table). And if you want to clean up you could use docker system prune, but be aware that this will remove all unused containers, networks or images.

Option 2: using docker compose file

Even if this approach work let’s make things a bit more interesting. Remember when I said you only need to have Docker installed? Well now you will also need a text editor for the next step (which I think you should have already). We will create an instruction file that will tell Docker to do exactly what we did with that long command.

First we need to create that instruction file, for its name use docker-compose.yml. A yml (or a yaml) file is a file that keeps human readable configuration inside, in this case we will configure how Docker should run the SQL server. First, for docker compose we have to specify the version, version: "3" its enough. Next we can start configuring services that Docker will run, in this case a single service. And the file will look like this:

version: "3"

services:
  mysql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sql-container
    ports:
      - 2000:1433
    environment:
      ACCEPT_EULA: Y
      MSSQL_SA_PASSWORD: asdf1234!

We have the mysql service that runs based on the same image we used previously. Now to make things more interesting I added a name for the container, this time Docker will use that specific name instead of creating a random one. Also I changed the outside port to be 2000, note that the SQL port from inside the container is still 1433. And at the end we have the environment variables.

Using a yml file made everything easier to understand or maintain and to run it you only need to type docker-compose up in a terminal and the result will be the same (you can stop the container with docker-compose down). You should be able to use even docker compose up / down, without the dash (-), this being the new way of running it.

Keep in mind that now the outside port is 2000 so the connection string will be a bit different, depending on your tool to interact with the DB you may not need to add the port. I am using a VS Code extension to work with SQL and when I specify the connection string there I have to use localhost,2000 (yes with , not :).

A problem that you may encounter now is that if you delete the container you will lose the database. We can fix that by using volumes, which are ensuring data persistence between host and container. To use them in the docker run command you need to add another flag, -v host:container and instead of host and container you will have the paths. But this will make a long command even longer, so let’s integrate the volumes in the yml file.

version: "3"

services:
  mysql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sql-container
    ports:
      - 2000:1433
    environment:
      ACCEPT_EULA: Y
      MSSQL_SA_PASSWORD: asdf1234!
    volumes:
      - ./db-dump/data:/var/opt/mssql/data
      - ./db-dump/log:/var/opt/mssql/log
      - ./db-dump/secrets:/var/opt/mssql/secrets

And we have three volumes to make things as explicitly as possible. Outside we create a folder that will contain the database data and inside the container we need to specify the path to the things we want to save, for this image we need to save three folders, data, log, and secrets to make sure everything we need related to the database is saved. And also our folder structure will look like this:

Folder Structure

We have the db-dump folder (btw you can use any name you want) with the folders from the database, I made a sql folder where I keep the SQL interrogations, the docker-compose.yml file (that we created) and I have the long docker run command in the run.sh file.

Before I finished with this I want to do just one more thing, instead of having the password to our database exposed like that in the yml file I created a .env file on root in which I just placed the two variables and I tweak the yml file to load environment variables from a file instead.

ACCEPT_EULA=Y
MSSQL_SA_PASSWORD=asdf1234!

And now the final version of docker-compose.yml is:

version: "3"

services:
  mysql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sql-container
    ports:
      - 2000:1433
    env_file:
      - .env
    volumes:
      - ./db-dump/data:/var/opt/mssql/data
      - ./db-dump/log:/var/opt/mssql/log
      - ./db-dump/secrets:/var/opt/mssql/secrets

And for this exact configuration you will connect to the database using:

• connection string: localhost,2000
• SQL login user: sa
• SQL login pass: asdf1234!

Keep in mind that dockerizing SQL is just a small step you could take. Basically you could use containers for everything, like having the entire infrastructure of a huge application running in different containers in a big docker-compose.yml file that will orchestrate all services (like how we added mysql you could also add more services in the same file).

And just imagine that you’re company has 100 developers who work on the same app, all of them using different computers with different configurations and OSs. But if everything is set right in Docker you could just run a command and everything will work the same for everyone. All of the developers will have the same versions running on the same operating system inside the container. You only have to install Docker and a text editor.

For you’re next project try to use Docker, because after you learn how to use it, you will thank yourself!