The webapi needs a decent database and SQLite is not the right tool in this case. So I played a bit with the docker images of SQL Server and decided to go with that. The docker images give you the option to run the database when you need them without the need to install any dependency (apart from Docker). When the project is done, delete the containers and images and you are done. And docker is multi platform so it runs on my Linux and Windows workstation.

SQL Server in a Docker container

I think most web developers using any major JavaScript based frontend (like Angular, React or Vue) will already use Docker, even on Windows. And there is no reason not to do that. Docker runs really well on Windows in combination with WSL2, especially for developers. And using containers gives you a easy reproducible environment to work with. The same goes for running SQL Server.

First of all the database needs to run. Based on the documentation I use this command.

docker run --rm -ti \
	-p 1433:1433 \
	--env "ACCEPT_EULA=Y" \
	--env "SA_PASSWORD=SecretPassword-865" \
	--env "MSSQL_PID=Express" \
	--env "TZ=Europe/Amsterdam" \
	--volume ~/src/football/__mssql/data:/var/opt/mssql/data \
	--volume ~/src/football/__mssql/log:/var/opt/mssql/log \
	--volume ~/src/football/__mssql/secrets:/var/opt/mssql/secrets \
	--volume $PWD:/app \
	--workdir /app \
	--name sql1 \
	--hostname sql1 \
	mcr.microsoft.com/mssql/server:2019-latest

There is a lot going on in that command.

First a lot of environment variables are defined. Let’s go through them.

ACCEPT_EULA=Y and SA_PASSWORD=SecretPassword-865 are required to get the database running. MSSQL_PID=Express determines which product I want to use. For this project I really don’t need anything more then the Express Edition of SQL Server. I also set the timezone to TZ=Europe/Amsterdam to make sure the times are handled correctly. Especially with the upcoming World Championship in Qatar, putting the times with a timezone offset in the database would be really nice.

The examples in the documentation all run the database detached, but I don’t need that. I don’t mind having one tab in my terminal dedicated to a running database while developing. Therefore I replaced -d in the samples with --rm -ti. To exit the database I can simply press Ctrl+C in that tab and it stops and the container is gone.

This has one major drawback. The data is destroyed once the container is gone. To fix that I added the --volume options. Now the data is stored outside the container in the given directories. Note that to prevent permission denied errors when you run the command for the first time, you need to manually create the data, log and secrets directories and chmod 777 them. This way the mssql user in the container can write to the volumes. Also note that I have a global gitignore rule to exclude everything that starts with two underscores. So the __mssql directory is not interfering with my git repository even though it is located within my source directory.

Create the database

With the SQL Server running, the database needs to be created.

This is where the options --volume $PWD:/app and --workdir /app come into play. With them you can docker exec into to container and use sqlcmd from within the container to run SQL commands to create or query the database. For instance:

docker exec -ti sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P SecretPassword-865 -i ./tables-mssql.sql

Again, no need to install sqlcmd just use it within the container. Not another tool you don’t use that much polluting the system. All I need are some scripts to run these commands.