SQL Server Database Integration Testing Using NUnit

On a current project I have a set of Azure Functions that process files and write various pieces of audit information to an Azure SQL Database to be used for monitoring and further processing. The C# application implements the repository pattern to encapsulate the data access logic and uses Dapper (https://github.com/StackExchange/Dapper) as a micro ORM to simplify the code and execute the stored procedures that perform the CRUD operations against the database.

I wanted a way to automate the integration testing of my repositories and stored procedures so I developed the solution described below using NUnit as the test framework and SQL Server LocalDB as the database to run my tests against.

I had the following requirements for my solution which NUnit has been able to satisfy:

  • Quick – tests should run quickly and not require massive amounts of set up / tear down
  • Independent – all tests should be independent from one another and responsible for their own set up / tear down
  • Simple – the test code should be simple to understand and easy to work with when writing new tests.
  • Work Everywhere – the tests should be able to work anywhere and not require huge dependencies like a full SQL Server instance, they should be able to work with SQL LocalDB

The full solution can be found on my GitHub at https://github.com/benjarvis18/NUnitSQLIntegrationTesting.

Solution Layout

The solution has the following projects:

  • NUnitSQLIntegrationTesting.Database – SSDT database project that includes tables, stored procedures and reference data.
  • NUnitSQLIntegrationTesting.Core – class library that includes the repositories that are used by the C# code to perform data access.
  • NUnitSQLIntegrationTesting.IntegrationTests – NUnit test project

Repository

The test database has two tables: dbo.Customer and dbo.CustomerType. CustomerType is a lookup table so the data is static and maintained within our SSDT project, Customer is the table that we want to perform read and write operations against from within our C# application so the NUnitSQLIntegrationTesting.Core project has a CustomerRepository class within it to call our stored procedures and map any returned objects to our domain model.

The code in our repository class is included below:

As above, the repository has the following methods:

  • CreateCustomerAsync – inserts a new row into dbo.Customer using the supplied Customer object
  • GetCustomerAsync – returns a customer object for the supplied CustomerId
  • SetCustomerEmailAsync – updates the EmailAddress column in dbo.Customer for the supplied CustomerId
  • GetCustomersByTypeAsync – returns a collection of customer objects that have the specified CustomerTypeId

The code in the repository is pretty simple with the only complication being some additional code required to map the nested CustomerType object when retrieving a Customer object.

One key element of our repository implementation is that all SQL connections are created using the ISqlServerConnectionFactory implementation that is injected into our repository. In our test scripts we are able to mock this interface so it instead provides a connection to our test database.

Test Infrastructure

The following elements can be found in the NUnitSQLIntegrationTesting.IntegrationTests project.

Helpers/TestDatabase.cs

TestDatabase represents an instance of our test database and provides methods for creating and dropping the database.

The initialise method (below) makes use of the APIs provided in the Microsoft.SqlServer.DacFx package to take the dacpac for the database that sits within our solution and deploy it to our SQL LocalDB instance.

We then have some other methods that are explained below:

  • Drop – drops the database
  • RunSetUpScript – executes the provided SQL against the test database (used for setting up data to be used within a test).
  • RunCleanUpScript – executes a script to delete test data from each table to clean up the database in preparation for running the next test. Currently this is hardcoded but the DatabaseDeleter script from Jimmy Bogard (https://gist.github.com/jbogard/5805783) could easily be adapted to work here.
  • GetSqlConnection – creates a new SQL connection for use in our test scripts.

Data/DatabaseTestConfig.cs

This class is implemented as an NUnit SetUpFixture with a SetUp method and a TearDown method, each being decorated with the NUnit OneTimeSetUp and OneTimeTearDown attributes respectively. This means that the SetUp method is called once at the beginning of a test run and the TearDown method is called once at the end of a test run.

This class provides a singleton instance of our TestDatabase meaning each test is connecting to the same database which prevents us having to deploy a new instance of the test database for each test. If we did deploy an instance of our test database for each test the run time of our tests would increase significantly.

Helpers/Assert.cs

This class contains our SQL specific assertions and is responsible for comparing our actual and expected results.

An example of one of the assertions is below, this method takes an expected result as a dynamic object and retrieves the actual result by querying the SQL database.

The method uses a library called Dynamitey (https://github.com/ekonbenefits/dynamitey) to retrieve the list of properties from the objects and then iterates through each property comparing the actual value with the expected value.

Data/DatabaseTestBase.cs

This class is the base class that all of our integration tests inherit from.

This class again provides SetUp and TearDown methods that are decorated with NUnit SetUp and TearDown attributes however, the difference this time is that these methods will be called at the beginning and end of each test.

One of my requirements was that each test should be able to run independently of the others with no dependencies between tests, to allow this I need to run each test in serial so only a single test is using my database at any one time. To implement this I have used a SemaphoreSlim (see https://docs.microsoft.com/en-us/dotnet/standard/threading/semaphore-and-semaphoreslim) with a maximum request count of 1. The SetUp method calls the Wait method on the semaphore which blocks the thread if another thread is already executing and only allow the thread to continue once the TearDown method has been called by the other thread to release the semaphore. Each test class shares the same instance of the semaphore meaning our tests run in serial; this negates the need for using ordered tests or another method that adds further complication. The TearDown method also executes any clean up scripts against our test database to ensure no test data is left behind.

This class also provides a method called GetFixture which makes use of a library called AutoFixture and it’s integration with Moq (see this article from The TrainLine https://engineering.thetrainline.com/an-introduction-to-xunit-moq-and-autofixture-995315f656f for more information on how this is useful) to provide a fixture that can be used to get an instance of our repository in our tests.

Tests

Now we’ve run through the infrastructure required by our tests we can look at the actual tests themselves.

The CustomerRepositoryTests class found in the Data folder of our NUnitIntegrationTesting.IntegrationTests project contains the tests for our CustomerRepository.

I won’t look at each test in this post as they all follow the same format however, the SetCustomerEmailAddress_WhenGivenAValidCustomerIdAndEmail_DatabaseRowIsUpdated test provides a good example as it includes some set up.

As above, we first call GetFixture to get our AutoFixture Fixture that we then use to create a new instance of our CustomerRepository, injecting our mocked instance of the ISqlServerConnectionFactory along the way.

We then execute the RunSetUpScript method to generate some test data in our database, I’m adding two rows in this case, one that I want to be updated and another that I don’t so I can confirm that my stored procedure is only updating the row I want it to. Once the database is set up we can call our SetCustomerEmailAsync method on our repository to set the email for customer id 1 to test.new.email@testing.com.

At this point the database row should be updated so we now call Helpers.Assert.SqlResultMatches to verify that the customer id for the customer with the email address test.new.email@testing.com is 1.

Our stored procedure is included below so the code should do what we expect and set the email address to test.new.email@testing.com for customer id 1.

As below, when we run our tests in visual studio they all pass:

image

Now, if we change our stored procedure slightly to update the email address where the customer id is the one passed in as a parameter + 1, and run our test again we get a different result:

image

If we look at the details we can see the expected customer id was 1 but what was actually returned is 2:

image

Now we can easily see if a change we’ve made to our stored procedures or our repository code has caused any regression in functionality.

Conclusion

As seen above it is extremely easy to get NUnit to provide a simple way to integration test C# repositories and SQL code. The solution now gives us peace of mind when making changes to the repository code or SQL stored procedures and means that any bugs can be picked up at the development stage rather than further along the SDLC or after the code is deployed to production.

In this example I am testing C# repositories that are calling the database however, it would be extremely easy to apply this to a typical BI scenario where you need to test stored procedures on their own.

As always, if there are any questions please comment below or contact me on Twitter via @BenJarvisBI.