PostgreSQL Hosting integration

To get started with the Aspire PostgreSQL integrations, follow the Get started with PostgreSQL integrations guide. If you want to learn how to use the PostgreSQL Entity Framework Core (EF Core) client integration, see Get started with the PostgreSQL Entity Framework Core integrations.

This article includes full details on the Aspire PostgreSQL Hosting integration, which allows you to model PostgreSQL server and database resources in your AppHost project.

Installation

The PostgreSQL hosting integration models various PostgreSQL resources as the following types.

• PostgresServerResource
• PostgresDatabaseResource
• PostgresPgAdminContainerResource
• PostgresPgWebContainerResource

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

Aspire CLI

Aspire CLI — Add Aspire.Hosting.PostgreSQL package

aspire add postgresql

The Aspire CLI is interactive, be sure to select the appropriate search result when prompted:

Aspire CLI — Example output prompt

Select an integration to add:

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

Note

If there's only one result, the CLI selects it automatically. You'll still need to confirm the version.

apphost.cs (C# file-based app)

C# — AppHost.cs

#:package Aspire.Hosting.PostgreSQL@*

PackageReference (*.csproj)

XML — Add Aspire.Hosting.PostgreSQL package reference

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

Add PostgreSQL server resource

In your AppHost 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:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

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

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

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

1. When 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 database instance (the postgresdb variable) are used to add a dependency to the ExampleProject.

2. When adding a database resource to the app model, the database is created if it doesn’t already exist. The creation of the database relies on the AppHost eventing APIs, specifically ResourceReadyEvent. In other words, when the postgres resource is ready, the event is raised and the database resource is created.

3. The PostgreSQL server resource includes default credentials with a username of "postgres" and randomly generated password using the CreateDefaultPasswordParameter method.

4. The WithReference method configures a connection in the ExampleProject named "messaging".

Tip

If you’d rather connect to an existing PostgreSQL server, chain a call to AsExisting instead—passing the appropriate parameters.

Note

When you call WithReference in the AppHost to pass PostgreSQL resources to a consuming project, Aspire makes several properties available to that project, such as connection strings, URIs, and port numbers. For a complete list of these properties, see Properties of the PostgreSQL resources.

Add PostgreSQL resource with database scripts

By default, when you add a PostgresDatabaseResource, it relies on the following script to create the database:

SQL — Default database creation script

CREATE DATABASE "<QUOTED_DATABASE_NAME>"

To alter the default script, chain a call to the WithCreationScript method on the database resource builder:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

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

var databaseName = "app_db";
var creationScript = $$"""
    -- Create the database
    CREATE DATABASE {{databaseName}};

    """;

var db = postgres.AddDatabase(databaseName)
                 .WithCreationScript(creationScript);

builder.AddProject<Projects.ExampleProject>()
       .WithReference(db)
       .WaitFor(db);

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

The preceding example creates a database named app_db. The script is run when the database resource is created. The script is passed as a string to the WithCreationScript method, which is then run in the context of the PostgreSQL resource.

Note

The connect to a database command (\c) isn’t supported when using the creation script.

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:

C# — AppHost.cs

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.

Configure the pgAdmin host port

To configure the host port for the pgAdmin container, call the WithHostPort method on the PostgreSQL server resource. The following example shows how to configure the host port for the pgAdmin container:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithPgAdmin(pgAdmin => pgAdmin.WithHostPort(5050));

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

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

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

The preceding code adds and configures the host port for the pgAdmin container. The host port is otherwise randomly assigned.

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:

C# — AppHost.cs

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.

Configure the pgWeb host port

To configure the host port for the pgWeb container, call the WithHostPort method on the PostgreSQL server resource. The following example shows how to configure the host port for the pgAdmin container:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithPgWeb(pgWeb => pgWeb.WithHostPort(5050));

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

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

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

The preceding code adds and configures the host port for the pgWeb container. The host port is otherwise randomly assigned.

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:

C# — AppHost.cs

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.

Caution

Some database integrations, including the Aspire PostgreSQL integration, can’t successfully use data volumes after deployment to Azure Container Apps (ACA). This is because ACA uses Server Message Block (SMB) to connect containers to data volumes, and some systems can’t use this connection. In the Aspire Dashboard, a database affected by this issue has a status of Activating or Activation Failed but is never listed as Running.

You can resolve the problem by using the managed service Azure Database for PostgreSQL to host the deployed database instead of a container in ACA, which is the recommended approach regardless of this issue. The following AppHost code shows how to deploy a database to Azure Database for PostgreSQL, but run it as a container, with a data volume, during development:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddAzurePostgresFlexibleServer("postgres")
                    .RunAsContainer(container =>
                    {
                        container.WithDataVolume();
                    });

builder.Build().Run();

Add PostgreSQL server resource with data bind mount

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

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

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

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

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

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

Note

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:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
                      .WithInitBindMount(@"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\Init on Windows (or /PostgreSQL/Init 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:

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.AddPostgres("postgres", username, password);
var postgresdb = postgres.AddDatabase("postgresdb");

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

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

Connection properties

When you reference a PostgreSQL resource using WithReference, the following connection properties are made available to the consuming project:

PostgreSQL server

The PostgreSQL server resource exposes the following connection properties:

Property Name	Description
Host	The hostname or IP address of the PostgreSQL server
Port	The port number the PostgreSQL server is listening on
Username	The username for authentication
Password	The password for authentication
Uri	The connection URI in postgresql:// format, with the format postgresql://{Username}:{Password}@{Host}:{Port}
JdbcConnectionString	JDBC-format connection string, with the format jdbc:postgresql://{Host}:{Port}. User and password credentials are provided as separate Username and Password properties.

Example connection strings:

Uri: postgresql://postgres:p%40ssw0rd1@localhost:5432
JdbcConnectionString: jdbc:postgresql://localhost:5432

PostgreSQL database

The PostgreSQL database resource inherits all properties from its parent PostgresServerResource and adds:

Property Name	Description
Uri	The connection URI with the database name, with the format postgresql://{Username}:{Password}@{Host}:{Port}/{DatabaseName}
JdbcConnectionString	JDBC connection string with database name, with the format jdbc:postgresql://{Host}:{Port}/{DatabaseName}. User and password credentials are provided as separate Username and Password properties.
DatabaseName	The name of the database

Example connection strings:

Uri: postgresql://postgres:p%40ssw0rd1@localhost:5432/catalog
JdbcConnectionString: jdbc:postgresql://localhost:5432/catalog

Note

Aspire exposes each property as an environment variable named [RESOURCE]_[PROPERTY]. For instance, the Uri property of a resource called db1 becomes DB1_URI.

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.

Using with non-.NET applications

The PostgreSQL hosting integration can be used with any application technology, not just .NET applications. When you use WithReference to reference a PostgreSQL resource, connection information is automatically injected as environment variables into the referencing application.

For applications that don’t use the client integration, you can access the connection information through environment variables. Here’s an example of how to configure environment variables for a non-.NET application:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

var postgres = builder.AddPostgres("postgres")
    .WithLifetime(ContainerLifetime.Persistent);

var database = postgres.AddDatabase("myDatabase");

// Example: Configure a non-.NET application with PostgreSQL access
var app = builder.AddExecutable("my-app", "node", "app.js", ".")
    .WithReference(database) // Provides ConnectionStrings__myDatabase
    .WithEnvironment(context =>
    {
        // Additional individual connection details as environment variables
        context.EnvironmentVariables["POSTGRES_HOST"] = postgres.Resource.PrimaryEndpoint.Property(EndpointProperty.Host);
        context.EnvironmentVariables["POSTGRES_PORT"] = postgres.Resource.PrimaryEndpoint.Property(EndpointProperty.Port);
        context.EnvironmentVariables["POSTGRES_USER"] = postgres.Resource.UserNameParameter;
        context.EnvironmentVariables["POSTGRES_PASSWORD"] = postgres.Resource.PasswordParameter;
        context.EnvironmentVariables["POSTGRES_DATABASE"] = database.Resource.DatabaseName;
    });

builder.Build().Run();

This configuration provides the non-.NET application with several environment variables:

• ConnectionStrings__myDatabase: The complete PostgreSQL connection string
• POSTGRES_HOST: The hostname/IP address of the PostgreSQL server
• POSTGRES_PORT: The port number the PostgreSQL server is listening on
• POSTGRES_USER: The database username
• POSTGRES_PASSWORD: The dynamically generated password
• POSTGRES_DATABASE: The name of the database

Your non-.NET application can then read these environment variables to connect to the PostgreSQL database using the appropriate database driver for that technology (for example, psycopg2 for Python, pg for Node.js, or pq for Go).