DEV Community

Cover image for 10-Day .Net Aspire Challenge: Day 3— Add PGSQL Component
Sukhpinder Singh
Sukhpinder Singh

Posted on • Originally published at Medium

10-Day .Net Aspire Challenge: Day 3— Add PGSQL Component

Step-by-step guide on how to use the .Net Aspire PGSQL component in Visual Studio.

Introduction

.Net Aspire framework is used to develop cloud and production-ready distributed applications. It consists of components to handle cloud-native concerns such as Redis, Postgres etc.

Prerequisites

Objectives

Learn how to create a starter project using .Net Aspire with the PGSQL EF Core component.

Github Sample: The solution structure is divided into the following projects

  • DotnetAspireChallenge.ApiService

  • DotnetAspireChallenge.AppHost

  • DotnetAspireChallenge.ServiceDefaults

  • DotnetAspireChallenge.Web

Getting Started

Step 1: Install the following NuGet package

Install the following Nuget package into the subsequent project “DotnetAspireChallenge.AppHost

dotnet add package Aspire.Hosting.PostgreSQL
Enter fullscreen mode Exit fullscreen mode

In the above project, register a server database and costume the PGSQL connection using following code.



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

    var apiService = builder.AddProject<Projects.DotnetAspireChallenge_ApiService>("apiservice")
        .WithReference(postgres);


Enter fullscreen mode Exit fullscreen mode

Step 2: Install another NuGet package

Install the following Nuget package into the subsequent project “DotnetAspireChallenge.ApiService

dotnet add package Aspire.Npgsql.EntityFrameworkCore.PostgreSQL
Enter fullscreen mode Exit fullscreen mode

then register the context into the Program.cs file as follows



    builder.AddNpgsqlDbContext<PgsqlDbContext>("pgsqldata");


Enter fullscreen mode Exit fullscreen mode

Step 3: Create a “Customer” class



    public class Customer
    {
        public int Id { get; set; }

        [Required]
        public string Title { get; set; } = string.Empty;

        [Required]
        public string Description { get; set; } = string.Empty;
    }


Enter fullscreen mode Exit fullscreen mode

Step 4: Create an extension class

Create an extension class and register a minimal API get method to demonstrate the PGSQL context usage in the API Service



    public static class AspirePgsqlExtension
    {
        public static void MapPgsqlAspireEndpoint(this WebApplication app)
        {
            app.MapGet("/pgsql", async (PgsqlDbContext pgsqlDbContext) =>
            {
                await pgsqlDbContext.CustomersPgsql.AddAsync(new Customer()
                {
                    Title = "test@gmail.com",
                    Description = "sukh"
                });
                int rows = await pgsqlDbContext.SaveChangesAsync();
                if (rows > 0)
                {
                    return await pgsqlDbContext.CustomersPgsql.FirstOrDefaultAsync();
                }
                else
                {
                    return null;
                }
            });
        }
    }


    internal class PgsqlDbContext(DbContextOptions options) : DbContext(options)
    {
        public DbSet<Customer> CustomersPgsql => Set<Customer>();
    }


Enter fullscreen mode Exit fullscreen mode

and finally, register in the Program.cs file



    app.MapPgsqlAspireEndpoint();


Enter fullscreen mode Exit fullscreen mode

Step 5: Hit the GET endpoint

Finally, navigate to the GET URL shown below in your browser. It will insert the specified customer into the PGSQL database, retrieve the most recently inserted row, and display it as a response.

Add additional connection string properties using the JSON syntax



    {
      "Aspire": {
        "Npgsql": {
          "EntityFrameworkCore": {
            "PostgreSQL": {
              "ConnectionString": "YOUR_CONNECTIONSTRING",
              "DbContextPooling": true,
              "DisableHealthChecks": true,
              "DisableTracing": true,
              "AnotherDbContext": {
                "ConnectionString": "AnotherDbContext_CONNECTIONSTRING",
                "DisableTracing": false
              }
            }
          }
        }
      }
    }


Enter fullscreen mode Exit fullscreen mode

Congratulations..!! You’ve successfully integrated the PGSQL component into the .Net Aspire project.

View Metrics

The .NET Aspire component will produce the following metrics using OpenTelemetry:

Microsoft.EntityFrameworkCore

  • ec_Microsoft_EntityFrameworkCore_active_db_contexts

  • ec_Microsoft_EntityFrameworkCore_total_queries

  • ec_Microsoft_EntityFrameworkCore_queries_per_second

  • ec_Microsoft_EntityFrameworkCore_total_save_changes

  • ec_Microsoft_EntityFrameworkCore_save_changes_per_second

  • ec_Microsoft_EntityFrameworkCore_compiled_query_cache_hit_rate

  • ec_Microsoft_Entity_total_execution_strategy_operation_failures

  • ec_Microsoft_E_execution_strategy_operation_failures_per_second

  • ec_Microsoft_EntityFramew_total_optimistic_concurrency_failures

  • ec_Microsoft_EntityF_optimistic_concurrency_failures_per_second

Npgsql

  • ec_Npgsql_bytes_written_per_second

  • ec_Npgsql_bytes_read_per_second

  • ec_Npgsql_commands_per_second

  • ec_Npgsql_total_commands

  • ec_Npgsql_current_commands

  • ec_Npgsql_failed_commands

  • ec_Npgsql_prepared_commands_ratio

  • ec_Npgsql_connection_pools

  • ec_Npgsql_multiplexing_average_commands_per_batch

  • ec_Npgsql_multiplexing_average_write_time_per_batch

Github Project

GitHub - ssukhpinder/DotnetAspireChallenge: 10 Day .Net Aspire Challenge

More Cheatsheets

Cheat Sheets — .Net

C# Programming🚀

Thank you for being a part of the C# community! Before you leave:

Follow us: Youtube | X | LinkedIn | Dev.to
Visit our other platforms: GitHub
More content at C# Programming

Top comments (0)