How to effectively unit test code with lots of database dependencies?

Question

I find myself writing a lot of boilerplate mocking code for my unit tests. I think there must be a better way.

Background

I am working on a project that relies on complex configuration that is stored in a database. The API accepts an id, which is then used to load the configuration.

A very simple example:

public class FooProcessor : IProcessor
{
    private readonly IRepository<Foo> repository;

    public class FooProcessor(IRepository<Foo> repository)
    {
        this.repository = repository;
    }

    public string Process(string id, string data)
    {
        var configuration = this.repository.Get(id);
        return this.ProcessHelper(configuration, data);
    }

    private string ProcessHelper(Foo configuration, string data)
    {
        // Do something based on the configuration and data...
        return "TODO";
    }
}

Current Approach: Mocking

So far, I have been unit testing this project by mocking out the DAL using Moq. However, that is cumbersome, because (1) lots of classes rely on the DAL and (2) the configuration can be quite complex. So I am writing a lot of boilerplate mocking code in my tests.

Another Idea: Load-First

Another option I considered is to refactor all my classes to accept the loaded configuration, rather than have a dependency on the database. I still want the public API to accept an ID, but the public entrypoint could load the configuration and pass it to all my other classes. Then in my unit tests, I could pass configuration objects to the methods I am testing, which is simpler than wiring up mock dependencies.

However, that idea breaks down in more complex scenarios. For example, I might need to load various pieces of configuration for some process. And I might not know what configuration I have to load up front. For example, I might load configuration A, then start processing, and realize I also need configuration B. So I don't think I can realistically only hit the database at the API entrypoint. But maybe I can still use some variation of this idea?

Another Idea: Integration Tests (instead of unit tests?)

Or perhaps unit tests are not a good fit given these requirements, and I should focus my efforts on integration tests with a real database?

Answer 1

Another Idea: Integration Tests (instead of unit tests?)

Integration tests are not a replacement for unit tests. They complement them to do further extensive testing, but one does not replace the other.

So far, I have been unit testing this project by mocking out the DAL using Moq. However, that is cumbersome, because (1) lots of classes rely on the DAL and (2) the configuration can be quite complex. So I am writing a lot of boilerplate mocking code in my tests.

No one said you can't build some test fixtures to help cut down on the boilerplating.

For example, a client I work for has a homebrew test fixture. Their codebase is intricately complex (think payroll/HR/accounting software), and it's not easy to register a simple person and perform a small task, because you need to configure a ton of things to get this person to be seen as an active employee.

So they created a test fixture that does it for them. The test fixture does all the complicated admin, and the tests themselves have been cut down to:

Assumer
    .PersonExists(123)
    .PersonExists(456)
    .PersonIsTeamleadFor(123, 456)
    .PersonHasApprovedLeaveDayOn(456, DateTime.Now);

var myService = Assumer.GetLeaveService();

// perform test to see if person 123 can cancel person 456's leave

This is just a barebones example, but it highlights how much you can reduce the nitty gritty boilerplating down to meaningfully describing how the test data is being set up.

You have no idea how much data needs to be set up to have a person registered in the system, or how to configure rights and such. But you don't need to know that in the tests themselves. The fixture makes things much cleaner in the test itself.

Such a test fixture is not a small thing to make, but it can pay back huge dividends when you have many tests which all need to mock the same data source. As you can imagine, in my client's software people need to be mocked for pretty much every single test (out of tens of thousands).

Another option I considered is to refactor all my classes to accept the loaded configuration, rather than have a dependency on the database. I still want the public API to accept an ID, but the public entrypoint could load the configuration and pass it to all my other classes.

This is a viable option, but beware the "just one more layer" fallacy. It reminds me of an old joke:

A lady is sitting on a train. The train is shaking uncomfortably. She asks the conductor what is going on. The conductor tells her "well, Madam, unfortunately you are seated in the last carriage of the train, and this is always a bumpier ride". To which the lady promptly responds "well they should just do away with the last carriage on the train then".

In the end, there is always a "last" layer, i.e. the one that directly connects to the external resource. You can never truly unit test this layer, as you cannot easily mock the external connection itself.

Sure, you could add another abstraction layer by injecting the DB connection as a dependency, and then you can mock your current layer, but then the goalpost shifts: you now can't unit test that new layer you introduced.

At some point, further abstraction ceases to be useful, and the complexity/effort in making that new layer is more detrimental than beneficial.

In order to truly test that last layer, you'd need to rely on a database provider that has support for an in-memory provider. Entity Framework, as one example among many, has support for this.

Answer 2

One common tactic that might suit your needs is to use an embedded in-memory database in your unit tests. Sqlite is a popular choice here. Your test framework should provide a hook for code that runs before each test, to reset the database to a known state.

Answer 3

In addition to the previous answers.

First, make yourself the following question What am I testing?.

If I'm testing the example class, I don't need to mock up complicated behaviours. Neither I need a fresh database for each test because that would be too expensive in time and maybe in resources like memory or CPU. Keep in mind that, the more configuration you have the more tests you need. The more tests you have, the faster you want it to be.

I will assume that configurations are common ERM. So, why don't you write config files with the precise state you need. One for each test. Choose the format you like most, e.g JSON. Then, code a new IRepository implementation backed by these files¹. E.g JsonFileRepository.

Note: That goes without saying that files and repository belong to the testing code, not production.

Even if configurations are very complex data structures, worth writing them in files because it makes configurations easier to reason about. You have a whole configuration at sight. Editing is simpler than tackling SQL statements or changing hardcoded code. There's also way less code involved. And simpler.

Finally,

however, that is cumbersome, because (1) lots of classes rely on the DAL

I understand that you have implemented many mocks all over the testing code for very specific purposes. I would dare to say that you have some duplicated too.

This is not a solution, but it helped me out to keep my testing code DRY, reusable and easier to read. Provide your testing code with reusable code and use your domain-specific language to make tests more readable.

class FooDSL {
  
   public static Foo defaultFoo(){ ... }
   //replace [concrete] with a meaningful name or description from your DSL
   public static Foo [concrete]Foo(arguments){ ... } 
   public static Foo loadFoo(File file){...}
   public static Foo invalidFood(){ ... }
   public static FooRepository fileRepositoy(Folder folder){...}
   public static FooRepository emptyRepositoy(){...}
   public static FooRepository failingRepository(){ /*mocked one*/ ...}
}

^{I'm not familiar with C# syntaxis, so I wrote in Java}

Put all the mocking logic in these classes/methods and keep tests clean

@Test
public void processingFooWithFailingRepositories(){
  //Given  a failing repository
  IRepository<Foo> repository = failingRepository();
  //when processing Foo, then fail
  new FooProcessor(repository).process("","");
}

---

^{1: The repository can load 1 or N config files. Just avoid ID collisions among files.}

Answer 4

My approach:

1. Implement a IRepository class for testing purpose. Because you already have delcared the interface and Inject the solid implementation into your processor, that gives us a very good foundation for Unit Test

   // Inside your Unit Testing code
   public class FooProcessorTest {
   
       static TestFooRepository implements IRepository<Foo> {
           public Foo get(String id) {
               return <what ever Foo instance you need to test your logic>;
           }
       }
   
       @Test
       public process() {
           FooProcessor fooProcessor = new FooProcessor(new TestFooRepository());
   
           String result = fooProcessor.process(id, data);
           Assertions.assertTrue(result, ...);
       }
   } 
   
   

   Above is an example for simple use cases. If your test scenarios are very complex, you can create a static collection to hold all the test data so you can iterate all these scenarios.

   You can make your test implementations of IRepository more flexible with less code by using dynamic proxy to provide a generic proxy to handle method calls. Dynamic Proxy is a Java technique but I think C# should have the similar thing.

2. As the reply by @closeparen has mentioned, you can use in-memory database to test your features. There are a few options like SQLite or H2 database.

   But this kind of testing is more like an Integration Test because you actually introduce the real persistence layer into your testing, so sometimes the database structure changes could break your test cases.

   For this kind of testing, you need to well prepare the seed data before starting the test.

By the way, please don't use Mocking tools e.g. Moq, Mockito as the first option on unit test. You have a clean defined Interface and dependency injection design, you can just use normal class implementation to test the feature. If a project has to use a lot of Mocking code for testing, it is alwasy a warning sign of bad design.