How to unit test an object with database queries

Question

I've heard that unit testing is "totally awesome", "really cool" and "all manner of good things" but 70% or more of my files involve database access (some read and some write) and I'm not sure how to write a unit test for these files.

I'm using PHP and Python but I think it's a question that applies to most/all languages that use database access.

Answer 1

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.