Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Cypher Support

patinaDB implements a large, Neo4j-compatible subset of Cypher. The engine is validated against the openCypher Technology Compatibility Kit (TCK): the latest run passes ~3712 / 3868 scenarios (~95.9%). The TCK is treated as a prioritized gap report, not a strict regression gate — so treat this chapter as the authoritative statement of what works.

Reading

  • MATCH with node/edge patterns, optional labels, property maps, and all three directions (->, <-, - undirected).
  • OPTIONAL MATCH.
  • Variable-length paths — -[:T*1..3]->, [*0..n] (length-0 self-row), shortestPath.
  • WHERE — full boolean expressions: AND / OR / NOT / XOR, comparisons, IN, IS NULL, STARTS WITH / ENDS WITH / CONTAINS, regex =~, nested parenthesisation.
  • RETURN with DISTINCT, aliases (AS), expressions, ordering.
  • WITH chained query stages (projection, filtering, aggregation hand-off, ordering before projection).
  • UNWIND, UNION / UNION ALL.
  • ORDER BY / SKIP / LIMIT — single-key ordering uses an index fast-path; multi-key falls back to a post-sort.

Loading CSV

LOAD CSV WITH HEADERS FROM 'file:///data/people.csv' AS row
WITH toInteger(row.id) AS id, row.name AS name
CREATE (:Person {id: id, name: name})

LOAD CSV [WITH HEADERS] FROM '<url>' AS <var> [FIELDTERMINATOR '<c>'] streams rows from a CSV file into the query as a row source — like UNWIND, but from a file. It is read row by row (the file is never fully buffered).

  • WITH HEADERS → each row is a map keyed by the header, so row.columnName works. Without it, each row is a list of string cells: row[0], row[1], ….
  • Cells are strings (openCypher semantics). Convert explicitly with toInteger / toFloat / toBoolean. A coercion may sit directly inside a CREATE/MERGE pattern property — CREATE (… {p: toInteger(row.x)}) — as well as in a WITH; only a row[i] list-index expression still needs a WITH stage first.
  • FIELDTERMINATOR '<c>' overrides the delimiter (default ,).
  • Sources: file:///absolute/path, file://host/absolute/path (the host is ignored), and bare/relative filesystem paths. Quoted fields and embedded delimiters/newlines are handled. http(s):// URLs are not yet supported.

Large loads (memory): a plain LOAD CSV … CREATE buffers all its writes into one transaction (on the server, one Raft entry), so a very large load has an O(rows) memory cliff. Wrap it in CALL { … } IN TRANSACTIONS (below) to chunk it into many small commits — the online answer. For millions of rows the offline patinadb <db> import bulk-loader is faster still. A LOAD CSV … RETURN row read query streams end-to-end.

Server-side file reads (security): on the server, LOAD CSV FROM 'file://…' reads a server-side file, so it is deny-by-default: it reads only directories whitelisted with --allow-csv-dir, and any file-I/O query is raised to require the global Admin role. See Bulk Loading → Server file-I/O sandbox and Authentication & TLS. The embedded/CLI path installs no sandbox and is unaffected.

Batched writes: CALL { … } IN TRANSACTIONS

LOAD CSV WITH HEADERS FROM 'file:///data/people.csv' AS row
CALL {
  WITH row
  CREATE (:Person {id: toInteger(row.id), name: row.name})
} IN TRANSACTIONS OF 1000 ROWS

CALL { <write-subquery> } IN TRANSACTIONS [OF <n> ROW[S]] [ON ERROR {CONTINUE|BREAK|FAIL}] chunks a large write into many small, independently committed transactions instead of one giant one. The outer stream (typically LOAD CSV … AS row or UNWIND $rows AS r) feeds rows to the inner subquery, which runs and commits every n rows as a separate transaction (default n = 1000). This bounds peak memory to one chunk — the fix for the LOAD CSV … CREATE RAM cliff — and, on a cluster, replicates as one small entry per chunk instead of one unbounded one.

  • Each chunk is its own commit ⇒ its own engram (so batched ingest is versioned and time-travellable, unlike the offline importer), and partial progress is durable: a crash between chunks recovers to a chunk boundary (all-or-nothing per chunk). A later chunk sees the writes committed by earlier chunks.
  • OF <n> ROW[S] sets the chunk size (omit for the 1000 default).
  • ON ERROR — FAIL (default) aborts the whole statement on a failing chunk; CONTINUE skips the failing chunk and commits the rest; BREAK stops after the failing chunk, keeping the chunks already committed. A skipped/failed chunk changes nothing (it is resolved against a throwaway copy of the graph, so a partial chunk is never left behind).
  • The CALL { … } IN TRANSACTIONS must be the final clause of the query.

Writing

  • CREATE, MERGE (match-or-create), SET (properties, map merge, and SET n:Label label mutation), REMOVE, DELETE / DETACH DELETE.
  • FOREACH.
  • Writes inside WITH stages.

Expressions & functions

  • Arithmetic, string, list, and map operators; list/map indexing and slicing.
  • Aggregation — count (incl. count(*)), sum, avg, min, max, collect, with GROUP BY semantics (mix of aggregated and grouping keys in RETURN), DISTINCT inside aggregates, and aggregate hoisting through nested function calls and arithmetic.
  • CASE (simple and generic).
  • List comprehensions and pattern comprehensions.
  • Quantifiers — any / all / none / single, including aggregate and count(*) sources.
  • Scalar functions — toInteger, toString, size, labels, type, nodes, relationships, range, and many more.
  • Temporal functions — date, time, localtime, datetime, localdatetime, duration, plus namespaced calls (date.truncate, duration.between, datetime.fromepoch) with ISO-8601 week numbering.

Procedures

CALL invokes built-in procedures (history, diff, full-text search). See Procedures. User-defined procedures are not supported.

DDL

  • CREATE INDEX / DROP INDEX / SHOW INDEXES — single-property and compound (multi-field) B-tree indexes.

  • CREATE CONSTRAINT / DROP CONSTRAINT / SHOW CONSTRAINTS — uniqueness (IS UNIQUE), existence (IS NOT NULL), and node-key (IS NODE KEY). See Constraints.

  • CREATE EDGE SORTED INDEX (patinaDB extension) — pre-orders, per anchor vertex, that anchor’s targets over one relationship type by a target property, so a traversal-fan-out + ORDER BY target.prop [DESC] LIMIT k is served by a seek + take (no fan-out, no post-sort — bench Q5–Q8). Two orientations:

    CREATE EDGE SORTED INDEX [name] FOR ()-[:REPORTED]->(m:Ticket) ON m.created_at
CREATE EDGE SORTED INDEX [name] FOR (m:Ticket)<-[:HAS_LABEL]-()  ON m.created_at
SHOW EDGE SORTED INDEXES

    Creating the index over a populated graph backfills existing edges; it is kept live on writes. EXPLAIN names limit.edge_sorted_topk once a covering index serves the query. On a server it replicates to every node (deterministic re-run). The name is optional and cosmetic (the def is identified by its shape). Not yet carried in Raft snapshots — see Limitations.

  • CREATE FULLTEXT INDEX / DROP INDEX / SHOW FULLTEXT INDEXES — see Full-Text Search.

  • CREATE DATABASE / DROP DATABASE / SHOW DATABASES (server only) — see Multi-Database.

  • EXPLAIN and PROFILE render the physical operator tree.

Query introspection

EXPLAIN MATCH (p:Person)-[:KNEW]->(q) WHERE p.born < 1850 RETURN q
PROFILE MATCH (p:Person {name: 'Ada'}) RETURN p

EXPLAIN shows the chosen plan (scan strategy: compound index, property-value index, label scan, or all-vertices) without running it; PROFILE runs and annotates it.

What is not supported

A precise list lives in Limitations. The headline gaps: no user-defined procedures, no stored procedures/triggers, and the ~5% of TCK scenarios that remain — mostly exotic temporal/list edge cases. (Index DDL and uniqueness / existence / node-key constraints are supported; see Limitations for the exact scope.) Side-effect counters (+nodes, -relationships in query summaries) are not tracked.