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 thedocker-compose.yaml
file. Note: The keys you use must match the ones used in thedocker-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 thedocker-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
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)