Start to see with tSQLt

Standard

Reading Time: 6 minutes

What is tSQLt, why to use and how can I make it a little easier by automating lots of tests with PowerShell

This is going to be my first post on tSQLt. I will tell you why I love this framework and why using it changed my way how I develop databases.

tSQLt is a unit testing framework for SQL Server databases. There are lots of articles out there that did a very good job describing what the framework is and what it does. The links below helped me out quite a bit:

How did I get started with tSQLt

A couple of months ago I got introduced into PowerShell unit testing with Pester, That has nothing to do with SQL Server databases but I learned why it’s important make these kind of tests.

The tSQLt framework already existed for a while but I never spend too much time on it because it seemed to me like another framework to learn and I did not have the time to dive into it. Until I got introduced to Pester and how good it worked for my projects.

The tSQLt is completely free and can be downloaded from here. You only need to change a couple of things in your development instance, install the framework into your development database and your done.

As with many new things there is a big learning curve. You spend hours reading documentation and you try out several ways you could use the framework. It took me a while to understand the framework but once I got used to it I was able to create lots of different kinds of tests.

I work in a smaller environment where we have lots of development. We have not yet implemented continuous integration or continuous delivery. As we grow in the amount of projects and complexity of it all, we need to implement these practices to improve the quality of our releases.

Why I love tSQLt

The thing with unit testing is, you don’t see the benefit of it until things go wrong. I remembered creating hundreds of tests for the stored procedures in our database and thinking why I was actually doing it.
Developing the extensive tests with mocked tables and integration with other procedures was a tedious task.

But I persevered, I finished my tests and it was not long after that, that a change had to made to that specific database. It was a change in a table to rename a certain column. I made the change, changed the procedures I knew relied on that column and ran my unit tests.
The result was that I had over 10 stored procedures that for some reason did not return the expected results. Apparently they relied on that column and I totally overlooked those procedures.

If you have over 400 stored procedures in your database, you don’t remember all the code you’ve written and at some point just forget. It’s exactly that reason why I love tSQLt.
If I had made the change and applied it to our testing environment I would’ve broken a lot of code and our testers would come back to me that thing didn’t work.

With the test results returning the failed procedures I was able to create a quality release.

Creating the tests

The integration tests were the first unit tests I created and I was really satisfied with the result. The integration tests would get results from a stored procedure to test the workings. These kinds of tests can be quite complicated but give you a very good view if your code does what it’s supposed to do.

But I wanted to take it further than that. With CI/CD (Continuous Integration/Continuous Delivery) in mind I wanted to create extra tests to assure a proper release.

I wanted to make the following tests:

  • Are all the tables present
  • Are all the stored procedures present
  • Check object permissions

This database does not use any views or user defined functions. I also did not want to go overboard to test for the existence of objects. You can create a test for any type of object.

Are all the tables present

Imagine a situation where a developer by accident deletes a table and doesn’t notice the change. He/she commits it to your source control (your have your database in source control right? ) and it goes to your build server.

I want to be sure all my tables are present before any other test get’s executed. It may seem redundant because the integration tests would fail too, but it would give me a clear error that an object was missing.

Example of the code:

Are all the stored procedures present

The same situation as with the tables applies to my stored procedures. If a stored procedure would be deleted I wanted to know it right away.

The difference with the tables is that deleting a stored procedure does not have to break any other part in the database. It would only be noticed when an application would rely on it.

Example of the code:

Check object permissions

I cannot count the times I created a new stored procedure and I forgot to grant permissions to it. I would implement the change and it was unusable because the application had no privileges on it.

If you know the permission needed for an object, it’s easy to get those permissions and test if they are what you expect them to be.

This one is a little different than the object existence. I first create the tables for the expected and actual values. I than insert the values I expect should be in the the result.

In the act-part I get the permissions from the object and insert them into the table for the actual values.

Did you create all those tests by manually?

OH HELL NO!

If you’ve read any automation posts from my blog before, you know that when it comes to repetitive tasks, I am as lazy as it comes. I do not do two things manually if I can automate it once.

The only tests that could not be generated where the integration tests. Besides those, I generated all the all of them.

The code to assert the existence of the objects is basically the same.The only thing that would change would be the object name and maybe the schema.
The same goes for the tests to check the permissions. In most of the cases the permissions may be the same (at least for the same schema). With some tinkering I was able to generate all the tests for that purpose too.

How did I generate all the tests?

Simple! I use dbatools and a loop to go through the objects.

  1. Generate the test you need but just for one object
  2. Go to PowerShell and let it retrieve all the objects needed
  3. Export the test you create in step on and copy it to a query variable in your PowerShell script
  4. Make a loop around the pasted script to replace the object name
  5. Run the PowerShell script

The result is the script below. The script tests for the existence of the stored procedures. You can of course change the types of objects.

 

I hope you enjoyed this article to create some handy tests using tSQLt. This framework really helped me with my day-to-day development and has saved me numerous times.

Comments are always appreciated. If you want to stay informed about new blog posts, please subscribe.

Leave a Reply