This is a continuation of an earlier post REST API with ASP.NET Core 7 and SQL Server. In this tutorial I will extend the sample to add integration tests to verify our implementation of SqlServerMoviesStore
.
Why Integration Test
As per definition from Wikipedia integration testing is the phase in which individual software modules are combined and tested as a group.
This is important in our case as we are using an external system to store our data and before we can declare that it is ready to use we need to make sure that it is working as intended. It would also help us verify if our Dapper mapping is correct especially if we are using any custom mapping.
Our options are
- One way would be to run the database server and our api project and invoke the endpoints either from the Swagger UI, curl or Postman with defined data and then verify if our service is storing and retrieving the data correctly.This is tedious to do everytime we make a change, add or remove a property to our domain model, add a new endpoint for new use case.
- Add set of integration tests to our source code and run everytime we make a change, this would ensure that any change we have made has not broken any existing funcationality and scenario. Important thing to remember is this is not set in stone and these should be updated as the funcationality evolves, new functionality would lead to adding new test cases.
Focus of this article would be to implement automated integration tests for SqlServerMoviesStore
we implemented earlier.
Test Project
Let's start by adding a new test project.
- Right click on Solution name -> Add -> New Project -> Tests -> xUnit Test Project
- Select Target Framework, I have selected
.NET 7.0
as we are targeting.NET 7.0
in this sample - Name your test project, I like to name as Project I am testing followed by
.Tests
and followed by test types.Integration
- Click create to finish creating test project.
Setup
Start by adding nuget reference to our project Movies.Api
in Movies.Api.Tests.Integration
project and following nuget packages
dotnet add pacakge AutoFixture.Xunit2 --version 4.18.0
dotnet add pacakge FluentAssertions --version 6.11.0
dotnet add package Dapper.Contrib --version 2.0.78
In order to test funcationality provided by SqlServerMoviesStore
we would need a way to access database without going through our store. This is to ensure that e.g. Create
funcationlity works independent of GetById
of the store. To accomplish that I would add a couple of helper classes under Helper
folder.
DatabaseHelper.cs
using System.Data;
using Dapper;
using Dapper.Contrib.Extensions;
using Microsoft.Data.SqlClient;
namespace Movies.Api.Tests.Integration.Helpers;
public class DatabaseHelper<TId, TRecord>
where TRecord : class
where TId : notnull
{
protected readonly string connectionString;
private readonly string tableName;
private readonly string idColumnName;
protected readonly Func<TRecord, TId> idSelector;
public DatabaseHelper(
string connectionString,
string tableName,
Func<TRecord, TId> idSelector,
string idColumnName = "Id")
{
this.connectionString = connectionString;
this.tableName = tableName;
this.idColumnName = idColumnName;
this.idSelector = idSelector;
}
public Dictionary<TId, TRecord> AddedRecords { get; } = new Dictionary<TId, TRecord>();
public virtual async Task<TRecord> GetRecordAsync(TId id)
{
await using var connection = new SqlConnection(connectionString);
return await connection.QueryFirstOrDefaultAsync<TRecord>(
$"SELECT * FROM {tableName} WHERE {idColumnName} = @Id",
new { Id = id },
commandType: CommandType.Text);
}
public virtual async Task AddRecordAsync(TRecord record)
{
this.AddedRecords.Add(idSelector(record), record);
await using var connection = new SqlConnection(connectionString);
await connection.InsertAsync<TRecord>(record);
}
public async Task AddRecordsAsync(IEnumerable<TRecord> records)
{
foreach (var record in records)
{
await AddRecordAsync(record);
}
}
public void TrackId(TId id) => AddedRecords.Add(id, default!);
public virtual async Task DeleteRecordAsync(TId id)
{
await using var connection = new SqlConnection(connectionString);
await connection.ExecuteAsync(
$"DELETE FROM {tableName} WHERE {idColumnName} = @Id",
new { Id = id },
commandType: CommandType.Text);
}
public async Task CleanTableAsync()
{
foreach (var addedRecord in AddedRecords)
{
await DeleteRecordAsync(addedRecord.Key);
}
}
}
MoviesDatabaseHelper.cs
using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using Movies.Api.Store;
namespace Movies.Api.Tests.Integration.Helpers;
public class MoviesDatabaseHelper : DatabaseHelper<Guid, Movie>
{
public MoviesDatabaseHelper(string connectionString)
: base(connectionString, "Movies", x => x.Id, "Id")
{ }
public async override Task AddRecordAsync(Movie record)
{
this.AddedRecords.Add(idSelector(record), record);
var parameters = new
{
record.Id,
record.Title,
record.Director,
record.ReleaseDate,
record.TicketPrice,
CreatedAt = DateTimeOffset.UtcNow,
UpdatedAt = DateTimeOffset.UtcNow,
};
var query = @"
INSERT INTO Movies(
Id,
Title,
Director,
ReleaseDate,
TicketPrice,
CreatedAt,
UpdatedAt
)
VALUES (
@Id,
@Title,
@Director,
@ReleaseDate,
@TicketPrice,
@CreatedAt,
@UpdatedAt
)";
await using var connection = new SqlConnection(connectionString);
await connection.ExecuteAsync(query, parameters, commandType: CommandType.Text);
}
public async override Task<Movie> GetRecordAsync(Guid id)
{
await using var connection = new SqlConnection(connectionString);
return await connection.QueryFirstOrDefaultAsync<Movie>(
$"SELECT Id, Title, Director, ReleaseDate, TicketPrice, CreatedAt, UpdatedAt FROM Movies WHERE Id = @Id",
new { Id = id },
commandType: CommandType.Text);
}
}
Next add a DatabaseFixture
class to pass shared database context between test classes testing database, in our case it is ConnectionString
that we would initialise in InitializeAsync
method.
namespace Movies.Api.Tests.Integration;
public class DatabaseFixture : IAsyncLifetime
{
public string ConnectionString { get; private set; } = default!;
public async Task InitializeAsync()
{
this.ConnectionString = "Server=localhost;Database=Movies;User ID=sa;Password=Password123;Encrypt=False";
await Task.CompletedTask;
}
public async Task DisposeAsync()
{
await Task.CompletedTask;
}
}
Next add a DatabaseCollection.cs
file, this is so that we can pass the same DatabaseContext
to multiple files. This is here as an example as we only have a single class to test.
namespace Movies.Api.Tests.Integration;
[CollectionDefinition("DatabaseCollection")]
public class DatabaseCollection : ICollectionFixture<DatabaseFixture>
{
// This class has no code, and is never created. Its purpose is simply
// to be the place to apply [CollectionDefinition] and all the
// ICollectionFixture<> interfaces.
}
Let's also delete default UnitTest1.cs
file.
SqlServerMoviesStoreTests
I like to mirror the structure in the source project but please feel free to place your test file where you like. I will add a Store
and under that a SqlServer
folder and then create SqlServerMoviesStoreTests.cs
file.
I will start by adding an instance of moviesDatabaseHelper
and sut
(system under test). In constructor, moviesDatabaseHelper
is initialised using ConnectionString
from DatabaseFixture
then initialised an in memory configuration object and passed that to SqlServerMoviesStore
.
I am calling CleanTableAsync
in DisposeAsync
method that will run after each test to cleanup any data inserted by the test.
SqlServerMoviesStoreTests.cs
using AutoFixture.Xunit2;
using FluentAssertions;
using Microsoft.Extensions.Configuration;
using Movies.Api.Store;
using Movies.Api.Store.SqlServer;
using Movies.Api.Tests.Integration.Helpers;
namespace Movies.Api.Tests.Integration.Store.SqlServer;
[Collection("DatabaseCollection")]
public class SqlServerMoviesStoreTests : IAsyncLifetime
{
private readonly MoviesDatabaseHelper moviesDatabaseHelper;
private readonly SqlServerMoviesStore sut;
public SqlServerMoviesStoreTests(DatabaseFixture databaseFixture)
{
moviesDatabaseHelper = new MoviesDatabaseHelper(databaseFixture.ConnectionString);
var myConfiguration = new Dictionary<string, string?>
{
{"ConnectionStrings:MoviesDb", databaseFixture.ConnectionString},
};
var configuration = new ConfigurationBuilder()
.AddInMemoryCollection(myConfiguration)
.Build();
sut = new SqlServerMoviesStore(configuration);
}
public Task InitializeAsync() => Task.CompletedTask;
public async Task DisposeAsync()
{
await moviesDatabaseHelper.CleanTableAsync();
}
}
GetById Tests
Time to add tests. Please note to run these test first we would need to start our database server and apply the migrations. Lets go ahead and do that before adding our test.
docker-compose -f docker-compose.dev-env.yml up -d
Our first test is very easy, I like to name my tests as MethodName_GivenCondition_ShouldExpectedResult
to follow the pattern, I have added GetById_GivenRecordDoesNotExist_ShouldReturnNull
and I am going to leavarage excellent AutoFixture to get a new Guid as parameter. For this test we don't need arrange part, we would skip to the act and then assert. For Assertion I am going to use FluentAssertions. For this test we need to assert the returned result is null.
[Theory]
[AutoData]
public async void GetById_GivenRecordDoesNotExist_ShouldReturnNull(Guid id)
{
// Arrange
// Act
var result = await sut.GetById(id);
// Assert
result.Should().BeNull();
}
Go ahead and run the test, it should be green.
Let's add our second test GetById_GivenRecordExists_ShouldReturnRecord
, in this test we would first insert a new record using the helper we added earlier. In assertion step we would compare the result with instance passed by AutoFixture
excluding CreatedAt
and UpdatedAt
properties as we know these would be set to current time when inserting. Instead we would test those are set to within 1 second of current time. I have also excluded ReleasedAt
property and will match it within 1 second of passed value, in my opinion this is acceptable as this time being within 1 second of inserted time is accurate enough for this use case, however if more accuracy is needed we would look for an appropriate column type that provides that accuracy. Newly inserted record will be cleared after the test from DisposeAsync
method.
[Theory]
[AutoData]
public async void GetById_GivenRecordExists_ShouldReturnRecord(Movie movie)
{
// Arrange
await moviesDatabaseHelper.AddRecordAsync(movie);
// Act
var result = await sut.GetById(movie.Id);
// Assert
result.Should().NotBeNull();
result.Should().BeEquivalentTo(
movie,
x => x.Excluding(p => p.ReleaseDate).Excluding(p => p.CreatedAt).Excluding(p => p.UpdatedAt));
result.ReleaseDate.Should().BeCloseTo(movie.ReleaseDate, TimeSpan.FromSeconds(1));
result.CreatedAt.Should().BeCloseTo(DateTimeOffset.UtcNow, TimeSpan.FromSeconds(1));
result.UpdatedAt.Should().BeCloseTo(DateTimeOffset.UtcNow, TimeSpan.FromSeconds(1));
}
GetAll Tests
First test is easy, we would just test if there are no records, GetAll
return an empty collection.
[Fact]
public async void GetAll_GivenNoRecords_ShouldReturnEmptyCollection()
{
// Arrange
// Act
var result = await this.sut.GetAll();
// Assert
result.Should().BeEmpty();
}
Next test is to insert some records and executing GetAll
and comparing the results. I have chosen not to assert the DateTime
field values as we are doing that in GetById
but those should be asserted if this method is in a separate class e.g. if we choose to have separate Command and Query classes.
[Theory]
[AutoData]
public async void GetAll_GivenRecordsExist_ShouldReturnCollection(List<Movie> movies)
{
// Arrange
await moviesDatabaseHelper.AddRecordsAsync(movies);
// Act
var result = await this.sut.GetAll();
// Assert
result.Should().BeEquivalentTo(movies, x => x.Excluding(p => p.ReleaseDate).Excluding(p => p.CreatedAt).Excluding(p => p.UpdatedAt));
}
Create Tests
First test for Create
is straight forward, its going to call Create
to create a record and then load that record using moviesDatabaseHelper
and compare it with passed parameter.
[Theory]
[AutoData]
public async void Create_GivenRecordDoesNotExist_ShouldCreateRecord(CreateMovieParams createMovieParams)
{
// Arrange
// Act
await sut.Create(createMovieParams);
moviesDatabaseHelper.TrackId(createMovieParams.Id);
// Assert
var createdMovie = await moviesDatabaseHelper.GetRecordAsync(createMovieParams.Id);
createdMovie.Should().BeEquivalentTo(createMovieParams, x => x.Excluding(p => p.ReleaseDate));
createdMovie.ReleaseDate.Should().BeCloseTo(createMovieParams.ReleaseDate, TimeSpan.FromSeconds(1));
createdMovie.CreatedAt.Should().BeCloseTo(DateTimeOffset.UtcNow, TimeSpan.FromSeconds(1));
createdMovie.UpdatedAt.Should().BeCloseTo(DateTimeOffset.UtcNow, TimeSpan.FromSeconds(1));
}
2nd test is to check if the method thorws an exeption if the id already exists. We will use moviesDatabaseHelper
to add a new record first and then try to create a new record.
[Theory]
[AutoData]
public async void Create_GivenRecordWithIdExists_ShouldThrowDuplicateKeyException(Movie movie)
{
// Arrange
await moviesDatabaseHelper.AddRecordAsync(movie);
var createMovieParams = new CreateMovieParams(movie.Id, movie.Title, movie.Director, movie.TicketPrice, movie.ReleaseDate);
// Act & Assert
await Assert.ThrowsAsync<DuplicateKeyException>(async () => await sut.Create(createMovieParams));
}
Update Tests
To test update, first we will create a record and then call the Update
method of store to update the record. After updating we will use the moviesDatabaseHelper
to load the saved record and verify if the saved record has expected values.
[Theory]
[AutoData]
public async void Update_GivenRecordExists_ShouldUpdateRecord(Movie movie, UpdateMovieParams updateMovieParams)
{
// Arrange
await moviesDatabaseHelper.AddRecordAsync(movie);
// Act
await sut.Update(movie.Id, updateMovieParams);
// Assert
var saved = await moviesDatabaseHelper.GetRecordAsync(movie.Id);
saved.Should().BeEquivalentTo(updateMovieParams, x => x.Excluding(p => p.ReleaseDate));
saved.ReleaseDate.Should().BeCloseTo(updateMovieParams.ReleaseDate, TimeSpan.FromSeconds(1));
saved.UpdatedAt.Should().BeCloseTo(DateTimeOffset.UtcNow, TimeSpan.FromSeconds(1));
}
Delete Tests
To test delete, first we will add a new record using moviesDatabaseHelper
, then call Delete
method on store. To verify we will load the record and then assert the loaded values is null
.
[Theory]
[AutoData]
public async void Delete_GivenRecordExists_ShouldDeleteRecord(Movie movie)
{
// Arrange
await moviesDatabaseHelper.AddRecordAsync(movie);
// Act
await sut.Delete(movie.Id);
// Assert
var loaded = await moviesDatabaseHelper.GetRecordAsync(movie.Id);
loaded.Should().BeNull();
}
This concludes the integration tests. Running these tests does need we start the databaes server prior to running the tests and run the migrations before running the tests. If the database is not running then the tests would not run.
Integration Tests in CI
I am also adding 2 GitHub Actions workflows to run these integration tests as part of the CI.
Workflow with setting up SQL Server using GitHub Service Containers
In this workflow we would make use of the GitHub service containers to start a SQL server. We will build migrations container and run it as part of the build process to apply migrations before running integration tests. Here is the full listing.
name: Integration Test SQL Server (.NET)
on:
push:
branches: [ "main" ]
paths:
- 'integration-test-sqlserver-dotnet/**'
jobs:
build:
runs-on: ubuntu-latest
defaults:
run:
working-directory: integration-test-sqlserver-dotnet
services:
movies.db.test:
image: mcr.microsoft.com/mssql/server:2022-latest
env:
ACCEPT_EULA: Y
MSSQL_SA_PASSWORD: Password123
MSSQL_PID: Express
ports:
- 1433:1433
steps:
- uses: actions/checkout@v3
- name: Setup .NET Core SDK
uses: actions/setup-dotnet@v3
with:
dotnet-version: 7.0.x
- name: Install dependencies
run: dotnet restore
- name: Build
run: dotnet build --configuration Release --no-restore
- name: Build migratinos Docker image
run: docker build --file ./db/Dockerfile -t movies.db.migrations ./db
- name: Run migrations
run: docker run --add-host=host.docker.internal:host-gateway movies.db.migrations "Server=host.docker.internal;Database=Movies;User ID=sa;Password=Password123;Encrypt=False"
- name: Run integration tests
run: dotnet test --configuration Release --no-restore --no-build --verbosity normal
Workflow with setting up SQL Server using docker-compose
In this workflow we will use the docker-compose.dev-env.yml
to start SQL Server and apply migrations as a first step of the workflow after checking out the code. Here is the full listing.
name: Integration Test SQL Server (.NET) with docker-compose
on:
push:
branches: [ "main" ]
paths:
- 'integration-test-sqlserver-dotnet/**'
jobs:
build:
runs-on: ubuntu-latest
defaults:
run:
working-directory: integration-test-sqlserver-dotnet
steps:
- uses: actions/checkout@v3
- name: Start container and apply migrations
run: docker compose -f "docker-compose.dev-env.yml" up -d --build
- name: Setup .NET Core SDK
uses: actions/setup-dotnet@v3
with:
dotnet-version: 7.0.x
- name: Install dependencies
run: dotnet restore
- name: Build
run: dotnet build --configuration Release --no-restore
- name: Run integration tests
run: dotnet test --configuration Release --no-restore --no-build --verbosity normal
- name: Stop containers
run: docker compose -f "docker-compose.dev-env.yml" down --remove-orphans --rmi all --volumes
Source
Source code for the demo application is hosted on GitHub in blog-code-samples repository.
Source for Integration Test SQL Server (.NET)
workflow is in integration-test-sqlserver-dotnet.yml.
Source for Integration Test SQL Server (.NET) with docker-compose
workflow is in integration-test-sqlserver-dotnet-docker-compose.yml.
References
In no particular order
Top comments (0)