Skip to main content

PostgreSQL

The postgres connector reads a PostgreSQL database with read-only SELECT queries and table reads, and writes tables from DuckDB and Spark job outputs. Use it to pull rows into a source, or to load a job's results into PostgreSQL.

Install​

sw connectors -i postgres

Connect​

Use the connection keys (the common form) or one DSN secret, not both.

source.yaml
kind: source
name: postgres_demo
spec:
config:
pg_host: { type: string, default: localhost }
start_date: { type: date, default: "-7d" }
secrets:
pg_password: { type: string }
auth:
provider: postgres
host: "{{ config.pg_host }}"
database: shop
user: reader
password: "{{ secrets.pg_password }}"
sslmode: require
statement_timeout: 5min
KeyWhat it isRequired / default
hostA host name, an IP address or a unix socket directoryRequired (with keys)
portThe port5432
databaseThe database (a source also accepts libpq's name, dbname)Required (with keys)
userThe role. Use one that can only readRequired (with keys)
passwordThe role's password. SecretOptional
sslmodelibpq's sslmode: disable, allow, prefer, require, verify-ca or verify-fullOptional
optionsMore libpq connection parameters, for example {connect_timeout: "10", application_name: streamwright}Optional
statement_timeoutThe server's statement timeout, for every statement on every engine: seconds, or 500ms, 30s, 5min, 1hOptional
dsnA postgresql:// URL or libpq key=value pairs, instead of the keys above. SecretOptional

With a DSN secret, for example when the whole connection string is one managed secret:

source.yaml
kind: source
name: postgres_demo
spec:
secrets:
pg_dsn: { type: string }
auth:
provider: postgres
dsn: "{{ secrets.pg_dsn }}"

A password inside a DSN is redacted (***) in logs and errors too.

Read data​

A request uses service: database and one of two methods:

MethodArgumentsWhat it does
queryquery, paramsRuns one read-only SELECT. $name placeholders take their values from params
tabletable, schema (default public), columns, whereReads a table: some or all columns, and the rows that match every where condition
streams/orders.yaml
incremental:
cursor_field: updated_at
start: "{{ config.start_date }}"
window: 1d
requests:
- name: raw_orders
operation:
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]
  • The stream reads one day of orders at a time (window: 1d).
  • $since is a bound parameter: its value comes from params and is never pasted into the query text.

In jobs, a DuckDB job uses a read step, and a Spark job the same operation: as a source stream:

steps:
- name: orders # DuckDB job
connection: shop
read: query # or read: table, with table: and schema:
query: SELECT id, total, updated_at FROM orders
EngineWhere a query runsArguments
sourceIn DuckDB, over the attached database (DuckDB SQL)query, params, table, schema, columns, where
DuckDB jobIn PostgreSQL (PostgreSQL SQL)query, or table and schema
Spark jobIn PostgreSQL, over JDBC (PostgreSQL SQL). $name values become escaped literalsAll of the above, plus options (JDBC options such as fetchsize)

Write data​

DuckDB and Spark job outputs write a step's rows into a table. In a DuckDB job, a missing table is created from the data.

outputs:
- name: save_orders # DuckDB job
connection: shop
step: orders
table: analytics.orders
mode: merge
primary_key: [id]

In a Spark job, the output uses operation: {service: database, method: write, arguments: {table, mode, ...}}.

ModeWhat it doesEngines
appendAdds the rowsDuckDB, Spark
overwriteReplaces the tableDuckDB
mergeUpdates the rows that match on primary_key and inserts the rest (INSERT ... ON CONFLICT, so the key needs a unique constraint)DuckDB, Spark
deleteDeletes the rows that match on primary_keySpark

On Spark, update lists the columns a merge updates (default: every column but the key). merge and delete load a staging table and run one statement in one transaction. Without staging_table the run creates and drops a temporary one, which needs CREATE and DROP privileges in the target schema.

Good to know​

  • Read-only queries. On streamwright run, a query is checked before it runs: one statement, no writes, and no system catalogs (pg_catalog, information_schema, DuckDB's own views), files or table functions other than unnest, range and generate_series. The database is attached read-only.
  • Types. Decimals stay exact. Dates and timestamps arrive as text (timestamps with time zone in UTC), so cast them in your SQL.
  • Job state. A DuckDB job can keep its state in PostgreSQL (runtime.state.connection); install pip install "streamwright-postgres[state]". For Spark jobs, install pip install "streamwright-postgres[spark]".
  • Spark connection options. libpq options become JDBC properties under their pgjdbc names (connectTimeout, ApplicationName, gssEncMode).
  • Vendor arguments. libpq connection options pass through unchanged. On Spark, a connection key or database.* argument the connector does not declare becomes a JDBC driver property (for example fetchsize, queryTimeout).

See also​