Cleanup & Arrange practices during integration testing to avoid dirty databases

Question

I'm coding tests in C# and I settled with this structure:

try
{
    // ==========
    // ARRANGE
    // ==========

    // Insert into the database all test data I'll need during the test

    // ==========
    // ACT
    // ==========

    // Do what needs to be tested

    // ==========
    // ASSERT
    // ==========

    // Check for correct behavior
}
finally
{
    // ==========
    // CLEANUP
    // ==========

    // Inverse of ARRANGE, delete the test data inserted during this test
}

The concept was "every test cleans up the mess it makes". However, some tests are leaving the database dirty and the failing the tests that come after.

What's the right way to do this? (minimize errors, minimize run time)

• Deletes everything » Insert defaults » Insert test data » Run test?

• Insert defaults » Insert test data » Run test » Delete everything?

• Currently:

  • (per session) Deletes everything » Insert defaults
  • (per test) Insert test data » Run test » Delete test data

Answer 1

Besides the fact that this is an integration test as opposed to a unit test, the operations you describe typically go in Setup and/or Teardown methods. Frameworks like nUnit allow one to decorate class methods with these attributes to indicate whether the method is a setup method or teardown method.

Then your tests should become cleaner and smaller as the setup and cleanup is done outside of the test itself.

Most likely multiple tests can re-use the same data so that is a plus as well as opposed to insert/remove on every test. Going back to nUnit, the FixtureSetup and FixtureTeardown attributes help to setup data for multiple tests at once.

I would use a test framework over a try/catch as many of these testing features are built into the framework itself. xUnit, nUnit, even the Microsoft built in testing framework are all solid choices and will help with the setup and clean up of database records in a consistent fashion.

Answer 2

The point that you should be aiming for with such tests is that as many of them as possible should be interacting with a mock of the database, rather than the database itself. The standard way to achieve this is to inject a DB access layer into the logic you are testing here, using interfaces. That way, the test code can create in-memory data sets prior to each test and then trash them afterwards. The tests can all then run in parallel and will not affect each other. This makes your tests faster, easier to write and understand and more robust.

Then you need to test the actual DB access layer itself. Because you will only have a few of these tests, they can then, for example, create a test table (or even database), unique to that test, and populate it with test data. After the test has run , the entire test table/DB is then destroyed. Again, these tests should be able to run in parallel, thus should not have a significant impact on the overall test execution time.

Answer 3

The big problem with databases and (unit-)tests is that databases are so darn good at persisting stuff.

The usual solution is to not use an actual database in your unit-tests, but instead mock the database or use an in-memory database that can easily be wiped completely in-between tests.
Only when testing the code that directly interacts with the database, or in end-to-end tests would the actual database be used.

Answer 4

Working on a C# Server with SQL Server and PetaPoco, this is the approach we took to clean-up data in Unit Tests.

A typical unit test would have Setup and Teardown as follows:

[TestFixture]
internal class PlatformDataObjectTests
{
    private IDatabaseConfiguration _dbConfig;
    private Database _pocoDatabase;
    private PlatformDataObject _platformDto;

    [SetUp]
    public void Setup()
    {
        _dbConfig = new CommonTestsAppConfig().GetDatabaseConfiguration();
        _pocoDatabase = new Database(_dbConfig.ConnectionString, SqlClientFactory.Instance);
        _platformDto = new PlatformDataObject(_pocoDatabase);
        _platformDto.BeginTransaction();
    }

    [TearDown]
    public void TearDown()
    {
        Console.WriteLine("Last Sql: {0}", _pocoDatabase.LastCommand);

        _platformDto.RollbackTransaction();
        _platformDto.Dispose();
    }

    // ... 
}

Where PlatformDataObject is a class responsible for communicating with the database e.g. performing Select Insert Update Deletes. All the *DataObject types inherit ServerDataObject - the base class has methods for aborting, rolling back or committing the transaction.

/// <summary>
/// A Data-Transfer Object which allows creation and querying of Platform types from the database
/// </summary>
[ExportType(typeof(IPlatformDataObject))]
public class PlatformDataObject : ServerDataObject, IPlatformDataObject
{
    private static readonly ILog Log = LogManager.GetLogger(typeof (ProductDataObject));

    private const string PlatformTable = "t_Platform";

    public PlatformDataObject(IPocoDatabase pocoDatabase) : base(pocoDatabase)
    {
    }

    ... 
}

/// <summary>
/// A base Data-Transfer Object type
/// </summary>
public abstract class ServerDataObject : IServerDataObject
{
    protected const string Star = "*";

    private readonly IPocoDatabase _pocoDatabase;

    public ServerDataObject(IPocoDatabase pocoDatabase)
    {
        _pocoDatabase = pocoDatabase;
    }

    public string LastCommand
    {
        get { return PocoDatabase.LastCommand; }
    }

    public IPocoDatabase PocoDatabase
    {
        get { return _pocoDatabase; }
    }

    public int TransactionDepth
    {
        get { return _pocoDatabase.TransactionDepth; }
    }

    public bool TransactionAborted { get; private set; }

    public void BeginTransaction()
    {
        _pocoDatabase.BeginTransaction();
    }

    public void AbortTransaction()
    {
        _pocoDatabase.AbortTransaction();
    }

    public void RollbackTransaction()
    {
        TransactionAborted = true;
    }

    public virtual void Dispose()
    {
        if (TransactionAborted)
            _pocoDatabase.AbortTransaction();
        else
            _pocoDatabase.CompleteTransaction();
    }
}

All of the unit tests would call RollbackTransaction(), would would ultimately call IDbTransaction.Rollback().

In tests we found it routine to create a new instance of a *DataObject, create some rows using Insert statements, perform tests on them (Selects, Updates etc...) and then roll-back.

We may setup a set of test data before all tests are run using a SetUpFixture - a class run once before all tests are run, and delete / rollback the data in teardown after all tests are run.