Get started with the PostgreSQL integrations

Select your programming language to get started

C# Python JavaScript

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 Aspire PostgreSQL integration provides a way to connect to existing PostgreSQL databases, or create new instances from the docker.io/library/postgres container image.

In this introduction, you’ll see how to install and use the Aspire PostgreSQL integrations in a simple configuration. If you already have this knowledge, see PostgreSQL Hosting integration for full reference details.

Note

To follow this guide, you must have created an Aspire solution to work with. To learn how to do that, see Build your first Aspire app.

Set up hosting integration

To begin, install the Aspire PostgreSQL Hosting integration in your Aspire AppHost project. This integration allows you to create and manage PostgreSQL database instances from your Aspire hosting projects:

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="*" />

Next, in the AppHost project, create instances of PostgreSQL server and database resources, then pass the database to the consuming client projects:

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")
                            .WaitFor(postgresdb)
                            .WithReference(postgresdb);

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

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

var exampleProject = builder.AddUvicornApp("api", "./api", "main.app")
                            .WithExternalHttpEndpoints()
                            .WaitFor(postgresdb)
                            .WithReference(postgresdb);

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

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

var api = builder.AddNodeApp("api", "./api", scriptPath: "index.js")
                 .WithExternalHttpEndpoints()
                 .WaitFor(postgresdb)
                 .WithReference(postgresdb);

Tip

This is the simplest implementation of PostgreSQL resources in the AppHost. There are many more options you can choose from to address your requirements. For full details, see PostgreSQL Hosting integration.

Use the integration in client projects

Now that the hosting integration is ready, the next step is to install and configure the client integration in any projects that need to use it.

Set up client projects

In each of these consuming client projects, install the Aspire PostgreSQL client integration:

.NET CLI

.NET CLI — Add Aspire.Npgsql package

dotnet add package Aspire.Npgsql

Program.cs (C# file-based app)

Program.cs

#:package Aspire.Npgsql@*

PackageReference (*.csproj)

XML — Add Aspire.Npgsql package reference

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

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

C# — Program.cs

builder.AddNpgsqlDataSource(connectionName: "postgresdb");

Tip

The connectionName parameter must match the name used when adding the PostgreSQL server resource in the AppHost project. For more information, see Set up hosting integration.

To interact with PostgreSQL databases in your Python consuming projects, you need to include a PostgreSQL adapter library. Two popular options are psycopg (synchronous) and asyncpg (asynchronous). You can install either of these libraries using pip. In this article, example code uses psycopg:

pip install psycopg[binary]

Ensure that you import psycopg in code files that interact with the database. You should also import the os module to access environment variables:

Python - Import psycopg

import psycopg
import os

To interact with PostgreSQL databases in your JavaScript consuming projects, you need to include a PostgreSQL client library. A popular option is pg. You can install this library using npm:

npm install pg

Ensure that you import pg in code files that interact with the database. This code also uses the express library to access environment variables:

JavaScript - Import pg

import pg from 'pg';
import express from 'express';

Use injected PostgreSQL properties

In the AppHost, when you used the WithReference method to pass a PostgreSQL server or database resource to a consuming client project, Aspire injects several configuration properties that you can use in the consuming project.

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

Use the GetValue() method to obtain these environment variables in consuming projects:

C# - Obtain configuration properties

string hostname = builder.Configuration.GetValue<string>("POSTGRESDB_HOST");
string databaseport = builder.Configuration.GetValue<string>("POSTGRESDB_PORT");
string jdbcconnectionstring = builder.Configuration.GetValue<string>("POSTGRESDB_JDBCCONNECTIONSTRING");
string databasename = builder.Configuration.GetValue<string>("POSTGRESDB_DATABASE");

Use the os.getenv() method to obtain these environment variables in consuming projects:

Python - Obtain configuration properties

postgres_uri = os.getenv("POSTGRESDB_URI")
postgres_name = os.getenv("POSTGRESDB_DATABASE", "db")

Use the process.env method to obtain these environment variables in consuming projects:

JavaScript - Obtain configuration properties

const pguser = process.env.POSTGRESDB_USERNAME;
const pgpassword = process.env.POSTGRESDB_PASSWORD;
const pghost = process.env.POSTGRESDB_HOST;
const pgport = process.env.POSTGRESDB_PORT;
const pgdatabase = process.env.POSTGRESDB_DATABASE;

Tip

The full set of properties that Aspire injects depends on whether you passed a PostgreSQL server or database resource. For more information, see Properties of the PostgreSQL resources.

Use PostgreSQL resources in client code

Now that you’ve added NpgsqlDataSource to the builder in the consuming project, you can use the PostgreSQL resource to get and store data. Get the NpgsqlDataSource 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:

C# — ExampleService.cs

public class ExampleService(NpgsqlDataSource dataSource)
{
    // Use dataSource to query the database...
}

Having obtained the data source, you can work with the PostgreSQL database as you would in any other C# application.

Use the information you have obtained about the PostgreSQL resource to connect to the database. Here is an example of how to connect using psycopg:

Python - Connect to PostgreSQL

async with await psycopg.AsyncConnection.connect(postgres_uri, autocommit=True) as conn:

Having obtained the connection, you can work with the PostgreSQL database as you would in any other Python application.

Use the information you have obtained about the PostgreSQL resource to connect to the database. Here is an example of how to connect using pg:

JavaScript - Connect to PostgreSQL

const client = new pg.Client({
  user: pguser,
  host: pghost,
  database: pgdatabase,
  password: pgpassword,
  port: pgport,
});

client.connect();

Having obtained the connection, you can work with the PostgreSQL database as you would in any other JavaScript application.

Next steps

Now, that you have an Aspire app with PostgreSQL integrations up and running, you can use the following reference documents to learn how to configure and interact with the PostgreSQL resources:

Understand the PostgreSQL hosting integration Discover how to configure persistence, pgAdmin resources, and more.

Understand the PostgreSQL client integration Discover how to configure keyed clients, client integration health checks, and more.

Understand the PostgreSQL community extensions Discover how to add the DbGate management user interface, and more.