Dapr support with Postgres

#postgres #cloud

Overview

Dapr supports using PostgreSQL as a configuration store component of type configuration.postgresql, with a stable API (v1) since Dapr runtime version 1.11 ([Dapr Docs][1]).

Component Setup

Component Definition

You create a Dapr component manifest like so:

apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
  name: <YOUR_NAME>
spec:
  type: configuration.postgresql
  version: v1
  metadata:
    - name: connectionString
      value: "<your connection string>"
    - name: table
      value: "<your_configuration_table_name>"
    # Optional metadata fields include:
    - name: timeout
      value: "30s"
    - name: maxConns
      value: "4"
    - name: connectionMaxIdleTime
      value: "5m"
    - name: queryExecMode
      value: "simple_protocol"

Key metadata options:

connectionString (required): Standard PostgreSQL connection string, allowing pool configuration parameters ([Dapr Docs][2]).
table (required): Name of the table to store configuration entries ([Dapr Docs][2]).
Optional tuning metadata:
- timeout (database operation timeout)
- maxConns (connection pool size)
- connectionMaxIdleTime
- queryExecMode: useful for compatibility with certain proxies like PgBouncer ([Dapr Docs][2]).

Database Schema & Triggers

Table Schema

You must create a table with the following structure:

CREATE TABLE IF NOT EXISTS <table_name> (
  KEY VARCHAR NOT NULL,
  VALUE VARCHAR NOT NULL,
  VERSION VARCHAR NOT NULL,
  METADATA JSON
);

KEY: configuration attribute key
VALUE: associated value
VERSION: version identifier
METADATA: optional JSON metadata ([Dapr Docs][2])

Notification Trigger

To enable subscription notifications, define a trigger function and create a trigger:

CREATE OR REPLACE FUNCTION notify_event() RETURNS TRIGGER AS $$
DECLARE 
    data json;
    notification json;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        data = row_to_json(OLD);
    ELSE
        data = row_to_json(NEW);
    END IF;
    notification = json_build_object(
                      'table', TG_TABLE_NAME,
                      'action', TG_OP,
                      'data', data);
    PERFORM pg_notify('config', notification::text);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER config
AFTER INSERT OR UPDATE OR DELETE ON <configtable>
FOR EACH ROW EXECUTE PROCEDURE notify_event();

You will then use the same channel name (e.g., "config") in pg_notify when subscribing ([Dapr Docs][2]).

Dapr Configuration API Integration

Dapr provides REST APIs to interact with the configuration store:

Get Configuration
GET http://localhost:<daprPort>/v1.0/configuration/<storeName>?key=...
- Returns key-value pairs as JSON ([Dapr Docs][3]).
Subscribe to Changes
GET http://localhost:<daprPort>/v1.0/configuration/<storeName>/subscribe?...&metadata.pgNotifyChannel=<channel>
- metadata.pgNotifyChannel must match the channel used in your PostgreSQL trigger ("config" in the example) ([Dapr Docs][3]).
Unsubscribe
GET http://localhost:<daprPort>/v1.0/configuration/<storeName>/<subscription-id>/unsubscribe
- Use the subscription ID returned earlier to cancel listening ([Dapr Docs][3]).

DEV Community