PostgreSQL

The postgres reader runs read-only SELECT queries on a PostgreSQL database. Use either connection keys (the common form) or a single DSN secret — not both.

Connection keys

Use when the connection is per-environment configuration and only the password is secret. This is what the demo uses.

source.yaml

spec:
  config:
    pg_host: { type: string, default: localhost }
  secrets:
    pg_password: { type: string }
auth:
  provider: postgres
  host: "{{ config.pg_host }}"
  port: 5432                      # optional (default 5432)
  database: shop                  # or its alias `dbname`
  user: reader
  password: "{{ secrets.pg_password }}"   # the only credential, a secret only
  sslmode: require                # disable | allow | prefer | require | verify-ca | verify-full
  options: { connect_timeout: "10", application_name: streamwright }   # extra libpq parameters
  statement_timeout: 5min

DSN secret

Use when the whole connection string is one managed secret, or needs libpq features the keys don't cover.

source.yaml

auth:
  provider: postgres
  dsn: "{{ secrets.pg_dsn }}"     # a libpq URL or keyword DSN, from secrets only

Queries

streams/orders.yaml

requests:
  - name: raw_orders
    sdk: postgres
    service: database
    method: query
    arguments:
      query: |
        SELECT id, total::VARCHAR AS total, updated_at
        FROM public.orders
        WHERE updated_at >= $since
      params: { since: "{{ window.start }}" }

transform:
  mode: page
  steps:
    - name: orders
      select: |
        SELECT
          (record->>'id')::BIGINT             AS order_id,
          (record->>'total')::DECIMAL(18,2)   AS total,
          (record->>'updated_at')::TIMESTAMPTZ AS updated_at
        FROM raw_orders

export:
  orders:
    step: orders
    primary_key: [order_id]

Queries are DuckDB SQL over the attached database, read-only (one SELECT per request), with values bound as parameters — never pasted into the query.