Edit

Share via

.NET Aspire PostgreSQL Entity Framework Core integration

  • Article

Includes: Hosting integration and Client integration

PostgreSQL is a powerful, open source object-relational database system with many years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. The .NET Aspire PostgreSQL Entity Framework Core integration provides a way to connect to existing PostgreSQL databases, or create new instances from .NET with the docker.io/library/postgres container image.

Hosting integration

The PostgreSQL hosting integration models a PostgreSQL server as the PostgresServerResource type. To access this type and APIs that allow you to add it to your 📦 Aspire.Hosting.PostgreSQL NuGet package in the app host project.

dotnet add package Aspire.Hosting.PostgreSQL

For more information, see dotnet add package or Manage package dependencies in .NET applications.

Add PostgreSQL server resource

In your app host project, call AddPostgres on the builder instance to add a PostgreSQL server resource then call AddDatabase on the postgres instance to add a database resource as shown in the following example:

var builder = DistributedApplication.CreateBuilder(args);

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

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

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

When .NET Aspire adds a container image to the app host, as shown in the preceding example with the docker.io/library/postgres image, it creates a new PostgreSQL server instance on your local machine. A reference to your PostgreSQL server and your PostgreSQL database instance (the postgresdb variable) are used to add a dependency to the ExampleProject. The PostgreSQL server resource includes default credentials with a username of "postgres" and randomly generated password using the CreateDefaultPasswordParameter method.

The WithReference method configures a connection in the ExampleProject named "messaging". For more information, see Container resource lifecycle.

Tip

If you'd rather connect to an existing PostgreSQL server, call AddConnectionString instead. For more information, see Reference existing resources.

Add PostgreSQL pgAdmin resource

When adding PostgreSQL resources to the builder with the AddPostgres method, you can chain calls to WithPgAdmin to add the dpage/pgadmin4 container. This container is a cross-platform client for PostgreSQL databases, that serves a web-based admin dashboard. Consider the following example:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithPgAdmin();

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

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

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

The preceding code adds a container based on the docker.io/dpage/pgadmin4 image. The container is used to manage the PostgreSQL server and database resources. The WithPgAdmin method adds a container that serves a web-based admin dashboard for PostgreSQL databases.

Add PostgreSQL pgWeb resource

When adding PostgreSQL resources to the builder with the AddPostgres method, you can chain calls to WithPgWeb to add the sosedoff/pgweb container. This container is a cross-platform client for PostgreSQL databases, that serves a web-based admin dashboard. Consider the following example:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithPgWeb();

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

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

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

The preceding code adds a container based on the docker.io/sosedoff/pgweb image. All registered PostgresDatabaseResource instances are used to create a configuration file per instance, and each config is bound to the pgweb container bookmark directory. For more information, see PgWeb docs: Server connection bookmarks.

Add PostgreSQL server resource with data volume

To add a data volume to the PostgreSQL server resource, call the WithDataVolume method on the PostgreSQL server resource:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithDataVolume(isReadOnly: false);

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

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

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

The data volume is used to persist the PostgreSQL server data outside the lifecycle of its container. The data volume is mounted at the /var/lib/postgresql/data path in the PostgreSQL server container and when a name parameter isn't provided, the name is generated at random. For more information on data volumes and details on why they're preferred over bind mounts, see Docker docs: Volumes.

Add PostgreSQL server resource with data bind mount

To add a data bind mount to the PostgreSQL server resource, call the WithDataBindMount method:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithDataBindMount(
                          source: @"C:\PostgreSQL\Data",
                          isReadOnly: false);

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

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

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

Important

Data bind mounts have limited functionality compared to volumes, which offer better performance, portability, and security, making them more suitable for production environments. However, bind mounts allow direct access and modification of files on the host system, ideal for development and testing where real-time changes are needed.

Data bind mounts rely on the host machine's filesystem to persist the PostgreSQL server data across container restarts. The data bind mount is mounted at the C:\PostgreSQL\Data on Windows (or /PostgreSQL/Data on Unix) path on the host machine in the PostgreSQL server container. For more information on data bind mounts, see Docker docs: Bind mounts.

Add PostgreSQL server resource with init bind mount

To add an init bind mount to the PostgreSQL server resource, call the WithInitBindMount method:

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithInitBindMount(source: @"C:\PostgreSQL\Init");

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

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

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

The init bind mount relies on the host machine's filesystem to initialize the PostgreSQL server database with the containers init folder. This folder is used for initialization, running any executable shell scripts or .sql command files after the postgres-data folder is created. The init bind mount is mounted at the C:\PostgreSQL\Data on Windows (or /PostgreSQL/Data on Unix) path on the host machine in the PostgreSQL server container.

Add PostgreSQL server resource with parameters

When you want to explicitly provide the username and password used by the container image, you can provide these credentials as parameters. Consider the following alternative example:

var builder = DistributedApplication.CreateBuilder(args);

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

var postgres = builder.AddPostgres("postgres", username, password);
var postgresdb = postgres.AddDatabase("postgresdb");

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

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

For more information on providing parameters, see External parameters.

Hosting integration health checks

The PostgreSQL hosting integration automatically adds a health check for the PostgreSQL server resource. The health check verifies that the PostgreSQL server is running and that a connection can be established to it.

The hosting integration relies on the 📦 AspNetCore.HealthChecks.Npgsql NuGet package.

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",
          "DbContextPooling": true,
          "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>",
          "DbContextPooling": true,
          "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.Infrastructure
  • 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

Azure PostgreSQL hosting integration

To deploy your PostgreSQL resources to Azure, install the 📦 Aspire.Hosting.Azure.PostgreSQL NuGet package:

dotnet add package Aspire.Hosting.Azure.PostgreSQL

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.

Add Azure authenticated Npgsql client

By default, when you call AddAzurePostgresFlexibleServer in your PostgreSQL hosting integration, it configures 📦 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