Migrating from SQL to TerminusDB

This guide shows how to model relational data in TerminusDB using terminusdb_ex. We take a typical SQL schema and translate it step by step, highlighting the differences and what you gain.

The SQL schema

Consider a simple blog application with users, posts, and comments:

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id       SERIAL PRIMARY KEY,
    title    VARCHAR(255) NOT NULL,
    body     TEXT,
    author_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE comments (
    id       SERIAL PRIMARY KEY,
    body     TEXT NOT NULL,
    post_id  INTEGER NOT NULL REFERENCES posts(id),
    author_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

SQL queries you would write

-- All posts by Alice
SELECT p.* FROM posts p
JOIN users u ON p.author_id = u.id
WHERE u.name = 'Alice';

-- All comments on Alice's posts
SELECT c.* FROM comments c
JOIN posts p ON c.post_id = p.id
JOIN users u ON p.author_id = u.id
WHERE u.name = 'Alice';

-- Count comments per post
SELECT p.title, COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title;

Step 1: Create a database

config = TerminusDB.Config.new(endpoint: "http://localhost:6363")

{:ok, _} =
  TerminusDB.Database.create(config, "blog",
    label: "Blog",
    comment: "A blog migrated from SQL",
    schema: true
  )

config = TerminusDB.Config.with_database(config, "blog")

Step 2: Define the schema

In SQL, tables define columns with types and foreign keys. In TerminusDB, classes define properties with types and references to other classes.

Key differences:

Insert the schema documents

# User class (maps to the users table)
{:ok, _} =
  TerminusDB.Document.insert(config,
    %{
      "@type" => "Class",
      "@id" => "User",
      "@key" => %{"@type" => "Lexical", "@fields" => ["email"]},
      "name" => "xsd:string",
      "email" => "xsd:string"
    },
    author: "admin",
    message: "Add User schema",
    graph_type: :schema
  )

# Post class (maps to the posts table)
# author_id foreign key becomes a reference: "User"
{:ok, _} =
  TerminusDB.Document.insert(config,
    %{
      "@type" => "Class",
      "@id" => "Post",
      "@key" => %{"@type" => "Random"},
      "title" => "xsd:string",
      "body" => "xsd:string",
      "author" => "User",
      "created_at" => "xsd:dateTime"
    },
    author: "admin",
    message: "Add Post schema",
    graph_type: :schema
  )

# Comment class (maps to the comments table)
# post_id and author_id foreign keys become references
{:ok, _} =
  TerminusDB.Document.insert(config,
    %{
      "@type" => "Class",
      "@id" => "Comment",
      "@key" => %{"@type" => "Random"},
      "body" => "xsd:string",
      "post" => "Post",
      "author" => "User",
      "created_at" => "xsd:dateTime"
    },
    author: "admin",
    message: "Add Comment schema",
    graph_type: :schema
  )

Verify the schema

{:ok, frame} = TerminusDB.Schema.frame(config, "Post")
# => %{
#   "@type" => "Class",
#   "title" => "xsd:string",
#   "body" => "xsd:string",
#   "author" => "User",
#   "created_at" => "xsd:dateTime"
# }

Step 3: Insert data

In SQL you INSERT rows. In TerminusDB you insert documents.

SQL INSERT vs TerminusDB document insert

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO posts (title, body, author_id) VALUES ('First Post', 'Hello', 1);
INSERT INTO comments (body, post_id, author_id) VALUES ('Nice!', 1, 2);

# Insert users
{:ok, _} =
  TerminusDB.Document.insert(config,
    %{"@type" => "User", "name" => "Alice", "email" => "alice@example.com"},
    author: "admin", message: "Add Alice"
  )

{:ok, _} =
  TerminusDB.Document.insert(config,
    %{"@type" => "User", "name" => "Bob", "email" => "bob@example.com"},
    author: "admin", message: "Add Bob"
  )

# Insert a post (reference the author by @id, not by numeric FK)
{:ok, _} =
  TerminusDB.Document.insert(config,
    %{
      "@type" => "Post",
      "title" => "First Post",
      "body" => "Hello world",
      "author" => "User/alice@example.com",
      "created_at" => DateTime.utc_now() |> DateTime.to_iso8601()
    },
    author: "admin", message: "Add first post"
  )

# Insert a comment (reference both post and author by @id)
{:ok, _} =
  TerminusDB.Document.insert(config,
    %{
      "@type" => "Comment",
      "body" => "Nice!",
      "post" => "Post/<auto-generated-id>",
      "author" => "User/bob@example.com",
      "created_at" => DateTime.utc_now() |> DateTime.to_iso8601()
    },
    author: "admin", message: "Add comment"
  )

The key difference: foreign keys become document references. You reference another document by its @id (e.g. "User/alice@example.com"), not by a numeric ID. TerminusDB follows these references automatically during retrieval, so there is no need for JOINs.

Step 4: Query data

All posts by Alice

SQL (with JOIN):

SELECT p.* FROM posts p
JOIN users u ON p.author_id = u.id
WHERE u.name = 'Alice';

TerminusDB (no JOIN, template query):

{:ok, posts} =
  TerminusDB.Document.query(config, %{
    "@type" => "Post",
    "author" => "User/alice@example.com"
  })

Or, if you only know the name and not the @id, first look up the user, then query their posts:

{:ok, users} = TerminusDB.Document.query(config, %{"@type" => "User", "name" => "Alice"})
alice_id = hd(users)["@id"]

{:ok, posts} = TerminusDB.Document.query(config, %{"@type" => "Post", "author" => alice_id})

All comments on Alice's posts

SQL (two JOINs):

SELECT c.* FROM comments c
JOIN posts p ON c.post_id = p.id
JOIN users u ON p.author_id = u.id
WHERE u.name = 'Alice';

TerminusDB (follow references, no JOINs):

# Get Alice's posts, then get comments for each post
{:ok, posts} = TerminusDB.Document.query(config, %{"@type" => "Post", "author" => alice_id})

comments =
  for post <- posts,
      {:ok, cs} <- [TerminusDB.Document.query(config, %{"@type" => "Comment", "post" => post["@id"]})],
      c <- cs do
    c
  end

With unfold: true (the default), TerminusDB follows references automatically, so you can retrieve a post and its author in one call:

{:ok, posts} = TerminusDB.Document.get(config, type: "Post", as_list: true, unfold: true)
hd(posts)["author"]["name"]  # => "Alice"

Step 5: Update and delete

SQL

UPDATE posts SET title = 'Renamed' WHERE id = 1;
DELETE FROM comments WHERE id = 5;

TerminusDB

# Replace (update) a document
{:ok, post} = TerminusDB.Document.get(config, id: "Post/abc123")
{:ok, _} =
  TerminusDB.Document.replace(config,
    Map.put(post, "title", "Renamed"),
    author: "admin", message: "Rename post"
  )

# Delete a document
{:ok, _} = TerminusDB.Document.delete(config, id: "Comment/xyz789", author: "admin", message: "Remove comment")

Key takeaways

What you gain by migrating

• No JOINs: references are followed automatically by the graph engine.
• Version history: every change is an immutable commit. Time-travel to any past state, diff two points in time, branch the entire database.
• Schema validation: the schema is enforced on every write, not optional.
• Flexible nesting: subdocuments allow deeply nested JSON without separate tables.
• Audit trail: every commit records author, timestamp, and message.