Integration test using local database
Tags: testing
Published: 2021-02-06 17:02:14

Much is said about unit tests, but not so much about integration tests. Integration tests certifies that the components of an application work correctly at a level that includes the supporting infrastructure, such as a database, file system and network.

[Source code](https://github.com/vcinquini/ApiPEP)

The purpose of this article is to describe an integrated testing technique that covers from the callee method until the repository and its database, however this technique can be applied to other types of infrastructure such as those described above.
Imagining that we have a simple REST Api which stores a number of SSN and if its owner is a politically exposed person (PPE). A Politically Exposed Person (PPE) is considered to be the one who was or is being performing in foreign countries, territories and dependencies, positions, jobs or relevant public functions. More info here .
The purpose of this article is not to build an Api REST project, so I will assume that the reader already has the knowledge for this. Our Api will have only CRUD operations, so I won't go into too much details. The internet contains thousands of articles on how to build a simple Api with CRUD operations.
Following an Onion Architecture, we would have our presentation, application, domain and repository layers.

The structure would look like this:

![image](https://dev-to-uploads.s3.amazonaws.com/i/z96owsj9e1ldmrkys425.png)
 
Assuming you have a Read method (or Get, programmers are not good at naming things), you will probably have a flow that goes through several layers to where the data is persisted. One of them is the repository that accesses the database effectively.
For this, we will start with an interface which define the data access methods:

![image](https://dev-to-uploads.s3.amazonaws.com/i/tdi8gk9q59axb3kr48oe.png)
 
Next, we have an Interface that just inserts the Person type in the base interface of the repository:

![image](https://dev-to-uploads.s3.amazonaws.com/i/8gih84a0a9fuj7pwpciv.png)

We will also define an interface that will be responsible for returning a connection to the database currently used as persistence:
 
![image](https://dev-to-uploads.s3.amazonaws.com/i/m7w4qn2scqi9z9m0q8yr.png)

So, our Domain project should look like this:

![image](https://dev-to-uploads.s3.amazonaws.com/i/3436qdo5tmbp06j7xh91.png)
  
Our infrastructure project implements the interfaces that define access to the SQL server. We start by defining the class that returns a connection object connected to the database:

![image](https://dev-to-uploads.s3.amazonaws.com/i/th11uzutyu8jlnwutzqb.png)
  
And finally, we have the implementation of our class that will be responsible for accessing the repository and obtaining data from a Person:

![image](https://dev-to-uploads.s3.amazonaws.com/i/30tz23xwd4kmsl59g0uo.png)
  
An the end the infrastructure project should look like this:

 ![image](https://dev-to-uploads.s3.amazonaws.com/i/796pfacduq1yo71hjii3.png)
 
I won’t go into details about the controller’s implementation and the application layer, they are available in the source code.

Now we have a functional project that can go to the database and retrieve a customer's data.

The next step would be to build unit and integration tests (assuming you are not using TDD, it is common for the tests to always be for later).

Just as the Main method of the Program class is the starting point for code execution (entry point), we need a starting point in a test execution environment. This is done using the *CustomWebApplicationFactory* class.

We can assume that, when you create an object of the *CustomWebApplicationFactory* class, you are essentially creating a customized *Program.cs*, where you are free to add filters and services as needed. The *Program.cs* class creates the actual host server. The *CustomWebApplicationFactory* class creates a TestServer for integration tests.

![image](https://dev-to-uploads.s3.amazonaws.com/i/0n5494swbzsd8tajsied.png)
 
Starting with the controller's GET method, an integrated test should start by calling the method, running the entire flow right until the database, retrieving customer's data and returning them to the test runner.

Our problem starts here: What data to fetch? You may think, ‘I'm going to get some data from the base and the test should work’. Yes, it will ... as long as the data (in this case a person) exists. Databases are dynamic and it cannot be inferred that the data you selected will always exist. Another developer in another project (or even in the same project) can select exactly the same record as you did to test an exclusion routine for example. In this case, your test will fail.

In addition, in CI / CD processes, tests are performed every time we say, 'take a step on the treadmill'. That is, the test is performed during the build in the Development environment, then it is performed again in the QA environment and performed again in the Production environment. So how to select a record that exists in all environments?

An alternative would be to create a record to be used in the test. In this case, a database bootstrap routine could create a record, 'preparing' the test environment. Almost all testing frameworks have initialization and termination ways where you can prepare the environment for the tests execution and clean the created data at their end (more information [here](https://xunit.net/docs/shared-context) ).

I see problems with this technique. Creating data in databases can mess those tables whose primary key is AUTOIDENTITY. So, the tables are 'dirty', which is not a big problem in development bases, but it can be for QA and production environments. For production it can be even more complicated, as there is no guarantee that tests wouldn’t break in the middle of their execution, leaving useless records created at the database.
For that, I suggest a technique that can be very simple and at the same time avoid polluting the data in the databases.

Since we use IoC in the repository class to obtain connections to the database, we can change the connection according to the context. For normal production environment, the database connection must be injected into the repository class. But in a testing environment, we can inject a class that connects to another temporary database in memory or location that can be manipulated during the tests, without causing damage to the production and QA databases, etc.

We started by defining another class that defines the connection to a mocked DB server for example. Note that it inherits from the *ISQLConnectionFactory* interface as the real connection class does.

![image](https://dev-to-uploads.s3.amazonaws.com/i/19h4fn8yd8nb1u8erc4j.png)
 
Therefore, we need to add connection strings to a local database:

![image](https://dev-to-uploads.s3.amazonaws.com/i/wn4u87mpm7tgesji52sp.png)
 
Now notice that *CustomWebApplicationFactory* inherits from *WebApplicationFactory<TEntryPoint>*, which needs a type which is the entry point in the application's assembly, typically the Startup or Program classes (more information [here](https://docs.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.mvc.testing.webapplicationfactory-1?view=aspnetcore-5.0) ).
In short, the test application entry point uses all services registered in the Startup class when starting the tests. Therefore, the service container has an instance of the DefaultSqlConnectionFactory class ready for use, as it is registered in the Startup class:

![image](https://dev-to-uploads.s3.amazonaws.com/i/qbs8rcbq6z68qd96kmvn.png)
 
How do we do it if we want to use a connection to a temporary database for testing? We would have to exchange the connection service being provided by the container for another one. This is done whit a class extension:

![image](https://dev-to-uploads.s3.amazonaws.com/i/a1qjoye8hp79dry3ezv1.png)
  
This method is an extension of the class that receives an object of type *IServiceCollection*, which contains a collection of Service Descriptors of the services added to the container. This method removes any service registered in the container whose interface is the same type as that informed in the *TService* type and at the end, it will add the object passed by the *implementationFactory* parameter.

![image](https://dev-to-uploads.s3.amazonaws.com/i/a4t5q3qmupxb9gfi7rql.png)
 
Now when requesting an instance of the connection class to the database, the service container will return an object that connects to the temporary database for the tests execution.
One suggestion is to have a bootstrap routine to be called during tests initialization that initializes a temporary database:

![image](https://dev-to-uploads.s3.amazonaws.com/i/hpw3fubtpu027mn2qt4m.png)
 
Run the script.sql file on your SQL Server to have the database used for production API execution.
As we can see, using interfaces and dependency injection, we can mock any access to infrastructure layers, simulating a real environment. This technique could be used for example to mock access to another Api, a file on disk, a NoSQL server, etc.