# upsert_record

> Source: https://docs.clonepartner.com/reference/steps/upsert-record/

<!-- sources: src/steps/upsert-record-step.ts, src/steps/step-connector-utils.ts, src/steps/types.ts, references/envoy/step-types.md -->

# 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](/reference/steps/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

```yaml
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.

## Related

- [ensure_table](/reference/steps/ensure-table/) — create the table before writing to it
- [sync_record](/reference/steps/sync-record/) — idempotent create/update against a destination API
- [Idempotent sync and the mapping DB](/building/idempotent-sync/) — designing safe reruns