Docs
View as markdown

Migrate from SQLite

Move tables, foreign keys and join tables from a SQLite database into a relate store, keep its keys as constraints, and rewrite joins as patterns.

clicypher

Before you start: Install relate

This guide shows you how to move a relational schema and its rows from a SQLite database into a relate store A relate database: a directory, a single .rl file, or :memory:, opened with --database. Read more in the glossary. Each table of records becomes a label A name that classifies a node, such as Person; a node has zero or more, and patterns, indexes and constraints refer to them. Read more in the glossary, each foreign key and each join table a relationship A directed, typed connection between two nodes, with properties of its own; every relationship has exactly one type and a start and an end node. Read more in the glossary, and each key and index a constraint A rule the store enforces on every write, such as a unique property, a required property, a property type or a node key; a violating write fails. Read more in the glossary or an index A structure the planner uses to find nodes or relationships by a property without scanning a label: range, text, point, vector or full-text. Read more in the glossary. The sqlite3 command-line shell exports every table as JSON, and one script loads the files with --fetch-params and keyed MERGE statements, so running it twice leaves the same graph. The steps write to ./team.db and ./team.rl, and each says so.

Prerequisites

  • relate on your PATH (Install relate).
  • The sqlite3 shell, version 3.33.0 or later, the first with the -json output mode.
  • Each table's JSON export at most 64 MiB, the --fetch-params limit per file. A larger table exports in ranges of its primary key, one file and one load run per range.
  • The whole graph, its indexes included, fits in memory (size a store and its memory).

Create the example database

The steps move an issue tracker for the team graph the Get started pages use: people, projects, who works on which project since when, who knows whom, and issues filed against projects. This command writes the SQLite database ./team.db. With your own database, skip it and use your file in the steps that follow:

sqlite3 team.db <<'SQL'
CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL
);
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  language TEXT,
  maintainer_id INTEGER REFERENCES people(id)
);
CREATE TABLE works_on (
  person_id INTEGER NOT NULL REFERENCES people(id),
  project_id INTEGER NOT NULL REFERENCES projects(id),
  since INTEGER NOT NULL,
  PRIMARY KEY (person_id, project_id)
);
CREATE TABLE knows (
  person_id INTEGER NOT NULL REFERENCES people(id),
  other_id INTEGER NOT NULL REFERENCES people(id),
  PRIMARY KEY (person_id, other_id)
);
CREATE TABLE issues (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id),
  title TEXT NOT NULL,
  opened TEXT NOT NULL,
  assignee_id INTEGER REFERENCES people(id)
);
CREATE INDEX issues_opened ON issues(opened);
INSERT INTO people VALUES (1, 'Ada', 'engineer'), (2, 'Grace', 'engineer'), (3, 'Noor', 'maintainer');
INSERT INTO projects VALUES (1, 'relate', 'Rust', 3), (2, 'opencypher-tck', 'Gherkin', NULL);
INSERT INTO works_on VALUES (1, 1, 2024), (2, 1, 2023), (2, 2, 2022);
INSERT INTO knows VALUES (1, 2);
INSERT INTO issues VALUES
  (1, 1, 'Plan cache misses on quoted labels', '2026-10-02', 1),
  (2, 1, 'Add a SQLite migration guide', '2026-10-20', NULL),
  (3, 2, 'Scenario for OPTIONAL MATCH ordering', '2026-09-14', 2);
SQL

Read the schema

sqlite3 team.db .schema prints every CREATE statement. Two queries list what the mapping needs. The foreign keys, each of which becomes a relationship:

sqlite3 -header -list team.db "SELECT m.name AS table_name, f.\"from\" AS column_name, f.\"table\" AS refers_to
FROM sqlite_master AS m JOIN pragma_foreign_key_list(m.name) AS f
WHERE m.type = 'table' ORDER BY 1, 2"
table_name|column_name|refers_to
issues|assignee_id|people
issues|project_id|projects
knows|other_id|people
knows|person_id|people
projects|maintainer_id|people
works_on|person_id|people
works_on|project_id|projects

The indexes, each of which becomes a constraint or a relate index. origin says where an index came from: pk a composite primary key, u a UNIQUE column, c a CREATE INDEX:

sqlite3 -header -list team.db "SELECT m.name AS table_name, i.name AS index_name, i.origin,
  (SELECT group_concat(name) FROM pragma_index_info(i.name)) AS columns
FROM sqlite_master AS m JOIN pragma_index_list(m.name) AS i
WHERE m.type = 'table' ORDER BY 1, 2"
table_name|index_name|origin|columns
issues|issues_opened|c|opened
knows|sqlite_autoindex_knows_1|pk|person_id,other_id
people|sqlite_autoindex_people_1|u|name
projects|sqlite_autoindex_projects_1|u|name
works_on|sqlite_autoindex_works_on_1|pk|person_id,project_id

An INTEGER PRIMARY KEY is the table's row id and has no index of its own, so people.id, projects.id and issues.id don't appear.

Plan the mapping

In SQLiteIn relate
A table of recordsA label, such as :Person, with one node per row
An INTEGER PRIMARY KEYA property under a node key constraint: the MERGE key, and what the foreign keys refer to
A foreign key columnA relationship to the node of the row it names. The column itself isn't stored
A join table whose primary key is two foreign keysA relationship between the two nodes. Its other columns become relationship properties
A UNIQUE columnA uniqueness constraint
A NOT NULL columnAn existence constraint
A CREATE INDEXA range index of the same name
INTEGER, REAL, TEXT valuesINTEGER, FLOAT, STRING properties
A date kept as ISO 8601 TEXTA DATE property, converted with date()
NULLNo property: SET p.x = null leaves x off the node

A foreign key points from the row that holds it to the row it names; the relationship can point either way. Pick the direction that reads as a sentence: Noor MAINTAINS relate, an issue is FILED_IN a project. For the example:

Table or columnBecomes
people(:Person {id, name, role})
projects(:Project {id, name, language})
projects.maintainer_id(:Person)-[:MAINTAINS]->(:Project)
issues(:Issue {id, title, opened})
issues.project_id(:Issue)-[:FILED_IN]->(:Project)
issues.assignee_id(:Issue)-[:ASSIGNED_TO]->(:Person)
works_on(:Person)-[:WORKS_ON {since}]->(:Project)
knows(:Person)-[:KNOWS]->(:Person)

Export each table as JSON

-json writes a table as one JSON array of objects keyed by column name. Integers and reals stay numbers and NULL stays null, so the values keep their types on the way in. A table with no rows writes an empty file, which is not JSON, so the loop writes [] in its place. This step writes the directory ./export:

mkdir export
for table in people projects works_on knows issues; do
  sqlite3 -json team.db "SELECT * FROM $table" > "export/$table.json"
  [ -s "export/$table.json" ] || echo '[]' > "export/$table.json"
done

Create the constraints and indexes

Create them before the first row arrives, so every write is checked. One statement per key, UNIQUE column, NOT NULL column you want enforced, and index. The DATE type constraint on opened refuses a date that arrives as a string. This step writes the script and runs it on ./team.rl, a single-file store it creates:

cat > schema.cypher <<'CYPHER'
CREATE CONSTRAINT person_id IF NOT EXISTS FOR (p:Person) REQUIRE p.id IS NODE KEY;
CREATE CONSTRAINT person_name IF NOT EXISTS FOR (p:Person) REQUIRE p.name IS UNIQUE;
CREATE CONSTRAINT person_role IF NOT EXISTS FOR (p:Person) REQUIRE p.role IS NOT NULL;
CREATE CONSTRAINT project_id IF NOT EXISTS FOR (p:Project) REQUIRE p.id IS NODE KEY;
CREATE CONSTRAINT project_name IF NOT EXISTS FOR (p:Project) REQUIRE p.name IS UNIQUE;
CREATE CONSTRAINT issue_id IF NOT EXISTS FOR (i:Issue) REQUIRE i.id IS NODE KEY;
CREATE CONSTRAINT issue_opened_type IF NOT EXISTS FOR (i:Issue) REQUIRE i.opened IS :: DATE;
CREATE CONSTRAINT works_on_since IF NOT EXISTS FOR ()-[w:WORKS_ON]-() REQUIRE w.since IS NOT NULL;
CREATE INDEX issues_opened IF NOT EXISTS FOR (i:Issue) ON (i.opened);
CYPHER
relate query --database ./team.rl < schema.cypher

The statements print nothing. A node key is a uniqueness and an existence constraint in one, the rule a primary key enforces. A composite primary key on a join table needs no constraint: MERGE on the relationship writes each pair once.

Write the load script

The script writes the nodes first, then one statement per foreign key and per join table, so every MATCH on an id finds a node that already exists. Each file arrives whole as a list, and UNWIND turns it into one row per record:

cat > load.cypher <<'CYPHER'
UNWIND $people AS row
MERGE (p:Person {id: row.id})
SET p.name = row.name, p.role = row.role;

UNWIND $projects AS row
MERGE (p:Project {id: row.id})
SET p.name = row.name, p.language = row.language;

UNWIND $issues AS row
MERGE (i:Issue {id: row.id})
SET i.title = row.title, i.opened = date(row.opened);

UNWIND $projects AS row
MATCH (proj:Project {id: row.id}), (p:Person {id: row.maintainer_id})
MERGE (p)-[:MAINTAINS]->(proj);

UNWIND $issues AS row
MATCH (i:Issue {id: row.id}), (proj:Project {id: row.project_id})
MERGE (i)-[:FILED_IN]->(proj);

UNWIND $issues AS row
MATCH (i:Issue {id: row.id}), (p:Person {id: row.assignee_id})
MERGE (i)-[:ASSIGNED_TO]->(p);

UNWIND $works_on AS row
MATCH (p:Person {id: row.person_id}), (proj:Project {id: row.project_id})
MERGE (p)-[w:WORKS_ON]->(proj)
SET w.since = row.since;

UNWIND $knows AS row
MATCH (a:Person {id: row.person_id}), (b:Person {id: row.other_id})
MERGE (a)-[:KNOWS]->(b);
CYPHER

Three choices make the script safe to rerun and true to the source:

  • MERGE on the key alone, then SET for the other columns. A MERGE on every column would create a second node when one value changes.
  • A NULL foreign key matches nothing. The opencypher-tck project has no maintainer, so its MATCH returns no row and the statement writes no MAINTAINS for it.
  • date(row.opened) turns the ISO 8601 text into a DATE, so ORDER BY and range comparisons work on dates.

Run the load

This command writes to ./team.rl. relate query reads the script from stdin, and each --fetch-params <name>=<file> binds one export file as a parameter:

relate query --database ./team.rl \
  --fetch-params people=export/people.json \
  --fetch-params projects=export/projects.json \
  --fetch-params issues=export/issues.json \
  --fetch-params works_on=export/works_on.json \
  --fetch-params knows=export/knows.json \
  < load.cypher

The load prints nothing: no statement returns rows, and the exit code is 0. A second run with the same files matches every key instead of creating anything, and says so on stderr for each statement that wrote nothing:

relate query --database ./team.rl \
  --fetch-params people=export/people.json \
  --fetch-params projects=export/projects.json \
  --fetch-params issues=export/issues.json \
  --fetch-params works_on=export/works_on.json \
  --fetch-params knows=export/knows.json \
  < load.cypher
{"planCache":"miss","timings":{"elapsed_ms":1.224125,"execute_ms":1.047083,"lex_parse_ms":0.007458,"lower_ms":0.005582999999999999,"parse_ms":0.16712500000000002,"plan_ms":0.0037500000000000003,"result_generation_ms":0.000166,"rewrite_ms":0.000375}}
{"planCache":"miss","timings":{"elapsed_ms":0.591291,"execute_ms":0.528625,"lex_parse_ms":0.0037500000000000003,"lower_ms":0.007958000000000002,"parse_ms":0.056541,"plan_ms":0.004667,"result_generation_ms":0.000042,"rewrite_ms":0.000375}}
{"planCache":"miss","timings":{"elapsed_ms":0.548125,"execute_ms":0.507,"lex_parse_ms":0.003958,"lower_ms":0.004792,"parse_ms":0.036791,"plan_ms":0.003583,"result_generation_ms":0.000041,"rewrite_ms":0.000375}}
{"planCache":"miss","timings":{"elapsed_ms":0.116958,"execute_ms":0.055708,"lex_parse_ms":0.0035,"lower_ms":0.005541,"parse_ms":0.052667000000000005,"plan_ms":0.007666999999999999,"result_generation_ms":0.000041,"rewrite_ms":0.00025}}
note: write statement 4 of 8 changed nothing; the graph is unchanged
{"planCache":"miss","timings":{"elapsed_ms":0.081083,"execute_ms":0.044333,"lex_parse_ms":0.002833,"lower_ms":0.0045000000000000005,"parse_ms":0.029834,"plan_ms":0.006334,"result_generation_ms":0.000041,"rewrite_ms":0.000167}}
note: write statement 5 of 8 changed nothing; the graph is unchanged
{"planCache":"miss","timings":{"elapsed_ms":0.112708,"execute_ms":0.049833,"lex_parse_ms":0.004,"lower_ms":0.006208,"parse_ms":0.05525,"plan_ms":0.006542,"result_generation_ms":0.000083,"rewrite_ms":0.000292}}
note: write statement 6 of 8 changed nothing; the graph is unchanged
{"planCache":"miss","timings":{"elapsed_ms":0.483625,"execute_ms":0.428375,"lex_parse_ms":0.003625,"lower_ms":0.006875,"parse_ms":0.048708,"plan_ms":0.005833,"result_generation_ms":0.000042,"rewrite_ms":0.00020800000000000001}}
{"planCache":"miss","timings":{"elapsed_ms":0.079958,"execute_ms":0.030916,"lex_parse_ms":0.0035,"lower_ms":0.005625000000000001,"parse_ms":0.041999999999999996,"plan_ms":0.006209,"result_generation_ms":0.000041,"rewrite_ms":0.00025}}
note: write statement 8 of 8 changed nothing; the graph is unchanged

Statements 1, 2, 3 and 7 have no note because their SET writes the same values again. When the database changes, export again and run the same load: it updates those properties in place and adds the new rows and references. It does not delete what the database deleted.

Rewrite the queries

A join along a foreign key becomes a step along a relationship, and a join table becomes one hop. Who works on a project with Ada, in SQL, joins works_on to itself and to both tables around it:

sqlite3 -header -list team.db "SELECT other.name AS teammate, pr.name AS project
FROM people AS ada
JOIN works_on AS mine ON mine.person_id = ada.id
JOIN works_on AS theirs ON theirs.project_id = mine.project_id AND theirs.person_id <> ada.id
JOIN people AS other ON other.id = theirs.person_id
JOIN projects AS pr ON pr.id = mine.project_id
WHERE ada.name = 'Ada'
ORDER BY teammate"
teammate|project
Grace|relate

In Cypher, the same question is one pattern. The store opens read-only:

relate --database ./team.rl --read-only --mode table "MATCH (:Person {name: 'Ada'})-[:WORKS_ON]->(proj:Project)<-[:WORKS_ON]-(other:Person)
RETURN other.name AS teammate, proj.name AS project
ORDER BY teammate"
+----------+----------+
| teammate | project  |
+----------+----------+
| "Grace"  | "relate" |
+----------+----------+

A LEFT JOIN, which keeps a row when the joined row is missing, becomes OPTIONAL MATCH. Every issue with its project, and its assignee when it has one:

sqlite3 -header -list team.db "SELECT i.title, pr.name AS project, p.name AS assignee
FROM issues AS i
JOIN projects AS pr ON pr.id = i.project_id
LEFT JOIN people AS p ON p.id = i.assignee_id
ORDER BY i.opened"
title|project|assignee
Scenario for OPTIONAL MATCH ordering|opencypher-tck|Grace
Plan cache misses on quoted labels|relate|Ada
Add a SQLite migration guide|relate|
relate --database ./team.rl --read-only --mode table "MATCH (i:Issue)-[:FILED_IN]->(proj:Project)
OPTIONAL MATCH (i)-[:ASSIGNED_TO]->(p:Person)
RETURN i.title AS title, proj.name AS project, p.name AS assignee
ORDER BY i.opened"
+----------------------------------------+------------------+----------+
| title                                  | project          | assignee |
+----------------------------------------+------------------+----------+
| "Scenario for OPTIONAL MATCH ordering" | "opencypher-tck" | "Grace"  |
| "Plan cache misses on quoted labels"   | "relate"         | "Ada"    |
| "Add a SQLite migration guide"         | "relate"         | <null>   |
+----------------------------------------+------------------+----------+

The rest of a SQL query carries over clause by clause:

SQLCypher
JOIN on a foreign keyA relationship step, (a)-[:TYPE]->(b)
A join table between two tablesOne relationship hop, with its columns as relationship properties
LEFT JOINOPTIONAL MATCH
WHERE EXISTS (SELECT …)WHERE EXISTS { <pattern> }
GROUP BYThe non-aggregated expressions in RETURN or WITH group the rows
A recursive common table expressionA variable-length pattern, -[:TYPE*1..5]->, or SHORTEST

Verify

Count the rows in the SQLite database and the nodes and relationships in the store, then compare. The first command writes the counts SQLite reports to ./expected.json; a nullable foreign key counts only its non-NULL values, the rows that become relationships. The second opens the store read-only:

sqlite3 -json team.db "SELECT
  (SELECT count(*) FROM people) AS Person,
  (SELECT count(*) FROM projects) AS Project,
  (SELECT count(*) FROM issues) AS Issue,
  (SELECT count(maintainer_id) FROM projects) AS MAINTAINS,
  (SELECT count(*) FROM issues) AS FILED_IN,
  (SELECT count(assignee_id) FROM issues) AS ASSIGNED_TO,
  (SELECT count(*) FROM works_on) AS WORKS_ON,
  (SELECT count(*) FROM knows) AS KNOWS" > expected.json
relate --database ./team.rl --read-only --mode table --fetch-params expected=expected.json 'WITH $expected[0] AS counts
UNWIND keys(counts) AS name
RETURN name, counts[name] AS in_sqlite,
       COUNT { MATCH (n) WHERE name IN labels(n) } + COUNT { MATCH ()-[r]->() WHERE type(r) = name } AS in_relate
ORDER BY name'
+---------------+-----------+-----------+
| name          | in_sqlite | in_relate |
+---------------+-----------+-----------+
| "ASSIGNED_TO" | 2         | 2         |
| "FILED_IN"    | 3         | 3         |
| "Issue"       | 3         | 3         |
| "KNOWS"       | 1         | 1         |
| "MAINTAINS"   | 1         | 1         |
| "Person"      | 3         | 3         |
| "Project"     | 2         | 2         |
| "WORKS_ON"    | 3         | 3         |
+---------------+-----------+-----------+

Equal columns mean every row arrived, and every foreign key found the row it names. A relationship count lower than its row count is a foreign key whose MATCH found no node, which drops the row without an error.

If it goes wrong

MessageExit codeCauseRemedy
Error: --fetch-params <name>: invalid JSON: EOF while parsing a value at line 1 column 01The table has no rows, and sqlite3 -json wrote an empty fileWrite [] to the file, as the export loop does.
type error: cannot parse date '<value>'6A date column holds text that is not ISO 8601, such as 10/02/2026Convert the column to YYYY-MM-DD in the export's SELECT, or store it as a string and drop the DATE type constraint.
semantic error: ConstraintValidationFailed: `issue_opened_type` property type6A load writes the date as a string, without date()Wrap the value in date().
semantic error: ConstraintValidationFailed: `person_id` uniqueness6A CREATE wrote a node with an id the store already holdsWrite the row with MERGE on its key.
A relationship count lower than its row count in Verify, and no error0A foreign key names a row that does not exist, which SQLite allows unless PRAGMA foreign_keys was on when the row was writtenFind the rows with a LEFT JOIN … WHERE <parent>.id IS NULL in SQLite, fix or drop them, export again, and run the load again.