I'm creating tests for our .Net 8 API. We use MySql for data storage and CQRS pattern for queries.
In my personal opinion, I shouldn't use the real database for testing, because you will use an existing set of data (from which to do the assertions) which might have changed at any moment, and so the tests would fail in the future, but at the same time I cannot (shouldn't) insert fake data (which would be permanent) into the real database.
My problem is I don't know how to mock MySql database within our CQRS architecture, or how to use in-memory generated data, so I'm stuck.
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 that.
I could just mock "QueryAsync" method (the result of it) with a sample set of data, and it works, but not sure if this is the correct way to go, I mean, I'll be generating a sample set of data as a result for the method, and then I'll be comparing this set with itself, so the tests won't ever fail (I guess).
In resume, which would be the way to go to test a controller get method in a cqrs architecture that uses mysql as database?
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:
public interface ICloudStackCoreConnectionFactory
{
DbConnection CreateConnection(Region region);
Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(IRegionRepository regionRepository, string fetchDataQuery, string? countDataQuery = null, Dictionary<string, string>? columnModelModel = null, Paging? paging = null, ColumnName[]? order = null, string[]? excludedOrder = null, Func<Region, TDbItem, TOut>? transform = null, object? queryParam = null, Func<Region, bool>? filter = null, bool skipDbPaging = false, CancellationToken ct = default(CancellationToken)) 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, Dictionary<string, string>? columnModelModel = null, Paging? paging = null, ColumnName[]? order = null, string[]? excludedOrder = null, Func<Region, TDbItem, TOut>? transform = null, object? param = null, Func<Region, bool>? filter = null, bool skipDbPaging = false, CancellationToken ct = default(CancellationToken)) 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):
public sealed class SearchQuery : VdcQuery<SearchResponse>
{
public SearchQuery(IdentityInfo identity, string? correlationId) :
base(identity, correlationId)
{
}
}
internal sealed class SearchQueryHandler : IQueryHandlerValidator<SearchQuery>, 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(),
sql.count.ToString(),
paging: request.Paging,
order: request.Order,
filter: regionFilter.filters,
transform: (regionId, item) =>
{
item.RegionId = regionId;
return item;
},
queryParam: regionFilter.param,
ct: ct
);
return new SearchResponse(count, data);
}
public ValueTask<IResponse> ValidatorAsync(SearchQuery request, RequestValidator<SearchQuery> validator, CancellationToken ct = default)
{
...
}
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, because this is the part I'm not sure it's correct according what it is expected from a database test, I mean:
I think I'm mocking a method result, and not the database as it should be. I will always get the expected results, because in fact I'm creating the expected results.
Lab we use to initialize tests:
public sealed class Lab
{
public async Task<EventSend?> InitAsync(IServiceProvider provider, bool useEvents = false)
{
...
}
/// <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),
CreateAccount("Account4", AccountTypes.Internal),
CreateAccount("Account5", AccountTypes.Hybrid),
];
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,
string? countDataQuery = null,
Dictionary<string, string>? columnModelModel = null,
Paging? paging = null,
ColumnName[]? order = null,
string[]? excludedOrder = null,
Func<Vdc.Libs.Region, TDbItem, TOut>? transform = null,
object? param = null,
Func<Vdc.Libs.Region, bool>? filter = null,
bool skipDbPaging = false,
CancellationToken ct = default(CancellationToken)) where TOut : class
{
return (_accounts.accounts.Length, _accounts.accounts as TOut[]);
}
public Task<int> ExecuteAsync(IRegionRepository regionRepository, string sql, object? @params = null, Func<Vdc.Libs.Region, bool>? filter = null, CancellationToken ct = default)
{
throw new NotImplementedException();
}
}
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(nameof(SearchResponse.Account.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:
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:
[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);
}
}
Do you think my approach for unit testing this query handler is correct? If not, how should I do it?
Edit 1: About what I expect to test.
In other APIs we created, what we test is that the response is the expected one, I mean, given a sample (fake) set of data (created by us in the "Lab" class) we test that the query handler (in the end, the "QuerySearchAsync") method returns what is expected to return.
In the case of this API (compared to our other APIs where tests are already working great with SQL Server), this API uses MySql, and I don't know how to mock MySql to accomplish that.
I'd need a MySql mocked set of data (that I would create in the "Lab" class) that can be used with the query handler.
Maybe you could add in your answers something about that, or at least a link that would be advisable for me to check.
