Simplifying local development with automated database migrations

01 Jul, 2024

Thanks to container technologies like Docker, we can run most kinds of databases nowadays within seconds. As a result, a common and standardized environment based on containers can enable developers to get started in no time. One thing that can simplify a developer's daily life even more, are automatically applied database migrations. This is especially useful for developers who have no deep knowledge about databases, and team members that don't want to check for new migrations every time they pull the latest changes from the repository.

The approach described here utilizes Docker, a Microsoft SQL Database and migrations managed with Entity Framework. The general thought, however, should be easily applicable to other tools and technologies.

How it works

We want to apply the database migrations immediately after our containerized environment has started. Therefore, it seems natural to put the database migrations also into a container image. When we run this image, the container will connect to the database container and will then apply the migrations. The only pitfall is that the migrations should not run before the database is available, which can take several seconds. With a custom, yet simple health check for our database container, the migration won't wait longer than necessary.

The migration container image

Let's assume our server-side code is placed in a folder named backend which contains a solution file and a project named Database with Entity Framework database migrations. The Dockerfile for our database migrations container image then looks as follows.

FROM mcr.microsoft.com/dotnet/sdk:8.0.101 AS build-env
WORKDIR /App

COPY ["./backend", "."]

RUN dotnet restore 
RUN dotnet tool restore

ENTRYPOINT dotnet ef database update --project src/Database --connection "$DATABASE_CONNECTION_STRING"

On top, we use FROM to load a container image of a suitable version of the .NET SDK that can run the migrations. Then we choose an arbitrary working directory with WORKDIR where all upcoming commands will be executed in. Next, we copy all source code to the working directory. ¹

With RUN we can execute any command in the container. First, we restore all project dependencies. Second, we restore all .NET tools. The latter requires a manifest file of .NET tools which contains information about the Entity Framework CLI. As an alternative, we could also install the CLI explicitly.

RUN dotnet tool install --version 8.0.0 dotnet-ef

Finally, we tell Docker to execute the migrations when the container starts with ENTRYPOINT. Here we read the connection string of the database from the environment variable named $DATABASE_CONNECTION_STRING. When we run the container, we must make sure to provide this environment variable.

To build the container image manually, we store the Dockerfile (e.g.: database-migrations.dockerfile) and run the build command.

docker build -f database-migrations.dockerfile .

The database orchestration

With Docker Compose we can easily run multiple containers with a specific configuration. As it is file based, we can store our Docker Compose files in the repository and make them available to every team member. Additionally, we can run multiple Docker Compose files together. This allows us to have multiple, smaller files and to run only what we really need. When we think, for example, of a basic client-server web application, we could create one Docker Compose file for the front-end, one for the back-end and one for the database. Depending on what we are working on, we can run just one Docker Compose file, two of them or all three.

The following Docker Compose file uses our previously created Dockerfile to build the database migration container image on the fly. We pass the environment variable with our connection string and tell Docker to start this container not before our database container is healthy.

The database container is quite basic except the health check. In simple words, the health check tries for one second to open a TCP connection on the default port of the database server (1433).² This works as soon as the database server is available and ready to process commands. The huge advantage of this approach is that it doesn't require any database credentials.

services:
  migrations:
    build: database-migrations.dockerfile
    environment:
      - DATABASE_CONNECTION_STRING
    depends_on:
      sqlserver:
        condition: service_healthy

  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
	# On Apple devices of the M-series we must specific the platform.
    platform: linux/amd64
    ports:
      # Exposing the port is only necessary if want to access the database
      # from outside. For the migration and health check this is not needed.
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=${DATABASE_PASSWORD}
    healthcheck:
      # Test if the SQL server is available without using any credentials. 
      test: timeout 1 bash -c 'cat < /dev/null > /dev/tcp/127.0.0.1/1433'
    volumes:
      - sqlserver_data:/var/opt/mssql

volumes:
  sqlserver_data:

Docker Compose supports various ways to provide environment variables. One of them is to create an environment file named .env and to define all variables in there.

DATABASE_PASSWORD=A secure passphrase for our database
DATABASE_NAME=EnterTheDatabaseNameHere
DATABASE_CONNECTION_STRING=Server=sqlserver;Database=${DATABASE_NAME};User Id=sa;Password=${DATABASE_PASSWORD};TrustServerCertificate=True;

Please note: we store sensitive information here as environment variables and in a not encrypted file. Both can be considered bad practice and we do this just for demonstration purposes.³

If we store the Docker Compose file as docker-compose.database.yml, we can now start our new database environment, including the automatic applied migrations.

docker compose --file docker-compose.database.yml up --build --detach

With docker ps and other common Docker commands, we can see how the database migrations container waits until the database server container is healthy. Then it starts and executes the migrations before it shuts down gracefully.

We can run the up command again and again. Docker will only start not running or changed containers. This is useful when we just want to rerun the migrations while all other containers continue running.

Conclusion

Assuming there is a basic knowledge of a container technology such as Docker, the automated execution of database migrations is only a few lines of code away. The biggest challenge is probably the optimal health check for the database container so that the migrations do not start prematurely.

References and notes

Copying all files to the container image can become problematic. First, you usually don't need all files and folders, so it slows down the image build. Second, build artifacts and system specific dependencies can produce unintended side effects. A .dockerignore file in the repository can exclude all those unwanted files and folders.↩
For more information, please refer to Steven Willis' and Dave Jarvis' comment about how to test if a remote TCP port is open from a shell script on Stack Overflow.↩
Please refer to the official Docker documentation for information about how to use secrets without having to use environment variables.↩

#devops #docker #dotnet #mssql