.NET Aspire: using SqlConnection in integration tests

This is the fourth blog in a series showing how to use .NET Aspire's dev-time orchestration to write integration tests for code that uses SQL Server.

So far, we've seen how to set up Aspire's dev-time orchestration, how to execute that orchestration from inside an integration test, and how to ensure that the database is correctly initialized. In this post, I'll show how we can use the SQL database directly from our test code.

Loading test data into the SQL database before a test

Most tests need to perform some sort of setup before executing the code under test. We sometimes talk about the arrange, act, assert sequence: a test arranges some initial conditions, performs actions that will invoke the code we wish to test, and then asserts that the results are as expected. I already showed how to initialize the database in the previous article, but an integration test might need to load some additional test-specific data. It might also need to check that the code changed the database in some particular way.

Either way, our test code is going to need to connect to the database. So far, only the web API and database initialization projects have connected to the database. Those projects were both launched for us by Aspire, so they can use the normal Aspire mechanisms for using the SQL database. But in a test, we need to do things slightly differently.

First, we'll need to wait for the database to be ready for use.

A small optimization for test-specific db initialization

In the preceding post in this series I showed how to make the test wait for the web API project to start, and this implicitly means it will also wait for the database initialization project to run. However, if we do need to load some additional data into the database just for the test, we can speed things up a little. The straightforward approach I showed last time looks like this:

Notice that we are unnecessarily waiting for the web API to be ready before we even start loading test-specific data into the database. The database was ready before the web API was even launched, so there's no reason our test can't be getting on with its data loading at the same time as the web API is initializing itself:

With this tweak, our test code gets started adding test data concurrently with the web API initializing itself. (The way that Mermaid happens to render this has made it look like the web API is now taking longer to initialize, but that's just an artifact of how it has laid out the diagram.) This may enable us to get to the point of running the actual code under test sooner. This won't save much time, probably less than a second unless you're loading a lot of test-specific data, but if that's repeated many times over a large test suite it can make a difference.

So we can use the ResourceNotificationService like we did earlier, but this time telling Aspire that we only want to wait for the local DB initialization to complete:

await resourceNotificationService
    .WaitForResourceAsync(
        "localdevinitializedb",
        KnownResourceStates.Finished)
    .WaitAsync(TimeSpan.FromSeconds(30));

(Of course, we'll still want to wait for the web API before actually performing the tests. This is just about enabling our test to proceed with its data setup phase a little sooner.)

Accessing the SQL database from a test

So how do we obtain a connection to the database in our tests? Normally with Aspire, you get a database connection by calling the AddSqlServerClient extension method on your application's IHostApplicationBuilder. That makes a SqlConnection available through dependency injection. However, that won't work in this context.

Aspire's dev-time orchestration makes resources such as databases available by setting environment variables when it launches the projects that require those resources. When we call AddSqlServerClient in our web API project, it looks for those environment variables to discover the connection string it should use. When we launch an Aspire application with the DistributedApplicationTestingBuilder in an integration test, the services we launch continue to work in the same way: Aspire launches whatever services we asked it to launch, and sets their environment variables just as it would when we debug the application.

But that doesn't help our test. Aspire didn't launch our test. Our test launched Aspire, so Aspire didn't get to supply environment variables for our test project. Normally the app host doesn't use any of the resources itself, it just ensures that the right resources are available to each of our application components. Integration tests don't quite fit this pattern because they are the app host, but they may also want to use some of the resources.

Our test code needs to obtain the SQL database connection information through other means. The way we do this is through the application host's Resources property. (This is a property that the IDistributedApplicationTestingBuilder interface adds that is not present on the base IDistributedApplicationBuilder that the normal app host code uses.) We can use this to find the SQL database resource:

var db = appHost.Resources
    .OfType<SqlServerDatabaseResource>()
    .Single(r => r.Name == "db");

This returns a SqlServerDatabaseResource that can supply us with the connection string:

string? sqlConnection = await db.ConnectionStringExpression.GetValueAsync(
    CancellationToken.None);

You can pass that into a SqlConnection, or to EF Core, or whatever library you are using to work with the database. And now your test can pre-load test specific data and/or check that the code under test made any expected changes to the database.

Next time, I'll discuss some options that let you reduce startup time, and, should you chose, allow data to persist across multiple executions.