SQL Server and Flyway: a Docker approach
Tags: docker, database
Published: 2023-03-14 17:59:12

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:

1. Clone [this repository](https://github.com/leopardorossi/sqlserver-and-flyway);
2. 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_.
3. 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_.
4. Put your migration files in `flyway/sql` folder.
5. 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:

```yaml
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:

1. Launch SQL Server container;
2. Create the target database and schema (if they not exist);
3. 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:

```shell
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:

```sql
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
Putting SQL Server and Flyway together in a Docker friendly way is nothing special. At the same time, it is a delicate operation which requires SQL Server container to be ready for Flyway execution. With a bunch of scripts and a bit of synchronisation among containers, your development environment is ready for your awesome projects.