Documentation by ClonePartner

Start typing to search.

Task Reference

upsert_record

View Markdown

Insert or update a record in a SQL table or MongoDB collection, keyed on one or more conflict columns, without raw upsert SQL.

upsert_record

Writes one row or document per input item into a SQL table or MongoDB collection, inserting when the key does not exist and updating when it does.

When to use it

Use upsert_record to stage or persist records in a database you control, such as a staging table in a migration. For writes to a destination API that also need dedup tracking and change detection, prefer sync_record.

Configuration

Key Type Required Default Description
connector string Yes — Name of a SQL (sqlite, mssql, mysql, postgres) or MongoDB Connector slot. Other types fail with upsert_record requires a SQL or MongoDB connector.
table string For SQL — Table name. One of table or collection is required.
collection string For MongoDB — Collection name. One of table or collection is required.
key string or string[] Yes — Column(s) that identify the record: conflict columns for SQL, filter fields for MongoDB.
data object Yes — The row or document fields to write.
metadata object No — Extra fields merged over data before writing.
update string[] or all_except_key No — SQL only: columns to update on conflict. all_except_key expands to every written column except the key column(s). Omitted, the Connector's upsert default applies.

Example

name: stage_tickets
connectors:
  source:
    type: mongodb
  staging:
    type: sqlite
steps:
  - name: read_tickets
    type: call_method
    config:
      connector: source
      method: find
      args:
        collection: tickets
  - name: stage_ticket
    type: upsert_record
    config: |
      {
        "connector": "staging",
        "table": "tickets",
        "key": "source_id",
        "data": {
          "source_id": $string(input._id),
          "subject": input.subject,
          "status": input.status
        },
        "metadata": { "synced_at": $now() },
        "update": "all_except_key"
      }

Behavior notes

  • SQL Connectors call the Connector's upsert method with table, data, conflict_columns (the key values), and — when update is set — update_columns. table is required; a missing table fails with table is required for SQL upsert_record.
  • MongoDB Connectors call update_one with upsert: true, a filter built from the key fields' values in the written row, and { $set: row } as the update. collection is required; a missing collection fails with collection is required for MongoDB upsert_record.
  • metadata fields are shallow-merged over data, so a metadata key overrides a data key of the same name.
  • The step emits the Connector method's return value, not the input item. Capture the original record through steps.<name>.output on an earlier step if a later step needs it.