"The best time to plant a tree is twenty years ago, the second-best time is now" Chinese Proverb
The download count of the tSQLt unit testing framework (http://tsqlt.org) is surprisingly low:
Yet this outstanding open-source software can revolutionise 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 are 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:
- SQL Data Partners Episode 93: Making Mistakes http://sqldatapartners.com/2017/04/25/episode-93-making-mistakes
- Dear SQL DBA Episode 40: How Does a DBA Build Confidence After Making Mistakes? https://littlekendra.com/2017/04/27/how-does-a-dba-build-confidence-after-making-mistakes-dear-sql-dba-episode-40
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:
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 at http://tsqlt.org/downloads and follow the quick start guide to install http://tsqlt.org/user-guide/quick-start
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 work, 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 are 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:
EXECUTE tSQLt.NewTestClass 'Music';
GO
CREATE OR ALTER PROCEDURE Music.[Test GetSong] AS
/*Assemble*/
DECLARE @Return INT;
/*create a table to store the expected result-set*/
SELECT TOP (0) Title, Band, ReleaseDate, Label INTO Music.Expected FROM dbo.Song;
/*create a table to store the actual result-set*/
SELECT TOP (0) Title, Band, ReleaseDate, Label INTO Music.Actual FROM dbo.Song;
EXECUTE tSQLt.FakeTable
@TableName = N'dbo.Song';
INSERT INTO dbo.Song (SongID, Title, Band, ReleaseDate, Label) VALUES
(1, N'A Day in the Life', N'The Beatles', '19670601', N'Parlophone'),
(2, N'Waterfall', N'The Stone Roses', '19911230', N'Silvertone');
INSERT INTO Music.Expected (Title, Band, ReleaseDate, Label) VALUES
(N'A Day in the Life', N'The Beatles', '19670601', N'Parlophone');
/*Act*/
INSERT INTO Music.Actual (Title, Band, ReleaseDate, Label)
EXECUTE @Return = dbo.GetSong @SongID = 1;
/*Assert*/
EXECUTE tsqlt.AssertEquals
@Expected = 0,
@Actual = @Return,
@Message = N'dbo.GetSong is returning an incorrect value';
EXECUTE tSQLt.AssertEqualsTable
@Expected = N'Music.Expected',
@Actual = N'Music.Actual',
@FailMsg = N'dbo.GetSong is returning an incorrect resultset';
GO
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 at http://tsqlt.org/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:
EXECUTE tsqlt.RunAll;
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.
CREATE OR ALTER PROCEDURE dbo.GetSong (
@SongID INT
) AS
DECLARE @Return INT = 0;
SELECT Title,
Band,
ReleaseDate,
Label
FROM dbo.Song
WHERE SongID = @SongID
AND 1 = 2;
IF @@ROWCOUNT <> 1
BEGIN;
SET @Return = -1;
END;
RETURN @Return;
GO
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.
EXECUTE tsqlt.RunAll;
[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 great 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 Cannot
Let us examine some of the common reasons why you cannot automate your database unit testing.
Q: I am 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 have heard some newer SQL Server features are not supported by tSQLt, is this true?
This is correct, you cannot fake memory optimized tables for example. It is 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 organisation, 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 are not a DevOps organisation, 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 are 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 are 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 do not 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 are adding performance enhancements.
Q: Some of our stored procedures are thousands of lines, can we still unit test these?
In theory, yes, 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 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 would like some assistance in starting your automated testing journey with SQL Server, then please get in touch, we would love to hear from you.