Why don’t you unit test SQL Server code?

The download count of the tSQLt unit testing framework is surprisingly low, yet this outstanding open source software can revolutionize your SQL Server development process. Hopefully, this post will show the benefits of test-driven development and including automated SQL Server unit testing within your release pipeline. This simple technique can have a profound effect on software quality and your sense of job satisfaction. Even if you have a large code base and no existing unit tests, you can start introducing tests now to make your database code more robust to change.

Broken

Your pulse starts to quicken post-release when the phone rings and you’re greeted with an ominous Did anything change? A cursory check of your application shows:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'TeamMember'

or

Msg 206, Level 16, State 2, Line 9
Operand type clash: datetime2 is incompatible with int

Two great podcasts have discussed DBA mistakes like these:

• SQL Data Partners episode 93: Making mistakes
• Dear SQL DBA episode 40: How does a DBA build confidence after making mistakes?

My favourite quote is Show me a DBA who never made a mistake and I’ll show you a liar. Unit testing is a technique for finding mistakes early and only deploying production-ready code to live.

Automation

Following the seminal 10+ Deploys per day: Dev and Ops cooperation at Flickr talk from John Allspaw and Paul Hammond in 2009, there are similar examples of staggering deployment cadence:

• How Etsy deploys more than 50 times a day
• Amazon deploying every 11.7 seconds

Manual testing is over for these companies. To operate at this scale, everything needs automation, including database testing. The following section will demonstrate an example of how to automate SQL Server unit testing using the tSQLt open source framework.

Install

Download the open source tSQLt framework for free and follow the quick start guide to install it.

There are two security requirements to use the framework: CLR must be enabled, and the trustworthy property of the test database must be on. This configuration is done by the SetClrEnabled.sql file within the zip file download.

The framework is deployed into your test database via tSQLt.class.sql. The framework and all tests are SQL Server objects that database developers will be very familiar with. Testing should be performed in your development, test or acceptance environments and not production.

Test-Driven Development

The key principles of test-driven development are:

• Add test
• Test fails
• Write the minimum code to pass the test
• All tests pass
• Re-factor
• All tests pass

Each test should be succinct and test a single unit of worl – in the context of SQL Server, this is a stored procedure, view or function, for example. The goal is to test this single unit only, isolating it from other objects it interacts with. tSQLt provides numerous powerful options to fake objects and spy procedures to facilitate this.

Example

We have a database table containing songs and want to write a stored procedure to select from it.

As we’re using the test-driven development methodology, we begin with the test. The requirements for our dbo.GetSong stored procedure are to accept a @SongID parameter and use it as a predicate to filter the dbo.Song table and select four columns. Therefore, our test is:

Test structure

Each test is a normal T-SQL stored procedure. The required format is:

• Assemble: set up
• Act: invoke the code under test
• Assert: actual = expected?

tSQLt provides powerful functionality to fake objects to isolate the testing of a single unit of work. In our example, a table (dbo.Song) is faked, which will rename the existing table and create a new dbo.Song table with no constraints or data. Each test is run within a transaction, so these changes are automatically reverted post-test. This allows us to populate the faked table, providing a consistent dataset to facilitate a concise, repeatable test.

Faking objects also allows us to run each test for every release. Tests interact with small targeted data-sets, not huge transactional tables.

In addition to faking objects, the example introduces two of the assert methods provided by the framework. AssertEquals will fail the test if two variables are not identical. AssertEqualsTable will similarly fail if two tables do not have identical structure and data.

Other methods are available, for further information see the user guide.

Naming convention

All tSQLt tests are defined within a test class, ‘Music’ in our example. Test classes are logical groupings of tests and you can limit your tests to a single class if desired. This is a similar concept to grouping SQL Server objects within schemas. In fact, tSQLt classes are SQL Server schemas behind the scenes. The schema is identified as a test class via an extended property, handled automatically if created via the tSQLt.NewTestClass stored procedure.

In addition to placing every test within a test class, each must begin with the word ‘test’ to be invoked correctly by the run methods.

Run

Next, you should run all tests via:

As expected, our test fails as we are testing new functionality not yet written:

[Music].[Test GetSong] failed: (Error) Could not find stored procedure 'dbo.GetSong'.[16,62]
{Test GetSong,35}

+------------------+
|Test Execution Summary|
+------------------+

|No|Test Case Name |Dur(ms)|Result|
+--+------------------+-------+------+
|1 |[Music].[Test GetSong]| 860|Error |
---------------------------------------
Msg 50000, Level 16, State 10, Line 106
Test Case Summary: 1 test case(s) executed, 0 succeeded, 0 failed, 1 errored.
---------------------------------------

The next principle of test-driven development is to write the minimum amount of code required to pass the test.

Note the 1=2 predicate. It is important to verify the test will fail, given incomplete functionality. It is easy to mistakenly write tests that always pass and add no value.

[Music].[Test GetSong] failed: (Failure) dbo.GetSong is returning an incorrect value Expected:
<0> but was: <-1>

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name |Dur(ms)|Result |
+--+----------------------+-------+-------+
|1 |[Music].[Test GetSong]| 297|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 106
Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

Finally, we remove the 1=2 predicate and all tests pass.

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name |Dur(ms)|Result |
+--+----------------------+-------+-------+
|1 |[Music].[Test GetSong]| 50|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Further learning

The previous section was a simple example to introduce some of the key concepts. In a development scenario, we would then write further tests for zero rows and one row within dbo.Song to achieve better code coverage.

Unit testing and tSQLt can feel strange and intimidating at first. You will soon be writing tests very quickly, building a bank of tests to run for each future deployment. This provides greater assurance that future changes will not break existing functionality when you have long forgotten the code you are working on today.

If you are interested in seeing more examples, the installation download includes an example database. Run Example.sql to create the tSQLt_Example database containing example tests.

I can’t automate

Let’s examine some of the common reasons why you can’t automate your database unit testing.

Q: I’m busy – where will I find time for this extra work?

This technique will be a net time-saver. Some short-term learning and extra development time to write tests will be offset many times by the errors caught early. Each test you write will be run in an automated fashion during each deployment, possibly hundreds or thousands of times in the future. How much time do bugs that make it through to your customers cost? When that scenario occurs, the number of people involved, drain on resources and loss of reputation can be incalculable.

No time estimate should ever be greater than one day. Each piece of work should be broken down into sub-tasks, each with its own estimate. A line item for writing each test should be added to your estimates.

Q: I’ve heard some newer SQL Server features aren’t supported by tSQLt

This is correct – you can’t fake memory optimized tables, for example. It’s still an extremely worthwhile exercise to write tests for the code you can cover. If a missing feature is truly a limitation, you can always contribute to the project.

Q: We don’t use open source software in our organization, is this safe?

Open source is now a way of life and is being fully embraced by Microsoft. There are such a wide variety of fantastic projects to contribute to and take advantage of.

Q: We’re not a DevOps organisation, so can we still use this?

You can automate your SQL Server unit tests without implementing a continuous integration/delivery/deployment process. All the benefits of automated testing are still available to you and you should still use tSQLt. If you’re interested in introducing continuous integration, however, then automated testing is a key pre-requisite.

Q: Do we have to pay for a third-party product to use tSQLt?

No. Such products do exist, two being SQL Test from Redgate and dbForge from Devart. These products can help productivity and provide GUI interfaces around tSQLt functionality. However, these are not required to use tSQLt.

Q: We want to performance test – will this tool help us?

No, tSQLt is a tool for functional unit testing. If you have 3,000 tests and are deploying 10 times per day, you are running 30,000 tests daily. You don’t want to run your 4-hour data warehouse load on every test iteration. Each test should be succinct and efficient. This is where faking objects provides the necessary functionality to provide targeted specific test data. Unit testing should be used however, to verify nothing breaks when you’re adding performance enhancements.

Q: Some of our stored procedures are thousands of lines – can we still unit test these?

In theory, yes, although in practice this is very difficult. 100% code coverage is the principal of testing every branch of your code. With objects this large, the exponential combination of possible branches soon makes testing all possibilities impractical. One of the great benefits of test-driven development is that, by writing the test first, it forces you to write modular, succinct code that performs a specific task only.

Q: Faking objects involves renaming the real objects – will this change the structure of my database?

All tSQLt tests are run within transactions and are rolled back post-test. The atomic property of the ACID principle of transactions means that everything is rolled back.

Unit testing should be performed in your non-production environments only. tSQLt objects will be promoted to source code control like any other SQL Server objects. The framework can be removed from the production database by the tSQLt.Uninstall stored procedure as a post-release task, if desired.

Summary

Changing your development practice to introduce an automated testing strategy can revolutionise your deployments. If you approach the software release date with a sense of dread, this technique is for you.

Implementing a test-driven development strategy using tSQLt leads to robust, modular code that becomes a pleasure to work with. As more tests are created, trust builds that releases will provide beneficial new functionality with no negative side-effects.

In The 7 habits of highly effective people, Stephen Covey talks about the principle of continuously improving. There is sometimes a better way than simply doing what we have always done.

This post only touched the surface of what is possible with tSQLt. There is a huge range of testing options available. If you’d like some assistance in starting your automated testing journey with SQL Server, then please get in touch. We’d love to hear from you.

Coeo is a Microsoft Gold Partner for Data Platform, Data Analytics and Cloud Platform. We work collaboratively to improve project outcomes and develop deeper knowledge and appreciation of Microsoft technologies, improving our customers’ experience. Find out more about us.