In my opinion, database layer shouldn’t be mocked for running test cases. Sometimes database constraints like foreign key, non null, unique, etc., might throw errors if we were making any mistake in the code. If we mock the database layer, then these issues will go unidentified & show surprises at runtime even though the test cases might pass.

In order to overcome this, we’ll be running the test cases against the databases like MySql or MSSQL in both local environment and in GitLab CI/CD.

In this article, we’ll use .NET 5 + Entity Framework Core + MySql database. For any other ORM/Database/CI-CD, we can take the concept out of this article & implement a solution tailored for our needs.

Let’s get started.

I’ve created following three projects using .NET CLI. Alternatively, this can be done in Visual Studio as well.

# New Solution to hold the projects
dotnet new sln -n MyApp
# Class library project to hold the service classes (a.k.a Business Layer)
dotnet new classlib MyApp.Domain
# xUnit project to run the test cases
dotnet new xunit -n MyApp.Domain.Tests
# Class library project to hold the entity framework core's
# dbcontext and migrations
dotnet new classlib -n MyApp.Infrastructure
# Add the projects to the solution
dotnet sln add MyApp.Infrastructure
dotnet sln add MyApp.Domain
dotnet sln add MyApp.Domain.Tests

. . .

Add Configuration Helper:

The purpose of the configuration helper is to read the connection string from the appsettings.json file and also from the environment variables(this will be used in the part 2 for Gitlab CI/CD) and bind to the strongly typed model.

Install the following packages to MyApp.Infrastructure Project

cd src/MyApp.Infrastructure
dotnet add package Microsoft.Extensions.Configuration.EnvironmentVariables
dotnet add package Microsoft.Extensions.Hosting.Abstractions
dotnet add package Microsoft.Extensions.Configuration.Binder
dotnet add package Microsoft.Extensions.Configuration.Json

Adding DBContext & Migrations:

I’ve added a simple DB context class with User entity

If you have a closer look at the above MyAppDbContext class, I’ve modified OnConfiguring method, to read the connection string using our ConfigurationHelper class.

Now we need to create code first migrations.

Install entity framework tools as global tool using the following command.

dotnet tool install --global dotnet-ef

Add the following packages to MyApp.Infrastructure project

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Pomelo.EntityFrameworkCore.MySql

Make sure that appsettings.json exists in MyApp.Infrastructure project as that will be used to read the connection string

Tip: Do not commit appsettings.json as this will contain credentials. Instead create a sample file appsettings.example.json and keep it in git

cd src/MyApp.Infrastructure
dotnet ef migrations add "InitialCreate"

Let’s use this migration to create the database

dotnet ef database update

Now, if we login to the MySql server using MySql Workbench or HeidiSql, the database is created for us.

Every time we run the tests, we need to recreate the database and seed the necessary data. Let’s create another two classes for this purpose.

Ok. So far, our Infrastructure layer is ready with DbContext, migrations, ConfigurationHelper, DatabaseSeeder, DatabaseMigrator

Adding User Service:

In MyApp.Domain project, I’ve created a new class called ‘UserService’ to write the logics for the user.

Adding Test Case:

I’ve added reference to MyApp.Domain and MyApp.Infrastructure for MyApp.Domain.Tests Project. Then I added UserServiceTest class and wrote some tests to test the methods in the UserService. Also, I added the appsettings.json to configure the connection string.

Here, the important thing to note is the constructor where we create and migrate the database for each test method.

So far, we’ve setup everything to the run the test cases against a real database in local environment.

If we need to drop the database & recreate it again for each test method, we can do so using the DropDatabase method in our DatabaseMigrator class

Running the Test Cases:

Before running the test cases, make sure the mysql server is up & running in your local environment and also make sure that the connection string is valid in appsettings.json in MyApp.Tests project.

Now, let’s run the test cases using dotnet cli / Visual Studio. It should pass.

dotnet test

Summary:

So far, we learned how to setup the code first migration using ef core, add test cases and run it against the MySql database.

In the next article, let’s see how to integrate this in Gitlab CI/CD and run the test cases whenever the merge request is raised.

The code for this project can be found in this repository