Testing an application logic against a real relational database with relationships on entities-CodePudding

I'm trying to figure out what is the best method for integration testing when testing an application logic against a real relational database. I'm developing my solution in C# using Entity Framework and NUnit, but this should not be a language dependent question.

Imagine you're building an application that lets the user create Car entities and Person entities. Each Car must have a Person related to it, so basically one Person can have 0-N Car entities, and one Car can have only 1 Person entity as a FK.

The entities could look like this:

public class Person {
    public int PersonId { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }

    public List<Car> Cars { get; set; } // the navigation property
}

public class Car {
    public int CarId { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }

    public int PersonId { get; set; } // the foreign key
    public Person Person { get; set; } // the navigation property
}

Suppose you have a class CarRequestHandler with a method GetList that accepts a name filter and returns a list of Car entities that match that name.

Now let's focus on the integration testing aspect of this: I want to write some tests that connect to a real SQL Server database and checks if my logic works correctly and if I wrote the right queries using EF Core.

If I want to test the CarRequestHandler.GetList(string name) method, I first have to seed the database with some sample Car entities and then I can execute the test to see what the results of the invocation are. But in order to create a Car, I need to have a Person object already created that can be assigned to the Car entity.

Now, doing this by hand in every test method (or even in a setup fixture) can become really tedious, and especially cumbersome to write and maintain, since in a more complex database, the graph of the dependencies of the entity handler under test could become huge, and that could mean I may need to build an entire object graph consisting of every dependency my entity needs to exist in the SQL Server real database.

Is there some kind of tip you can give me in order to avoid having a big ol' spaghetti codebase that will make me and my team mates go "let's skip testing, we don't have time to do that"?

I hope I explained it well enough, let me know if I need to expand on anything.

CodePudding user response：

The best way I know of to run against real databases in tests would be using docker. There is a library called testcontainers that can greatly simplify the setup for .net testing. The real chore is getting realistic test data. For Some things you can use faker to generate realistic looking test data. But for others you´re gonna have to manually maintain a test data set when it´s too hard to generate.

Testing against production databases is also an option for some things that don´t need to write a lot to the database, or is easy to undo / written in a way a user will never seen it. Though, your integration tests loose portability and everything that runs said tests now needs access to the production database.

CodePudding user response：

If you have trouble adding data to your database you might want to reconsider how your database is structured, and what data the database should contain by default.

Consider looking at it from the applications perspective, it should be fairly simple for the application to store and retrieve objects from the database. So you should test your CarRequestHandler in a similar way it would be used by the actual application. I.e. test the interface, not the implementation. In some cases you might need to access the database directly in order to setup some specific cases, but this should ideally be fairly rare.

In some cases this might involve running a fairly large part of the application if there are many dependencies between various components. This might be easier to manage if using some kind of Dependency injection framework.

Also note the idea of "default data", your application probably needs some amount of pre-existing data to work, and you will need some system to add this data to any new system. Ideally it should be possible to add this data either from code, or by calling a script. You should add this default data to the database before running your tests. This allows the unit tests both to test the queries, and also test that various components work correctly with the default data.