DEV Community


Posted on

SQL Server and Flyway: a Docker approach

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!


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

  1. Clone this repository;
  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:

├─ db/
│  ├─ scripts/
│  │  ├─
│  │  ├─
│  │  ├─ init.sql
├─ flyway/
│  ├─ conf/
│  │  ├─ flyway.conf
│  ├─ sql/
│  │  ├─ <migrations goes here>
├─ docker-compose.yaml
Enter fullscreen mode Exit fullscreen mode
  • 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'
    container_name: sqlserver-2022-database 
    command: /bin/bash /scripts/
      - dbdata:/var/opt/mssql
      - ./db/scripts:/scripts
      - 1433:1433
    image: redgate/flyway
    container_name: flyway
    command: migrate -user=${DB_USER} -password=${DB_PASSWORD} -connectRetries=60
      - ./flyway/conf/:/flyway/conf
      - ./flyway/sql/:/flyway/sql
      - db
Enter fullscreen mode Exit fullscreen mode

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 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/ & \
/scripts/ & \
Enter fullscreen mode Exit fullscreen mode

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

It is important to notice that and SQL Server are executed in parallel.

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

sleep 30s

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

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:

Enter fullscreen mode Exit fullscreen mode

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!


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.

Top comments (0)