Running MS SQL Server locally with test data using Docker

When developing locally on my machine I like to have a dev database that I can play with (= to wipe out) with no impact to anyone, and optionally load test data into it.

The purpose of this post is to show how we can use Docker to launch a local instance of Ms SQL server that will ingest local SQL script and tables data from csv files.

By extension it can be used for end to end testing as your database will have an expected state before running your tests, and will run in a container.

That’s quite handy for API testing!

What is happening?

The command docker-compose up launches locally (on windows or mac) the docker image microsoft/mssql-server-linux and will excecute the queries in /table/*.sql , and import the data for each table in /data/*.csv

Once started the database is accessible at 0.0.0.0:1433 for local development, or even for integration/end to end tests.

Code organisation

src/
├── sql/
│ ├── data/
│ │ ├── MyTable.csv
│ ├── table/
│ │ ├── MyTable.sql
│ ├── entrypoint.sh
│ ├── init.sql
├── docker-compose.yml

1. docker-compose.yml launch sqlserver AND entrypoint.sh.
2. entrypoint.sh wait for sql server to start and then:

• execute all the sql files in /table
• import all the csv in /data (filename = table name)

docker-compose.yml

entrypoint.sh

init.sql

MyTable.sql

MyTable.csv