How to unit test an object with database queries

Question

I would suggest mocking out your calls to the database. Mocks are basically objects that look like the object you are trying to call a method on, in the sense that they have the same properties, methods, etc. available to caller. But instead of performing whatever action they are programmed to do when a particular method is called, it skips that altogether, and just returns a result. That result is typically defined by you ahead of time.

In order to set up your objects for mocking, you probably need to use some sort of inversion of control/ dependency injection pattern, as in the following pseudo-code:

class Bar
{
    private FooDataProvider _dataProvider;

    public instantiate(FooDataProvider dataProvider) {
        _dataProvider = dataProvider;
    }

    public getAllFoos() {
        // instead of calling Foo.GetAll() here, we are introducing an extra layer of abstraction
        return _dataProvider.GetAllFoos();
    }
}

class FooDataProvider
{
    public Foo[] GetAllFoos() {
        return Foo.GetAll();
    }
}

Now in your unit test, you create a mock of FooDataProvider, which allows you to call the method GetAllFoos without having to actually hit the database.

class BarTests
{
    public TestGetAllFoos() {
        // here we set up our mock FooDataProvider
        mockRepository = MockingFramework.new()
        mockFooDataProvider = mockRepository.CreateMockOfType(FooDataProvider);

        // create a new array of Foo objects
        testFooArray = new Foo[] {Foo.new(), Foo.new(), Foo.new()}

        // the next statement will cause testFooArray to be returned every time we call FooDAtaProvider.GetAllFoos,
        // instead of calling to the database and returning whatever is in there
        // ExpectCallTo and Returns are methods provided by our imaginary mocking framework
        ExpectCallTo(mockFooDataProvider.GetAllFoos).Returns(testFooArray)

        // now begins our actual unit test
        testBar = new Bar(mockFooDataProvider)
        baz = testBar.GetAllFoos()

        // baz should now equal the testFooArray object we created earlier
        Assert.AreEqual(3, baz.length)
    }
}

A common mocking scenario, in a nutshell. Of course you will still probably want to unit test your actual database calls too, for which you will need to hit the database.

Ideally, your objects should be persistent ignorant. For instance, you should have a "data access layer", that you would make requests to, that would return objects. This way, you can leave that part out of your unit tests, or test them in isolation.

If your objects are tightly coupled to your data layer, it is difficult to do proper unit testing. the first part of unit test, is "unit". All units should be able to be tested in isolation.

In my c# projects, I use NHibernate with a completely seperate Data layer. My objects live in the core domain model and are accessed from my application layer. The application layer talks to both the data layer and the domain model layer.

The application layer is also sometimes called the "Business Layer".

If you are using PHP, create a specific set of classes for ONLY data access. Make sure your objects have no idea how they are persisted and wire up the two in your application classes.

Another option would be to use mocking/stubs.

The easiest way to unit test an object with database access is using transaction scopes.

For example:

    [Test]
    [ExpectedException(typeof(NotFoundException))]
    public void DeleteAttendee() {

        using(TransactionScope scope = new TransactionScope()) {
            Attendee anAttendee = Attendee.Get(3);
            anAttendee.Delete();
            anAttendee.Save();

            //Try reloading. Instance should have been deleted.
            Attendee deletedAttendee = Attendee.Get(3);
        }
    }

This will revert back the state of the database, basically like a transaction rollback so you can run the test as many times as you want without any sideeffects. We've used this approach successfully in large projects. Our build does take a little long to run (15 minutes), but it is not horrible for having 1800 unit tests. Also, if build time is a concern, you can change the build process to have multiple builds, one for building src, another that fires up afterwards that handles unit tests, code analysis, packaging, etc...

You should mock the database access if you want to unit test your classes. After all, you don't want to test the database in a unit test. That would be an integration test.

Abstract the calls away and then insert a mock that just returns the expected data. If your classes don't do more than executing queries, it may not even be worth testing them, though...

I can perhaps give you a taste of our experience when we began looking at unit testing our middle-tier process that included a ton of "business logic" sql operations.

We first created an abstraction layer that allowed us to "slot in" any reasonable database connection (in our case, we simply supported a single ODBC-type connection).

Once this was in place, we were then able to do something like this in our code (we work in C++, but I'm sure you get the idea):

GetDatabase().ExecuteSQL( "INSERT INTO foo ( blah, blah )" )

At normal run time, GetDatabase() would return an object that fed all our sql (including queries), via ODBC directly to the database.

We then started looking at in-memory databases - the best by a long way seems to be SQLite. (http://www.sqlite.org/index.html). It's remarkably simple to set up and use, and allowed us subclass and override GetDatabase() to forward sql to an in-memory database that was created and destroyed for every test performed.

We're still in the early stages of this, but it's looking good so far, however we do have to make sure we create any tables that are required and populate them with test data - however we've reduced the workload somewhat here by creating a generic set of helper functions that can do a lot of all this for us.

Overall, it has helped immensely with our TDD process, since making what seems like quite innocuous changes to fix certain bugs can have quite strange affects on other (difficult to detect) areas of your system - due to the very nature of sql/databases.

Obviously, our experiences have centred around a C++ development environment, however I'm sure you could perhaps get something similar working under PHP/Python.

Hope this helps.

The book xUnit Test Patterns describes some ways to handle unit-testing code that hits a database. I agree with the other people who are saying that you don't want to do this because it's slow, but you gotta do it sometime, IMO. Mocking out the db connection to test higher-level stuff is a good idea, but check out this book for suggestions about things you can do to interact with the actual database.