Integration testing database, am I doing it right?

Question

I want to test methods in my MVC4 application that rely on and work with a database. I do not want to use mock methods / objects because the queries can be complicated and creating test objects for that is too much of an effort.

I found the idea of integration testing that wraps your test's database manipulating logic in a TransactionScope object that rolls back the changes when done.

Unfortunately, this does not start with an empty database at first and it also makes the primary keys count on (i.e., when there are already a few items in the database with Primary keys 1 and 2 then after I run the test it counts on with 4), I do not want this.

This is an "integration test" I came up with just to test if products are actually added (an example, I want to create more difficult test that check the methods once I have the infrastructure right).

    [TestMethod]
    public void ProductTest()
    {
        // Arrange
        using (new TransactionScope())
        {
            myContext db = new myContext();
            Product testProduct = new Product
            {
                ProductId = 999999,
                CategoryId = 3,
                ShopId = 2,
                Price = 1.00M,
                Name = "Test Product",
                Visible = true
            };

            // Act
            db.Products.Add(testProduct);
            db.SaveChanges();

            // Assert
            Assert.AreEqual(1, db.Products.ToList().Count());
            // Fails since there are already items in database

        }

    }

This raises a lot of questions, here's a selection: How can I start with an empty database? Should I attach another database to the project with its own context and connection string? And most importantly, how do I properly test methods on an actual database without ruining my old data?

I have been busy all day trying to figure out how to unit/integration test my database logic. I hope some experienced developers here can provide some help!

/edit The NDbUnit test that DOES affect/change my database...

public class IntegrationTests
{
    [TestMethod]
    public void Test()
    {
        string connectionString = "Data Source=(LocalDb)\\v11.0;Initial Catalog=Database_Nieuw;
            Integrated Security=false;"; 
        //The above is the only connectionstring that works... And is the "real" local database
        //This is not used on Jenkins but I can perhaps attach it???
        NDbUnit.Core.INDbUnitTest mySqlDatabase = new 
        NDbUnit.Core.SqlClient.SqlDbUnitTest(connectionString);
        mySqlDatabase.ReadXmlSchema(@"..\..\NDbUnitTestDatabase\NDbUnitTestDatabase.xsd");
        mySqlDatabase.ReadXml(@"..\..\NDbUnitTestDatabase\DatabaseSeeding.xml"); // The data
        mySqlDatabase.PerformDbOperation(NDbUnit.Core.DbOperationFlag.CleanInsertIdentity);
}

Answer 1

I do not want to use mock methods / objects because the queries can be complicated and creating test objects for that is too much of an effort.

This is the right strategy. Most "interesting" bugs tend to happen at the "boundary" between client code and the (real) database.

How can I start with an empty database?

Purge the database programmatically before each test. You can automate that by putting the purging code in a method marked with [TestInitialize] attribute. If your database happens to use ON DELETE CASCADE, deleting all data might be as simple as deleting few "top" tables.

Alternatively, just write your tests to be resilient in case there is already some data in the database. For example, each test would generate its own test data and use the specific IDs of the generated data only. This allows you better performance as you don't need to run any extra purging code.

And most importantly, how do I properly test methods on an actual database without ruining my old data?

Forget about it. Never run such tests on anything but a development database that you can throw-away as needed. Sooner or later you will commit something you did not intend to, or hold some lock longer than acceptable in production (e.g. by hitting a breakpoint in the debugger), or modify the schema in an incompatible manner, or just hammer it with load tests that would otherwise affect the productivity of real users...

Answer 2

I have found myself in a situation to write integration tests, but I didn't execute the tests against the development database since it was a subject of change. Since we used scrum methodology with sprints that lasted for two weeks, we were able to adopt the following approach:

1. At the end of each sprint we would make test database that matches the schema of the development database. In most of the cases this database would be restored on the test db server before each test executed, and it would be dropped after the test has finished.
2. Fill the test database with predictable set of data, that would not be a subject of change, except for the tests that need to change the data.
3. Configure out test projects to execute against the test database.

The tests that we wrote were separated in two parts.

1. Tests that only perform select queries against the database.
2. Tests that perform insert, update, delete queries against the database.

Above mentioned approach allowed us to always know what to expect after each test has executed. We used MSTest framework to write our test and used its abilities to execute logic before and after each test, or before and after each set of tests. Below code applies to the tests that perform only select queries.

[TestClass]
public class Tests_That_Perform_Only_Select   
{
    [ClassInitialize]
    public static void MyClassInitialize()
    {
        //Here would go the code to restore the test database.
    }

    [TestMethod]
    public void Test1()
    {
        //Perform logic for retrieving some result set.
        //Make assertions.
    }

    [TestMethod]
    public void Test2()
    {
        //Perform logic for retrieving some result set.
        //Make assertions.
    }

    [ClassCleanup]
    public static void MyClassCleanup()
    {
        //Here would go logic to drop the database.
    }
}

This way the tests would execute against predictable set of data and we would always know what to expect. Restoring and dropping of the database would be performed once per test class which would speed up executing of the tests.

For the tests that perform changes in the database, restoring and dropping of the database would be mandatory before each test executes, since we didn't want our next test to execute against a database that have unknown state because we wouldn't know what to expect. Here is a code sample for that scenario:

[TestClass]
public class Tests_That_Perform_Insert_Update_Or_Delete
{
    [TestInitialize]
    public void MyTestInitialize()
    {
        //Here would go the code to restore the test database.
    }

    [TestMethod]
    public void Test1()
    {
        //Perform logic.
        //Make assertions.
    }

    [TestMethod]
    public void Test2()
    {
        //Perform some logic.
        //Make assertions.
    }

    [TestCleanup]
    public void MyClassCleanup()
    {
        //Here would go logic to drop the database.
    }
}

In this scenario the test database is restored and dropped before and after each test.