Containers | Building a local TSQL development environment
Tags: docker, sqlserver, tutorial, dockercompose
Published: 2020-02-10 10:38:32

---
title: Containers | Building a local TSQL development environment
published: true
date: 2020-02-10 00:00:00 UTC
tags: docker, sqlserver, tutorial, dockercompose
canonical_url: 
---

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](https://dev.to/kgeorge314/containers-quick-start-sql-server-containers-41c3) and [docker-compose](https://dev.to/kgeorge314/containers-quickstart-docker-compose-for-sql-server-2opi). 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](https://datastories.aletasystems.com/assets/images/Posts/2020-02-10-building-a-sql-dev-environment/local-development-environment.png)

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`](https://github.com/aleta-systems/aletasystems.images.tsqlrunner) which is a custom-image based on `mcr.microsoft.com/mssql-tools`._

## SQL Server
{% gist https://gist.github.com/kgeorge314/3872318e9f23434a51b8e1994ecb843f file=db-docker-compose.yml %}

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_.

1. In your compose file, you map a local directory containing TSQL files to the containers `/tsqlscripts`
2. The local directory follows the convention of `databasename\tsql-filename.sql`
3. The runner will first run the scripts in `master\filename` (in alphabetical order)
4. 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`

{% gist https://gist.github.com/kgeorge314/3872318e9f23434a51b8e1994ecb843f file=create-db.sql %}

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.
{% gist https://gist.github.com/kgeorge314/3872318e9f23434a51b8e1994ecb843f file=inittools-docker-compose.yml %}

## 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](https://flywaydb.org/getstarted/how)

The basics:

- `volumes` shows flyway where the `SQL` & `conf` folder are
- `command` tells it what we want to do along with the various parameters
{% gist https://gist.github.com/kgeorge314/3872318e9f23434a51b8e1994ecb843f file=flyway-docker-compose.yml %}
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](https://github.com/aleta-systems/aletasystems.images.tsqlrunner/blob/e4d5471434587aa15166f9beda20a61aced5daaa/tooling/execute-sql-scripts.sh#L5-L10)