The Agile software development methodology is ever increasing in popularity among software development teams. One of the key disciplines of Agile is Test-Driven Development (TDD). The basic premise of Test-Driven Development is to develop software in many small steps driven by the requirement to make tests pass. The sequence of events goes like this: write a simple test for how a bit of code should work. Because the code to make the test pass has not been written yet the test will fail. Then, write the simplest code necessary to make the test pass. Once the test passes, refactor the code if necessary to remove code duplication and improve the overall design of the code while preserving its functionality. Once refactoring is complete, move on to the next test and repeat. If done correctly and within the guidelines of properly structured unit tests, the production code will be driven by passing tests. If done well, the resulting code is not only covered by unit tests that can be executed automatically, but is simple in design and contains only those features that are required for the application.
There are many tools available to help with the process of TDD. For the .NET developer, NUnit is a popular choice: (http://nunit.sourceforge.net). For the SQL Server developer, TSQLUnit is available:(http://tsqlunit.sourceforge.net). In this article I discuss what TSQLUnit is and how it can be used for TDD of database code.
What is TSQLUnit?
TSQLUnit is a SQL Server database testing framework based on the xUnit (http://xunitpatterns.com) unit testing framework. It is a free, open-source framework that is installed by simply executing a T-SQL script against a SQL Server database. The script creates a handful of tables and stored procedures that are used to enable test-driven development of database code.
Getting Started
Let's assume we are developing an application that will be used to analyze stock price trending patterns. One requirement of the application is to calculate the average price of a stock. A stored procedure will be created that takes in a stock symbol as an argument and returns the average price of the stock. In the future we may add additional arguments to account for moving averages (200 day, 40 day, etc) but because this is not currently a requirement only the most basic functionality will be implemented. For simplicity, let's assume our stock price data is stored in a table with the following table definition:
CREATE TABLE dbo.StockPrice ( StockId INT IDENTITY(1,1) PRIMARY KEY, Symbol VARCHAR(10) NOT NULL, ClosePrice MONEY NOT NULL, CloseDate DATETIME NOT NULL )
Step 1: Create a test
In order for TSQLUnit to identify a stored procedure as a test the name must be prefixed with 'ut_'. For those familiar with NUnit, this is similar to decorating a method with the [Test] attribute. In TDD, there are essentially 4 parts to a unit test: Setup, Exercise, Assert, and Teardown (SEAT).
Setup - prepare the test conditions by manipulating the objects, tables, and/or data
Exercise - invoke the production code
Assert - check that the actual result equals the expected result
Teardown - return everything back to the way it was before the test started
In the following TSQLUnit test, test data is staged (Setup), the production code is invoked (Exercise), the actual result is validated against the expected result (Assert), and everything is returned to its previous state (Teardown), albeit implicitly via TSQLUnit's automatic invocation of ROLLBACK TRAN at the end of each unit test.
By default, TSQLUnit tests will pass unless the tsu_Failure stored procedure is invoked. Therefore, all TSQLUnit tests must explicitly call tsu_Failure when the actual result does not equal the expected result.
CREATE PROC dbo.ut_TestGetAveragePriceBySymbol AS SET NOCOUNT ON -- Setup the test conditions by inserting test data INSERT INTO dbo.StockPrice VALUES ('XYZ', 10, GETDATE() - 2) INSERT INTO dbo.StockPrice VALUES ('XYZ', 15, GETDATE() - 1) INSERT INTO dbo.StockPrice VALUES ('XYZ', 5, GETDATE()) INSERT INTO dbo.StockPrice VALUES ('PDQ', 100.00, GETDATE()) -- Exercise the test DECLARE @ActualAvgClosePrice MONEY EXEC dbo.GetAveragePriceBySymbol 'XYZ', @ActualAvgClosePrice OUT -- Assert expectations DECLARE @ExpectedAvgClosePrice MONEY SET @ExpectedAvgClosePrice = 10 --(10 + 15 + 5) / 3 = 10 IF (@ExpectedAvgClosePrice != @ActualAvgClosePrice) EXEC dbo.tsu_Failure 'GetAveragePriceBySymbol failed.' -- Teardown -- Implicitly done via ROLLBACK TRAN GO
Step 2: Run the test
Executing tsu_runTests will run all unit tests. Running the stored procedure above would result in a failed test because the GetAveragePriceBySymbol stored procedure does not exist. This is good since no production code should be written until you have a failing test. Therefore, the next step is to create the GetAveragePriceBySymbol stored procedure.
Step 3: Create the GetAveragePriceBySymbol stored procedure
TDD encourages us to implement our solutions by doing the simplest thing possible in order to make the test pass. After the test passes, the code can be refactored to make it better by removing duplicate code, extracting code into smaller units, etc. In my opinion, it is much more difficult to refactor SQL code than it is to refactor .NET or Java code because of the lack of tooling (i.e. ReSharper for Visual Studio, etc) and the lack of object-oriented design of code modules within T-SQL code.
CREATE PROCEDURE dbo.GetAveragePriceBySymbol @Symbol VARCHAR(10), @AvgClosePrice MONEY OUT AS SET NOCOUNT ON SELECT @AvgClosePrice = AVG(ClosePrice) FROM dbo.StockPrice WHERE Symbol = @Symbol
GO
Step 4: Run the test and watch it pass.
Step 5: Refactor
Now that the test passes the code can be refactored. The production code appears fine so no changes are necessary. And that's it. You now have a test that can be automatically called by invoking the tsu_RunTestsstored procedure from a NAnt task or other task as part of your automated build / continuous integration (CI)process.
Now, if you want to see the unit tests that you've created so far simply execute the tsu_Describe stored procedure:
The above screen shot shows every unit test created within the current database. The SUITE, HASSETUP, and HASTEARDOWN columns are meaningful when working with test suites.
Test Suites
A test suite is similar to a TestFixture in NUnit. Individual unit tests can be grouped into test suites. Some of the advantages of using test suites are:
- Tests that exercise similar code can be grouped together.
- Individual test suites can be run independent of all other tests.
- Tests within a suite can share Setup and Teardown procedures.
Only test suites can have a Setup (a procedure that is run before each test within the suite) and a Teardown (a procedure that is run after each test within the suite) stored procedure. Setup stored procedures are commonly used to insert or update test data that the tests can use to exercise the expected behavior.
Stored procedures must adhere to the following naming convention to be included in a suite:
ut_%SUITENAME%_%TESTNAME%.
For example, if a suite called 'StockPrice' is created to group all stored procedures related to stock price then the stored procedure that was created above should be renamed to: ut_StockPrice_TestGetAveragePriceBySymbol.
SP_RENAME 'dbo.ut_TestGetAveragePriceBySymbol', 'ut_StockPrice_TestGetAveragePriceBySymbol'
Run tsu_Describe and notice how the unit test is now part of a suite:
The test can now be refactored (both production code and test code should be refactored) by creating a Setup stored procedure that creates common test data rather than duplicating insert statements across multiple tests. A Setup stored procedure must adhere to the following naming convention:
ut_%SUITENAME%_Setup
Create the Setup stored procedure and move the insert statements from ut_StockPrice_TestGetAveragePriceBySymbol to ut_StockPrice_Setup as follows:
CREATE PROCEDURE dbo.ut_StockPrice_Setup AS SET NOCOUNT ON
-- Setup the test conditions by inserting test data INSERT INTO dbo.StockPrice VALUES ('XYZ', 10, GETDATE() - 2) INSERT INTO dbo.StockPrice VALUES ('XYZ', 15, GETDATE() - 1) INSERT INTO dbo.StockPrice VALUES ('XYZ', 5, GETDATE()) INSERT INTO dbo.StockPrice VALUES ('PDQ', 100.00, GETDATE())
GO
Run tsu_Describe and verify our unit test has a Setup routine:
Because we've refactored our test code, the test(s) should be run again to ensure they still pass. Specific test suites can be executed by passing the suite name as an input parameter (@Suite) to tsu_RunTests:
EXEC tsu_RunTests 'StockPrice'
Any additional unit tests created within the StockPrice suite will have the same test data available since the Setup procedure is run before every unit test within the suite.
Although each unit test is run within a transaction that is rolled back, a teardown procedure can be created to clean up after each test is run. Similar to the setup procedure, a teardown procedure must adhere to the following naming convention:
ut_%SUITENAME%_Teardown
Teardown procedures can be used to clean up resources outside the scope of a database transaction such as files that were created on the filesystem to support the test suite, etc.
Testing Recordsets
The example above illustrates the process for testing a stored procedure that returns data via an output parameter. Stored procedures that return recordsets are also testable by using an INSERT EXEC command. In SQL Server 2000 and above a stored procedure can insert records directly into a physical table or temp table. SQL Server 2005 and 2008 allow inserting of records directly into table variables.
For example, if the GetAveragePriceBySymbol stored procedure returns a recordset, the test can be written to insert the resulting recordset into a temp table or table variable that can be queried during the assertion step of the test.
DECLARE @Temp TABLE (AvgClosePrice MONEY)
-- Exercise the test DECLARE @ActualAvgClosePrice MONEY INSERT INTO @Temp EXEC dbo.GetAveragePriceBySymbol 'XYZ' --changed to return recordset
SET @ActualAvgClosePrice = (SELECT TOP 1 AvgClosePrice FROM @Temp)
-- Assert expectations DECLARE @ExpectedAvgClosePrice MONEY SET @ExpectedAvgClosePrice = 10 IF (@ExpectedAvgClosePrice != @ActualAvgClosePrice) EXEC dbo.tsu_Failure 'GetAveragePriceBySymbol failed.'
In addition to testing stored procedures, unit tests can be written to test the existence of constraints, indexes, keys, and other table attributes to ensure database objects are scripted correctly and applied as expected. It does not make sense to test the enforcement of such constraints as this would be testing the functionality of SQL Server itself. However, on multiple occasions I have witnessed painfully slow-running queries in a production environment as a result of a missing index that was incorrectly scripted or simply never applied.
Summary
It should be evident that tools like TSQLUnit can be extremely valuable to database development. Test-Driven Development has proven to be an effective discipline for software developers across many different languages and platforms. Tools like NUnit, JUnit, Resharper, TestDriven.NET, Ant, NAnt, and CruiseControl have brought and will continue to bring tremendous benefit to development teams. TSQLUnit is a simple, yet powerful and effective tool designed to provide the same benefits as non-database TDD tools.
Resources
TSQLUnit
http://tsqlunit.sourceforge.net
Introduction to Test Driven Design (TDD)
http://www.agiledata.org/essays/tdd.html
NUnit
http://nunit.sourceforge.net
CruiseControl.NET
http://cruisecontrol.sourceforge.net/
