.NET Aspire SQL Server integration tests and local development

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

In this post I'll show how we can ensure that a SQL database set up by Aspire's dev-time orchestration is suitably initialized both for local debugging, and also for an integration test suite's needs.

Orchestrating SQL database initialization

Only your application knows what tables, views, indexes, and so on that it requires, so Aspire won't create these things for you. For local development and test scenarios, you can use EF Core's EnsureCreated method to set up the schema. If you're not using EF Core—perhaps you're using Dapper—then you'll need to take other steps to set up the database.

(Until Aspire 9.2 shipped on 2025/04/10, when you asked Aspire for a database server and a database, it only created the database server, leaving you to execute a CREATE DATABASE command yourself. EF Core's EnsureCreated can do that for you, but a lot of people using other APIs were surprised that they had to perform this step themselves. If you're on the latest version of Aspire, this is no longer necessary.)

No matter which API you use to talk to the database, most applications require the database to be pre-loaded with certain data. For example, your application might expect a list of countries or product categories to be present. There are a few ways applications can solve this problem. If you use a SQL database project to define your schema, you can include scripts that populate tables with fixed data. Or you might write C# code that inserts the data your application needs. Alternatively, you might manually build up a database in a suitable initial state and then export a backup as a .bacpac file.

Whichever technique you use, you'll need to ensure that you initialize your database appropriately when Aspire launches your application. Remember: it's going to create a completely new SQL Server instance in a container, so you'll be starting from a blank slate. (In fact you can ask it to retain the database so that you don't need to wait for this to happen every time you debug, and I'll show how to do that later in this series. But each time a new developer joins the project, they will be starting from scratch. Also, if you run tests in a build agent, the hosted build agents supplied by systems such as GitHub Actions or Azure DevOps don't persist anything, so you'll need to ensure that the database is appropirately initialized for these environments.)

It's common to add a separate project to your Aspire solution that initializes the database. This will typically be a console app that exits after it has done its work. In the Aspire app host, we would typically launch it like this:

IResourceBuilder<ProjectResource> initializeDb = builder
    .AddProject<Projects.MyApp_InitializeDatabase>("localdevinitializedb")
    .WithReference(sqlDb)
    .WaitFor(sqlDb);

Notice that I'm using the same WithReference(sqlDb) as I did for the MyApp.WebApi project in the first post in this series. That's because this database initializer project also needs access to the database. Likewise, I've used WaitFor(sqlDb) because we need the database to be available before we can initialize it.

With this project in place, I'll make a slight alteration to how my app host describes the web API project:

builder
    .AddProject<Projects.MyApp_WebApi>("webapi")
    .WithReference(sqlDb)
    .WaitForCompletion(initializeDb);

I've replaced the call to WaitFor(sqlDb) with WaitForCompletion(initializeDb). The web API doesn't just need the database to be up and running, it needs it to be fully initialized. That final line tells Aspire that it's not enough just to wait for the database initialization project to start: we need that to run to completion before launching the web API. (The web API does still need the database to be available too, but I no longer need to call WaitFor(sqlDb) for the web API project: that would be redundant because the DB initialization project also waits for the database to be available. By waiting for DB initialization to complete, the web API is implicitly also waiting for the DB to start.)

This ensures that database initialization takes place not just for integration tests but also for normal debugging of our application. But what should go into this MyApp.InitializeDatabase project?

Initialization project contents

Now that we've told Aspire that it should run our database initialization project before running any other code that relies on the database, we'll need to write the code to perform whatever initialization we require. So we'll need to connect to the database.

Aspire makes connection details available to the processes it launches through environment variables, but we don't normally read those directly. Aspire presumes that we are using the .NET Generic Host (not to be confused with Aspire's app host) or some framework-specific specialization of this such as ASP.NET Core's WebApplicationBuilder (not to be confused with Aspire's IDistributedApplicationBuilder). For example, ASP.NET Core apps typically start with this:

WebApplicationBuilder builder = WebApplication.CreateBuilder(args);

WebApplicationBuilder implements the generic hosting IHostApplicationBuilder interface, and Aspire defines extension methods for that interface. We tell Aspire that we want to use a SQL database by writing this in our application startup:

builder.AddSqlServerClient(connectionName: "db");

Or if you're using EF Core, you can do this:

builder.AddSqlServerDbContext<MyDbContext>("db");

This makes a SqlConnection (or MyDbContext) available through the generic host's dependency injection system. (Aspire also supports keyed dependency injection if you need to connect to multiple databases.)

But our database initialization project won't be a web application. We need it to perform its job and then exit, so a console application is better suited. Instead of using ASP.NET Core's WebApplicationBuilder we'll just use the generic builder from the Microsoft.Extensions.Hosting NuGet package, and then we can use Aspire's AddSqlServerClient method in exactly the same way as a web app would:

HostApplicationBuilder builder = Host.CreateApplicationBuilder();
builder.AddSqlServerClient(connectionName: "db");

Or if you're using EF Core:

HostApplicationBuilder builder = Host.CreateApplicationBuilder();
builder.AddSqlServerDbContext<MyDbContext>("db");

To get access to dependencies in a console app that uses the generic host, we typically write a class that implements IHostedService:

internal class CreateDb(
    MyDbContext dbContext,
    ILogger<CreateDb> logger,
    IHostApplicationLifetime hostApplicationLifetime) : IHostedService
{
    public async Task StartAsync(CancellationToken cancellationToken)
    {
        dbContext.Database.EnsureCreated();

        // The logger delivers messages to Aspire's dashboard.
        logger.LogInformation("Data setup complete. Shutting down.");
        hostApplicationLifetime.StopApplication();
    }
}

Notice that we call IHostApplicationLifetime.StopApplication once we're done. Without this, the generic host will keep the process running, and Aspire will never launch our other projects because we told it to wait for this one to complete.

In this example I'm using EF Core, and that EnsureCreated method will set up the schema. If you need to load some initial data, this would be the place to do it.

Finally, our application entry point needs to start things running, and then report success or failure:

IHost host = builder.Build();

try
{
    host.Run();
}
catch (Exception)
{
    // Aspire checks our exit code. We return 1 to let it know we failed. (This
    // will then prevent services depending on data setup from starting.)
    return 1;
}

return 0;

That's a basic but complete example that uses EF Core's EnsureCreated method to set up the database schema. But what if you're not using EF Core? I already showed the AddSqlServerClient that makes a SqlConnection available through dependency injection, but how do we use that to set up the schema?

Schema population without EF Core

One way to populate the schema is to use the Microsoft.SqlServer.DacFx NuGet package. If you have a .bacpac file containing a backup of a suitably initialized database, you can use its DacServices and BacPackage classes to populate the database Aspire created:

internal class CreateDb(
    SqlConnection sqlConnection,
    ILogger<CreateDb> logger,
    IHostApplicationLifetime hostApplicationLifetime) : IHostedService
{
    private const string BacPacPath = "SqlData/MyApp.bacpac";

    public async Task StartAsync(CancellationToken cancellationToken)
    {
        DacServices dacServices = new(sqlConnection.ConnectionString);
        var bacpac = BacPackage.Load(BacPacPath);
        dacServices.ImportBacpac(bacpac, TestDbName);

        logger.LogInformation("Data setup complete. Shutting down.");
        hostApplicationLifetime.StopApplication();
    }
}

Alternatively, you could use a .dacpac file. SQL database projects create these as their build output. There are a couple of ways you can use these from Aspire. The Database Projects hosting integration can automate the deployment from a SQL database project. That is a community-supported integration, meaning it is not officially supported by the .NET Aspire team. If you'd prefer to stick entirely with supported components, it doesn't take much code to deploy a .dacpac. Using the same library as the preceding example used to deploy a .bacpac, you can write this instead:

DacServices dacServices = new(sqlConnection.ConnectionString);
var dacPackage = DacPackage.Load(DacPacPath);
dacServices.Deploy(dacPackage, TestDbName, upgradeExisting: true);

Both the .bacpac and .dacpac approach will set up the database schema, and they can also load additional data. .bacpac files back up data as well as the schema, and the ImportBacpac method restores that data too. SQL database projects can include post-initialization scripts that populate tables with data. These are incorporated in the .dacpac, and the Deploy method runs those scripts.

So we've now arranged our Aspire solution so that the database will be correctly initialized with a schema and, if necessary, any initial data that the application requires. We've configured orchestration to ensure that this happens both for normal debugging and when executing integration tests. However, we might want integration tests to perform some additional test-specific database initialization, or they might need to check that the application code under test made the expected modifications to the database. So in the next post I'll show how our integration tests can obtain SQL database connection details.