ArcadeDB query cheat sheet: SQL, Cypher, Gremlin

The same everyday tasks in the three graph query languages ArcadeDB speaks, side by side. They all work on the same data, so you can use whichever reads best for the job. Every snippet was tested against a running ArcadeDB server.

Full query reference

The examples use a tiny social graph: Person vertices with name and city, connected by Knows edges. Run them from the Query panel of ArcadeDB Studio, from the console, or over HTTP with the language field set to sql, sqlscript, cypher or gremlin. Moving an existing Cypher codebase over? The Neo4j migration guide covers Bolt drivers and compatibility.

Create types

SQL declares schema up front. Cypher and Gremlin create a type the first time you use a label, so you can start schemaless and add constraints later.

SQL
CREATE VERTEX TYPE Person;
CREATE EDGE TYPE Knows;
CREATE PROPERTY Person.name STRING;
Cypher
// No DDL needed: the Person type is
// created on first use of the label
CREATE (:Person {name: 'Alice'})
Gremlin
// No DDL needed: the Person type is
// created on first use of the label
g.addV('Person').property('name', 'Alice')

Insert a vertex

Each statement creates one Person vertex with two properties.

SQL
CREATE VERTEX Person SET name = 'Alice', city = 'Rome'

-- INSERT works too
INSERT INTO Person SET name = 'Bob', city = 'Rome'
Cypher
CREATE (p:Person {name: 'Dave', city: 'Paris'})
RETURN p
Gremlin
g.addV('Person')
 .property('name', 'Frank')
 .property('city', 'Rome')

Insert an edge

Connect two existing vertices with a Knows edge that carries a property.

SQL
CREATE EDGE Knows
  FROM (SELECT FROM Person WHERE name = 'Alice')
  TO   (SELECT FROM Person WHERE name = 'Bob')
  SET since = 2020
Cypher
MATCH (a:Person {name: 'Alice'}),
      (b:Person {name: 'Bob'})
CREATE (a)-[:Knows {since: 2020}]->(b)
Gremlin
g.V().has('Person', 'name', 'Alice')
 .addE('Knows')
 .to(__.V().has('Person', 'name', 'Bob'))
 .property('since', 2020)

Match a pattern

Who does Alice know? One hop along outgoing Knows edges.

SQL
MATCH {type: Person, as: a, where: (name = 'Alice')}
      -Knows->{as: b}
RETURN a.name, b.name
Cypher
MATCH (a:Person {name: 'Alice'})-[:Knows]->(b)
RETURN b.name
Gremlin
g.V().has('Person', 'name', 'Alice')
 .out('Knows')
 .values('name')

Traverse 1 to 3 hops

Everyone Alice reaches in one, two or three Knows hops, without duplicates.

SQL
MATCH {type: Person, as: a, where: (name = 'Alice')}
      -Knows->{as: b, while: ($depth < 3),
                where: ($depth > 0)}
RETURN DISTINCT b.name AS name
Cypher
MATCH (a:Person {name: 'Alice'})-[:Knows*1..3]->(b)
RETURN DISTINCT b.name
Gremlin
g.V().has('Person', 'name', 'Alice')
 .repeat(out('Knows')).emit().times(3)
 .dedup()
 .values('name')

Shortest path

The fewest hops between two people, ignoring edge direction.

SQL
SELECT shortestPath($a, $b, 'BOTH', 'Knows') AS path
LET $a = (SELECT FROM Person WHERE name = 'Alice'),
    $b = (SELECT FROM Person WHERE name = 'Dave')
Cypher
MATCH p = shortestPath(
  (a:Person {name: 'Alice'})-[:Knows*]-(b:Person {name: 'Dave'}))
RETURN [n IN nodes(p) | n.name] AS names
Gremlin
g.V().has('Person', 'name', 'Alice')
 .repeat(both('Knows').simplePath())
 .until(has('name', 'Dave'))
 .path().by('name')
 .limit(1)

Aggregation

Count people per city, largest group first.

SQL
SELECT city, count(*) AS people
FROM Person
GROUP BY city
ORDER BY people DESC
Cypher
MATCH (p:Person)
RETURN p.city AS city, count(*) AS people
ORDER BY people DESC
Gremlin
g.V().hasLabel('Person')
 .groupCount()
 .by('city')

Indexes and constraints

A unique index doubles as a uniqueness constraint. Gremlin has no index DDL: create indexes with SQL or Cypher, from any client.

SQL
CREATE INDEX ON Person (name) UNIQUE;
CREATE INDEX ON Person (city) NOTUNIQUE;

-- idempotent form
CREATE INDEX IF NOT EXISTS ON Person (city) NOTUNIQUE;
Cypher
CREATE INDEX FOR (p:Person) ON (p.city)

CREATE CONSTRAINT FOR (p:Person)
  REQUIRE p.email IS UNIQUE
Gremlin

No index DDL in Gremlin. Create the index with SQL or Cypher; Gremlin traversals run on the same data.

Full-text search

A FULL_TEXT index is backed by Apache Lucene and ranks results with BM25. Queries use Lucene syntax.

SQL
CREATE INDEX ON Article (content) FULL_TEXT;

SELECT title, $score
FROM Article
WHERE SEARCH_INDEX('Article[content]', '+graph +database')
ORDER BY $score DESC
Cypher
// index created with the SQL on the left
CALL db.index.fulltext.queryNodes(
  'Article[content]', 'graph AND database')
YIELD node, score
RETURN node.title, score
Gremlin

Not available as a Gremlin step. Use the SQL or Cypher form from the same database.

Vector search

Approximate nearest-neighbor search on an LSM_VECTOR index. Pass the query embedding as a parameter; its length must match the index dimensions.

SQL
CREATE PROPERTY Doc.embedding ARRAY_OF_FLOATS;
CREATE INDEX ON Doc (embedding) LSM_VECTOR
  METADATA {dimensions: 384, similarity: 'COSINE'};

-- 10 nearest neighbors, with distance
SELECT expand(vector.neighbors('Doc[embedding]', :queryVector, 10))
Cypher
// index created with the SQL on the left
CALL db.index.vector.queryNodes(
  'Doc[embedding]', 10, $queryVector)
YIELD node, score
RETURN node.title AS title, score
Gremlin

Not available as a Gremlin step. Use the SQL or Cypher form from the same database.

Transactions

Every single command is already atomic. To group several, use a SQL script, an HTTP session, or your driver's transaction API.

SQL
-- language: sqlscript
BEGIN;
LET a = CREATE VERTEX Person SET name = 'Gina';
LET b = CREATE VERTEX Person SET name = 'Hal';
CREATE EDGE Knows FROM $a TO $b;
COMMIT RETRY 3;
Cypher
# HTTP: begin returns an arcadedb-session-id header
curl -i -X POST -u root:pwd \
  http://localhost:2480/api/v1/begin/mydb

# send it with each command, then commit
curl -X POST -u root:pwd \
  -H 'arcadedb-session-id: <id>' \
  -d '{"language":"cypher","command":"CREATE (:Person {name: \"Ivy\"})"}' \
  http://localhost:2480/api/v1/command/mydb
curl -X POST -u root:pwd \
  -H 'arcadedb-session-id: <id>' \
  http://localhost:2480/api/v1/commit/mydb
Gremlin
// Java, remote Gremlin driver
Transaction tx = g.tx();
GraphTraversalSource gtx = tx.begin();
try {
  gtx.addV("Person").property("name", "Alice").iterate();
  tx.commit();
} catch (Exception e) {
  tx.rollback();
}
Going further

Frequently Asked Questions

Can I mix SQL, Cypher and Gremlin on the same ArcadeDB database?

Yes. All three languages read and write the same records. A type created in SQL is a label in Cypher and Gremlin, and a vertex inserted with Gremlin is returned by a SQL SELECT. Pick the language per query.

Which query language should I use in ArcadeDB?

Use SQL for schema, indexes, bulk updates and reporting, Cypher for readable graph patterns and for code moving from Neo4j, and Gremlin when you already have TinkerPop traversals. Full-text and vector search are available from SQL and Cypher.

Do I need to define a schema before inserting data?

No. Cypher and Gremlin create a vertex or edge type the first time you use a label. SQL can declare types, properties and indexes explicitly when you want constraints.