Edit

Share via

.NET Aspire Azure PostgreSQL Entity Framework Core integration

  • Article

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:

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

For more information, see dotnet add package.

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.

Important

By default, AddAzurePostgresFlexibleServer configures Microsoft Entra ID authentication. This requires changes to applications that need to connect to these resources. For more information, see Client integration.

Tip

When you call AddAzurePostgresFlexibleServer, 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.

Generated provisioning 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, instead 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:


Toggle Azure PostgreSQL Bicep. 

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

param principalId string

param principalType string

param principalName string

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
}

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

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

The preceding Bicep is a module that provisions an Azure PostgreSQL flexible server with the following defaults:

  • authConfig: The authentication configuration of the PostgreSQL server. The default is ActiveDirectoryAuth enabled and PasswordAuth disabled.
  • availabilityZone: The availability zone of the PostgreSQL server. The default is 1.
  • backup: The backup configuration of the PostgreSQL server. The default is BackupRetentionDays set to 7 and GeoRedundantBackup set to Disabled.
  • highAvailability: The high availability configuration of the PostgreSQL server. The default is Disabled.
  • storage: The storage configuration of the PostgreSQL server. The default is StorageSizeGB set to 32.
  • version: The version of the PostgreSQL server. The default is 16.
  • sku: The SKU of the PostgreSQL server. The default is Standard_B1ms.
  • tags: The tags of the PostgreSQL server. The default is aspire-resource-name set to the name of the Aspire resource, in this case 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 can be customized to meet your specific requirements.

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—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 Azure Cosmos DB resource:

builder.AddAzureCosmosDB("cosmos-db")
    .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:

There are many more configuration options available to customize the PostgreSQL flexible server resource. For more information, see Azure.Provisioning.PostgreSql. For more information, see 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...

Note

Connection strings are used to represent a wide range of connection information, including database connections, message brokers, endpoint URIs, and other services. In .NET Aspire nomenclature, the term "connection string" is used to represent any kind of connection information.

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.

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, such adding pgAdmin, pgWeb, adding a data volume or data bind mount, and adding an init bind mount. For more information, see the .NET 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:

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.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.Npgsql.EntityFrameworkCore.PostgreSQL

Add Npgsql database context

In the Program.cs file of your client-consuming project, call the AddNpgsqlDbContext extension method on any IHostApplicationBuilder to register your DbContext subclass for use via the dependency injection container. The method takes a connection name parameter.

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

Tip

The connectionName parameter must match the name used when adding the PostgreSQL server resource in the app host project. For more information, see Add PostgreSQL server resource.

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.

Add Npgsql database context with enrichment

To enrich the DbContext with additional services, such as automatic retries, health checks, logging and telemetry, call the EnrichNpgsqlDbContext method:

builder.EnrichNpgsqlDbContext<YourDbContext>(
    connectionName: "postgresdb",
    configureSettings: settings =>
    {
        settings.DisableRetry = false;
        settings.CommandTimeout = 30;
    });

The settings parameter is an instance of the NpgsqlEntityFrameworkCorePostgreSQLSettings class.

Configuration

The .NET Aspire PostgreSQL Entity Framework Core integration provides multiple configuration approaches and options to meet the requirements and conventions of your project.

Use a connection string

When using a connection string from the ConnectionStrings configuration section, you provide the name of the connection string when calling the AddNpgsqlDbContext method:

builder.AddNpgsqlDbContext<MyDbContext>("pgdb");

The connection string is retrieved from the ConnectionStrings configuration section:

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

The EnrichNpgsqlDbContext won't make use of the ConnectionStrings configuration section since it expects a DbContext to be registered at the point it's called.

For more information, see the ConnectionString.

Use configuration providers

The .NET Aspire PostgreSQL Entity Framework Core integration supports Microsoft.Extensions.Configuration. It loads the NpgsqlEntityFrameworkCorePostgreSQLSettings from configuration files such as appsettings.json by using the Aspire:Npgsql:EntityFrameworkCore:PostgreSQL key. If you have set up your configurations in the Aspire:Npgsql:EntityFrameworkCore:PostgreSQL section you can just call the method without passing any parameter.

The following example shows an appsettings.json file that configures some of the available options:

{
  "Aspire": {
    "Npgsql": {
      "EntityFrameworkCore": {
        "PostgreSQL": {
          "ConnectionString": "Host=myserver;Database=postgresdb",
          "DisableHealthChecks": true,
          "DisableTracing": true
        }
      }
    }
  }
}

For the complete PostgreSQL Entity Framework Core client integration JSON schema, see Aspire.Npgsql.EntityFrameworkCore.PostgreSQL/ConfigurationSchema.json.

Use inline delegates

You can also pass the Action<NpgsqlEntityFrameworkCorePostgreSQLSettings> delegate to set up some or all the options inline, for example to set the ConnectionString:

builder.AddNpgsqlDbContext<YourDbContext>(
    "pgdb",
    static settings => settings.ConnectionString = "<YOUR CONNECTION STRING>");

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.AddNpgsqlDbContext<AnotherDbContext>();

Health checks

By default, .NET Aspire integrations enable health checks for all services. For more information, see .NET Aspire integrations overview.

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

Add Azure authenticated Npgsql client

By default, when you call AddAzurePostgresFlexibleServer in your PostgreSQL hosting integration, it requires 📦 Azure.Identity NuGet package to enable authentication:

dotnet add package Azure.Identity

The PostgreSQL connection can be consumed using the client integration and Azure.Identity:

builder.AddNpgsqlDbContext<YourDbContext>(
    "postgresdb", 
    configureDataSourceBuilder: (dataSourceBuilder) =>
{
    if (!string.IsNullOrEmpty(dataSourceBuilder.ConnectionStringBuilder.Password))
    {
        return;
    }

    dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
    {
        var credentials = new DefaultAzureCredential();
        var token = await credentials.GetTokenAsync(
            new TokenRequestContext([
                "https://ossrdbms-aad.database.windows.net/.default"
            ]), ct);

        return token.Token;
    },
    TimeSpan.FromHours(24),
    TimeSpan.FromSeconds(10));
});

The preceding code snippet demonstrates how to use the DefaultAzureCredential class from the Azure.Identity package to authenticate with Microsoft Entra ID and retrieve a token to connect to the PostgreSQL database. The UsePeriodicPasswordProvider method is used to provide the token to the connection string builder.

See also