Documentation by ClonePartner

Start typing to search.

Connector Reference

PostgreSQL

View Markdown

Configure the postgres connector with pooling and SSL options, query and write tables, and run read-only SQL on a dedicated pool.

PostgreSQL

The postgres connector queries and writes PostgreSQL tables over a connection pool, with a separate read-only pool for dashboard queries.

When to use it

Use postgres as a relational source or destination, and as a mapping database or cursor store for sync tasks. For MySQL-protocol servers use MySQL; for SQL Server use MS SQL Server.

Configuration

Key Type Required Default Description
connection_string string Yes — PostgreSQL connection string (postgres://user:pass@host:5432/db), stored as a secret
max_connections number No 10 Max pool connections
idle_timeout number No 30 Idle connection timeout in seconds
ssl string No — SSL mode: disable, prefer, require, verify-ca, verify-full
readonly_connection_string string No — Optional read-only connection string for dashboard queries (separate pool). Defaults to the primary with default_transaction_read_only=on. Stored as a secret.
readonly_max_connections number No 2 Max connections on the read-only pool

Methods

Method Arguments Returns Behavior
find table (required), filter, select, limit, offset, order_by array Streams matching rows to downstream steps
find_one table (required), filter, select object Returns the first matching row
insert_one table (required), data (required) object Inserts one record and returns it (RETURNING *)
upsert table (required), data (required), conflict_columns (required), update_columns object INSERT ... ON CONFLICT on conflict_columns; updates update_columns (or all columns) on conflict
delete table (required), filter object Deletes rows matching the filter
execute statements (required) object Executes a raw SQL statement on the primary pool
select sql (required), params array Runs a read-only SELECT on the dedicated read pool (SELECT or WITH only)

filter uses the shared JSON filter syntax (gt, gte, lt, lte, ne, in, nin, like, ilike, regex, exists, plus and/or). ilike maps to PostgreSQL ILIKE and regex to the ~ operator.

Example

name: export_active_users
connectors:
  source:
    type: postgres
steps:
  - name: read_users
    type: call_method
    config: |
      {
        "connector": "source",
        "method": "find",
        "args": { "table": "users", "filter": { "status": "active" } }
      }

The matching connectors.yaml entry for CLI runs:

source:
  type: postgres
  config:
    connection_string: ${POSTGRES_CONNECTION_STRING}
    ssl: require

Behavior notes

  • select rejects anything that is not a SELECT or WITH ... SELECT statement and runs on a separate pool whose sessions default to read-only transactions, so dashboard queries cannot mutate data. Point readonly_connection_string at a replica to isolate them further.
  • The connector implements the mapping-DB interface (a resource_mapping table created on first use) and the cursor store, so it can serve as lookup_connector or cursor_store for sync tasks. See Idempotent sync and the mapping DB.
  • insert_one and upsert return the affected row via RETURNING *.