Bulk Upsert (match-or-create)

Where Bulk Loading is about getting a lot of data in quickly, upsert is about reconciling data: for each input record, find an existing node (or edge) by a match key and UPDATE it, or CREATE it if it isn’t there. It is a bulk, index-accelerated MERGE — served online, over REST, by the leader.

By default, every upsert runs the versioned path — through the engram log, bracketed by a pre-load marker — so the whole load, however large, reverts as one unit (see Time Travel). A non-unique match key (or any other ambiguity) fails loud and leaves the graph untouched.

There are three flavours, all admin- and leader-only REST endpoints:

All three accept a chunk_rows field: each chunk is one replicated Raft entry, so a very large load doesn’t ship as one giant entry (see Chunking a very large load). For /mgmt/upsert-graph, chunk_rows counts resolved graph operations per entry rather than input rows, since the whole document is one coupled payload.

A non-unique endpoint/match key that resolves to more than one node always fails loud — there is no policy that can silently pick one.

Node upsert — POST /mgmt/upsert

Match-or-create nodes on a per-batch match key. The file path is confined to the server’s filesystem via the same --allow-csv-dir sandbox LOAD CSV uses (see Bulk Loading → Server file-I/O sandbox).

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert \
  -H 'content-type: application/json' \
  -d '{
    "db": "default",
    "file": "/srv/import/people.csv",
    "label": "Person",
    "match": ["email"],
    "merge": "add",
    "on_conflict": "error",
    "strategy": "seek"
  }'

{"created": 2, "updated": 1, "skipped": 0, "conflicts": 0,
 "pre_load": "6f2f…", "post_load": "a1e3…",
 "pre_load_tag": "_upsert_pre_1699…",
 "revert_hint": "restore_to('6f2f…')"}

Per row, an existing :Person is found by exact equality on the match key (one attribute, or a composite ["first","last","dob"]). Found → UPDATE per merge; not found → CREATE. Re-running the same file is idempotent (it updates in place, never duplicates).

• merge: "add" (default) — add/overwrite the row’s attributes, keep the rest.
• merge: "replace" — the node keeps ONLY the row’s attributes (drops others).
• merge: "add-missing" — set only attributes the node does not already have.
• on_conflict: "error" (default) — a match key that hits more than one node fails loud. "update-all" updates every match; "skip" leaves them, counts them.
• strategy: "seek" (default) or "sort-merge" — two byte-identical lookup strategies; sort-merge reads the index in one sequential pass and is faster when the input is a large fraction of the label.
• chunk_rows — for a load too large to hold in RAM as one changeset (see below).

Concurrency safety. The resolve+propose runs through the same optimistic conflict-check pipeline REST autocommit writes use, so two concurrent upserts of the same value don’t both create a duplicate — the loser’s retry re-resolves against fresh HEAD and MATCHes the winner’s just-created node. This detection needs the match key to be a UNIQUE or NODE KEY constraint (see Constraints); an unconstrained match key is not conflict-detected online.

Chunking a very large load

By default an upsert is applied as one changeset (one engram), so the whole resolved batch is held in RAM. That is fine up to millions of rows, but a truly huge load (tens of millions of nodes/edges) can exhaust memory.

Pass chunk_rows to process the load in chunks of N rows, each applied as its own changeset — so peak memory stays bounded by the chunk size, not the total row count, and an arbitrarily large file loads:

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert \
  -H 'content-type: application/json' \
  -d '{"db":"default","file":"/srv/import/huge.parquet","label":"Person","match":["email"],"chunk_rows":100000}'

The final graph is identical to an un-chunked load for any chunk size (a row whose key an earlier chunk already created is matched, not duplicated), and the whole load is still revertable as one unit via the returned pre-load tag.

Two things to know about the trade-off:

• Chunking is somewhat slower (each chunk pays a small per-changeset overhead) — a larger chunk_rows means less overhead but more memory.
• A chunked load is not all-or-nothing: if a chunk fails partway through (e.g. a non-unique match key), the earlier chunks stay applied. Recover the pre-load state with the returned revert hint. (Without chunk_rows, a failed upsert leaves the graph completely untouched — use the default when the whole load must be atomic and fits in memory.)

/mgmt/upsert-edges takes the same chunk_rows field with the same semantics. /mgmt/upsert-graph applies all the subgraph’s nodes in chunks first (so an edge always finds its endpoints), then the edges in chunks. Note the JSON document itself is still held in memory as one coupled payload — chunking bounds the per-changeset memory, not the size of the document you can post in one call.

Edge upsert — POST /mgmt/upsert-edges

Match-or-create relationships between existing nodes. Each row’s start and end endpoints are resolved by the same match machinery as node upsert, then the edge is matched on its canonical (start, end, type) identity.

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert-edges \
  -H 'content-type: application/json' \
  -d '{
    "db": "default",
    "file": "/srv/import/works_at.csv",
    "start_label": "Person", "start_match": ["email"],
    "end_label": "Company", "end_match": ["name"],
    "rel": "WORKS_AT"
  }'

Every column named in start_match/end_match is consumed as an endpoint match value and is never an edge property; every other column is an edge property. Because patinaDB edges are id-less (at most one edge per (start,end,type)), there is no conflict policy for the edge itself — only on_missing governs a row whose endpoint match finds no node:

• "error" (default) — fail the whole load loud, touching nothing.
• "skip" — leave the row uncreated, count it, keep going.
• "create" — CREATE the missing endpoint node too, instead of failing or skipping. The created node’s properties are set to exactly its match-key attribute(s) — nothing else. Two rows in the same batch that reference the SAME missing endpoint (same label + same match value) resolve to the SAME newly-created node, so a file with many edges into one new node never creates duplicates.

{"created": 2, "updated": 0, "skipped": 0, "missing_endpoints": 1,
 "pre_load": "6f2f…", "post_load": "a1e3…",
 "pre_load_tag": "_upsert_edges_pre_1699…"}

Subgraph upsert — POST /mgmt/upsert-graph

The most powerful form: match-or-create a whole node+edge subgraph described in ONE coupled JSON document, posted as the request body, applied as one revertable engram.

Edges reference nodes by a fake id — a payload-local handle (unique within the document) used ONLY to wire edges[].from/.to to nodes[].id. A fake id is never stored; the server replaces each with the real DB UUID the node resolved to. This is what makes a homogeneous self-relationship (e.g. Person KNOWS Person) trivial — something a value-only edge match can’t express.

{
  "identity": { "Person": ["email"], "Company": ["name"] },
  "merge": "add",
  "nodes": [
    { "id": "p1", "label": "Person",  "props": { "email": "a@x", "name": "Alice", "age": 30 } },
    { "id": "c1", "label": "Company", "props": { "name": "Acme", "industry": "tech" } }
  ],
  "edges": [
    { "from": "p1", "to": "c1", "type": "WORKS_AT", "props": { "since": 2020 } }
  ]
}

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert-graph \
  -H 'content-type: application/json' --data-binary @graph.json

{"nodes_created": 2, "nodes_updated": 0, "edges_created": 1, "edges_updated": 0,
 "pre_load": "6f2f…", "post_load": "a1e3…",
 "pre_load_tag": "_upsert_graph_pre_1699…"}

Any number of node and relationship types

A subgraph document can mix as many node labels and relationship types as you like — each label is matched-or-created by its own identity, and the edges wire fake ids together across type boundaries. One document, one revertable engram:

{
  "nodes": [
    { "id": "p1", "label": "Person",  "props": { "email": "alice@x", "name": "Alice" } },
    { "id": "c1", "label": "Company", "props": { "name": "Acme" } },
    { "id": "s1", "label": "Skill",   "props": { "name": "Rust" } },
    { "id": "s2", "label": "Skill",   "props": { "name": "Graphs" } }
  ],
  "edges": [
    { "from": "p1", "to": "c1", "type": "WORKS_AT",  "props": { "since": 2020 } },
    { "from": "p1", "to": "s1", "type": "HAS_SKILL", "props": { "level": 5 } },
    { "from": "p1", "to": "s2", "type": "HAS_SKILL" }
  ]
}

Post this with an identity block naming Person=email, Company=name, and Skill=name — Alice is now connected to a Company and two Skills in one shot: three node types, two relationship types, resolved and applied together.

Where the match key (identity) comes from

Which properties make a node unique is resolved per label:

1. a persisted UNIQUE or NODE KEY constraint on the label — then you can omit the identity block entirely; else
2. the document’s identity block.

If both a constraint and a differing identity block exist, or neither exists, the load fails loud — patinaDB never guesses how to match your nodes.

An edge whose from/to names a fake id that isn’t in the document fails loud (the payload must be self-contained). merge (default "add") applies to node and edge properties, exactly as for node upsert.

The homogeneous case (Person KNOWS Person)

{
  "identity": { "Person": ["email"] },
  "nodes": [
    { "id": "p1", "label": "Person", "props": { "email": "a@x" } },
    { "id": "p2", "label": "Person", "props": { "email": "b@x" } },
    { "id": "p3", "label": "Person", "props": { "email": "c@x" } }
  ],
  "edges": [
    { "from": "p1", "to": "p2", "type": "KNOWS" },
    { "from": "p2", "to": "p3", "type": "KNOWS" }
  ]
}

The KNOWS edges land on exactly a→b and b→c (and NOT a→c) — the fake ids disambiguate which Person is which, even though every node has the same label.

Edge cardinality — 1-to-many vs 1-to-1

By default an edge is 1-to-many: many :WORKS_AT edges can leave one Person. Set an edge’s "identity" field to make it 1-to-1 — useful when a relationship is exclusive (one employer, one current owner, …).

1-to-many (default) — targets coexist. Load a-[:WORKS_AT]->Acme, then a-[:WORKS_AT]->Globex, and Alice works at both:

{ "from": "p1", "to": "c1", "type": "WORKS_AT" }

1-to-1 ("from") — the new target replaces the old. With a-[:WORKS_AT]->Acme already present, upserting:

{ "from": "p1", "to": "c2", "type": "WORKS_AT", "identity": "from" }

deletes the a→Acme edge and creates a→Globex — Alice now works at exactly one company. Re-upserting the same target just updates that edge’s properties.

Extra edge-property uniqueness — "match"

Because patinaDB edges are id-less, there is at most one physical edge per (from,to,type). An optional per-edge "match": ["attr",…] names edge properties that are part of the edge’s logical identity and acts as a guard: if a committed edge already occupies the triple but its match-property values differ from what you’re upserting, the load fails loud rather than silently overwriting a semantically-different edge. It composes with any cardinality mode.

Reverting a load

Every upsert is a single revertable changeset. The returned pre_load HEAD (and its replicated tag) is the exact state before the load. On the server:

USE default AS OF TAG '_upsert_pre_1699…'
MATCH (n) RETURN count(n)

Or inspect the whole load as one diff — CALL patinadb.diffRange('<pre_load>', '<post_load>') (see Diffs and Time Travel). The pre-load tag is replicated, so USE <db> AS OF TAG '<tag>' reads the graph as it was before the load on any node.