Follow @RoyOsherove on Twitter

Database Unit Testing issues and solutions: Rollback And RestoreDatabase abilities

Update: Later on, after this article was written, I've created a much more elegant way to have your own [Rollback] attribute without resorting to a whole new binary version of Nunit. In fact, that attribute will work with any testing framework out there for .NET. It's called XTUnit and you can learn more about it here.
There are at least two good ways to solve the database rollback problem we are all facing when running unit tests against a real database.  Each method has different strength and weaknesses and I'd say they compliment each other.

 Feature 1 – Database rollback - RollbackAttribute:


One of the most elegant ways I've found to implement database rollback is through transaction. Actually – COM+ transactions.

I won't specify all of the idea here – but I'll point you to an article I've written on the subject:

 Simplified Database Unit testing using Enterprise Services

 In short the article discusses inheriting your test fixture from a serviced component fixture, which has a TransactionOption.Required on it, and calls ContextUtil.SetAbort() on teardown.  This achieves a cool thing: all test code runs inside a transaction and is rolled back automatically. This works well in many scenarios but also has some disadvantages (listed in the article).

 So, after a while I came up with an even more elegant way to achieve this functionality with less overhead and hassle: By using Services without components you can take care of many issues arising in the previous way. But I took it one step forward: Introducing: NUnitX and the Rollback attribute - Seamless database rollback with Nunit

I've actually added a new attribute to the Nunit code and a new test case type that inherits from NormalTestCase. Basically – the new class – TransactionTestCase enters a serviced domain before setup and leaved after teardown. One of the coolest things in this is that each test can be marked separately if we want it to run with transaction or not.

 You can download NUnitX from the link above.  

I'd love to be part of the dev team on this thing and integrate it myself if I could. But I don’t know how things work around here so I'm posting the code for the person who is most likely to know what to do with it. I've sent an email to the NUnit-Developers mailing list and we'll see what we come up with.

 Feature 2 – Database Restore – DatabaseRestoreAttribute and DatabaseRestoreInfoAttribute:


The idea here is simple: I don't want some of my tests to run under a transaction for various reasons:

-          if I am testing large amounts of data in multiple threads this is a big perf hit

-          if I have tested components that use TransactionOption.NotSupported or TransactionOption.RequiresNew

-          I don't want to add "context" into my tested code

 So the other "best" way is to restore the database each time programmatically from a backup file.

There is a great library in the works, by Jonathan de Halleux (creator of MbUnit) that is called TestFu.  Among other things it has a SqlAdministrator class that allows one to programmatically restore and backup a database and other things. Very cool.

It had only one downside – when you restore a database you need to make sure it has no open connections  from other processes. This class did not check this and so the code fails sometimes. However – I've integrated a derived class that *does* check this and shuts down those process's connections to the DB to be restored.  this new functionality will be available in the next version of TestFu. You can download that class *today* directly from here (along with example tests)

Moving right along about the Attributes I am talking about: What I have already implemented in a version of Nunit (but don't have finalized code yet) is support for this functionality using attributes as well. This support is divided in to 2 attributes.

The first one DatabaseRestoreInfo is meant on sit on top of the test fixture and provides metadata on the following:

-          db connection string

-          db name

-          backup file/destination to restore from


the second attribute is simple: RestoreDatabaseAttribute , Sits on any specific test we want to add this functionality to.

It has no data. It is simply a signal for a specific test to do a restore after it is run using the metadata from the attribute found on top of the class. So – for a simple class that uses this functionality this could looks something like this:


[DatabaseRestoreInfo("server=localhost;integrated security=SSPI", "Northwind", @"C;\backup\nwind.bak",DeviceType.File)]

Public class MySimpleClassFixture



Public void SomeDBTest()


            //this test will be automatically rolled back using a COM+ transaction




Public void SomeDBTest()


            //After this test is run the database will be automatically restored form the details on the attribute on the class




Like I said - we don't have these attributes today - but you can use TestFu in conjunction with your tests to get a similer effect. For now.

I'm working on integrating both of these into Nunit by posting these ideas and issues on the NUnit-Developers mailing list.. Let's see if I can make that work.

Unit Testing scalability and performance

Blogger/geek dinner is canceled for today