# Mutations guide

How to build write operations with `MutationBuilder`. A mutation compiles to a
native write per engine and reuses the same [filter engine](QUERIES.md#filters)
as reads.

- [Overview](#overview)
- [Insert](#insert)
- [Upsert](#upsert)
- [Update](#update)
- [Granular update actions](#granular-update-actions)
- [Delete](#delete)
- [Safety](#safety)
- [What each engine emits](#what-each-engine-emits)

---

## Overview

```ts
import { MutationBuilder, type MutationInput } from '@qrvey/query-builder';

const input: MutationInput = { operation: 'update', /* … */ };
const result = new MutationBuilder(input, { engine: 'postgresql' }).transpile();
```

`operation` is one of `insert`, `update`, `delete`. Upsert is an `insert` with
`onConflict`. All results carry the `operation`, so an executor knows which
endpoint/statement to run (see the [Integration guide](INTEGRATION.md)).

---

## Insert

Insert one or more rows. All rows must share the same columns.

```ts
{
  operation: 'insert',
  source: { name: 'orders' },
  rows: [
    { id: 'a1', amount: 100, active: true },
    { id: 'a2', amount: 250, active: false },
  ],
}
```

- **PostgreSQL** → `INSERT INTO "orders" ("id", "amount", "active") VALUES ($1, $2, $3), ($4, $5, $6)`
- **ClickHouse** → same statement with named params `{pN:Type}`
- **ES/OS** → the raw `documents` for a bulk index

---

## Upsert

Add `onConflict` (the key columns) to an insert. On conflict, the remaining
columns are updated.

```ts
{
  operation: 'insert',
  source: { name: 'orders' },
  rows: [{ id: 'a1', amount: 100, active: true }],
  onConflict: ['id'],
}
```

- **PostgreSQL** → `INSERT … ON CONFLICT ("id") DO UPDATE SET "amount" = EXCLUDED."amount", "active" = EXCLUDED."active"` (or `DO NOTHING` when every column is a key).
- **ClickHouse** → a plain `INSERT`; deduplication is delegated to the table engine (e.g. `ReplacingMergeTree`).
- **ES/OS** → the result exposes `onConflict` so the executor derives each document `_id` and a re-index overwrites.

`onConflict` keys must be among the inserted columns.

---

## Update

Set columns on rows matching a filter.

```ts
{
  operation: 'update',
  source: { name: 'orders' },
  set: [
    { field: 'status', type: 'string', value: 'archived' },
    { field: 'archived_at', type: 'date', value: '2024-01-01' },
  ],
  filter: { field: 'amount', type: 'number', operator: 'lt', value: 10 },
}
```

- **PostgreSQL** → `UPDATE "orders" SET "status" = $1, "archived_at" = $2 WHERE "amount" < $3`
- **ClickHouse** → `ALTER TABLE \`orders\` UPDATE \`status\` = {p0:String}, … WHERE \`amount\` < {p2:Int64}`
- **ES/OS** → `_update_by_query` with a painless `script`

---

## Granular update actions

Beyond `set`, an update may declare `actions` for common field operations. Use
`set`, `actions`, or both (at least one is required).

```ts
{
  operation: 'update',
  source: { name: 'orders' },
  set: [{ field: 'status', type: 'string', value: 'shipped' }],
  actions: [
    { op: 'increment', field: 'views', by: 1 },
    { op: 'append', field: 'tags', type: 'string', value: 'urgent' },
    { op: 'removeField', field: 'temp_note' },
  ],
  filter: { field: 'id', type: 'string', operator: 'eq', value: 'o1' },
}
```

| Action | PostgreSQL | ClickHouse | ES/OS (painless) |
| ------ | ---------- | ---------- | ---------------- |
| `set` | `col = $n` | `col = {pN}` | `ctx._source['f'] = params.pN` |
| `increment` | `col = col + $n` | `col = col + {pN}` | `ctx._source['f'] = (ctx._source['f'] ?: 0) + params.pN` |
| `append` | `col = array_append(col, $n)` | `col = arrayPushBack(col, {pN})` | null-guard + `ctx._source['f'].add(params.pN)` |
| `removeField` | `col = NULL` | `col = NULL` | `ctx._source.remove('f')` |

Action targets are top-level columns (no nested `path`).

---

## Delete

Delete rows matching a filter.

```ts
{
  operation: 'delete',
  source: { name: 'orders' },
  filter: { field: 'status', type: 'string', operator: 'eq', value: 'draft' },
}
```

- **PostgreSQL** → `DELETE FROM "orders" WHERE "status" = $1`
- **ClickHouse** → `ALTER TABLE \`orders\` DELETE WHERE \`status\` = {p0:String}`
- **ES/OS** → `_delete_by_query` with the filter as `body.query`

---

## Safety

An `update` or `delete` **without a `filter`** affects every row/document. That is
rejected with an `UNFILTERED_MUTATION` error unless you confirm explicitly:

```ts
{ operation: 'delete', source: { name: 'orders' }, allowUnfiltered: true }
// ClickHouse → ALTER TABLE `orders` DELETE WHERE 1 = 1
```

`insert` has no filter and is never subject to this rule, but it requires a
non-empty `rows` array whose rows all share the same columns.

Validate before executing if you prefer errors as data:

```ts
const { valid, errors } = new MutationBuilder(input).validate();
```

---

## What each engine emits

| Operation | PostgreSQL / ClickHouse | Elasticsearch / OpenSearch |
| --------- | ----------------------- | -------------------------- |
| `insert` | `INSERT … VALUES …` | `documents` for bulk index |
| upsert | PG `ON CONFLICT`; CH plain insert | `documents` + `onConflict` for `_id` derivation |
| `update` | `UPDATE …` / `ALTER TABLE … UPDATE …` | `_update_by_query` + painless `script` |
| `delete` | `DELETE …` / `ALTER TABLE … DELETE …` | `_delete_by_query` |

The exact result shape and how to run it is documented in the
[Integration guide](INTEGRATION.md); all types are in the
[API reference](API.md).