What’s the best strategy for unit-testing database-driven applications

databasemockingormunit testing

I work with a lot of web applications that are driven by databases of varying complexity on the backend. Typically, there's an ORM layer separate from the business and presentation logic. This makes unit-testing the business logic fairly straightforward; things can be implemented in discrete modules and any data needed for the test can be faked through object mocking.

But testing the ORM and database itself has always been fraught with problems and compromises.

Over the years, I have tried a few strategies, none of which completely satisfied me.

Load a test database with known data. Run tests against the ORM and confirm that the right data comes back. The disadvantage here is that your test DB has to keep up with any schema changes in the application database, and might get out of sync. It also relies on artificial data, and may not expose bugs that occur due to stupid user input. Finally, if the test database is small, it won't reveal inefficiencies like a missing index. (OK, that last one isn't really what unit testing should be used for, but it doesn't hurt.)
Load a copy of the production database and test against that. The problem here is that you may have no idea what's in the production DB at any given time; your tests may need to be rewritten if data changes over time.

Some people have pointed out that both of these strategies rely on specific data, and a unit test should test only functionality. To that end, I've seen suggested:

Use a mock database server, and check only that the ORM is sending the correct queries in response to a given method call.

What strategies have you used for testing database-driven applications, if any? What has worked the best for you?

Best Answer

I've actually used your first approach with quite some success, but in a slightly different ways that I think would solve some of your problems:

Keep the entire schema and scripts for creating it in source control so that anyone can create the current database schema after a check out. In addition, keep sample data in data files that get loaded by part of the build process. As you discover data that causes errors, add it to your sample data to check that errors don't re-emerge.
Use a continuous integration server to build the database schema, load the sample data, and run tests. This is how we keep our test database in sync (rebuilding it at every test run). Though this requires that the CI server have access and ownership of its own dedicated database instance, I say that having our db schema built 3 times a day has dramatically helped find errors that probably would not have been found till just before delivery (if not later). I can't say that I rebuild the schema before every commit. Does anybody? With this approach you won't have to (well maybe we should, but its not a big deal if someone forgets).
For my group, user input is done at the application level (not db) so this is tested via standard unit tests.

Loading Production Database Copy:
This was the approach that was used at my last job. It was a huge pain cause of a couple of issues:

The copy would get out of date from the production version
Changes would be made to the copy's schema and wouldn't get propagated to the production systems. At this point we'd have diverging schemas. Not fun.

Mocking Database Server:
We also do this at my current job. After every commit we execute unit tests against the application code that have mock db accessors injected. Then three times a day we execute the full db build described above. I definitely recommend both approaches.

From Martin Fowler about Mock and Stub

Fake objects actually have working implementations, but usually take some shortcut which makes them not suitable for production

Stubs provide canned answers to calls made during the test, usually not responding at all to anything outside what's programmed in for the test. Stubs may also record information about calls, such as an email gateway stub that remembers the messages it 'sent', or maybe only how many messages it 'sent'.

Mocks are what we are talking about here: objects pre-programmed with expectations which form a specification of the calls they are expected to receive.

From xunitpattern:

Fake: We acquire or build a very lightweight implementation of the same functionality as provided by a component that the SUT depends on and instruct the SUT to use it instead of the real.

Stub : This implementation is configured to respond to calls from the SUT with the values (or exceptions) that will exercise the Untested Code (see Production Bugs on page X) within the SUT. A key indication for using a Test Stub is having Untested Code caused by the inability to control the indirect inputs of the SUT

Mock Object that implements the same interface as an object on which the SUT (System Under Test) depends. We can use a Mock Object as an observation point when we need to do Behavior Verification to avoid having an Untested Requirement (see Production Bugs on page X) caused by an inability to observe side-effects of invoking methods on the SUT.

Personally

I try to simplify by using : Mock and Stub. I use Mock when it's an object that returns a value that is set to the tested class. I use Stub to mimic an Interface or Abstract class to be tested. In fact, it doesn't really matter what you call it, they are all classes that aren't used in production, and are used as utility classes for testing.

C# – How to mock out the file system in C# for unit testing

Edit: Install the NuGet package System.IO.Abstractions.

This package did not exist when this answer was originally accepted. The original answer is provided for historical context below:

You could do it by creating an interface:
interface IFileSystem {
    bool FileExists(string fileName);
    DateTime GetCreationDate(string fileName);
}
and creating a 'real' implementation which uses System.IO.File.Exists() etc. You can then mock this interface using a mocking framework; I recommend Moq.

Edit: somebody's done this and kindly posted it online here.

I've used this approach to mock out DateTime.UtcNow in an IClock interface (really really useful for our testing to be able to control the flow of time!), and more traditionally, an ISqlDataAccess interface.

Another approach might be to use TypeMock, this allows you to intercept calls to classes and stub them out. This does however cost money, and would need to be installed on your whole team's PCs and your build server in order to run, also, it apparently won't work for the System.IO.File, as it can't stub mscorlib.

You could also just accept that certain methods are not unit testable and test them in a separate slow-running integration/system tests suite.

Best Answer

Related Solutions

What’s the difference between faking, mocking, and stubbing

From Martin Fowler about Mock and Stub

From xunitpattern:

Personally

C# – How to mock out the file system in C# for unit testing

Related Topic