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??
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.
The first thing we need to do is spin up our docker container which has our SQL Server instance:
Just to verify that it is running let’s run
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
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:
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
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:
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/
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
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.
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
or these other great sites:
and in no time at all you will be writing your very own unit test.
2 thoughts on “How to use tSQLt unit test framework with a SQL Server database in a docker container”
Thank you wise man for writing this post,
It was just what I needed to get tSQLt into my container. I would also add that if you need tSQLt in your Visual Studio solution and are going to use Azure DevOps to deploy it you need to add a reference to the master database in the project otherwise it won’t build 🙂