Skip to content
Ian Griffiths By Ian Griffiths Technical Fellow I
.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:

sequenceDiagram participant Test as Test Code participant Host as App Host participant DBInit as Database Initialization participant DB as Database participant WebAPI as Web API Test->>Host: Start Test->>Host: Wait for Web API Host->>DBInit: Start database initialization DBInit->>DB: Create database DBInit->>DB: Set up schema DBInit->>DB: Load initial data DBInit->>Host: Database initialized Host->>WebAPI: Start Web API activate WebAPI WebAPI-->WebAPI: Initialization WebAPI->>Host: Web API started deactivate WebAPI Host->>Test: Web API started Test->>DB: Add test-specific data to database Test->>WebAPI: Run code under test

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:

sequenceDiagram participant Test as Test Code participant Host as App Host participant DBInit as Database Initialization participant DB as Database participant WebAPI as Web API Test->>Host: Start Test->>Host: Wait for Database Host->>DBInit: Start database initialization DBInit->>DB: Create database DBInit->>DB: Set up schema DBInit->>DB: Load initial data DBInit->>Host: Database initialized Host->>WebAPI: Start Web API activate WebAPI Host->>Test: Database initialized Test->>DB: Add test-specific data to database WebAPI-->WebAPI: Initialization Test->>Host: Wait for Web API WebAPI->>Host: Web API started deactivate WebAPI Host->>Test: Web API started Test->>WebAPI: Run code under test

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.

Ian Griffiths

Technical Fellow I

Ian Griffiths

Ian has worked in various aspects of computing, including computer networking, embedded real-time systems, broadcast television systems, medical imaging, and all forms of cloud computing. Ian is a Technical Fellow at endjin, and 17 times Microsoft MVP in Developer Technologies. He is the author of O'Reilly's Programming C# 12.0, and has written Pluralsight courses on WPF fundamentals (WPF advanced topics WPF v4) and the TPL. He's a maintainer of Reactive Extensions for .NET, Reaqtor, and endjin's 50+ open source projects. Ian has given over 20 talks while at endjin. Technology brings him joy.