Edit

Tutorial: Use a PostgreSQL service for development

  • Article

Azure Container Apps allows you to connect to development and production-grade services to provide a wide variety of functionality to your applications.

In this tutorial, you learn to use a development PostgreSQL service with Container Apps.

Azure CLI commands and Bicep template fragments are featured in this tutorial. If you use Bicep, you can add all the fragments to a single Bicep file and deploy the template all at once.

  • Create a Container Apps environment to deploy your service and container apps
  • Create a PostgreSQL service
  • Create and use a command line app to use the dev PostgreSQL service
  • Create a pgweb app
  • Write data to the PostgreSQL database

Prerequisites

Note

For a one command deployment, skip to the last azd template step.

Setup

  1. Define variables for common values.

    RESOURCE_GROUP="postgres-dev"
LOCATION="northcentralus"
ENVIRONMENT="aca-env"
PG_SVC="postgres01"
PSQL_CLI_APP="psql-cloud-cli-app"

  2. Log in to Azure.

    az login

  3. Upgrade the CLI to the latest version.

    az upgrade

  4. Upgrade Bicep to the latest version.

    az bicep upgrade

  5. Add th containerapp extension.

    az extension add --name containerapp --upgrade

  6. Register required namespaces.

    az provider register --namespace Microsoft.App

    az provider register --namespace Microsoft.OperationalInsights

Create a Container Apps environment

  1. Create a resource group.

    az group create \
    --name "$RESOURCE_GROUP" \
    --location "$LOCATION"

  2. Create a Container Apps environment.

    az containerapp env create \
  --name "$ENVIRONMENT" \
  --resource-group "$RESOURCE_GROUP" \
  --location "$LOCATION"

Create a PostgreSQL service

  1. Create a PostgreSQL service.

    az containerapp add-on postgres create \
    --name "$PG_SVC" \
    --resource-group "$RESOURCE_GROUP" \
    --environment "$ENVIRONMENT"

  2. View log output from the Postgres instance

    Use the logs command to view log messages.

    az containerapp logs show \
    --name $PG_SVC \
    --resource-group $RESOURCE_GROUP \
    --follow --tail 30

    Screenshot of container app PostgreSQL service logs.

Create an app to test the service

When you creat the app, you begin by creating a debug app to use the psql CLI to connect to the PostgreSQL instance.

  1. Create a psql app that binds to the PostgreSQL service.

    az containerapp create \
    --name "$PSQL_CLI_APP" \
    --image mcr.microsoft.com/k8se/services/postgres:14 \
    --bind "$PG_SVC" \
    --environment "$ENVIRONMENT" \
    --resource-group "$RESOURCE_GROUP" \
    --min-replicas 1 \
    --max-replicas 1 \
    --command "/bin/sleep" "infinity"

  2. Run the CLI exec command to connect to the test app.

    az containerapp exec \
    --name $PSQL_CLI_APP \
    --resource-group $RESOURCE_GROUP \
    --command /bin/bash

    When you use --bind or serviceBinds on the test app, the connection information is injected into the application environment. Once you connect to the test container, you can inspect the values using the env command.

    env | grep "^POSTGRES_"

POSTGRES_HOST=postgres01
POSTGRES_PASSWORD=AiSf...
POSTGRES_SSL=disable
POSTGRES_URL=postgres://postgres:AiSf...@postgres01:5432/postgres?sslmode=disable
POSTGRES_DATABASE=postgres
POSTGRES_PORT=5432
POSTGRES_USERNAME=postgres
POSTGRES_CONNECTION_STRING=host=postgres01 database=postgres user=postgres password=AiSf...

  3. Us psql to connect to the service

    psql $POSTGRES_URL

    Screenshot of container app using pgsql to connect to a PostgreSQL service.

  4. Create a table named accounts and insert data.

    postgres=# CREATE TABLE accounts (
    user_id serial PRIMARY KEY,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP 
);

postgres=# INSERT INTO accounts (username, email, created_on)
VALUES
('user1', 'user1@example.com', current_timestamp),
('user2', 'user2@example.com', current_timestamp),
('user3', 'user3@example.com', current_timestamp);

postgres=# SELECT * FROM accounts;

    Screenshot of container app using pgsql connect to PostgreSQL and create a table and seed some data.

Using a dev service with an existing app

If you already have an app that uses PostgreSQL, you can change how connection information is loaded.

First, create the following environment variables.

POSTGRES_HOST=postgres01
POSTGRES_PASSWORD=AiSf...
POSTGRES_SSL=disable
POSTGRES_URL=postgres://postgres:AiSf...@postgres01:5432/postgres?sslmode=disable
POSTGRES_DATABASE=postgres
POSTGRES_PORT=5432
POSTGRES_USERNAME=postgres
POSTGRES_CONNECTION_STRING=host=postgres01 database=postgres user=postgres password=AiSf...

Using the CLI (or Bicep) you can update the app to add --bind $PG_SVC to use the dev service.

Binding to the dev service

Deploy pgweb to view and manage the PostgreSQL instance.

See Bicep or azd example.

Screenshot of pgweb Container App connecting to PostgreSQL service.

Deploy all resources

Use the following examples to if you want to deploy all resources at once.

Bicep

The following Bicep template contains all the resources in this tutorial.

You can create a postgres-dev.bicep file with this content.

targetScope = 'resourceGroup'
param location string = resourceGroup().location
param appEnvironmentName string = 'aca-env'
param pgSvcName string = 'postgres01'
param pgsqlCliAppName string = 'psql-cloud-cli-app'

resource logAnalytics 'Microsoft.OperationalInsights/workspaces@2022-10-01' = {
  name: '${appEnvironmentName}-log-analytics'
  location: location
  properties: {
    sku: {
      name: 'PerGB2018'
    }
  }
}

resource appEnvironment 'Microsoft.App/managedEnvironments@2023-04-01-preview' = {
  name: appEnvironmentName
  location: location
  properties: {
    appLogsConfiguration: {
      destination: 'log-analytics'
      logAnalyticsConfiguration: {
        customerId: logAnalytics.properties.customerId
        sharedKey: logAnalytics.listKeys().primarySharedKey
      }
    }
  }
}

resource postgres 'Microsoft.App/containerApps@2023-04-01-preview' = {
name: pgSvcName
  location: location
  properties: {
    environmentId: appEnvironment.id
    configuration: {
      service: {
          type: 'postgres'
      }
    }
  }
}

resource pgsqlCli 'Microsoft.App/containerApps@2023-04-01-preview' = {
  name: pgsqlCliAppName
  location: location
  properties: {
    environmentId: appEnvironment.id
    template: {
      serviceBinds: [
        {
          serviceId: postgres.id
        }
      ]
      containers: [
        {
          name: 'psql'
          image: 'mcr.microsoft.com/k8se/services/postgres:14'
          command: [ '/bin/sleep', 'infinity' ]
        }
      ]
      scale: {
        minReplicas: 1
        maxReplicas: 1
      }
    }
  }
}

resource pgweb 'Microsoft.App/containerApps@2023-04-01-preview' = {
  name: 'pgweb'
  location: location
  properties: {
    environmentId: appEnvironment.id
    configuration: {
      ingress: {
        external: true
        targetPort: 8081
      }
    }
    template: {
      serviceBinds: [
        {
          serviceId: postgres.id
          name: 'postgres'
        }
      ]
      containers: [
        {
          name: 'pgweb'
          image: 'docker.io/sosedoff/pgweb:latest'
          command: [
            '/bin/sh'
          ]
          args: [
            '-c'
            'PGWEB_DATABASE_URL=$POSTGRES_URL /usr/bin/pgweb --bind=0.0.0.0 --listen=8081'
          ]
        }
      ]
    }
  }
}

output pgsqlCliExec string = 'az containerapp exec -n ${pgsqlCli.name} -g ${resourceGroup().name} --revision ${pgsqlCli.properties.latestRevisionName} --command /bin/bash'

output postgresLogs string = 'az containerapp logs show -n ${postgres.name} -g ${resourceGroup().name} --follow --tail 30'

output pgwebUrl string = 'https://${pgweb.properties.configuration.ingress.fqdn}'

Use the Azure CLI to deploy it the template.

RESOURCE_GROUP="postgres-dev"
LOCATION="northcentralus"

az group create \
    --name "$RESOURCE_GROUP" \
    --location "$LOCATION"

az deployment group create -g $RESOURCE_GROUP \
    --query 'properties.outputs.*.value' \
    --template-file postgres-dev.bicep

Azure Developer CLI

A final template is available on GitHub.

Use azd up to deploy the template.

git clone https://github.com/Azure-Samples/aca-dev-service-postgres-azd
cd aca-dev-service-postgres-azd
azd up

Clean up resources

Once you're done, run the following command to delete the resource group that contains your Container Apps resources.

Caution

The following command deletes the specified resource group and all resources contained within it. If resources outside the scope of this tutorial exist in the specified resource group, they will also be deleted.

az group delete \
    --resource-group $RESOURCE_GROUP