Using Transactions for Unit Tests

Unit tests should preferably be independent of external services, systems and files. The standard way to achieve this is to create mocks. A mock is an object that can be used in place of the real resource and act in a predictable way to ensure the tests always give the same result. I think that this is a perfectly valid approach for any external services that the system is integrated with. The problem for business applications is the database. Mocking the database by providing a mock implementation of the database access layer is a huge task. It is too huge to be worth the effort. Another approach that I often use is to use a database in the tests, but keep it unchanged through transactions.

Transactions are meant to be used to ensure everything is in place before committing and actually saving anything in the database. If anything is wrong, the entire batch of updates is rolled back. When doing tests this can be used to always roll back the database to a known state when a test has been run.

Assume that I want to test a retirement function for cars:

/// <summary>
/// Retires a car, removing it from the database.
/// </summary>
public static bool Retire(string registrationNo)
    using (DBContext dbContext = new DBContext())
        Car car = dbContext.Cars
          .Where(c => c.RegistrationNumber == registrationNo)
        if (car == null)
            return false;
        return true;

I want to test both flow of controls, by first retiring an existing car and then try to retire it once again. The problem is that calling Retire will delete the car from the database. The next time I run the test using the same registration number there will be no such car. Using a TransactionScope to roll back the deletion when the test is done will keep the database in its original state. With a transaction scope, my test looks like this:

public void TestRetire()
    using (TransactionScope transaction = new TransactionScope())
        // Deliberately not commiting transaction.

This approach gives me a database with known initial state, containing data that is suitable for the tests. Thanks to the transaction being rolled back, the tests never change the data in the database, so it is always in a known state. Compared to mocking the entire database this is much easier. If the entire database layer has to be mocked, I doubt there would be any tests at all. It would be too much trouble creating the tests.

  • Rémi BOURGAREL on 2012-09-12

    These are not unit test, these are integration test. It is not “preferable” for a unit test to avoid external services, it’s mandatory : it’s in its definition : a unit test test only one unit (a method in a class).

    • Anders Abel on 2012-09-12

      You’re right that technically they are integration tests as they access an external service. Looking at the purpose and what they are testing, I still think that they are closer to unit tests. To me, an integration test is something that tests that two or more parts of the system works together. A unit test is something that tests the logic of a single method, which is what these tests do. But that discussion is just a discussion of naming, the important message in this post is the concept.

      Regardless of what you choose to call them, the concept I want to show is to use transactions to avoid having to mock/stub the database layer. In any project having a database, with an ORM on top of it, mocking or stubbing them is so huge a task that it will very seldom be done. Using transactions for the tests is the second best. Mocking or stubbing the entire data access layer is better. But if I have to choose between tests involving the database with transactions or having no tests at all I prefer the transaction based tests.

      • Rémi BOURGAREL on 2012-09-12

        Totally agree with you ! Mocking/ Faking the data access layer is a big PITA and make your development effort and data access optimization way harder.

  • Tej on 2014-02-26

    If you’re testing the database, this works just fine.

    I had code doing a bulk delete. But the data deleted is used in other integration tests. So this just works for me. I can’t mock db out when I am testing the db.

  • mike on 2014-08-14

    Another good article on this subject. I use this for a database framework.

  • Yelinna on 2019-12-03

    I was doing this while testing against a DB in production, really useful :D

  • Leave a Reply

    Your name as it will be displayed on the posted comment.
    Your e-mail address will not be published. It is only used if I want to get in touch during comment moderation.
    Your name will be a link to this address.
Software Development is a Job – Coding is a Passion

I'm Anders Abel, an independent systems architect and developer in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

Code for most posts is available on my GitHub account.

Popular Posts



Powered by WordPress with the Passion for Coding theme.