High performance database rollback in automated tests with SQL Server
A couple months ago I wrote this article explaining why I think it is reasonable for unit tests to hit a real database. Subsequently, I wrote a follow up article describing some techniques for rolling back your database to its original state after each test. In that article I found that just using simple transactions did not solve the problem because you need access to all database connections being used, and they all have to be rolled back. I have since found a way around this problem using distributed transactions.
With the Microsoft Distributed Transaction Coordinator (MSDTC) the activity over multiple connections can be lumped into a single transaction using the TransactionScope class. MSDTC needs to be running for this to work, but since this is just for unit tests it doesn’t need to be enabled on your production environment.
In order to use the TransactionScope class your project will need a reference to System.Transactions. Here’s a sample unit test using MSTest and Entity Framework where the database is altered with multiple connections within a transaction and then the changes are rolled back:
Imports System.Transactions Imports System Imports System.Text Imports System.Collections.Generic Imports Microsoft.VisualStudio.TestTools.UnitTesting <TestClass()> _ Public Class UnitTestSample <TestMethod()> _ Public Sub ProofOfConceptTest() Using New TransactionScope Dim conn1 As New DataTestEntities Dim conn2 As New DataTestEntities Dim row1 As New Users With {.userName = "user1", .password = "pass"} Dim row2 As New Users With {.userName = "user2", .password = "pass"} conn1.AddToUsers(row1) conn2.AddToUsers(row2) conn1.SaveChanges() conn2.SaveChanges() Dim conn3 As New DataTestEntities Assert.AreEqual(conn3.Users.Count, 6) End Using End Sub End Class
Alternatively, if you want every test method inside a test class to be within its own TransactionScope without adding a Using block to every single test, you can use the initialization and cleanup methods like this:
Imports System.Transactions Imports System Imports System.Text Imports System.Collections.Generic Imports Microsoft.VisualStudio.TestTools.UnitTesting <TestClass()> _ Public Class UnitTestSample Private _transaction As TransactionScope <TestInitialize()> _ Public Sub Setup() _transaction = New TransactionScope End Sub <TestCleanup()> _ Public Sub TearDown() _transaction.Dispose() End Sub <TestMethod()> _ Public Sub ProofOfConceptTest() Dim conn1 As New DataTestEntities Dim conn2 As New DataTestEntities Dim row1 As New Users With {.userName = "user1", .password = "pass"} Dim row2 As New Users With {.userName = "user2", .password = "pass"} conn1.AddToUsers(row1) conn2.AddToUsers(row2) conn1.SaveChanges() conn2.SaveChanges() Dim conn3 As New DataTestEntities Assert.AreEqual(conn3.Users.Count, 6) End Sub End Class
As long as the use of MSDTC is an option, I have found this method to be far better than any of those described in the last article. It guarantees that the state or your database is maintained and is extremely fast (at least on small amounts of data).
[...] (Jan 23, 2010): I have made a new post explaining a method that I think is better than any of those described [...]
This is quite good, I was wondering if there’s a good way of doing this. Recently I’ve been working on a large-ish Silverlight/RIA services/EF app and the unit tests ended up as integration tests; I wrote a couple stored procedures that cleaned up and restored test data but couldn’t possibly run them after each test method - they were reasonably fast but not that fast. I’ll definitely give this a go.
Great! But: This sounds more like integration tests than unit test. Its hard to include these tests in continous integration because if the database schema changes, the tests will fail on the build server. Of course you can manage scripts and run them on the build server before executing the tests, but it makes it more difficult. I really like EF but i miss the data access test possibilities in NHibernate.
If the tests fail on the build server then that is a feature. It notifies you that your schema is out of sync with your tests (and quite possibly your application code as well).
Is it fast enough to run the tests against a real DBMS?
I guess the only answer is “it depends”, but I have found it to gives better performance than any other method. I use very small amounts of data in the test db though.
[...] to Graeme Hill (High performance database rollback in automated tests with SQL Server) for the [...]