database (output)

The database output executes a SQL query for each record (or all of them inside one transaction). Values are bound through the parameters list — never spliced into the SQL text — with optional transactional wrapping.

Minimal example

output:
  type: database
  connectionStringRef: ${WAREHOUSE_DATABASE_URL}
  query: |
    INSERT INTO orders (id, email, amount)
    VALUES ($1, $2, $3)
    ON CONFLICT (id) DO UPDATE SET
      email = EXCLUDED.email,
      amount = EXCLUDED.amount
  parameters:
    - record.id
    - record.email
    - record.amount

Options

Query or query file

Provide one of query (inline) or queryFile (external). Inline is convenient for short statements; external files are clearer for anything beyond a one-liner.

queryFile: ./sql/upsert_order.sql
parameters:
  - record.id
  - record.email
  - record.amount
  - record.paid_at

INSERT INTO orders (id, email, amount, paid_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE SET
  email     = EXCLUDED.email,
  amount    = EXCLUDED.amount,
  paid_at   = EXCLUDED.paid_at;

Write $1, $2, … placeholders regardless of the database — Cannectors translates them per driver (kept for PostgreSQL, ? for MySQL/SQLite). Each parameters entry is an expression evaluated against the record (parameters[0] → $1); values bind natively typed and a missing field binds as NULL. See Templating.

Transactional writes

For atomic batch writes — either all records persist or none do — wrap in a transaction:

output:
  type: database
  connectionStringRef: ${WAREHOUSE_DATABASE_URL}
  queryFile: ./sql/upsert_order.sql
  transaction: true

The runtime opens one transaction per output batch, executes the query once per record, then commits at the end. On any error, the transaction rolls back.

Connection

Same as the database input — see Connection for driver auto-detection and connection-pool tuning. The output reuses the same pool config (maxOpenConns, maxIdleConns, etc.).

The pool is opened before the input runs, not when the first record arrives: a database you cannot reach fails the run before the source API has been called and before any enrichment has been billed. The connection string and the driver are resolved earlier still, when the module is built, so a missing ${VAR} or an undetectable driver is reported at startup.

Dry-run

cannectors run --dry-run prints the statement instead of executing it, and opens no connection at all — a DSN pointing at a host you cannot reach still produces a full preview:

📋 Dry-Run Preview (what would have been done):

  Operation: INSERT INTO events (postgres)
  Target: postgres://lab_user:[REDACTED]@warehouse.internal:5432/analytics
  Records: 1000
  Transaction: all statements in one transaction
  Statement:
    INSERT INTO events (id, seq) VALUES ($1, $2)
  Parameters: record.id, record.seq
  Values (truncated, use --verbose for all):
    1: "VOL-00001", 1
    2: "VOL-00002", 2
    ... (995 more executions)

The password in the connection string is redacted unless dryRunOptions.showCredentials is set; the bound values are record data and are shown as they are, truncated for width unless you pass --verbose. Parameters names what each value on a Values line binds to, read off the rendered statement — so a conditional clause that was left out, or a placeholder used twice, is labeled as it actually binds. See dry-run mode for the full behaviour.

Examples

name: database-output-transaction-query-file
version: 1.0.0
description: Write records with a SQL query file in a transaction.
tags:
  - database-output
  - transaction
input:
  type: httpPolling
  schedule: "0 * * * *"
  endpoint: https://source.example.com/api/products
  dataField: products
filters:
  - type: mapping
    mappings:
      - source: id
        target: product_id
      - source: sku
        target: sku
      - source: price
        target: price
        transforms:
          - op: toFloat
      - source: updated_at
        target: updated_at
output:
  type: database
  connectionStringRef: ${WAREHOUSE_DATABASE_URL}
  driver: postgres
  queryFile: examples/assets/sql/upsert_product.sql
  parameters:
    - record.product_id
    - record.sku
    - record.price
    - record.updated_at
  transaction: true
  onError: fail

Cross-references