Now that we have some very good test frameworks for SQL Server, particularly tSQLt, all that was missing was a way of running your unit tests from within SQL Server Management Studio, and 'seeing red change to green'. Now the wait is over, with the arrival of SQL Test in preview. Rob Sullivan is one of those users who are helping shape the way that SQL Test develops, and explains what is going on, and why.
If you are an application developer at an agile shop and spend a good chunk of your time in Visual Studio, you are no doubt familiar with unit testing and Test Driven Development (TDD). There are various tools and frameworks that have been created and have matured over time to make this type of development fun and effective. Part of the reason this is so successful is that languages like C# are incredibly static which contrasts with the highly dynamic nature of databases. This means that a table with 1 row today can have 1M rows tomorrow, execution plans change, and so on. Such complexities are part of the reason why, when it comes to database testing, there have been lots of attempts, frameworks and ultimately failures to scratch this itch. Frameworks such as TSQLUnit have come close to filling the void, but the lack of mocking, object pollution, examples and a fluid test runner not only made this process a hard sale to myself but also to our development process. There is a newer open source testing framework on the rise that is called tSQLt. It has a very nice mocking system. Having talked to one of its creators at the PASS Summit, I know for a fact that these guys completely get and buy into the TDD mantra. There is still something missing though... that handy test runner inside of SQL Server Management Studio (SSMS). This is where our friends over at Red Gate are coming to the rescue again.
So let’s see this tool in action! In the AdventureWorks database, there is this fun little function called [ufnGetContactInformation] which has all sorts of sub queries, conditions and case statements with some magic quoted values. We have just been given a ticket from the business unit saying they need some of the verbiage in the “contract type” changed from ‘Store Contact’ to ‘Kiosk Contact’. Falling in line with the ‘Red then Green’ mantra of testing, we’re going to do the following:
- Create a test that checks for the expected result
- Run the test and have it go red (fail)
- Change our function to reflect the desired business rule
- Run the test and step 3 until the test goes green (Winning!)
In the SQL Test window, click on the “+Add Database to SQL Test” link and add Adventure Works to it.
Now that we have our database wired up, it is time to click “+ New Test” in the SQL Test window and fill out the modal that pops up.
As you can see in the image, we put our test name in, confirm our database and put in a test class. The test classes will help isolate our various test suits into their own schema. As soon as we click ‘Create Test’ we are created with a nice test skeleton that gives us the 3 pillars of a test: Assemble - Act - Assert.
Our test:
ALTER PROCEDURE [ContactInformationFunction].[test Make Sure Contact Type is Correct]
AS
BEGIN
--Assemble. This is where we will be mocking our data using fake tables with fake data sets.
EXEC tSQLt.FakeTable 'Person.Contact';
INSERT INTO Person.Contact (ContactID, FirstName, LastName)
VALUES (1,'Rob','Sullivan');
EXEC tSQLt.FakeTable 'Person.Contacttype';
INSERT INTO Person.Contacttype (ContacttypeID, Name, ModifiedDate)
VALUES (11,'Owner',GETDATE());
EXEC tSQLt.FakeTable 'Sales.StoreContact';
INSERT INTO Sales.StoreContact (CustomerID, ContactID, ContactTypeID, RowGuid,ModifiedDate)
VALUES (1, 1, 11, NEWID(), GETDATE());
--Act: Here we are running our function against our fake tables and logging the results.
SELECT [ContactType]
INTO #Actual
FROM [ufnGetContactInformation] (1)
--Assert: Create the table/schema for the expected
SELECT TOP(0) * INTO #Expected FROM #Actual;
-- Per ticket #2314 we need Contract to return Kiosk Contract
-- when they exist in the [Sales].[StoreContact]
INSERT INTO #Expected ([ContactType]) VALUES ('Kiosk Contact');
-- Let's see if everything adds up correctly. We are automating our confidence.
EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';
END;
We use our handy hot key of Alt + shift + x to run our tests right in management studio and as expected, our test fails. Now we just need to hop into the function itself and change the section of code dealing with the Store Contact to Kiosk Contact:
WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc
INNER JOIN [Person].[ContactType] ct
ON sc.[ContactTypeID] = ct.[ContactTypeID]
WHERE sc.[ContactID] = @ContactID)
THEN 'Store Contact'
Will turn into:
WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc
INNER JOIN [Person].[ContactType] ct
ON sc.[ContactTypeID] = ct.[ContactTypeID]
WHERE sc.[ContactID] = @ContactID)
THEN 'Kiosk Contact' -- fixed for ticket 2314
After we make the change, we run our test again and it passes and we look like heroes. Not only do we look like heroes, we have created living documentation of our database so that the noob 8 months from now who has to change it back to ‘Store Contact’ will hopefully already have a nice set of tests to help him make the change with confidence.
Cons:
- The tSQLt framework will completely litter your database with its objects. (but it does have a nice uninstaller if the objects trigger your OCD too much).
- The SQL Test tool has a few quirks as it is quite early in its build process.
- The dynamic nature of how we get data makes traditional unit testing like you see with C#(incredibly static) a bit trickier.
- This is a fairly new and unexplored space. It can be hard to see the value in unit testing until you have really bought in and it saved your bacon.
Pros:
- Huge amount of potential in this space to change how we do things or to automate our code confidence.
- The team is listening to feed back and you see with the quick iterations. There have already been 3 new builds fixing issues since I started playing with it the other day.
- tSQLt (or other frameworks when they are supported) provide nice hooks for your continuous integration server.
- While it is married to the tSQLt framework right now, as this space picks up heat, one would assume that Red Gate’s SQL Test would become a test runner for various frameworks in the same manner that SQL Source Control hooks you up with various source control providers.
- It brings to you unit testing in a language you know... T-SQL
Resources
