Connecting Postgres to ASP.NET Core Web Api with EF Core and .NET Aspire

Introduction:

The introduction of .NET Aspire is a significant improvement that enables developers to define and deploy infrastructure and connect them across resources. The following post will demonstrate the use case for connecting PostgreSQL to an ASP.NET Core Web API.

Prerequisites:

• ASP.NET Core SDK 9 (.NET 9) or above.
• Visual Studio 2022 or any other text editor.
• Docker Engine (MacOS, Linux) or Docker Desktop (MacOS, Linux, Windows) or podman(MacOS, Linux, Windows) or podman desktop (macos, Linux, Windows)

Background:

• .NET Aspire: Highly opinionated first party set of implementations.

• Postgres: An open source and very popular relational database management server.

• ASP.NET Core: A popular open source framework for building web applications and REST API's.

• EF Core: An ORM (Object Relational Mapper); At a fundamental level, it enables interactions with the database server with C#.

Let's get started;

Step 1: Create an empty aspire application

• Instruction for Visual Studio:
  

• Instruction for CLI:
  

  dotnet new install Aspire.ProjectTemplates
  dotnet new list aspire
  

  

  Use the command below to create the project.
  

  dotnet new aspire -o <ProjectName>
  

You will see two different projects - The AppHost and Service Defaults.

• AppHost.csproj: Loosely think of this as the project that manages all other projects in the entire application. It utilizes the DistributedApplication Class from the Aspire.Hosting package to create resources and connect them to other projects.

• ServiceDefaults.csproj: These are highly opinionated extension methods written by Microsoft that follow best practices for configuring various everyday use cases in an application (e.g., Adding logging, health checks, etc.).

Step 2: Add the ASP.Net Core Web Api to the created solution

• Instruction for Visual Studio:

  • Select the ASP.NET Core Web API template
  • Additionally, ensure that the Enlist in .NET Aspire Orchestration checkbox is selected. The container support checkbox may be left unselected.

• Instruction for CLI
  

  dotnet new webapi -o <ProjectName>
  dotnet sln add <ProjectName>
  

The final project structure will look something like this:

Feel free to update NuGet packages for all projects in the solution.

Step 3: Connecting the WEB Api to the .NET Aspire App Host

The AppHost project from aspire provides the DistributedApplication builder class, which allows us to orchestrate the creation of resources and startup of any project.

• Step 3(a): Inside the Aspire AppHost project, add a reference to the ASP.NET Web API project (here called ExampleApp.Web).

  • Instructions for Visual Studio
    • Right click on the AppHost project and navigate to the built-in reference manager
    • Mark the checkbox next to the Web Api project and click on 'OK'

  • Instruction for CLI:

    • Change working directory to the AppHost project folder (cd <PATH_TO_APP_HOST_PROJECT>)

    cd ExampleApp.AppHost
    

    • Now add a reference to the Web Api project using dotnet add reference <PATH_TO_WEB_PROJECT>

    In my case that would be:
    

    dotnet add reference ..\<ProjectName>.Web
    

• Step 3(b): Register the Web API project and trigger its startup inside AppHost

Original ExampleApp\ExampleApp.AppHost\Program.cs

var builder = DistributedApplication.CreateBuilder(args);

builder.Build().Run();

Updated ExampleApp\ExampleApp.AppHost\Program.cs

var builder = DistributedApplication.CreateBuilder(args);

builder.AddProject<Projects.Example_Web>("web");

builder.Build().Run();

Step 4: Configure Web Api project to use the service defaults

• Step 4(a): Inside the API project add a reference to the ServiceDefaults project

  This step is very similar to step 3(a)

  Instructions for CLI:

  • From inside the solution folder, change working directory to the web API project

    cd ExampleProject.Web

  • Add the reference

    dotnet add reference ..\ExampleProject.ServiceDefaults

• Step 4(b): Invoke the extensions from the web API project.

Modify ExampleApp.Web\Program.cs to include the following line.

builder.AddServiceDefaults()

Finally, when running the AppHost project, we should see the following:

• Step 5: Add the postgres server and database and integrate them.

  • Step 5(a): First add the PostgreSQL hosting NuGet package to the AppHost project.

    • Using VS Code NuGet Package Manager
    • Using CLI command: dotnet add package Aspire.Hosting.PostgreSQL

  • Step 5(b): Create the PostgreSQL server and the example database
    
    

   var postgresServer = builder.AddPostgres("postgreSQLServer");
  
   var exampleDatabase = postgresServer.AddDatabase("exampleDB");
  

  Optionally, you may include pgadmin to manage the database, as shown below.
  

   var postgresServer = builder.AddPostgres("postgreSQLServer")
                               .WithPgAdmin();
  
   var exampleDatabase = postgresServer.AddDatabase("exampleDB");
  

  • Step 5(c): Add a reference for the database to the web project.

    builder.AddProject<Projects.ExampleApp_Web>("web")
  .WithReference(exampleDatabase);
  

The final program.cs should be as below.

ExampleApp\ExampleApp.AppHost\Program.cs

var builder = DistributedApplication.CreateBuilder(args);


var postgresServer = builder.AddPostgres("postgreSQLServer").WithPgAdmin();

var exampleDatabase = postgresServer.AddDatabase("exampleDB");

builder.AddProject<Projects.ExampleApp_Web>("web")
    .WithReference(exampleDatabase);

builder.Build().Run();

• ### Step 6: Configure the web project to use the PostgreSQL database.

The next step may depend on the level of customization you require. For the scope of this blog, let's take the most straightforward approach.

The next step may depend on the level of customization you require. For the scope of this blog, let's take the most straightforward approach.

• Without EFCore:

  • Add the Aspire.Npgsql nuget package to the web project

    dotnet add package Aspire.Npgsql

  • Modify the ExampleApp\ExampleApp.Web\Program.cs to include
    

    builder.AddNpgsqlDataSource("<POSTGRES_DATABASE_NAME>");
    

    For my example this would be builder.AddNpgsqlDataSource('exampleDB');

  • With EFCore:

    • Add the Aspire.Npgsql.EntityFrameworkCore.PostgreSQL nuget package to the web project dotnet add package Aspire.Npgsql.EntityFrameworkCore.PostgreSQL

      builder.AddNpgsqlDbContext<YourDbContext>(connectionName: "<DATABASE_NAME>");
    

    • For my example this would be builder.AddNpgsqlDbContext<ENTER_DB_CONTEXT_CLASS>(connectionName: "exampleDB");
    • Note: I have not shown the creation of DB Context here to keep the blog succinct. Furthermore, using EFCore world may also require you to add Microsoft.EntityFrameworkCore and Npgsql.EntityFrameworkCore.PostgreSQL NuGet packages depending on your needs.

Result

We have created the architectural design shown below using the steps outlined above.

While Aspire's inner workings are outside the scope of our discussion, understanding them is still essential.

Aspire is a mechanism that among other capabilities allows developers to spin up OCI-compliant containers using C#. We can configure Aspire to use either Docker or Podman, the top two leading containerization applications.

Think of it as the alternatives to writing docker-compose files and nothing more.

Furthermore, let's understand the connection between a deployed resource (such as a PostgreSQL database) and a project.

builder.AddProject<Projects.ExampleApp_Web>("web")
    .WithReference(exampleDatabase);

Idiomatically speaking, the above line adds a connecting string configuration to the ExampleApp.Web project, which can be accessed using the project's HostApplicationBuilder.

For example

var builder = WebApplication.CreateBuilder(args);
var databaseConnectionString = builder.Configuration.GetConnectionString("ExampleDB");

However, these details can/may be abstracted away when we use the Aspire hosting packages.

Conclusion

Aspire is an incredible framework that significantly improves developer productivity, especially for local development. It also reduces the barrier for new software engineers/programmers/developers to pick up the fundamental concepts and best practices of DevOps.

Please feel free to comment your thoughts on it. I welcome all feedback and will do my best to update the content to reflect up-to-date and accurate information.

Link to companion github repo: https://github.com/chandanbsd/aspire-postgres-efcore-aspnet-template/tree/main