SurrealDB tutorial

Let’s learn more about SurrealDB with a real world use case, albeit stripped down for learning purposes.

For the purposes of this SurrealDB tutorial I have a collection of old farming magazines. Suppose I was to build a website where I sell them. It might be useful to have a feature that allows people to search what topics are covered in a particular issue, or search for all issues that cover a specific topic.

SurrealDB makes your RELATE relationships when you write the data making searches faster.

Send some sats if you like this article

Watch this if you want to see how to use SurrealDB with Python

How to create the tables

Let’s start this SurrealDB tutorial by looking at how to create 2 schemaless tables – you actually create them just by adding records using your desired table name as part of the CREATE syntax.

For instance this will create a table called ‘powerfarming‘ (in reality I’d probably call it pf or even call it mag or magazine to accommodate other publications.

CREATE powerfarming SET issue="February", year=1983;

Well have a table for the issues (a monthly publication, so we’ll keep it to just 1 year)

We’ll have a table for the topics, this can also contain more detail about the article titles and the page it starts on

The tables

So far so good. We now have to create the tables and add the relationships (RELATE).

REMOVE  TABLE powerfarming;
REMOVE TABLE topic;
REMOVE TABLE features;
--
CREATE powerfarming SET issue="January", year=1983;
CREATE powerfarming SET issue="February", year=1983;
CREATE powerfarming SET issue="March", year=1983;
CREATE powerfarming SET issue="April", year=1983;
CREATE powerfarming SET issue="May", year=1983;
CREATE powerfarming SET issue="June", year=1983;
CREATE powerfarming SET issue="July", year=1983;
CREATE powerfarming SET issue="August", year=1983;
CREATE powerfarming SET issue="October", year=1983;
CREATE powerfarming SET issue="November", year=1983;
CREATE powerfarming SET issue="December", year=1983;
--
CREATE topic SET name="ploughs";
CREATE topic SET name="potato";
CREATE topic SET name="harvesters";
CREATE topic SET name="sprays";
CREATE topic SET name="trailers";
CREATE topic SET name="tractors";
CREATE topic SET name="straw";
CREATE topic SET name="mowers";
CREATE topic SET name="buyersguide";
CREATE topic SET name="welding";
CREATE topic SET name="hedgers";

Add the relationships and content

Much like adding data to a normal SQL table except we’re also adding content to the NEW table which is the “Features” table

Let $mag = SELECT id from powerfarming where issue="January";
Let $top = SELECT id from topic where name="trailers";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Buyer's Guide to trailers",
        page: {
			start: 57
		    },
    
	}};

Let $mag = SELECT id from powerfarming where issue="January";
Let $top = SELECT id from topic where name="sprays";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Sprays and sprayers for the spring",
        page: {
			start: 23
		    },
    
	}};

Let $mag = SELECT id from powerfarming where issue="January";
Let $top = SELECT id from topic where name="welding";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Ways with welding: 1",
        page: {
			start: 76
		    },
    
	}};

Let $mag = SELECT id from powerfarming where issue="March";
Let $top = SELECT id from topic where name="welding";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Ways with welding: 2",
        page: {
			start: 72
		    },
    
	}};

Let $mag = SELECT id from powerfarming where issue="May";
Let $top = SELECT id from topic where name="welding";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Ways with welding: 3",
        page: {
			start: 64
		    },
    
	}};

Let $mag = SELECT id from powerfarming where issue="June";
Let $top = SELECT id from topic where name="welding";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Ways with welding: 4",
        page: {
			start: 74
		    },
    
	}};

Let $mag = SELECT id from powerfarming where issue="June";
Let $top = SELECT id from topic where name="trailers";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Buyer's guide to big trailers",
        page: {
			start: 29
		    },
    
	}};

Let $mag = SELECT id from powerfarming where issue="May";
Let $top = SELECT id from topic where name="hedgers";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Buyer's guide hedgers and ditchers",
        page: {
			start: 39
		    },
    
	}};


Let $mag = SELECT id from powerfarming where issue="August";
Let $top = SELECT id from topic where name="ploughs";

RELATE $mag->features->$top
CONTENT {
    {
		title: "Buyer's Guide to ploughs",
        page: {
			start: 33
		    },
    
	}};

Querying the database

Query 1 – Find the topics featured in the January issue

-- find the topics featured in the January issue
SELECT id, ->features->topic as tp from powerfarming where issue="January" fetch tp;

[
  {
    "result": [
      {
        "id": "powerfarming:phz7ltxexthyriq0e77u",
        "tp": [
          {
            "id": "topic:i9nx50jf4bzv2clxozhn",
            "name": "trailers"
          },
          {
            "id": "topic:jtlnabauul27tsjpj42p",
            "name": "welding"
          },
          {
            "id": "topic:o5vo4uihdejeaylrnhhk",
            "name": "sprays"
          }
        ]
      }
    ],
    "status": "OK",
    "time": "2.315015ms"
  }
]

We’ve successfully retrieved the topics we’d entered for the January issue.

Query 2 – Find the Power Farming issues that feature topic of welding

-- find the power farming issues that feature topic of welding
Let $weld = SELECT id from topic where name="welding";
$weld;

SELECT  <-features<-powerfarming  as issues from $weld fetch issues ;

[
  {
    "result": null,
    "status": "OK",
    "time": "1.754916ms"
  },
  {
    "result": [
      {
        "id": "topic:jtlnabauul27tsjpj42p"
      }
    ],
    "status": "OK",
    "time": "286.664µs"
  },
  {
    "result": [
      {
        "issues": [
          {
            "id": "powerfarming:1caj1n760zf875mfgoa4",
            "issue": "March",
            "year": 1983
          },
          {
            "id": "powerfarming:phz7ltxexthyriq0e77u",
            "issue": "January",
            "year": 1983
          },
          {
            "id": "powerfarming:rgo7xtwg1rqlxvw42j9u",
            "issue": "June",
            "year": 1983
          },
          {
            "id": "powerfarming:tbjf3940jlv51148s0mn",
            "issue": "May",
            "year": 1983
          }
        ]
      }
    ],
    "status": "OK",
    "time": "1.695317ms"
  }
]