Database Unit Testing
If there is a case of application with extensive business logic embedded in a database, at some point most people will ask themselves ‘how to test it’ or ‘is it worth testing it at all?’. I would like to skip the second question and answer only the first one :).
There are at least two methods of developing database unit tests:
The first method is related to SQL Server Data Tools package which in integrated with Visual Studio. Both solutions can be easily integrated with the development process without a lot of effort. Because of full integration with Visual Studio and a set of interesting features this article will be devoted to the first solution (SQL Server Data Tools) which came to us straight from the stable of Microsoft.
SQL Server Data Tools:
At the end of the last year, a new information appeared – a new functionality of unit testing has been added to the SSDT package. This functionality was a game changer – the full integration with Visual Studio 2010 and 2012 allows us to use numerous options directly from Visual Studio. What is more, all the database unit tests created by us are available in the Test Explorer and can be run together with other tests. And what’s even more important is that it is possible to debug them.Let us dive into the SSDT package, then.
Tests can be added directly to the test projects. They are listed as SQL Server Unit Test in the SQL Server section. To add a test using Add New item, we need to write a testing procedure in the TSQL language.
Once the SSDT package has been installed, we get a special component to configure it.
Let’s create the SQL Server Unit Test called CreateTableRowTest. Within a single SQL Server Unit Test it is possible to define many tests and specify various test conditions for each of them.
Additionally, it is also possible to make certain TSQL fragments to run at certain times, to enhance the control over testing
- Test Initialize – before the entire SQL Server Unit Test
- Test Clean Up – after the entire SQL Server Unit Test
- Pre-Test – before every single test
- Post-Test – after every single test is done
Then, we define two test cases:
As you probably know, one of them should always pass, whereas the other should inform us about an error.
Let’s have a look at a component which allows us to configure the SQL Server Unit Test.
The upper part of the control is used to enter the TSQL code.
In the lower part we can define the Test Conditions that a tested fragment should meet.
We can choose from the following Test Conditions:
- Data Checksum
- Scalar Value
- Empty ResultSet
- Not Empty ResultSet
- Execution Time
- Excpected Schema
- Row Count
After choosing a Test Condition, we have to specify criteria for specific Test Conditions. To be able to refer to test results it is necessary to use the SELECT operation. This will be shown later in this article.
For now, let’s have a look at the Scalar Value Test Condition. The TSQL fragment presented below calls the procedure named [Table].[CreateTableRow], whose task is to create single row and then return its id. For the purpose of this article, the procedure will always return the value 1. In the second line the SELECT operation is used precisely to let us use this information while defining test criteria.
EXECUTE @Id = [Table].[CreateTableRow] @name, @place; SELECT @id as ID
In the case of Scalar Value Test Condition, you need to set the following parameters:
Unfortunately, the configuration is not really intuitive. A number of column we would like to test is required and afterwards we need to set a reference value we test against.
Additionally, it is irritating that the TSQL editor acts differently than the one of SSMS which I know well. The problem might be a lack of intellisense. As SSMS uses exactly the same engine as the VS so it should be given out of the box.
After starting tests, the following information occurred:
Integrating with the SQL Server Database Project:
I have good news for everyone using this type of project to design a database – one does not have to write lots of code anymore, a lot can be done using clicking only. For example, we can choose create unit tests directly from the context menu:
When you select 'Create Unit Test’ option from the Context menu, SSDT will generate the TSQL script to test procedures. This option is available from the Explorer’s SQL Server Object, as shown above.
To sum up, the Unit Testing in SSDT package is a very useful tool for people working with projects having a lot of business logic in the database. It allows to test things which might be too complex to test otherwise. Sadly, the solution to database testing is not very intuitive, therefore some people might bail on it and say 'Databases should not be tested’.
However if you get past the interface and learn SSDT you will have a very powerful tool at your disposal, able to test the most crucial elements of your application. And because of that I recommend at least a glance at this tool – it may be worth it.