Containers | Building a local development environment

Containers, Docker Compose, Local Development, DLM with Containers · 10 Feb 2020

Docker-Compose, Flyway and a T-SQL runner to help you get up and running with a local development environment.

In my introductory posts on docker and docker-compose. We managed to set up an empty SQL Server container, but in the real world, that is not very useful.

Okay great - we can create SQL Server containers now let us build on that to create a ready-to-go development environment with docker-compose up.

Let us take a moment to talk about the objective. We need a SQL Server, a database and something to deploy code to it (build). We will define a service (an instance of a container) in our compose file for each of these tasks.

local-environment

Containers used:

SQL Server using image mcr.microsoft.com/mssql/server A simple T-SQL Script runner aletasystems/tsqlrunner Flyway for Build/Deployment boxfuse/flyway

Flyway cannot create a database it expects the database to exist before deploying the code. This is why we use aletasystems/tsqlrunner which is a custom-image based on mcr.microsoft.com/mssql-tools.

SQL Server

We have configured our SQL-Server as follows.

Running on port 14333
Username sa
Password is set in a .env file

Initialise

We now need to initialise SQL server using the container aletasystems/tsqlrunner.Our initialisation process is really simple, execute a create database script against the master database.

First a quick tour on how aletasystems/tsqlrunner works.

In your compose file, you map a local directory containing TSQL files to the containers /tsqlscripts
The local directory follows the convention of databasename\tsql-filename.sql
The runner will first run the scripts in master\filename (in alphabetical order)
Then process each database and script (again in alphabetical order)

With this information, we place a create database TSQL script file (create-db.sql) in ./path2initsqlfiles/master

We now add inittools service to our docker-compose file, we configure it as below.

The SQL Server it connects to is the db service we defined earlier
It users the username sa and password (again it is taken from our .env file).
We give it the path to the SQL Scripts to run.

Deployment

We will use flyway to deploy our code, but you can plugin an alternative that suits your need. I found flyway the easiest to work with, it is a fantastic tool and I hope to write/talk about it in the future.

I won’t get into too much detail about how flyway works as that deserves it own post, but if you are interested you can read How Flyway works

The basics:

volumes shows flyway where the SQL & conf folder are
command tells it what we want to do along with the various parameters

Some useful commands:

To re-run flyway with the latest changes to source code docker-compose restart flyway
To view the flyway log docker-compose logs -f flyway

How did we control order?

We used the depends_on property to tell each service what it depends on. This is not exactly true, as it is (and from what I understand) the moment a service comes online, docker believes it is ready - which can be problematic as SQL Server takes a few seconds to become accessible.

It is therefore important that your services have a retry/delay mechanism built-in. Flyway has an additional parameter -connectRetries=60 and aletasystems/tsqlrunner has a modest retry mechanism