tSQLt technical implementation

In tSQLt, a test is a stored procedure that meets these requirements:

1. The stored procedure must be named something that starts with the word test so for example [test does something nice] is valid and [make sure something is nice] is not valid, tSQLt will find the first one and execute it and not the second. It does seem odd to create stored procedures with spaces in the names, but it makes working with a suite of tests vital.
2. The stored procedure must be in a schema that contains an extended property called tSQLt.TestClass with a value of 1, for example in the tSQLt_Example database this code is used to create the extended property for the AcceleratorTests "class":

EXEC sys.sp_addextendedproperty @name=N'tSQLt.TestClass', @value=1 , @level0type=N'SCHEMA',@level0name=N'AcceleratorTests'

Typically everyone at some point will either not name a test [test *] or manually create the schema without the extended property, so refer back here if you are expecting a test to run, but it doesn't.

To help create the schema with the correcct extended property, you can use tSQLt.NewTestClass:

tSQLt.NewTestClass 'bertie'

This will create a new schema called "bertie" and you can add stored procedures to that schema that begin with the word "test" and your stored procedures will be executed as tSQLt tests.

What does it mean to be a "tSQLt test"?

tSQLt takes care of many things for us, and the main part is that it finds and executes tests; wraps each test in a transaction so any modifications are rolled back and saves the results of any tests so that we can know if they failed or succeeded. You can run the stored procedures manually as they are just standard stored procedures, however, you will not get any of the services that tSQLt provides, and it is not recommended.

What makes a test a unit test

Now we have covered the technical aspects of what a unit test is in tSQLt; we have the more interesting topic of what is a unit test means to SQL Server or T-SQL code. Unit tests, in general, give us the following benefits:

1. Documentation
2. Ability to refactor
3. Ensure unrelated changes do not affect other parts of the system
4. Help structure code into distinct components
5. Sometimes reduces the number of bugs we write

1. Documentation

To provide a level of documentation, we need to have a suite of tests that are well named, for example, if we take a stored procedure that creates a new user, the procedure is responsible for:

1. Checking if the user already exists and updating rather than inserting a new user
2. Adding an audit entry to say a new user was created (either directly or via a trigger on a table) NB I didn't say the example was pretty!
3. Setting some defaults (either directly or via default constraints)
4. Returning a new guid for the user id

What we would look for is a set of tests along the lines of:

[AddUser Tests].[test creates a new user when the user does not exist]
[AddUser Tests].[test updates an existing user when the user already exists]
[AddUser Tests].[test adds entry to audit table]
[AddUser Tests].[test sets group id to no_group (0)]
[AddUser Tests].[test sets email preference to free_to_spam]
[AddUser Tests].[test returns unique guid for the new user]
[AddUser Tests].[test returns existing guid for existing user]

It is pretty easy to look at the tests and understand both the requirements and what it is that the code does. The only real hardship is making sure that when the code changes you update the tests to reflect what it is that the code does but that is a development team process issue which you will need to work out :)

2. Ability to refactor

To allow us to refactor our code, we need a suite of unit tests that cover, if not all the database, the critical parts and ideally also a suite of integration tests. The unit tests will allow us to make sure we haven't broken another part of the database, for example, if we have code that uses a specific column, having a test around this fail if that column is dropped. We may have other tools such as SSDT that validate that references are correct when compiling, but if you have some dynamic T-SQL or something else of similar ugliness, then SSDT won't catch that.

To be safe when refactoring and changing big chunks of code you also really need a good suite of integration and user acceptance tests. The full suite of tests, test that the actual application works as expected, this is out of scope of this course but something to bear in mind is that we need to build up layers of tests from the smallest layer (unit testing to the highest, user acceptance testing)

3. Ensure unrelated changes do not affect other parts of the system

This point is, pretty much, the same as refactoring except without unit tests we blame other people when something goes wrong. When we do have unit tests in place, there is no blame to hand out as the person responsible won't check in the code until all the tests pass. I say this slightly tongue-in-cheek, but if your code does not have unit tests, it is you who is to blame (or me if I write the code).

4. Help structure code into distinct components

The key here is that if you take a test-first approach to T-SQL development and write unit tests, the code you write is more likely to be a shorter more succinct unit of code. The reason is that we need to think up-front about what tests will be needed and it is much simpler for us to write tests describing the functionality rather than having to think about additional branching logic. I am sure we have all seen thousand line stored procedures with many if, then and probably multiple nested if, then statements. To test that in its entirety means thinking about the possible paths through the code and writing unit tests to validate the paths are correct.

When we take a test first approach, we should take the opportunity to write shorter pieces of code. There is always one major objection that developers have, and that is that writing long stored procedures is more efficient than multiple smaller ones. In many cases, this is unlikely to be true. However, it is sometimes true that you need to write slightly uglier code than you would like. If you do get in this situation, then taking the approach of covering your code with tests allows you the ability to refactor, albeit into an uglier form.

If you had no unit tests, then it would be riskier, and the time you will need to do this sort of thing is when production has suddenly ground to an achingly slow halt and being able to make code changes quickly, pays off the most.

5. Sometimes reduces the number of bugs we write

In many ways, this is often the least important benefit of writing unit tests - when we use tests to find bugs then it is a great experience but as good developers we know we don't write bugs in the first place, it is all the others....