# Row operations (DML)

> Operations that write rows: insert, update, merge, delete, move, adopt and batch.

Operations that write rows: insert, update, merge, delete, move, adopt and batch. The [overview](https://datanotes.dev/reference/operations/) lists every operation by kind. The MCP server, `datanotes mcp`, offers each operation as an MCP tool of the same name, and `engineTools()` of `datanotes/mcp` gives the same definitions to [your own MCP server](https://datanotes.dev/develop/build-on-datanotes/#build-your-own-mcp-server). `DatanotesClient.call(name, args)` runs one from code. Over HTTP each one is `POST /datanotes/operations/<name>/`, with its arguments as the JSON body: the [HTTP API reference](https://datanotes.dev/api/) lists them.

Every write is validated. Writes to rows and to the schema are journaled and undone with `undo_migration`; `configure_database`, the webhook operations, `vacuum_table` and `sync_semantic_index` are not journaled.

## `insert_row`

**Insert Row** · write

Insert a row (new note). Values are validated against the columns; the note path comes from the table’s `path`/`filename` patterns (e.g. an event logged today about last night lands in last night’s day folder when you set `occurred_at` to last night). `body` is the note text. Generated / identity columns are computed (do not pass them); CHECK constraints are enforced. Returns `migration_id` (undo) and the created path in `planned_creates`. `body` links the notes it mentions by name, at their first mention (automatic links; off with `autolink: false` or the database’s `autolink` setting). `links_added` lists the links made; `ambiguous_mentions` lists the mentions left as text with the notes they may name: read those, then link the right one with update_row and `autolink: false`, or leave the text.

Examples: [Catalogs, schemas and the database](https://datanotes.dev/guides/database-objects/catalogs-and-schemas/), [Tables](https://datanotes.dev/guides/database-objects/tables/), [Columns and types](https://datanotes.dev/guides/database-objects/columns/), [Bases](https://datanotes.dev/guides/database-objects/bases/), [Relations and hierarchies](https://datanotes.dev/guides/database-objects/relations/), [Validation](https://datanotes.dev/guides/data-quality/validation/), [CHECK constraints](https://datanotes.dev/guides/data-quality/check-constraints/), [Write and move rows](https://datanotes.dev/guides/read-and-write/write-rows/), [Automatic links](https://datanotes.dev/guides/read-and-write/automatic-links/), [Batch writes](https://datanotes.dev/guides/read-and-write/batch/), [Import existing notes](https://datanotes.dev/guides/read-and-write/import-notes/), [Journal and undo](https://datanotes.dev/guides/history/journal-and-undo/), [Table history and time travel](https://datanotes.dev/guides/history/table-history/), [Change data feed](https://datanotes.dev/guides/history/change-data-feed/), [Restore a table or the database](https://datanotes.dev/guides/history/restore/).

| Parameter  | Type       | Required | Default | Description |
| ---------- | ---------- | -------- | ------- | ----------- |
| `table`    | string     | yes      |         |             |
| `values`   | object     | yes      |         |             |
| `body`     | string \\  | null     |         | `null`      |
| `path`     | string \\  | null     |         | `null`      |
| `dry_run`  | boolean    |          | `false` |             |
| `autolink` | boolean \\ | null     |         | `null`      |

## `update_row`

**Update Row** · destructive write

Update exactly ONE row, addressed by its `id` (the system UUID) or its `path`; refused when the row does not exist (or id and path disagree). Same validation, journal and history as `update_rows`.

* `set` / `unset`: columns to set or remove.
* `body`: replace the whole note body; or edit it keeping the rest: `body_sections` `{"History": "…"}` replaces the text under that heading (any level, up to the next heading of the same level; added as `## History` when missing), `body_prepend` / `body_append` add text before / after.
* `expect`: compare-and-set — `{"status": "open"}` writes only if the row still has those values (a person may have changed it since you read it); otherwise refused, nothing written.
* `autolink`: the text written links the notes it mentions by name, at their first mention (off with `autolink: false` or the database’s setting). `links_added` lists the links made; `ambiguous_mentions` the mentions left as text with the notes they may name: read those, then link the right one with update_row and `autolink: false`, or leave the text.

Examples: [Columns and types](https://datanotes.dev/guides/database-objects/columns/), [Bases](https://datanotes.dev/guides/database-objects/bases/), [Relations and hierarchies](https://datanotes.dev/guides/database-objects/relations/), [CHECK constraints](https://datanotes.dev/guides/data-quality/check-constraints/), [Hand edits](https://datanotes.dev/guides/data-quality/hand-edits/), [Write and move rows](https://datanotes.dev/guides/read-and-write/write-rows/), [Automatic links](https://datanotes.dev/guides/read-and-write/automatic-links/), [Batch writes](https://datanotes.dev/guides/read-and-write/batch/), [Journal and undo](https://datanotes.dev/guides/history/journal-and-undo/), [Table history and time travel](https://datanotes.dev/guides/history/table-history/), [Change data feed](https://datanotes.dev/guides/history/change-data-feed/), [Restore a table or the database](https://datanotes.dev/guides/history/restore/).

| Parameter       | Type         | Required | Default | Description |
| --------------- | ------------ | -------- | ------- | ----------- |
| `table`         | string       | yes      |         |             |
| `id`            | string \\    | null     |         | `null`      |
| `path`          | string \\    | null     |         | `null`      |
| `set`           | object \\    | null     |         | `null`      |
| `unset`         | string\[] \\ | null     |         | `null`      |
| `body`          | string \\    | null     |         | `null`      |
| `body_sections` | object \\    | null     |         | `null`      |
| `body_prepend`  | string \\    | null     |         | `null`      |
| `expect`        | object \\    | null     |         | `null`      |
| `body_append`   | string \\    | null     |         | `null`      |
| `dry_run`       | boolean      |          | `false` |             |
| `autolink`      | boolean \\   | null     |         | `null`      |

## `update_rows`

**Update Rows** · destructive write

Update every row matching `where` (required: `{}` is every row). `where`: keys AND-ed; values are equality or operators eq, ne, gt, gte, lt, lte, in, contains, icontains (case/accent-insensitive, also inside lists — use it for names and aliases), exists, within (a hierarchy link: that row and everything below); combine with `or` / `and` / `not` (nestable); `path` is a pseudo-column. A dotted key follows a link column: `{"project.area": "[[Work]]"}` matches rows whose `project` links a row with that `area`. `set` sets, `unset` removes; all rows are validated first. `expect_count`: refuse unless exactly that many match. One row, or its text: update_row.

Examples: [Write and move rows](https://datanotes.dev/guides/read-and-write/write-rows/).

| Parameter      | Type         | Required | Default | Description                                       |
| -------------- | ------------ | -------- | ------- | ------------------------------------------------- |
| `table`        | string       | yes      |         |                                                   |
| `where`        | object       | yes      |         | The rows to change; {} is every row of the table. |
| `set`          | object \\    | null     |         | `null`                                            |
| `unset`        | string\[] \\ | null     |         | `null`                                            |
| `dry_run`      | boolean      |          | `false` |                                                   |
| `expect_count` | integer \\   | null     |         | `null`                                            |

## `merge_rows`

**Merge Rows** · destructive write

MERGE (upsert) `rows` into `table` by the key columns `on` (e.g. `["occurred_at"]` or `["id"]`): a row matching an existing one updates it (`when_matched`: “update” default or “ignore”), the others are inserted (`when_not_matched`: “insert” default or “ignore”). Date-times are compared after completion with the database time zone. A source key matching several rows, or repeated, is a conflict. One journaled, undoable operation.

Examples: [Write and move rows](https://datanotes.dev/guides/read-and-write/write-rows/).

| Parameter          | Type          | Required      | Default | Description |
| ------------------ | ------------- | ------------- | ------- | ----------- |
| `table`            | string        | yes           |         |             |
| `on`               | string\[]     | yes           |         |             |
| `rows`             | object\[]     | yes           |         |             |
| `when_matched`     | `"update"` \\ | `"ignore"` \\ | null    |             |
| `when_not_matched` | `"insert"` \\ | `"ignore"` \\ | null    |             |
| `dry_run`          | boolean       |               | `false` |             |

## `delete_rows`

**Delete Rows** · destructive write

Delete rows matching `where`. `mode`: “archive” (default, move to the table’s archive folder) or “delete” (trash; content journaled for undo). `on_referenced`: “restrict” (default, refuse rows other notes link to), “set_null” (clear front matter links to them) or “ignore” (the links stay, to no row: an archived row is gone as a trashed one, so check_table reports them as `broken_link` and new links to it are refused). `where` is required: `{}` deletes every row.

Examples: [Relations and hierarchies](https://datanotes.dev/guides/database-objects/relations/), [Write and move rows](https://datanotes.dev/guides/read-and-write/write-rows/), [Table history and time travel](https://datanotes.dev/guides/history/table-history/).

| Parameter       | Type            | Required        | Default       | Description                                       |
| --------------- | --------------- | --------------- | ------------- | ------------------------------------------------- |
| `table`         | string          | yes             |               |                                                   |
| `where`         | object          | yes             |               | The rows to change; {} is every row of the table. |
| `mode`          | `"archive"` \\  | `"delete"` \\   | null          |                                                   |
| `on_referenced` | `"restrict"` \\ | `"set_null"` \\ | `"ignore"` \\ | null                                              |
| `dry_run`       | boolean         |                 | `false`       |                                                   |

## `move_rows`

**Move Rows** · destructive write

Move rows matching `where` (required: `{}` is every row) to another table (e.g. split a domain out of `log` into its own table): sets each row’s `table` to the destination, applies `rename` (`{"metric_distance_km": "distance_km"}`), `set` and `drop`, adds the destination’s missing dense fields, and validates every row against the destination first. Keys unknown to the destination are reported, never dropped silently — rename or drop them explicitly. `relocate: true` also moves the files to the destination table’s folder (named by its filename pattern); by default rows stay where they are, as rows already in folders of their own usually do (adopt_rows, for notes that are no rows yet, moves them by default). Refused while rows staying behind link to a moved row through a column whose `ref` is the source table (the conflicts name them): change those links first. Undo with `undo_migration`.

Examples: [Write and move rows](https://datanotes.dev/guides/read-and-write/write-rows/).

| Parameter  | Type         | Required | Default | Description                                       |
| ---------- | ------------ | -------- | ------- | ------------------------------------------------- |
| `table`    | string       | yes      |         |                                                   |
| `to_table` | string       | yes      |         |                                                   |
| `where`    | object       | yes      |         | The rows to change; {} is every row of the table. |
| `rename`   | object \\    | null     |         | `null`                                            |
| `set`      | object \\    | null     |         | `null`                                            |
| `drop`     | string\[] \\ | null     |         | `null`                                            |
| `relocate` | boolean      |          | `false` |                                                   |
| `dry_run`  | boolean      |          | `false` |                                                   |

## `adopt_rows`

**Adopt Rows** · destructive write

Turn existing notes (not rows of any table yet) into rows of `table`, keeping their body and links: `rename` keys (`{"data": "occurred_at"}`), `set` for all, `per_row` values by note path (`{"Home/x.md": {"zone": "[[Kitchen]]"}}`), `drop` keys the table does not have. Every note gets `table` and a system `id`; with `relocate` (default true, unlike move_rows: notes become rows where the table keeps its rows) it moves to the table’s folder, named by its filename pattern (links to it are rewritten). All notes are validated first (as rows of `table`, so notes of the same call may link to one another): unknown keys and bad values are listed and nothing is written. Run with `dry_run` first; undo with `undo_migration`. Many notes: adopt them in calls of about 1,000 `paths` each, so a stop or a crash interrupts less (each call is one journal entry).

Examples: [Import existing notes](https://datanotes.dev/guides/read-and-write/import-notes/).

| Parameter  | Type         | Required | Default | Description |
| ---------- | ------------ | -------- | ------- | ----------- |
| `table`    | string       | yes      |         |             |
| `paths`    | string\[]    | yes      |         |             |
| `rename`   | object \\    | null     |         | `null`      |
| `set`      | object \\    | null     |         | `null`      |
| `per_row`  | object \\    | null     |         | `null`      |
| `drop`     | string\[] \\ | null     |         | `null`      |
| `relocate` | boolean      |          | `true`  |             |
| `dry_run`  | boolean      |          | `false` |             |

## `batch`

**Batch** · destructive write

Several writes that succeed or fail together. `operations`: `[{"op": ..., ...arguments}]` where `op` is a row operation (`insert_row`, `update_row`, `update_rows`, `delete_rows`, `merge_rows`, `move_rows`, `adopt_rows`, `recompute_columns`, `align_relations`, `restore_table`) or a DDL op (`create_table`, `add_column`, `alter_column`, …), with the same arguments as the operation (an unknown one refuses the batch) (e.g. `values` for insert_row, `set` for update_row / update_rows). They run in order, each seeing the previous ones; if one is refused, the ones already applied are undone (`rolled_back`). Returns `steps` (per operation, each with its result, where `ambiguous_mentions` is as in insert_row) and `migration_id`: undo_migration on it reverts the whole batch. Use it when a change spans several rows (create an event and update the people in it). `dry_run` checks each operation against the current data only.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: [Views](https://datanotes.dev/guides/database-objects/views/), [Relations and hierarchies](https://datanotes.dev/guides/database-objects/relations/), [CHECK constraints](https://datanotes.dev/guides/data-quality/check-constraints/), [Batch writes](https://datanotes.dev/guides/read-and-write/batch/), [Restore a table or the database](https://datanotes.dev/guides/history/restore/).

| Parameter    | Type      | Required | Default | Description |
| ------------ | --------- | -------- | ------- | ----------- |
| `operations` | object\[] | yes      |         |             |
| `summary`    | string \\ | null     |         | `null`      |
| `dry_run`    | boolean   |          | `false` |             |

## `align_relations`

**Align Relations** · destructive write

Add the reciprocal links missing on a table’s `inverse` link columns (e.g. Alice lists Carol in `friends` but Carol does not list Alice). Run it after declaring `inverse` on existing data; check_table reports such rows (code `inverse`). Single-value links already pointing elsewhere are conflicts (nothing written). The links it adds are checked like other writes (CHECKs that follow links, `ref_where` / `ref_by`). Writes through the tools already keep both sides in step.

Examples: [Relations and hierarchies](https://datanotes.dev/guides/database-objects/relations/).

| Parameter | Type         | Required | Default | Description |
| --------- | ------------ | -------- | ------- | ----------- |
| `table`   | string       | yes      |         |             |
| `paths`   | string\[] \\ | null     |         | `null`      |
| `dry_run` | boolean      |          | `false` |             |

## `recompute_columns`

**Recompute Columns** · destructive write

Bring generated and identity columns up to date (all rows, or only `paths`), e.g. after rows were edited by hand (an editor, git, sync) or a generated expression changed with `alter_column`. Also gives rows without an `id` a new one, and a duplicated note (same `id` as an older row) a new id. `new_ids=true` regenerates every id in scope (new ones on every call): only on the user’s explicit request. `check_table` reports stale values with code `generated`. No-op when nothing is stale; otherwise journaled (undo_migration) and a RECOMPUTE version in the table’s history.

Examples: [Hand edits](https://datanotes.dev/guides/data-quality/hand-edits/).

| Parameter | Type         | Required | Default | Description |
| --------- | ------------ | -------- | ------- | ----------- |
| `table`   | string       | yes      |         |             |
| `paths`   | string\[] \\ | null     |         | `null`      |
| `new_ids` | boolean      |          | `false` |             |
| `dry_run` | boolean      |          | `false` |             |