How to use tSQLt unit test framework with a SQL Server database in a docker container

This blog post describes how to add the tSQLt unit testing framework to a database running in a docker container. You may have already read my article on how easy it is to install against a ‘normal’ SQL Server instance – running on windows…

How to install the tSQLt unit test framework to start unit testing your database code

..but what about SQL Server running in a docker container??

Background:

I have a SQL Server instance running in a docker container as described in my previous post:

Setting up CentOS to run docker for SQL Server

The main reason why I run SQL Server in a container is so that I can run up specific versions – all from a script and generally as part of an automated build within Continuous Integration processes.

Method:

The first thing we need to do is spin up our docker container which has our SQL Server instance:

1. Spinning up our SQL Server in container — Let’s spin up SQL Server and use the latest version of SQL Server 2017

Just to verify that it is running let’s run

docker ps

We can see that the container has an ID of 789f58fbef3f and a name of sql1 .

I have also mapped the default port of 1433 to 59666

2. container SQL Server is running

We can now connect to SQL Server on port 59666 and do some typical queries whenever we connect to an instance for the first time:

3. What version is running — Connecting to our SQL Server instance running in a container known as 789f58fbef3f

Note that in the screenshot above – the servername result is the name of our container.

Now we can create our database and SQL Authenticated users as normal.

Yes – you interpreted that sentence correctly – right now you cannot use windows authenticated users against SQL Server running in a docker container on Linux. There is a way to get around this that I will be blogging about in late January 2019.

We can add our database to Source Control as per normal and apply the latest changes in our branch. I use the popular Redgate tooling for Source Control (and DevOps) activities

9. Adding stuff from Source Control — We can treat our database in the container like any other database (except for windows auth…)

10. Progress — Applying changes from Source Control to a database running in a container – easy as..!!

So all that is left to do is download the tSQLt framework (a whopping 86KB zip file) from http://tsqlt.org/download/tsqlt/

Inside the zip file are some files:

7. tSQLt download — tSQLt comes with an example database you can use to try things out!!

There is:

Example.sql

Which is a script that allows you to create a database and run your first unit tests. It’s a great example and you can follow the documentation at https://tsqlt.org/user-guide/quick-start/

SetClrEnabled.sql

Which is a script to enable CLR. This is partly why you should only run tSQLt on your DEV or selected TEST databases.

This is where I diverge slightly from a stock standard install:

The SetClrEnabled script does the following:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

Whereas for Linux based SQL Server we need to run this – as it will not install with clr strict security enabled – note we have to ‘show advanced options’ first:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'clr enabled', 1
EXEC sp_configure 'clr strict security', 0
RECONFIGURE
GO

tSQLt.Class.sql

This is the framework itself. Now before you run this you have to do the following to get it to run in a database running in a container:

In the script you need to change PERMISSION_SET = EXTERNAL_ACCESS to be PERMISSION_SET = SAFE

We now run the script and voila – we have tSQLt in our database and can now write or run all the unit tests we want.

8. Running tSQLt against container SQL Server — Hooray – we now have tSQLt running in a container

So there you have it – you can add tSQLt very easily (just 2 changes to make) to a database running in a docker container.

As mentioned the reason why I am very interested in running tests against databases running on container is around testing – I can spin up the container, download a particular patch level of SQL Server 2017 and above, add my database from source control, apply any referential data from source control and apply tSQLt which allows me to run a plethora of unit tests.

All within seconds and all consistently across multiple environment levels (QA, Integration, Functional test etc).

So go visit

https://tsqlt.org/user-guide/tsqlt-tutorial/

or these other great sites:

https://www.simple-talk.com/sql/t-sql-programming/getting-started-testing-databases-with-tsqlt

http://www.pluralsight.com/courses/unit-testing-t-sql-tsqlt

https://www.itprotoday.com/sql-server/getting-started-test-driven-design-sql-server

http://d-a-green.blogspot.co.uk/search/label/tSQLt

http://datacentricity.net/tag/tsqlt/

and in no time at all you will be writing your very own unit test.

Yip.