SQL Server and Flyway: a Docker approach

leopardorossi — Tue, 14 Mar 2023 17:59:12 +0000

For a personal project I wanted to realise a development environment with the following characteristics: easy to setup, easy to maintain and easy to share with my teammates. With this problem to solve, three tools immediately came to my mind: Docker, SQL Server and Flyway.

In this article I will describe how I glued them together in order to create a flexible development environment.

Let's dive into it!

TLTR

If you like to setup SQL Server and Flyway with Docker:

Clone this repository;
Create an .env file at root level. In it define the environmental variables that will be used in the docker-compose.yaml file. Note: The keys you use must match the ones used in the docker-compose.yaml file.
Modify Flyway configuration file in flyway/conf folder with your parameters. Note: The database host name must match the SQL Server service name in the docker-compose.yaml file.
Put your migration files in flyway/sql folder.
Open a terminal window and execute docker compose up -d.

Project structure

The structure of the project is the following:

project-folder/
├─ db/
│  ├─ scripts/
│  │  ├─ entrypoint.sh
│  │  ├─ db-init.sh
│  │  ├─ init.sql
├─ flyway/
│  ├─ conf/
│  │  ├─ flyway.conf
│  ├─ sql/
│  │  ├─ <migrations goes here>
├─ docker-compose.yaml

db/scripts folder contains a set of shell and SQL scripts used in SQL Server container initialisation phase.
flyway folder contains the information Flyway needs: configuration and migration files.
docker-compose.yaml is the Docker compose file where SQL Server and Flyway containers are defined and linked together.

The compose file

The docker-compose.yaml file is where the development environment is formally described and it looks like the following code snippet:

version: '3'
services:
  db:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sqlserver-2022-database 
    command: /bin/bash /scripts/entrypoint.sh
    volumes:
      - dbdata:/var/opt/mssql
      - ./db/scripts:/scripts
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=${DB_PASSWORD}
    ports:
      - 1433:1433
  flyway:
    image: redgate/flyway
    container_name: flyway
    command: migrate -user=${DB_USER} -password=${DB_PASSWORD} -connectRetries=60
    volumes:
      - ./flyway/conf/:/flyway/conf
      - ./flyway/sql/:/flyway/sql
    depends_on:
      - db
volumes:
  dbdata:

There are two services defined in the file, one for the SQL Server container (db) and the other for Flyway container (flyway). They both use volumes to persist data and to copy files from the host machine into the containers themselves. In particular, SQL Server service copies scripts folder; while the Flyway one binds the conf and sql directories.

Until here, nothing special.

Before launching our services it must be considered that, in order to apply migrations, Flyway looks for an existing database. Unluckily, this condition is not satisfied the first time our containers are executed. Therefore, to fully automate the development environment, an initialisation procedure is needed.

The initialisation procedure

The desired order of operations is the following:

Launch SQL Server container;
Create the target database and schema (if they not exist);
Launch Flyway container and apply migrations to the database.

Everything begins with the entrypoint.sh file, which is executed when the db service starts (see the command tag defined for it in the compose file).

It is defined below:

chmod +x /scripts/db-init.sh & \
/scripts/db-init.sh & \
/opt/mssql/bin/sqlservr

Here three things are happening. First of all, execution permission is granted to db-init.sh file (we will dive into it in a second). Then, db-init.sh is executed and eventually SQL Server is started.

It is important to notice that db-init.sh and SQL Server are executed in parallel.

At this point, it is time to initialise our database! And this is exactly what db-init.sh does.

sleep 30s

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${MSSQL_SA_PASSWORD} -d master -i /scripts/init.sql

The script waits a fixed amount of time (30 seconds in this case) to be sure that SQL Server is up and running. Then, it runs the SQL script defined in init.sql file. The script is quite simple, indeed it checks if the database exists and creates it if it doesn't:

IF DB_ID('<YOUR_DB_NAME>') IS NULL
  CREATE DATABASE [YOUR_DB_NAME];
  GO
  USE [YOUR_DB_NAME];
  GO
  CREATE SCHEMA YOUR_SCHEMA;
  GO

And this is the initialisation procedure. Thanks to it when SQL Server container is launched for the first time everything is automatically setup.

At this point, our containers can finally be executed with the docker compose up command. Once they are up, if you look into the Flyway container's logs you can notice that at the beginning it fails to connect to the database: this is because SQL Server container is still starting. Despite this, after a while, the migration scripts are successfully executed: the initialisation procedure did its job. You can verify yourself by connecting to your database!

Conclusion