Azure PostgreSQL Entity Framework Core 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 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 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 AppHost project, install the 📦 Aspire.Hosting.Azure.PostgreSQL NuGet package:

Aspire CLI

Aspire CLI — Добавить пакет Aspire.Hosting.Azure.PostgreSQL

aspire add azure-postgresql

Aspire CLI интерактивен; выберите подходящий результат поиска при запросе:

Aspire CLI — Пример вывода

Select an integration to add:

> azure-postgresql (Aspire.Hosting.Azure.PostgreSQL)
> Other results listed as selectable options...

Заметка

Если всего один результат, он выбирается автоматически. Всё равно подтвердите версию.

apphost.cs (C# file-based app)

C# — AppHost.cs

#:package Aspire.Hosting.Azure.PostgreSQL@*

PackageReference (*.csproj)

XML — Добавить ссылку на пакет Aspire.Hosting.Azure.PostgreSQL

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

Add an Azure Database for PostgreSQL resource

To add an Azure Database for PostgreSQL resource to your AppHost project, call the AddAzurePostgres method providing a name:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

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

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

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

The preceding code adds an Azure PostgreSQL resource named postgres with a database named database to the AppHost project. The WithReference method passes the connection information to the ExampleProject project.

Осторожно

When you call AddAzurePostgres, it implicitly calls AddAzureProvisioning—which adds support for generating Azure resources dynamically during app startup. The app must configure the appropriate subscription and location. For more information, see Local provisioning: Configuration.

Connect to an existing Azure PostgreSQL flexible server

You might have an existing Azure Database for PostgreSQL service that you want to connect to. You can chain a call to annotate that your AzurePostgresResource is an existing resource:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

var existingPostgresName = builder.AddParameter("existingPostgresName");
var existingPostgresResourceGroup = builder.AddParameter("existingPostgresResourceGroup");

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
    .AsExisting(existingPostgresName, existingPostgresResourceGroup);

builder.AddProject<Projects.ExampleProject>()
    .WithReference(postgres);

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

For more information on treating Azure PostgreSQL resources as existing resources, see Use existing Azure resources.

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:

C# — AppHost.cs

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.

Tip

The RunAsContainer method is useful for local development and testing. The API exposes an optional delegate that enables you to customize the underlying PostgresServerResource configuration. For example, you can add pgAdmin and pgWeb, add a data volume or data bind mount, and add an init bind mount. For more information, see the Aspire PostgreSQL hosting integration section.

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:

C# — AppHost.cs

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 AppHost as parameters, and the WithPasswordAuthentication method is called to configure the Azure PostgreSQL server to use password authentication.

Provisioning-generated Bicep

If you’re new to Bicep, it’s a domain-specific language for defining Azure resources. With 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:

Generated Bicep — postgres.bicep

@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:

Generated Bicep — postgres-flexible-roles.bicep

@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 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 using the ConfigureInfrastructure API:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

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 resource. For more information, see Azure.Provisioning customization.

Client integration

To get started with the 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 Aspire PostgreSQL Entity Framework Core client integration registers your desired DbContext subclass instances that you can use to interact with PostgreSQL.

.NET CLI

.NET CLI — Add Aspire.Azure.Npgsql.EntityFrameworkCore.PostgreSQL package

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

Program.cs (C# file-based app)

Program.cs

#:package Aspire.Azure.Npgsql.EntityFrameworkCore.PostgreSQL@*

PackageReference (*.csproj)

XML — Add Aspire.Azure.Npgsql.EntityFrameworkCore.PostgreSQL package reference

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

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

C# — Program.cs

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

Tip

The connectionName parameter must match the name used when adding the PostgreSQL server resource in the AppHost project.

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:

C# — Program.cs

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

Note

The connection string name that you pass to the GetConnectionString method must match the name used when adding the PostgreSQL server resource in the AppHost project. For more information, see Add PostgreSQL server resource.

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 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 Aspire retries, health checks, logging, and telemetry features by calling the EnrichAzureNpgsqlDbContext method:

C# — Program.cs

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 System.Data.Entity.DbContext in other ways. In this case, you do so by calling the EnrichAzureNpgsqlDbContext extension method, as shown in the following example:

C# — Program.cs

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

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

builder.EnrichAzureNpgsqlDbContext<YourDbContext>();

Configuration

The 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:

C# — Program.cs

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

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

JSON — appsettings.json

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

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

Note

The username and password are automatically inferred from the credential provided in the settings.

Use configuration providers

The 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:

JSON — appsettings.json

{
  "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:

C# — Program.cs

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:

C# — Program.cs

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:

JSON — appsettings.json

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

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

C# — Program.cs

builder.AddAzureNpgsqlDbContext<AnotherDbContext>();

By default, the 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

Aspire integrations automatically set up Logging, Tracing, and Metrics configurations.

Logging

The 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 Aspire PostgreSQL Entity Framework Core integration will emit the following tracing activities using OpenTelemetry:

• Npgsql

Metrics

The 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