Whilst I think unit testing a simple Data Access Layer may not offer many advantages (and isn’t the subject of this post) I do see the value in unit testing T-SQL and Stored Procedures that include some level of business logic.
If you’re using Unit Tests then you should have a Build Server running either Continuous Integration or at least some sort of regular build that triggers all the Unit Tests to run. Creating the database schema on the build server (or other suitable server) during the test run is unavoidable since mocking out the SQL Engine is probably beyond most people :). However, once the schema is created each test will require a set of data appropriate for that test. There are a number of fairly easy options to getting the data inserted into the database but the problem arises trying to maintain one huge set of data that resides to one side from the unit tests that use it. Here are some issues :
- For a simple test that involves only testing an insert into one table there may exist many other related tables involved in foreign key relationships that are required to exist.
- The tests may be very sensitive to schema changes which require tedious code updates for no real benefit.
- One set of data for one particular test may not be able to exist alongside another set of data for a different test.
- Unit tests should be as self-contained as possible and have as few dependencies on other code as possible. Having shared test data contained in an external location to the tests creates a huge inter-dependency issue between tests. Tweak data for one test and you may break another test.
A couple of these issues could be solved by using the xUnit setup and teardown to insert data appropriate for the tests but there still has to be a huge amount of data added due to the first issue above and we may still end up breaking tests after relatively independent schema or data changes.
Consider the ‘Unit’
A better solution is to consider what we’re trying to achieve with the tests in the first place. The xUnit mantra encourages us to take the smallest unit of code, isolate it, then test with specific scenarios. This is exactly what should be attempted with SQL but what is the smallest unit?
In most cases we’re only really interested in a sub-set of the columns on any particular table(s) and certainly only on tables that are involved in the query under test. However, there are going to be numerous foreign key relationships and non-nullable column constraints that increase the size of the data required to test a query. This can all be reduced by allowing null fields (including for foreign key columns) and not inserting any data into those columns during the test. The test can now insert data into only the columns that apply to the current test – it’s important to make sure that the insert SQL statements only specify the columns that matter for the test since any presumption on column order or quantity will be fragile.
So the pattern here requires constraints to be dropped and data to be inserted for each test or perhaps during the xUnit setup. Either way, there should be a lot less data to maintain and it exists alongside the tests it’s related to.
To illustrate this idea consider the typical order management system where Customers create an Order which has many Order Lines. There will exist a foreign key relationship from Order Line to Order to Customer but if we’re unit testing the creation of Order Lines and shopping basket type interaction of adding and removing items then we may not be interested at all in testing anything to do with the Customer records. Therefore, before running the tests, we make the Customer foreign key on Order nullable before inserting data to test each case.
Hopefully that provoked some thoughts and you might like to add comments to expand on what I’ve said with your own opinions. In a second part to this blog I’m going to look at how I’ve chosen to combine NUnit, TSqlUnit and some Visual Studio Text Templates to create an integrated test environment in VS.