Database Unit Testing: Managing Database State

One important consideration in database unit testing is managing the database state. This aspect of database unit testing makes it more difficult than its app-tier counterpart. However, it’s fundamental to what database unit testing is, as a database in a nutshell is nothing more than a collection of data or state.

 

So the primary question here is: how do I guarantee that the data in my database is what I expect it to be when I run my tests?

 

There are two primary aspects to this:

§ First, ensuring the database has the expected state prior to running a collection of tests

§ Second, ensuring that between each test in the test run, the database has the appropriate state

 

There are several techniques to address the issue of setting up the database state initially:

 

1. Use a data generation tool to set the database state prior to running your collection of unit tests. This is the best practice that we encourage here in the product team. In order to do this, we have built a data generation tool for you. This tool allows to use develop a data generation plan that specifies exactly how you want to generate data for the tables in your database. We attempt to setup your plan with smart defaults, but also allow to you completely customize the generation. This tool tightly integrates with database unit testing, so you can automatically setup a data generation plan to execute and set your database state prior to running your database tests. The tool generates repeatable test data so that your tests can always expect the same values. This is very important for test verification. See my earlier post on A better approach to test data generation in order to find out some of the additional features of this tool.

 

2. Restore a database from backup\attach an existing database. If you already have a set of test data that you would like to use, often an easy approach is to automatically restore a database from a backup prior to running your unit tests. Similarly, you could attach an existing database to your server prior to running your tests. At some point I’ll add a post showing you exactly how to do this in Team Edition for Database Professionals.

 

3. Have your tests assume no state and as part of each pre-test, setup the appropriate state. This technique, while very “pure” from a philosophical unit testing perspective, is not very practical. Not only is it expensive to write such tests, but performance of setting up and cleaning up the database state for each test can be prohibitive.

 

The second problem of managing the state between tests has several techniques of its own:

 

1. Transactions. The best approach to solving this problem is to wrap your unit tests into transactions and abort the transaction after you are done performing your test verification and prior to running the next test. This will ensure that your database tests are transacted and thus return the database to its prior state before each test. I’ll post an example showing you easy it is to do exactly this in Team Edition for Database Professionals.

 

2. Cleaning up state changes in each post-test script. Another technique is to have each test take care of returning the database state back to its prior condition. This is a more manual approach than the above suggestion. For example, if you are testing a stored procedure dbo.CreateAuction(), in the post-test script of the test, you would go in and delete the auction from the relevant tables to return the database to its previous state.

 

I hope that gives you a sense of how you can handle the issue of managing database state inside of your database unit tests.

 

Do you have any other techniques that you typically use? Please let me know! I’d love to know how others are doing this today.

 

Sachin Rekhi

Comments

• Anonymous
  August 09, 2006
  I use a development version of the database in production. It only contains data that are usually found in lookup tables, besides that there is a script that cleans all transaction tables spare the lookup tables should running a test case crash and leave the database with test data in it.
  
  then there is a test database located on another server which is like the development database but it is the production database with just lookup data. This helps deployment when you would run the same test cases against the test database before the deoployment scripts of new/updated stored procedures, triggers and so forth. all new tests will fail and when the deployment script is applied all should pass. If not there is something missing from the deployment scripts.
  
  Then the deployment scripts are run against the production database with a fair amout of confidence that all the changes to stored procedures are contained.
  
  email me at avchung@gmail.com, I would be willing to discuss this further.
  
  best regards,
  
  andrew