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

This blog post describes how easy it is to install the tSQLt framework.

Don’t know much about tSQLt? If you are serious about dealing with data in SQL Server then you should be as serious about unit testing the changes you are making to your stored procedures, functions and the like.

In fact I wrote a blog post about unit testing here:

Doing DevOps for your Database? You need to start here…

So I am assuming that you have realised how important it is to unit test your data and code so here’s how to install tSQLt to your database.

First – go to the tSQLt website:

https://tsqlt.org/

and go to download the framework:

tsqlt_website.png
Yip – it is just 86KB of unit testing goodness

The direct URL is http://tsqlt.org/download/tsqlt/

You will get a zip file with some files in it:

7. tSQLt download

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.

tSQLt.Class.sql

This is the framework itself.

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

I guess at this stage you might ask – why do I need to enable CLR – well let’s say you don’t enable it and just run tSQLt.Class.sql – you will get the following error:

tSQLt_error
You need CLR – but that’s OK as only install tSQLt in your DEV or selected TEST environments.

So yeah – enable CLR and then when you run the tSQLt.Class.sql script – things will go much better:

tSQLt_good
This really is the most simple and easy method of  installing a unit test framework for SQL code

So now you can start writing your own tests. The tSQLt framework adheres to the three A’s of unit testing:

Assemble

Act

Assert

tSQLt_test
It is very easy to create a template that you just use to write your unit tests.

The tSQLt website has some great examples and tutorials and honestly – the learning curve is very small – I’d say most Data Professionals can be up and running unit tests within 1-2 hours.

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.

In my opinion tSQLt is that sweet spot of easy to use and comprehensive enough to make a difference.

Yip.

Advertisements

One thought on “How to install the tSQLt unit test framework to start unit testing your database code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s