Using SQL Database Components in .NET Aspire

We previously looked at Aspire components, special libraries used by .NET Aspire to wrap commonly used applications and infrastructure components. These libraries consist of containers, but it’s simpler to use them instead of using containers directly. After all, Aspie components were specifically designed to work in Aspire.

Today, we will look at the components that represent SQL databases. At the time of writing, the following relational database types are supported:

• SQL Server
• Oracle
• PostgreSQL
• MySQL

SQL Database Components Basics

In general, each Aspire component is represented by two types of libraries:

• The host library that allows the container with the app to be hosted in a distributed Aspire setup
• The consumer library that allows individual services to interact with the component

It’s no different for SQL databases. Each of these has a library that is added to the .NET Aspire host project. The library allows the host to register, configure, and launch the container with the database management system.

There are also client libraries for each of the database types. Those are the libraries that allow services to interact with databases by executing queries, inserting data, modifying data, etc.

Some components have more than one client library to choose from, depending on your preferred approach to working with SQL inside C# code. For example, one client library will allow you to use an object-relational mapper, such as Entity Framework, while another library enables direct interaction via raw SQL.

Let’s see how these libraries work by using concrete examples.

Hosting a SQL Server Component

Let’s first look at the SQL Server component.

To host a SQL Server container via Aspire components, we will need to add the following NuGet package to the Aspire host project:

Aspire.Hosting.SqlServer

Then, this is how we set this component up in the Program.cs file of the host project:

var sql = builder.AddSqlServer("sql");
var sqldb = sql.AddDatabase("sqldb");

var apiService = builder
    .AddProject<Projects.AspireApp_ApiService>("apiservice")
    .WithReference(sqldb);

Let’s examine this code step-by-step.

1. We first add a SQL server component. We assign the resource the name sql, but it can be any arbitrary name. There are several overrides of the AddSqlServer() method. This specific invocation will auto-generate the password for the sa user that is used as the default SQL Seever admin. The port will be automatically assigned too.
2. We then create a reference to the database. What’s important to know is that the AddDatabase() method will not add the database, despite its name. It will merely add the database reference to the connection string used by the client libraries. The database has to be created separately. The sqldb is an arbitrary name we give to the resource. However, because we haven’t added any other parameters, this will also be the database name.
3. We then use the WithReference() method invocation to insert the database reference into any service that we want to interact with the database.

An example of this setup can be found here. If we launch this application, we will see both the SQL sever and the database reference shown in the dashboard, as per the following screenshot:

While the database server itself is a container, the database reference belongs to a special type. In this example, its SqlServerDatabaseResource, but the type will be different for other database types.

Let’s now look at other ways the database can be set up. In the following example, we are setting up our own password for the admin sa user:

var password = builder.AddParameter("password", secret: true);
var sql = builder.AddSqlServer("sql", password);
var sqldb = sql.AddDatabase("sqldb");

The password is then passed to the AddSqlServer() method. The value of the password is extracted via the AddParameter() method. This method will look for an entry in the Parameters section of the appsettings.json file. This is what it may look like:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Aspire.Hosting.Dcp": "Warning"
    }
  },
  "Parameters": {
    "password": "P@$$w0rd"
  }
}

We can also pass a specific port number in the AddSqlServer() method invocation:

var sql = builder.AddSqlServer("sql", port: 3000);
var sqldb = sql.AddDatabase("sqldb");

This will ensure that the SQL Server is run on a specific port and not the one automatically assigned by the system.

We can also supply the database name to the AddDatabase() method:

var sql = builder.AddSqlServer("sql");
var sqldb = sql.AddDatabase("sqldb", "master");

In this example, the connection named sqldb references the master database.

This is how we add a SQL Server instance to our Aspire setup. Let’s now look at how we can consume the instance.

Consuming a SQL Server Component

With SQL Server, we have two types of the client library:

• A library that uses an SQL Client from ADO.NET
• An Entity Framework library

Let’s first look at how to use the ADO.NET library, which is enabled by installing the following NuGet package:

Aspire.Microsoft.Data.SqlClient

Once we have this NuGet package, we can add the following line to the Program.cs file of the consuming project to register the SQL Sever client dependencies:

builder.AddSqlServerClient("sqldb");

Please note that in this example, we are using the same name as we used when we registered the database resource in the host applictaion This means that the SQL Server connection string will be provided by the host. We don’t need to construct our own connection string in the client app. However, if we ever want to supply our own connection string, we can use different overrides of this method.

This method adds an instance of the SqlConnection class to dependency injection (DI) with the appropriate connection string configured. This will allow us to execute raw SQL against the SQL Server instance from anywhere in our application code.

Let’s see an example of how it’s used. A simple example would be to use a database to contain weather forecast data similar to what’s provided in the API service in the Aspire starter project. To do so, we will create the following block of code:

using (var scope = app.Services.CreateScope())
{
}

Inside this block, we will resolve the SqlConnection instance from the DI container and invoke the Open() method to open the connection:

var connection = scope.ServiceProvider.GetRequiredService<SqlConnection>();
connection.Open();

Our example assumes that the original SQL database reference was pointing at the master database. Anyone who’s experienced with SQL Server will tell you that working with the master database directly is a bad idea. Therefore, we will need to create another database. Let’s call it WeatherDB. We can create it if it doesn’t exist already by running the following command:

var createDbCommand = new SqlCommand(@"
        IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'WeatherDB')
        BEGIN
              CREATE DATABASE WeatherDB;
        END;", connection);
createDbCommand.ExecuteNonQuery();

An alternative would be to run custom SQL during the original container setup in the host to create the database. Then the original connection string would point directly at that database.

Since a SQL Server component is a wrapper around a Docker container, this database-creation step can be done by executing a custom script in the same way it can be done by running a Docker container directly.

Next, we will create a database table that will contain our weather forecast information if this table doesn’t exist already:

var createTableCommand = new SqlCommand(@"
        USE WeatherDB;
        IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='WeatherForecasts' and xtype='U')
        CREATE TABLE WeatherForecasts (
            Id INT PRIMARY KEY IDENTITY,
            Date DATE NOT NULL,
            TemperatureC INT NOT NULL,
            Summary NVARCHAR(100) NOT NULL
        )", connection);

createTableCommand.ExecuteNonQuery();

Please note the usage of USE WeatherDB, which is needed because our original connection string referenced the master database.

In the next step, we will check if the database table has any data:

var checkDataCommand = new SqlCommand("SELECT COUNT(*) FROM WeatherForecasts", connection);
var count = (int)checkDataCommand.ExecuteScalar();

If there is no data, we will populate it:

if (count == 0)
{
    foreach (var index in Enumerable.Range(1, 5))
    {
        var date = DateTime.Now.AddDays(index).Date;
        var temperatureC = Random.Shared.Next(-20, 55);
        var summary = summaries[Random.Shared.Next(summaries.Length)];

        var insertCommand = new SqlCommand(@"
                INSERT INTO WeatherForecasts (Date, TemperatureC, Summary)
                VALUES (@Date, @TemperatureC, @Summary)", connection);

        insertCommand.Parameters.AddWithValue("@Date", date);
        insertCommand.Parameters.AddWithValue("@TemperatureC", temperatureC);
        insertCommand.Parameters.AddWithValue("@Summary", summary);

        insertCommand.ExecuteNonQuery();
    }
}

Now, we can query the database and return the data from it in our API endpoint:

app.MapGet("/weatherforecast", ([FromServices] SqlConnection connection) =>
{
    connection.Open();

    var command = new SqlCommand(@"
    USE WeatherDB;
    SELECT Date, TemperatureC, Summary FROM WeatherForecasts", connection);
    var weatherForecasts = new List<WeatherForecast>();

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            weatherForecasts.Add(new WeatherForecast
            {
                Date = DateOnly.FromDateTime(reader.GetDateTime(0)),
                TemperatureC = reader.GetInt32(1),
                Summary = reader.GetString(2)
            });
        }

        return weatherForecasts.ToArray();
    }
});

The complete example is available here.

Let’s now look at how the same can be achieved via the Entity Framework client library.

Using Entity Framework

Entity Framework client library is represented by the following NuGet package:

Aspire.Microsoft.EntityFrameworkCore.SqlServer

Entity Framework represents a database abstraction by using an override of the DbContext class. here is an example of a DbContext that represents our weather forecast database:

using AspireApp.ApiService.Models;
using Microsoft.EntityFrameworkCore;

namespace AspireApp.ApiService;

public class WeatherDbContext : DbContext
{
    public WeatherDbContext(
        DbContextOptions<WeatherDbContext> options) : base(options)
    {
    }

    public DbSet<WeatherForecast> WeatherForecasts { get; set; }
}

Once we have the DB context, we can execute the following method inside the Program.cs file to register all the database dependencies:

builder.AddSqlServerDbContext<WeatherDbContext>("sqldb");

This method will register an instance of the WeatherDbContext class which can be then resolved anywhere via the DI.

The same principles apply as before. Because we are referencing the sqldb connection name, we will use the connection string constructed by the Aspire host. However, we can customize the connection string via method overrides.

Here is what our initial database setup code looks like:

using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<WeatherDbContext>();
    context.Database.EnsureCreated();

    if (!context.WeatherForecasts.Any())
    {
        foreach (var index in Enumerable.Range(1, 5))
        {
            context.WeatherForecasts.Add(new WeatherForecast
            {
                Date = DateOnly.FromDateTime(DateTime.Now.AddDays(index)),
                TemperatureC = Random.Shared.Next(-20, 55),
                Summary = summaries[Random.Shared.Next(summaries.Length)]
            });

            context.SaveChanges();
        }
    }
}

Please note the EnsureCreated() invocation on the Database property of the context object. Calling this method will automatically create the database if it doesn’t exist already.

Of course, we don’t always want to do this. In many cases, we would want to fine-tune the database-creation process, such as setting a specific collation. However, this demonstrates how simple it is to create a database via Entity Framework if we are happy with the default database configuration.

Finally, this is how we retrieve the data from the database in the API endpoint:

app.MapGet("/weatherforecast", ([FromServices] WeatherDbContext context) =>
{
    return context.WeatherForecasts.ToArray();
});

Please note the ToArray() invocation. This is a method that causes the underlying SQL query to execute. Once the results are returned by the query, they are converted into an array of the WeatherForecast objects.

The complete setup of the above example is available here.

This concludes the steps needed to set up a SQL Server container in .NET Aspire. Let’s now look at how to do it with other database types.

Consuming an Oracle Component

To host an Oracle database in Aspire, the NuGet package we would need to install in the host project is as follows:

Aspire.Hosting.Oracle

Then, this is how we create a database server container and the database reference:

var oracle = builder.AddOracle("oracle");
var oracledb = oracle.AddDatabase("oracledb");

Once again, both these methods have overrides that allow us to customize the database configuration.

At the time of writing, the Oracle component only has an Entity Framework client library. This is the NuGet package we need to install in the service that will consume the database:

Aspire.Oracle.EntityFrameworkCore

Then, the following method can be executed inside the Program.cs file to register the DB context and connect it to the database:

builder.AddOracleDatabaseDbContext<WeatherDbContext>("oracledb");

Other than this, we would use the same Entity Framework and LINQ code we would use with other database types. While different Entity Framework database providers work differently behind the scenes, the top-level abstractions look the same.

Consuming a PostgreSQL Component

The PostgreSQL component is enabled via the following NuGet package:

Aspire.Hosting.PostgreSQL

The following code is then used for creating the database server and the database reference:

var postgres = builder.AddPostgres("postgres");
var postgresdb = postgres.AddDatabase("postgresdb");

Once again, this code will only create the database reference and not the actual database. Also, as before, these methods are overridable.

The client services can consume PostgreSQL code via both Entity Framework and raw SQL. If we want to use raw SQL, we can install the following NuGet package:

package Aspire.Npgsql

Then, we can execute the following code inside our Program.cs file:

builder.AddNpgsqlDataSource("postgresdb");

This code will register an instance of the NpgsqlDataSource class, which can then be inserted as a service dependency in the services where it’s needed. If we used the same name in the parameter that we used when we registered the database reference in the host application, the right connection string would already be configured.

NpgsqlDataSource is a standard class for interacting with PostgreSQL databases. This documentation explains how it works.

An alternative way to interact with a PostgreSQL database is via Entity Framework, which is enabled via the following NuGet package:

Aspire.Npgsql.EntityFrameworkCore.PostgreSQL

The following invocation is then used in the application startup code to register the DB context:

builder.AddNpgsqlDbContext<WeatherDbContext>("postgresdb");

Consuming MySQL Components

To host a MySQL component, we would need to install the following NuGet package in the host application:

Aspire.Hosting.MySql

The process of adding the database server component and the database reference is similar to how it’s done with other database types. Here’s an example of such code:

var mysql = builder.AddMySql("mysql");
var mysqldb = mysql.AddDatabase("mysqldb");

The MySQL client is represented by the following NuGet package:

Aspire.MySqlConnector

Once we install it, we can execute the following code to register appropriate dependencies in DI:

builder.AddMySqlDataSource("mysqldb");

This will register an instance of the MySqlDataSource which we can then inject into any class where we want to facilitate database interaction. The official documentation explains how this class can be used.

Wrapping Up

As we saw in the examples, hosting SQL-based relational databases in .NET Aspire is easy. While we would use different libraries for different database types, the principles are largely the same.

The important things to remember are as follows:

• The AddDatabase() method, despite its name, will not add the actual database. It will only add a database reference to the connection string while creating the database will still be up to us.
• We can use any library to connect to the database hosted in Aspire, but only Aspire-specific NuGet packages will allow us to automatically resolve the connection string from the host.

Next time, we will talk about using NoSQL data storage components with .NET Aspire.

---

P.S. If you want me to help you improve your software development skills, you can check out my courses and my books. You can also book me for one-on-one mentorship.