This is a rewrite of my own
.Net 8 XUnit: Should my tests use real data or how to mock MySql with CQRS?
in a much more clear way:
The reason for rewriting my previous question is because in the previous one I didn't explained my problem correctly (and clearly) and one user suggested me that instead of editing and modifying a question which already has answers, I should create a new question, so here it is:
I'm creating integration tests for our .Net 8 CQRS API. We use MySql for data storage and CQRS pattern for queries.
We have a "Lab" class we use to initialize tests where I should create a reduced set of sample data to be used then in the cqrs query handler, and this is my problem, not being able / knowing how to mock MySql data in our "Lab" class context so the "QueryAsync" method in the query handler will return that data, instead of working with the real database.
In our query handlers we inject an interface with a "QueryAsync" method which implementation creates a connection to MySql for the queries using just MySqlConnection, but don't know how to test the data part in the integration test.
I was able to mock that interface (code shown below) to return a set of data I create myself in the lab, and with that I can assert the data is traveling through the layers and arrives to destiny, but I'm not testing the data returned is actually the data expected this way, because I haven't faked the database, and this is something I also should do.
In resume, I'm doing an integration test of a get controller endpoint and would need to have a mocked MySql database where the data will be picked of in the query handler (instead of using the real database), and I don't know how to do it.
I'll paste some relevant pieces of code in case it helps.
Program.cs:
await RunServer.RunAsync<Startup>(
args,
(builder, options) =>
{
...
options.OtherServices = () =>
{
builder.Services.AddScoped<ICloudStackDbUnitOfWork, CloudStackDbUnitOfWork>();
builder.Services.AddScoped<ICloudStackCoreConnectionFactory, MySqlCloudStackCoreConnectionFactory>();
...
};
...
},
);
ICloudStackCoreConnectionFactory (connection to MySql):
public interface ICloudStackCoreConnectionFactory
{
DbConnection CreateConnection(Region region);
Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(IRegionRepository regionRepository, string fetchDataQuery, string? countDataQuery = null, ... more parameters) where TOut : class;
Task<int> ExecuteAsync(IRegionRepository regionRepository, string sql, object? @params = null, Func<Region, bool>? filter = null, CancellationToken ct = default(CancellationToken));
}
MySqlCloudStackCoreConnectionFactory (just the skeleton):
public sealed class MySqlCloudStackCoreConnectionFactory : ICloudStackCoreConnectionFactory
{
public MySqlCloudStackCoreConnectionFactory()
{
}
public DbConnection CreateConnection(Region region)
{
return new MySqlConnection(GetConnString(region));
}
Public Async Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(IRegionRepository regionRepository, string fetchDataQuery, string? countDataQuery = null, ... more parameters) where TOut : class;
{
...
DbConnection connection = CreateConnection(region);
...
}
public async Task<int> ExecuteAsync(IRegionRepository regionRepository, string sql, object? @params = null, Func<Region, bool>? filter = null, CancellationToken ct = default(CancellationToken))
{
...
DbConnection connection = CreateConnection(region);
...
}
}
QueryHandler (CQRS): (retrieving data from MySql)
internal sealed class SearchQueryHandler : IVdcQueryHandler<SearchQuery, SearchResponse>
{
private readonly IRegionRepository _regionRepository;
private readonly ICloudStackCoreConnectionFactory _cloudStackCoreConnectionFactory;
public SearchQueryHandler(
ICloudStackCoreConnectionFactory cloudStackCoreConnectionFactory,
IRegionRepository regionRepository)
{
_cloudStackCoreConnectionFactory = cloudStackCoreConnectionFactory;
_regionRepository = regionRepository;
}
public async ValueTask<SearchResponse> HandleAsync(SearchQuery request, CancellationToken ct = default)
{
var sql = GetSearchQuerySql();
var regionFilter = GetFilters(request.Filter, sql);
var (count, data) = await _cloudStackCoreConnectionFactory.QuerySearchAsync<SearchResponse.Account, SearchResponse.Account>(
_regionRepository,
sql.fetch.ToString(),
... all the rest of parameters
);
return new SearchResponse(count, data);
}
private (StringBuilder fetch, StringBuilder count) GetSearchQuerySql()
{
var fetch = new StringBuilder($"""
SELECT
UUID AS {nameof(SearchResponse.Account.Id)},
account_name AS {nameof(SearchResponse.Account.Name)}
FROM cloud.account acc
WHERE acc.state != 'disabled' AND removed IS NULL
""");
return (fetch, ...);
}
}
Unit Testing:
In this next lab pay attention on how I do mock the results of QuerySearchAsync through the implementation MySqlMock and IAccountList:
What I do that way is to test the flow of data from beginning to end, and not the actual data.
I should also mock the database and test the returned data is the expected one.
Lab we use to initialize tests:
public sealed class Lab
{
/// <summary>
/// Create a lab with the requirement of this project
/// </summary>
public static Lab Create(ITestOutputHelper output)
{
var appFactory = Vdc.Libs.AspNet.Testing.Setup.Factory<Startup, InnerDbContextRead, InnerDbContextWrite>(
out var client,
out var authorize,
out var identity,
out var faker,
role: $"{VdcSecurity.Role.Management},{VdcSecurity.Role.ManagementAdmin}",
output: output,
setup: services =>
{
services.AddSingleton<IAccountList, AccountList>();
services.AddScoped<ICloudStackCoreConnectionFactory, MySqlMock>();
}
);
return new Lab(identity, appFactory, client, authorize, faker);
}
public class AccountList : IAccountList
{
SearchResponse.Account[] IAccountList.accounts => [
CreateAccount("Account1", AccountTypes.Managed),
CreateAccount("Account2", AccountTypes.Managed),
CreateAccount("Account3", AccountTypes.Unmanaged)
];
private SearchResponse.Account CreateAccount(string accountName, AccountTypes typeId)
{
SearchResponse.Account account;
account = new SearchResponse.Account
{
Id = Guid.NewGuid(),
Name = accountName,
AccountOrder = 0,
RegionId = 1
};
return account;
}
}
}
MySqlMock:
public interface IAccountList
{
SearchResponse.Account[] accounts { get; }
}
public class MySqlMock : ICloudStackCoreConnectionFactory
{
IAccountList _accounts;
public MySqlMock(IAccountList accounts)
{
_accounts = accounts;
}
public DbConnection CreateConnection(Vdc.Libs.Region region)
{
return new MySqlConnection();
}
public async Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(
IRegionRepository regionRepository,
string fetchDataQuery,
... all the parameters) where TOut : class
{
return (_accounts.accounts.Length, _accounts.accounts as TOut[]);
}
}
My Test:
[Theory]
[InlineData(0, 5)]
public async Task GetFirst5Element_OrderByName_ReturnOnly5ElementOrderByName(int page, int expectedElements)
{
using var scope = _lab.AppFactory.Services.CreateScope();
var provider = scope.ServiceProvider;
// Arrange
var events = await _lab.InitAsync(provider, false);
// Act
var request = new SearchRequest(
null,
new Paging { Page = page, PageSize = expectedElements },
[new Vdc.Libs.Web.ColumnNameRequest("Name")) { Asc = true }]
);
var response = await _client.SearchAsync(_lab.Identity, request);
// Assert
//TODO: still need to check the results with the sample data I've created
response.Results.GetType().Should().Be(typeof(List<SearchResponse.Account>));
response.Results.Count().Should().Be(expectedElements);
}
IApiService (used to call the controller when testing):
public interface IAccountsApiService : IApiService
{
Task<SearchResponse> SearchAsync(IdentityInfo identity, SearchRequest request, string? correlationId = null, CancellationToken ct = default);
}
public sealed class AccountsApiService : BaseApiService, IAccountsApiService
{
private readonly AuthorizeOptions _options;
public AccountsApiService(IOptions<AuthorizeOptions> options, IApiClient apiClient, ILogger<AccountsApiService> logger)
: base(apiClient, logger: logger)
{
_options = options.Value;
}
public async Task<SearchResponse> SearchAsync(IdentityInfo identity, SearchRequest request, string? correlationId = null, CancellationToken ct = default)
{
var (response, _) = await ApiClient.SendAsync<SearchResponse>(
HttpMethod.Get,
"/api/accounts",
message => message.AddAuthorization(identity, _options.ApiKey, correlationId),
model: request,
ct: ct
);
return response!;
}
}
Controller (the actual controller I'm testing):
[ApiController]
[Route("api/[controller]")]
[Produces("application/json")]
[Authorize(Roles = ...]
public class AccountsController : ControllerBase
{
private readonly IServiceProvider _provider;
public AccountsController(IServiceProvider provider)
{
_provider = provider;
}
[HttpGet]
public async Task<ActionResult<SearchResponse>> Get(
[FromServices][IsSensitive] IQueryDispatcher dispatcher,
[FromQuery] SearchRequest request,
[IsSensitive] CancellationToken ct = default
)
{
var identity = HttpContext.GetIdentity();
var query = new SearchQuery(identity, HttpContext.TraceIdentifier)
{
Filter = request.Filter,
Order = request.Order?.ToColumnName() ?? Constants.Page.Sorting,
Paging = request.Paging ?? Constants.Page.Paging,
};
var result = await dispatcher.DispatchAsync(_provider, query, ct);
return result.ToActionResult(this);
}
}
Any help / documentation urls / code to mock MySql database so I can test that the results (when testing) are the expected results will be welcome.
Edit 1: How we do it with MSSQL (by request of @Ewan to understand what I'm trying to replicate with MySql).
Lab.cs:
private readonly Job[] _data;
private Lab(IdentityInfo identity, WebApplicationFactory<Startup> appFactory, IApiClient apiClient, AuthorizeOptions authorize, Faker faker)
{
_faker = faker;
_identity = identity;
_appFactory = appFactory;
_apiClient = apiClient;
_authorize = authorize;
_data = [
GetJob(JobInfo.UsageAllocationName, UsageAllocationJobInEuropeWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 1, 1, Region.Europe, null)),
GetJob(JobInfo.UsageAllocationName, UsageAllocationJobInAsiaWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 1, 1, Region.Asia, null)),
GetJob(JobInfo.UsageAllocationName, UsageAllocationJobInNAWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 1, 1, Region.NA, null)),
GetJob(JobInfo.UsageDedicatedName, UsageDedicatedJobInEuropeWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 2, 1, Region.Europe, null)),
GetJob(JobInfo.BillingProcessName, BillingJobInEuropeWithPendingStatusIndex + 1, StatusValues.Pending, new JobInfo.Args(2024, 1, 1, Region.Europe, null)),
GetJob(JobInfo.ObjectStorageAllocationName, ObjectStorageAllocationJobInEuropeWithCancelledStatusIndex + 1, StatusValues.Cancelled, new JobInfo.Args(2024, 1, 1, Region.Europe, null)),
GetJob(JobInfo.BillingProcessName, BillingJobInEuropeWithAssignedStatusIndex + 1, StatusValues.Assigned, new JobInfo.Args(2024, 2, 1, Region.Europe, null)),
];
}
public async Task<EventSend?> InitAsync(IServiceProvider provider, bool useEvents = false, FilterObject? filter = null)
{
var unitOfWork = provider.GetRequiredService<IVdcUnitOfWork>();
PopulateJobs(provider, _data, filter); //=> set of sample data for testing.
await unitOfWork.SaveChangesAsync();
EventSend? events = null;
if (useEvents)
Events.Capture(provider, events = new EventSend());
return events;
// ==============================================================================================================
static void PopulateJobs(IServiceProvider provider, Job[] dataSample, FilterObject? filter)
{
var jobRepository = provider.GetRequiredService<IVdcRepository<Job>>();
IEnumerable<Job> data = dataSample;
if (filter is not null)
{
if (filter.Status is not null)
data = data.Where(x => filter.Status(x.StatusId));
}
jobRepository.AddRange(data.ToArray());
}
}
private Job GetJob(string jobName, int id, StatusValues status, JobInfo.Args args)
{
Job job;
var serializer = AppFactory.Services.GetRequiredService<IJsonSerializer>();
job = new Job
{
Id = id,
Name = jobName,
Assigned = null,
Description = $"{jobName} description",
Notes = _faker.Random.String(20),
Progress = 0,
StatusId = status,
TimeOut = JobInfo.GetTimeOut(jobName),
TriggerId = TriggerValues.Scheduled,
WorkerId = _faker.Random.String2(6),
Created = _faker.Date.Past()
};
job.Params = JobInfo.GetArgs(serializer, jobName, args);
job.JobLogs = new HashSet<JobLog>();
job.Started = null;
job.Completed = null;
...
}
IVdcUnitOfWork:
public interface IVdcUnitOfWork : IUnitOfWork, IDisposable, IDbContextWrapper
{
}
IUnitOfWork:
public interface IUnitOfWork : IDisposable
{
int SaveChanges();
Task<int> SaveChangesAsync(CancellationToken ct = default(CancellationToken));
IEnumerable<Type> GetModelEntityTypes();
}
IDbContextWrapper:
public interface IDbContextWrapper
{
DbContext GetDbContext();
}
Jobs query handler (minified):
private readonly IVdcQueryRepository<Job> _jobQueryRepository;
public async ValueTask<SearchJobsResponse> HandleAsync(SearchQuery request, CancellationToken ct = default)
{
IQueryable<Job> query = _jobQueryRepository
.FindAll()
.Include(i => i.Status)
.Include(i => i.Trigger);
...
var (count, data) = await query.ToSearchAsync(request.Paging, request.Order, ct);
return new SearchJobsResponse(count, _mapper.Map<JobResponse[]>(data)!);
}
IJobRepository:
public interface IJobRepository
{
Task<int> ExpireJobsAsync(CancellationToken ct = default);
Task<int> FailAsync(int jobId, CancellationToken ct = default);
Task<Job> CreateAsync(Job job, CancellationToken ct = default);
Task<int> CompleteAsync(int jobId, string note, CancellationToken ct = default);
Task<int> UpdateJobAsync(int jobId, int progress, string notes, CancellationToken ct = default);
Task<int> AssignAsync(int id, CancellationToken ct= default);
}
public sealed class JobRepository : IJobRepository
{
private readonly ISysClock _clock;
private readonly IVdcUnitOfWork _unitOfWork;
private readonly IVdcRepository<Job> _jobRepository;
public JobRepository(ISysClock clock, IVdcUnitOfWork unitOfWork, IVdcRepository<Job> jobRepository)
{
_clock = clock;
_unitOfWork = unitOfWork;
_jobRepository = jobRepository;
}
public Task<int> FailAsync(int jobId, CancellationToken ct = default)
{
var jobIdParam = new SqlParameter("@id", jobId);
return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync(
"exec FailJob @id",
[jobIdParam],
ct
);
}
public Task<int> CompleteAsync(int jobId, string notes, CancellationToken ct = default)
{
var notesParam = new SqlParameter("@notes", notes);
var jobIdParam = new SqlParameter("@id", jobId);
return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync(
"exec CompleteJob @id, @notes",
[jobIdParam, notesParam],
ct
);
}
public Task<Job> CreateAsync(Job job, CancellationToken ct = default) => Task.Run(() =>
{
var query = _unitOfWork.GetDbContext().Set<Job>().FromSql(
$"""
[RequestJob]
@Name={job.Name},
@TriggerId={job.TriggerId:D},
@Description={job.Description},
@TimeOut={job.TimeOut},
@Params={job.Params},
@Notes={job.Notes}
"""
);
return query.ToArray()[0];
});
public Task<int> UpdateJobAsync(int jobId, int progress, string notes, CancellationToken ct = default)
{
var notesParam = new SqlParameter("@notes", notes);
var progressParam = new SqlParameter("@progress", progress);
var jobIdParam = new SqlParameter("@id", jobId);
return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync(
"exec UpdateJob @id, @progress, @notes",
[jobIdParam, progressParam, notesParam],
ct
);
}
public async Task<int> ExpireJobsAsync(CancellationToken ct = default)
{
var utcNow = _clock.UtcNow;
var jobs = await _jobRepository.FindAll()
.Include(i => i.JobLogs)
.Where(p =>
(p.StatusId == StatusValues.Assigned && EF.Functions.DateDiffSecond(p.Assigned, utcNow) >= p.TimeOut) ||
(p.StatusId == StatusValues.InProgress && EF.Functions.DateDiffSecond(p.Started, utcNow) >= p.TimeOut)
)
.ToArrayAsync(ct);
foreach (var job in jobs)
{
job.Completed = utcNow;
job.StatusId = StatusValues.Expired;
job.JobLogs!.Add(new JobLog { Created = utcNow, Note = "Time out" });
}
return await _unitOfWork.SaveChangesAsync(ct);
}
public Task<int> AssignAsync(int jobId, CancellationToken ct = default)
{
var jobIdParam = new SqlParameter("@id", jobId);
return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync("exec AssignJob @id", jobIdParam);
}
}
IVdcRepository:
public interface IVdcRepository<TEntity> : IEFRepository<DbContextWrite, TEntity>, IRepository<TEntity>, IQueryRepository<TEntity>, IVdcQueryRepository<TEntity> where TEntity : class
{
}
DbContextWrite:
public abstract class DbContextWrite : DbContext
{
protected DbContextWrite([NotNull] DbContextOptions options)
: base(options)
{
}
}
Job test:
[Theory]
[InlineData(0, 5)]
[InlineData(1, 2)]
public async Task GetFirst5Element_OrderById_ReturnOnly5ElementOrderById(int page, int expectedElements)
{
using var scope = _lab.AppFactory.Services.CreateScope();
var provider = scope.ServiceProvider;
// Arrange
var events = await _lab.InitAsync(provider, true);
var statusRepository = provider.GetRequiredService<IVdcQueryRepository<Status>>();
// Act
var request = new SearchJobsRequest(
null,
new Paging { Page = page, PageSize = 5 },
[new Vdc.Libs.Web.ColumnNameRequest(nameof(JobResponse.Id)) { Asc = true }]
);
var response = await _client.SearchAsync(_lab.Identity, request);
// Assert
response.Count.Should().Be(_lab.Jobs.Length);
response.Results.Count().Should().Be(expectedElements);
}
IQueryRepository:
public interface IQueryRepository<TEntity> where TEntity : class
{
IQueryable<TEntity> FindAll();
ValueTask<TEntity?> FindAsync(object[] keyValues, CancellationToken ct = default(CancellationToken));
}
DesignTimeDbContextFactory:
public sealed class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<InnerDbContextWrite>
{
public const string ConnString = "JobManagerDb";
public InnerDbContextWrite CreateDbContext(string[] args)
{
var builder = new ConfigurationBuilder()
.AddJsonFile(Path.Combine(Directory.GetCurrentDirectory(), "appsettings.Development.json"))
.Build();
var connString = builder.GetConnectionString(ConnString);
var builderOptions = new DbContextOptionsBuilder<InnerDbContextWrite>();
SqlServerInitHelper.SQLWriteBuider<InnerDbContextWrite>(connString!, builderOptions);
return new InnerDbContextWrite(builderOptions.Options);
}
}
InnerDbContextWrite:
public sealed class InnerDbContextWrite : DbContextWrite
{
public InnerDbContextWrite([NotNull] DbContextOptions<InnerDbContextWrite> options) :
base(options)
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
this.OnModelCreating(typeof(EntityTypeConfiguration<>), builder, _ => true);
}
}
