.NET Aspire Azure PostgreSQL Entity Framework Core integration

Includes: Hosting integration and Client integration

Azure Database for PostgreSQL—Flexible Server is a relational database service based on the open-source Postgres database engine. It's a fully managed database-as-a-service that can handle mission-critical workloads with predictable performance, security, high availability, and dynamic scalability. The .NET Aspire Azure PostgreSQL integration provides a way to connect to existing Azure PostgreSQL databases, or create new instances from .NET with the docker.io/library/postgres container image.

Hosting integration

The .NET Aspire Azure PostgreSQL hosting integration models a PostgreSQL flexible server and database as the AzurePostgresFlexibleServerResource and AzurePostgresFlexibleServerDatabaseResource types. Other types that are inherently available in the hosting integration are represented in the following resources:

• PostgresServerResource
• PostgresDatabaseResource
• PgAdminContainerResource
• PgWebContainerResource

To access these types and APIs for expressing them as resources in your app host project, install the 📦 Aspire.Hosting.Azure.PostgreSQL NuGet package:

dotnet add package Aspire.Hosting.Azure.PostgreSQL

<PackageReference Include="Aspire.Hosting.Azure.PostgreSQL"
                  Version="*" />

The Azure PostgreSQL hosting integration takes a dependency on the 📦 Aspire.Hosting.PostgreSQL NuGet package, extending it to support Azure. Everything that you can do with the .NET Aspire PostgreSQL integration and .NET Aspire PostgreSQL Entity Framework Core integration you can also do with this integration.

Add Azure PostgreSQL server resource

After you've installed the .NET Aspire Azure PostgreSQL hosting integration, call the AddAzurePostgresFlexibleServer extension method in your app host project:

var builder = DistributedApplication.CreateBuilder(args);

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

var exampleProject = builder.AddProject<Projects.ExampleProject>()
                            .WithReference(postgresdb);

The preceding call to AddAzurePostgresFlexibleServer configures the PostgresSQL server resource to be deployed as an Azure Postgres Flexible Server.

Provisioning-generated Bicep

If you're new to Bicep, it's a domain-specific language for defining Azure resources. With .NET Aspire, you don't need to write Bicep by hand, because the provisioning APIs generate Bicep for you. When you publish your app, the generated Bicep is output alongside the manifest file. When you add an Azure PostgreSQL resource, the following Bicep is generated:

@description('The location for the resource(s) to be deployed.')
param location string = resourceGroup().location

resource postgres_flexible 'Microsoft.DBforPostgreSQL/flexibleServers@2024-08-01' = {
  name: take('postgresflexible-${uniqueString(resourceGroup().id)}', 63)
  location: location
  properties: {
    authConfig: {
      activeDirectoryAuth: 'Enabled'
      passwordAuth: 'Disabled'
    }
    availabilityZone: '1'
    backup: {
      backupRetentionDays: 7
      geoRedundantBackup: 'Disabled'
    }
    highAvailability: {
      mode: 'Disabled'
    }
    storage: {
      storageSizeGB: 32
    }
    version: '16'
  }
  sku: {
    name: 'Standard_B1ms'
    tier: 'Burstable'
  }
  tags: {
    'aspire-resource-name': 'postgres-flexible'
  }
}

resource postgreSqlFirewallRule_AllowAllAzureIps 'Microsoft.DBforPostgreSQL/flexibleServers/firewallRules@2024-08-01' = {
  name: 'AllowAllAzureIps'
  properties: {
    endIpAddress: '0.0.0.0'
    startIpAddress: '0.0.0.0'
  }
  parent: postgres_flexible
}

output connectionString string = 'Host=${postgres_flexible.properties.fullyQualifiedDomainName}'

output name string = postgres_flexible.name

The preceding Bicep is a module that provisions an Azure PostgreSQL flexible server resource. Additionally, role assignments are created for the Azure resource in a separate module:

@description('The location for the resource(s) to be deployed.')
param location string = resourceGroup().location

param postgres_flexible_outputs_name string

param principalType string

param principalId string

param principalName string

resource postgres_flexible 'Microsoft.DBforPostgreSQL/flexibleServers@2024-08-01' existing = {
  name: postgres_flexible_outputs_name
}

resource postgres_flexible_admin 'Microsoft.DBforPostgreSQL/flexibleServers/administrators@2024-08-01' = {
  name: principalId
  properties: {
    principalName: principalName
    principalType: principalType
  }
  parent: postgres_flexible
}

In addition to the PostgreSQL flexible server, it also provisions an Azure Firewall rule to allow all Azure IP addresses. Finally, an administrator is created for the PostgreSQL server, and the connection string is outputted as an output variable. The generated Bicep is a starting point and is influenced by changes to the provisioning infrastructure in C#. Customizations to the Bicep file directly will be overwritten, so make changes through the C# provisioning APIs to ensure they are reflected in the generated files.

Customize provisioning infrastructure

All .NET Aspire Azure resources are subclasses of the AzureProvisioningResource type. This type enables the customization of the generated Bicep by providing a fluent API to configure the Azure resources by using the ConfigureInfrastructure<T>(IResourceBuilder<T>, Action<AzureResourceInfrastructure>) API. For example, you can configure the kind, consistencyPolicy, locations, and more. The following example demonstrates how to customize the PostgreSQL server resource:

builder.AddAzurePostgresFlexibleServer("postgres")
    .ConfigureInfrastructure(infra =>
    {
        var flexibleServer = infra.GetProvisionableResources()
                                  .OfType<PostgreSqlFlexibleServer>()
                                  .Single();

        flexibleServer.Sku = new PostgreSqlFlexibleServerSku
        {
            Tier = PostgreSqlFlexibleServerSkuTier.Burstable,
        };
        flexibleServer.HighAvailability = new PostgreSqlFlexibleServerHighAvailability
        {
            Mode = PostgreSqlFlexibleServerHighAvailabilityMode.ZoneRedundant,
            StandbyAvailabilityZone = "2",
        };
        flexibleServer.Tags.Add("ExampleKey", "Example value");
    });

The preceding code:

• Chains a call to the ConfigureInfrastructure API:
  • The infra parameter is an instance of the AzureResourceInfrastructure type.
  • The provisionable resources are retrieved by calling the GetProvisionableResources() method.
  • The single PostgreSqlFlexibleServer is retrieved.
  • The sku is set with PostgreSqlFlexibleServerSkuTier.Burstable.
  • The high availability properties are set with PostgreSqlFlexibleServerHighAvailabilityMode.ZoneRedundant in standby availability zone "2".
  • A tag is added to the flexible server with a key of ExampleKey and a value of Example value.

There are many more configuration options available to customize the PostgreSQL flexible server resource. For more information, see Azure.Provisioning.PostgreSql and Azure.Provisioning customization.

Connect to an existing Azure PostgreSQL flexible server

You might have an existing Azure PostgreSQL flexible server that you want to connect to. Instead of representing a new Azure PostgreSQL flexible server resource, you can add a connection string to the app host. To add a connection to an existing Azure PostgreSQL flexible server, call the AddConnectionString method:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddConnectionString("postgres");

builder.AddProject<Projects.WebApplication>("web")
       .WithReference(postgres);

// After adding all resources, run the app...

The connection string is configured in the app host's configuration, typically under User Secrets, under the ConnectionStrings section. The app host injects this connection string as an environment variable into all dependent resources, for example:

{
    "ConnectionStrings": {
        "postgres": "Server=<PostgreSQL-server-name>.postgres.database.azure.com;Database=<database-name>;Port=5432;Ssl Mode=Require;User Id=<username>;"
    }
}

The dependent resource can access the injected connection string by calling the GetConnectionString method, and passing the connection name as the parameter, in this case "postgres". The GetConnectionString API is shorthand for IConfiguration.GetSection("ConnectionStrings")[name].

Run Azure PostgreSQL resource as a container

The Azure PostgreSQL hosting integration supports running the PostgreSQL server as a local container. This is beneficial for situations where you want to run the PostgreSQL server locally for development and testing purposes, avoiding the need to provision an Azure resource or connect to an existing Azure PostgreSQL server.

To run the PostgreSQL server as a container, call the RunAsContainer method:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
                      .RunAsContainer();

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

var exampleProject = builder.AddProject<Projects.ExampleProject>()
                            .WithReference(postgresdb);

The preceding code configures an Azure PostgreSQL Flexible Server resource to run locally in a container.

Configure the Azure PostgreSQL server to use password authentication

By default, the Azure PostgreSQL server is configured to use Microsoft Entra ID authentication. If you want to use password authentication, you can configure the server to use password authentication by calling the WithPasswordAuthentication method:

var builder = DistributedApplication.CreateBuilder(args);

var username = builder.AddParameter("username", secret: true);
var password = builder.AddParameter("password", secret: true);

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
                      .WithPasswordAuthentication(username, password);

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

var exampleProject = builder.AddProject<Projects.ExampleProject>()
                            .WithReference(postgresdb);

The preceding code configures the Azure PostgreSQL server to use password authentication. The username and password parameters are added to the app host as parameters, and the WithPasswordAuthentication method is called to configure the Azure PostgreSQL server to use password authentication. For more information, see External parameters.

Client integration

To get started with the .NET Aspire PostgreSQL Entity Framework Core client integration, install the 📦 Aspire.Azure.Npgsql.EntityFrameworkCore.PostgreSQL NuGet package in the client-consuming project, that is, the project for the application that uses the PostgreSQL client. The .NET Aspire PostgreSQL Entity Framework Core client integration registers your desired DbContext subclass instances that you can use to interact with PostgreSQL.

dotnet add package Aspire.Azure.Npgsql.EntityFrameworkCore.PostgreSQL

<PackageReference Include="Aspire.Azure.Npgsql.EntityFrameworkCore.PostgreSQL"
                  Version="*" />

The PostgreSQL connection can be consumed using the client integration by calling the AddAzureNpgsqlDataSource:

builder.AddAzureNpgsqlDbContext<YourDbContext>(connectionName: "postgresdb");

The preceding code snippet demonstrates how to use the AddAzureNpgsqlDbContext method to register an YourDbContext (that's pooled for performance) instance that uses Azure authentication (Microsoft Entra ID). This "postgresdb" connection name corresponds to a connection string configuration value.

After adding YourDbContext to the builder, you can get the YourDbContext instance using dependency injection. For example, to retrieve your data source object from an example service define it as a constructor parameter and ensure the ExampleService class is registered with the dependency injection container:

public class ExampleService(YourDbContext context)
{
    // Use context...
}

For more information on dependency injection, see .NET dependency injection.

Enrich an Npgsql database context

You may prefer to use the standard Entity Framework method to obtain a database context and add it to the dependency injection container:

builder.Services.AddDbContext<YourDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("postgresdb")
        ?? throw new InvalidOperationException("Connection string 'postgresdb' not found.")));

You have more flexibility when you create the database context in this way, for example:

• You can reuse existing configuration code for the database context without rewriting it for .NET Aspire.
• You can use Entity Framework Core interceptors to modify database operations.
• You can choose not to use Entity Framework Core context pooling, which may perform better in some circumstances.

If you use this method, you can enhance the database context with .NET Aspire-style retries, health checks, logging, and telemetry features by calling the EnrichAzureNpgsqlDbContext method:

builder.EnrichAzureNpgsqlDbContext<YourDbContext>(
    configureSettings: settings =>
    {
        settings.DisableRetry = false;
        settings.CommandTimeout = 30;
    });

The settings parameter is an instance of the AzureNpgsqlEntityFrameworkCorePostgreSQLSettings class.

You might also need to configure specific options of Npgsql, or register a DbContext in other ways. In this case, you do so by calling the EnrichAzureNpgsqlDbContext extension method, as shown in the following example:

var connectionString = builder.Configuration.GetConnectionString("postgresdb");

builder.Services.AddDbContextPool<YourDbContext>(
    dbContextOptionsBuilder => dbContextOptionsBuilder.UseNpgsql(connectionString));

builder.EnrichAzureNpgsqlDbContext<YourDbContext>();

Configuration

The .NET Aspire Azure PostgreSQL EntityFrameworkCore Npgsql integration provides multiple options to configure the database connection based on the requirements and conventions of your project.

Use a connection string

When using a connection string defined in the ConnectionStrings configuration section, you provide the name of the connection string when calling AddAzureNpgsqlDataSource:

builder.AddAzureNpgsqlDbContext<YourDbContext>("postgresdb");

The connection string is retrieved from the ConnectionStrings configuration section, for example, consider the following JSON configuration:

{
  "ConnectionStrings": {
    "postgresdb": "Host=myserver;Database=test"
  }
}

For more information on how to configure the connection string, see the Npgsql connection string documentation.

Use configuration providers

The .NET Aspire Azure PostgreSQL EntityFrameworkCore Npgsql integration supports Microsoft.Extensions.Configuration. It loads the AzureNpgsqlEntityFrameworkCorePostgreSQLSettings from configuration using the Aspire:Npgsql:EntityFrameworkCore:PostgreSQL key. For example, consider the following appsettings.json file that configures some of the available options:

{
  "Aspire": {
    "Npgsql": {
      "EntityFrameworkCore": {
        "PostgreSQL": {
          "DisableHealthChecks": true,
          "DisableTracing": true
        }
      }
    }
  }
}

Use inline delegates

You can configure settings in code, by passing the Action<AzureNpgsqlEntityFrameworkCorePostgreSQLSettings> configureSettings delegate to set up some or all the options inline, for example to disable health checks from code:

builder.AddAzureNpgsqlDbContext<YourDbContext>(
    "postgresdb",
    settings => settings.DisableHealthChecks = true);

Alternatively, you can use the EnrichAzureNpgsqlDbContext extension method to configure the settings:

builder.EnrichAzureNpgsqlDbContext<YourDbContext>(
    settings => settings.DisableHealthChecks = true);

Use the AzureNpgsqlEntityFrameworkCorePostgreSQLSettings.Credential property to establish a connection. If no credential is configured, the DefaultAzureCredential is used.

When the connection string contains a username and password, the credential is ignored.

Troubleshooting

In the rare case that the Username property isn't provided and the integration can't detect it using the application's Managed Identity, Npgsql throws an exception with a message similar to the following:

Npgsql.PostgresException (0x80004005): 28P01: password authentication failed for user ...

In this case you can configure the Username property in the connection string and use EnrichAzureNpgsqlDbContext, passing the connection string in UseNpgsql:

builder.Services.AddDbContextPool<YourDbContext>(
    options => options.UseNpgsql(newConnectionString));

builder.EnrichAzureNpgsqlDbContext<YourDbContext>();

Configure multiple DbContext classes

If you want to register more than one DbContext with different configuration, you can use $"Aspire:Npgsql:EntityFrameworkCore:PostgreSQL:{typeof(TContext).Name}" configuration section name. The json configuration would look like:

{
  "Aspire": {
    "Npgsql": {
      "EntityFrameworkCore": {
        "PostgreSQL": {
          "ConnectionString": "<YOUR CONNECTION STRING>",
          "DisableHealthChecks": true,
          "DisableTracing": true,
          "AnotherDbContext": {
            "ConnectionString": "<ANOTHER CONNECTION STRING>",
            "DisableTracing": false
          }
        }
      }
    }
  }
}

Then calling the AddNpgsqlDbContext method with AnotherDbContext type parameter would load the settings from Aspire:Npgsql:EntityFrameworkCore:PostgreSQL:AnotherDbContext section.

builder.AddAzureNpgsqlDbContext<AnotherDbContext>();

Client integration health checks

By default, .NET Aspire client integrations have health checks enabled for all services. Similarly, many .NET Aspire hosting integrations also enable health check endpoints. For more information, see:

• .NET app health checks in C#
• Health checks in ASP.NET Core

By default, the .NET Aspire PostgreSQL Entity Framework Core integrations handles the following:

• Adds the DbContextHealthCheck, which calls EF Core's CanConnectAsync method. The name of the health check is the name of the TContext type.
• Integrates with the /health HTTP endpoint, which specifies all registered health checks must pass for app to be considered ready to accept traffic

Observability and telemetry

.NET Aspire integrations automatically set up Logging, Tracing, and Metrics configurations, which are sometimes known as the pillars of observability. For more information about integration observability and telemetry, see .NET Aspire integrations overview. Depending on the backing service, some integrations may only support some of these features. For example, some integrations support logging and tracing, but not metrics. Telemetry features can also be disabled using the techniques presented in the Configuration section.

Logging

The .NET Aspire PostgreSQL Entity Framework Core integration uses the following Log categories:

• Microsoft.EntityFrameworkCore.ChangeTracking
• Microsoft.EntityFrameworkCore.Database.Command
• Microsoft.EntityFrameworkCore.Database.Connection
• Microsoft.EntityFrameworkCore.Database.Transaction
• Microsoft.EntityFrameworkCore.Migrations
• Microsoft.EntityFrameworkCore.Infrastructure
• Microsoft.EntityFrameworkCore.Migrations
• Microsoft.EntityFrameworkCore.Model
• Microsoft.EntityFrameworkCore.Model.Validation
• Microsoft.EntityFrameworkCore.Query
• Microsoft.EntityFrameworkCore.Update

Tracing

The .NET Aspire PostgreSQL Entity Framework Core integration will emit the following tracing activities using OpenTelemetry:

• Npgsql

Metrics

The .NET Aspire PostgreSQL Entity Framework Core integration will emit 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

See also

• PostgreSQL docs
• Azure Database for PostgreSQL
• .NET Aspire PostgreSQL Entity Framework Core integration
• .NET Aspire integrations
• .NET Aspire GitHub repo