Most .Net developers have probably written unit tests to test their code. Once tests are in place, refactoring and adding functionality can be done with more confidence, because the coder will be able to demonstrate that a given method will still function correctly. However, these unit tests remove the dependency on the database, so we still would want to set up tests for the database and database objects. Unit testing a database proves to be a little more difficult, since the data can change so frequently and we can’t count on the data to be in a given state.
tSQLt provides a framework for creating and running database-level tests. The tests are set up in T-SQL, so database developers are still able to work with the language that they’re most efficient in.
This framework will provide a way to create stored procedures that will test specific aspects in the database. The framework can execute all of the testing procs and alert us to any errors that arise. tSQLt will use transactions for the tests and will roll back any data changes, so the user can re-run tests without collision.
The tSQLt home page provides a link to download a set of tSQL scripts that will set up the testing framework. On the testing database, run the SetClrEnabled.sql first, which will set the database to TRUSTWORTHY and will enable CLR functions to be executed. The tSQLt.class.sql will set up the testing framework. The package also includes a Sample.sql script with some sample code.
There are two scripts on GitHub: One to create tables, stored procedures and data for a sample database and a second to create the testing procs.
There is plenty of documentation on The tSQLt site on using the framework. In the UnitTest_Tests.sql script there are some simple examples.
First, we create a test class that will server as a namespace for the testing procedures. This will create a class TestClass.
exec tSQLt.NewTestClass 'TestClass';
Then we’ll create our stored procedure to test code. We’ll use the test class we created as a namespace for the proc.
create procedure TestClass.[TestDB_InsertPlayer]
Within the testing proc, we’ll use an assertion to compare the data we expect to the actual data. In one case, we’re testing an insert proc, so we’ll create the table as we expect it to the actual table. Within the test class, we’ll create an ‘Expected’ table with a schema that matches the table we’re testing. It will be compared to the ActualTable. If they don’t match, then the framework will throw an exception that will let us know the rows that don’t match.
exec tSQLt.assertEqualsTable 'TestClass.expected', 'ActualTable';
In other cases, we may have a function or a procedure that returns a value. We can compare that actual value to the expected value with an assertion.
exec tSQLt.AssertEquals @ExpectedValue, @ActualValue;
The tSQLt User Guide lists other assertions that can be used.
Finally, we execute all of the tests within our test class:
exec tSQLt.Run 'TestClass';
The tests won’t be executed in any particular order, so we can’t arrange the tests that depend on changes made by another test(the data changes are rolled back in any case).
Redgate has created a GUI called SQL Test that provides a test runner user interface for the tSQLt framework. It is a paid application, but there is a 28 day free trial available.
I haven’t used the SQL Test program, but Troy Hunt has a good post on using it.