SurrealDB and Graph Search

SurrealDB is a sexy new database server. It has a SQL-style query language, real-time queries with highly-efficient related data retrieval, advanced security permissions for multi-tenant access, and support for performant analytical workloads. SurrealDB is aiming to be a DBaaS and already supports many languages and protocols, something like Firebase plus many more features and capabilities. I've been wanting a tool like this for a log time now. I'm ready to dig in!

Today, I want to look at the graph search capabilities.

Setup

Let's start the server in a container.

docker run --name surrealdb --rm -p 8000:8000 surrealdb/surrealdb:latest start --log debug --user root --pass root memory

Now, from a new terminal, lets open the console on that container so we can run their SQL REPL.

docker exec -it surrealdb /surreal sql --conn http://localhost:8000 --user root --pass root --ns test --db test --pretty

Insert Example Data

Create Tables

An interesting thing about SurrealDB is that it can be schemaless or not. I'm not certain I am sold on this schemalessness but it surely speeds things up. So, lets take advange of it. We are going to make an example that attempts to model, trivially, a project that demands

INSERT INTO part (id, name, type, company, quantity, price) VALUES (part:FET1_diodes, "FET1", "MOSFET", company:diodes, 100, 0.27);
INSERT INTO part (id, name, type, company, quantity, price) VALUES (part:FET2_diodes, "FET2", "MOSFET", company:diodes, 300, 0.72);
INSERT INTO part (id, name, type, company, quantity, price) VALUES (part:PCB1, "PCB1", "PCB", company:acme, 0, 0.45);
INSERT INTO part (id, name, type, company, quantity, price) VALUES (part:PCB2, "PCB2", "PCB", company:acme, 200, 0.15);

INSERT INTO project (id, name, description, priority) VALUES (project:gizmo1, "Gizmo1", "Gizmo in high demand.", 99);
INSERT INTO project (id, name, description, priority) VALUES (project:gizmo2, "Gizmo2", "Gizmo no one wants", 1);

SELECT * FROM project;

This SELECT will yield:

[
  {
    "result": [
      {
        "description": "Gizmo in high demand.",
        "id": "project:gizmo1",
        "name": "Gizmo1",
        "priority": 99
      },
      {
        "description": "Gizmo no one wants",
        "id": "project:gizmo2",
        "name": "Gizmo2",
        "priority": 1
      }
    ],
    "status": "OK",
    "time": "44.049µs"
  }
]

Setup Graph

One thing that an electronics product always requires is a bill of materials (BOM). If you don't have all the parts on hand right now, you cannot build it. Let's define a datastructure that relates parts to projects.

Rather than following the usual route of making a translation table to link projects with parts, lets take advantage of the Graph features of SurrealDB.

RELATE project:gizmo1->bom->part:FET1_diodes SET quantity = 10;
RELATE project:gizmo1->bom->part:FET2_diodes SET quantity = 20;
RELATE project:gizmo1->bom->part:PCB1 SET quantity = 10;

RELATE project:gizmo2->bom->part:FET1_diodes SET quantity = 10;
RELATE project:gizmo2->bom->part:FET2_diodes SET quantity = 20;
RELATE project:gizmo2->bom->part:PCB2 SET quantity = 10;

We can now query based on these relationships. Show me all the parts for a project.

SELECT *, ->bom->part as parts FROM project:gizmo1 FETCH parts

Notice how calling FETCH parts brings in the fields as well.

[
  {
    "result": [
      {
        "description": "Gizmo in high demand.",
        "id": "project:gizmo1",
        "name": "Gizmo1",
        "parts": [
          {
            "company": "company:diodes",
            "id": "part:FET2_diodes",
            "name": "FET2",
            "price": "0.72",
            "quantity": 300,
            "type": "MOSFET"
          },
          {
            "company": "company:diodes",
            "id": "part:FET1_diodes",
            "name": "FET1",
            "price": "0.27",
            "quantity": 100,
            "type": "MOSFET"
          },
          {
            "company": "company:acme",
            "id": "part:PCB1",
            "name": "PCB1",
            "price": "0.45",
            "quantity": 0,
            "type": "PCB"
          }
        ],
        "priority": 99
      }
    ],
    "status": "OK",
    "time": "160.377µs"
  }
]

We can search the graph in the opposite direction as well. Show me all the projects with a given part.

SELECT *, <-bom<-project as projects FROM part:FET1_diodes

Here I exclude the FETCH and we just see the project keys. Also observe that rather than using a WHERE clause where id=part:FET1_diodes, I just point to the row in the FROM clause. Pretty convenient syntax.

[
  {
    "result": [
      {
        "company": "company:diodes",
        "id": "part:FET1_diodes",
        "name": "FET1",
        "price": "0.27",
        "projects": [
          "project:gizmo2",
          "project:gizmo1"
        ],
        "quantity": 100,
        "type": "MOSFET"
      }
    ],
    "status": "OK",
    "time": "111.559µs"
  }
]

Feel The Power

Let's try something a bit more exciting now. Let's query the database for the highest priority project with all the parts in inventory.

SELECT * FROM project
WHERE count(->(bom WHERE out.quantity >= quantity)->part) = count(->bom->part)
ORDER BY priority DESC
LIMIT 1;

This yields Gizmo2 because we don't have any PCB1 in stock. It works because we ask it to compare the bom part's inventory with the bom quantity field. If inventory is >= quantity, we know we have that part. Then we just compare the total parts with the available parts and if they are equal we know we are in business.

[
  {
    "result": [
      {
        "description": "Gizmo no one wants",
        "id": "project:gizmo2",
        "name": "Gizmo2",
        "priority": 1
      }
    ],
    "status": "OK",
    "time": "354.205µs"
  }
]

Let's write a query to find all the projects with missing parts.

SELECT
  name,
  ->(bom WHERE out.quantity < quantity)->part AS missing_parts
FROM project
WHERE count(->(bom WHERE out.quantity < quantity)->part) > 0
ORDER BY priority DESC
FETCH missing_parts;

[
  {
    "result": [
      {
        "missing_parts": [
          {
            "company": "company:acme",
            "id": "part:PCB1",
            "name": "PCB1",
            "price": "0.45",
            "quantity": 0,
            "type": "PCB"
          }
        ],
        "name": "Gizmo1"
      }
    ],
    "status": "OK",
    "time": "176.148µs"
  }
]

Now, lets add some PCB1s to inventory and find the highest priority project again.

-- Pretend 20 more PCB1s came in the mail
UPDATE part SET quantity = quantity + 20 WHERE id=part:PCB1;
-- Find the highest priority project with all parts in stock
SELECT * FROM project
WHERE count(->(bom WHERE out.quantity >= quantity)->part) = count(->bom->part)
ORDER BY priority DESC
LIMIT 1;

I submitted both these at the same time so the results return in an array (separated by a ;). SurrealDB also supports ACID transactions which is super cool!

[
  {
    "result": [
      {
        "company": "company:acme",
        "id": "part:PCB1",
        "name": "PCB1",
        "price": "0.45",
        "quantity": 20,
        "type": "PCB"
      }
    ],
    "status": "OK",
    "time": "114.179µs"
  },
  {
    "result": [
      {
        "description": "Gizmo in high demand.",
        "id": "project:gizmo1",
        "name": "Gizmo1",
        "priority": 99
      }
    ],
    "status": "OK",
    "time": "392.314µs"
  }
]

Conclusion

I would like to see a recursive graph search baked into the system so that I could more easily support the idea building of parts from other parts in inventory.

I will be keeping a close eye on SurrealDB and I hope to see it grow into a full fledged database. I think it has a lot of potential and I am excited to see where it goes. In fact, I am going to reach out to them and find out if I can help make this a reality.

craftyc0der/README.md