.NET Aspire: SQL persistence

This is the fifth 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 the preceding posts, I showed examples where the SQL database was created from scratch every time we run. Depending on the complexity of the database, that might slow things down too much, so in this post, I'll look at how you can trade off the isolation offered by the "brand new database every time" model off against improved startup time.

Aspire SQL Server container lifetime

By default, Aspire will create a brand new containerised SQL Server instance every time you run. However, it doesn't have to work that way. In fact, Aspire's SQL integration docs actually recommend a different approach. They suggest that you enable container persistence, which we can do with just one extra method call when describing the SQL Server resource in the app host:

IResourceBuilder<SqlServerServerResource> sql = builder
    .AddSqlServer("sql")
    .WithLifetime(ContainerLifetime.Persistent);

This WithLifetime(ContainerLifetime.Persistent) call tells Aspire that instead of creating a brand new containerised SQL Server instance every time we run (and deleting it afterwards) it should create one the first time we run, and then reuse that next time we run.

This typically reduces startup time considerably. Not only does Aspire not delete the container, it leaves it running, so unless you shut it down, it will be available instantly the next time you run. Even if you do shut it down (either manually, or because you rebooted your machine) SQL Server does some first-run initialization that it won't need to repeat, so startup will be faster even if the container had shut down.

A side effect of making the container persistent is that the data stored in the database will also persist between executions. But if that's your main goal then you don't need to make the whole container persistent. You can just tell Aspire that you'd like it to create a volume for the SQL Server.

Using a volume for SQL Server data persistence

Volumes are common feature of container systems such as Docker or podman. A volume is effectively a virtual storage device that can be made accessible to a container, but which has its own separate lifetime. If we write this:

IResourceBuilder<SqlServerServerResource> sql = builder
    .AddSqlServer("sql")
    .WithDataVolume();

Aspire will create a new volume specifically for use by our application for storing the SQL Server data. So it remains isolated from any other applications we might develop on our machines, but will retain data from one run to the next, even though I've now removed the WithLifetime call I showed in the earlier example.

Of course, you can combine both options. Why would you, when container persistence automatically implies data persistence? One reason is that if you want to rebuild the container for any reason (e.g., an update to the SQL Server image) you won't lose any persisted data when that happens if the data lives in a volume, separate from the container.

Conclusion

In this series, I showed the basic operation of Aspire dev-time orchestration, and the way in which Aspire makes its orchestration services available to integration tests. I showed how to manage database initialization in Aspire, and how integration tests can communicate directly with the database. These same techniques would work for other resource types such blob storage or Cosmos DB. So these are the basic elements that enable integration tests to verify that an Aspire application uses its resources correctly.