Chapter 2: The grey book

Time elapsed: 8h

It’s the afternoon of your first day with SurrealDB. You are having tea outside with a grey book that you noticed earlier this morning.

‎

There are a lot of books in the library that you don’t understand. This doesn’t concern you for the most part, as you will probably understand them later and have other things to do in the meantime.

‎

But this grey book in your hand is certainly a mystery! It has no author, publisher, date, or anything else, just a simple title: THE FORMULA.

‎

Inside the cover of the book is a sheet of paper on which someone has scribbled a few words telling you to “calculate and track the output of the formula”.

‎

How you wish you could! But the rest of the book is hundreds and hundreds of pages of mathematical signs and equations that have something to do with a “humanity hope ratio”, whatever that is. How are you supposed to calculate and track something that you don’t even understand?

‎

Well, perhaps one day! And learning to use this SurrealDB database should eventually provide the answers. It’s time to go back inside and continue learning how to use it.

Record IDs

We learned in the last chapter that all record IDs in SurrealDB must be unique, are randomly generated by default, but that we can set a record ID ourselves. Let’s get into some of the details on how record IDs work.

Standard record IDs

Record IDs have the format <table_name>:<id> . Let’s try creating some records with our own IDs to see what happens.

A person with the ID aeon :

CREATE person:aeon;

Response
[
    {
        id: person:aeon
    }
]

A book with the ID aeon , created after a person with the ID aeon . This is not a duplicate, because the record ID is “book:aeon”, not “aeon”:

CREATE book:aeon;

Response
[
    {
        id: book:aeon
    }
]

One more person with the ID aeon . This ID already exists, so SurrealDB refuses to create it.

CREATE person:aeon;

Response
'Database record `person:aeon` already exists'

At this point you might be curious about what else you can create a record ID with. How about Egyptian hieroglyphs? Let’s see if we can create a record with an ID set to 𓅃 , the hieroglyph for a falcon.

CREATE bird:𓅃;

Response
"Parse error: Invalid token `𓅃`"

Even Greek or common accented characters won’t work either:

CREATE person:Αἰών;
CREATE person:éon;

So it looks like the answer is no?

But don’t despair, because the answer is actually yes! Let’s learn some more about record IDs to find out how.

Other record ID formats

Record IDs in SurrealDB are extremely flexible and allow for a lot of possibilities.

A regular record ID can contain unaccented letters, numbers, and underscores. A record ID that isn’t composed of one of these three elements won’t parse.

If you need to use accented or other characters, you can enclose it in backticks. SurrealDB will then save them internally as strings and enclose such IDs between a ⟨ and ⟩ (mathematical braces) to mark them as complex characters. Note that these braces are different from < and > on your keyboard. You could also directly type ⟨ and ⟩ instead of using backticks, but it’s not very likely that you have these characters on your keyboard.

Knowing this, we can create record IDs with any string we like:

CREATE building:`façade`;

Response
[
    {
        id: building:⟨façade⟩
    }
]

CREATE building:`faç
   ad
   e`;

Response
[
	{
		id: building:⟨faç
ad
e⟩
	}
]

CREATE user:`7962a62e-7782-423c-b899-04a3c9d2d552`;

Response
[
    {
        id: user:⟨7962a62e-7782-423c-b899-04a3c9d2d552⟩
    }
]

The same rules apply to the table name part of the record ID, which can also be enclosed in backticks.

CREATE `Gebäude`:`façade`;

Response
[
	{
		id: ⟨Gebäude⟩:⟨façade⟩
	}
]

The Egyptian hieroglyph 𓅃 we tried before will now work, and so will emojis.

CREATE 
    bird:`𓅃`,
    artist:`👉**BRUCE DICKINSON**👈`,
    `𓊖`:`𓏠𓈖𓄤𓆑𓂋𓉴𓊖`;

Response
[
	{
		id: bird:⟨𓅃⟩
	},
	{
		id: artist:⟨👉**BRUCE DICKINSON**👈⟩
	},
	{
		id: ⟨𓊖⟩:⟨𓏠𓈖𓄤𓆑𓂋𓉴𓊖⟩
	}
]

Bonus points if you can read the last record - it’s the name of a real place!

How record IDs are stored

Now it’s time for a small quiz. The next two records created have almost the same input, but the output in the second record ID is enclosed in ⟨⟩ . Why do you think that is the case?

CREATE person:9223372036854775807, person:9223372036854775808;

Response
[
	{
		id: person:9223372036854775807
	},
	{
		id: person:⟨9223372036854775808⟩
	}
]

If you’re not sure, here is another hint. We can see the same behaviour for these two records as well.

CREATE person:-9223372036854775808, person:-9223372036854775809;

Response
[
	{
		id: person:-9223372036854775808
	},
	{
		id: person:⟨-9223372036854775809⟩
	}
]

It looks like only the second records are being stored as strings! There is a good reason for this.

If you create a record with an ID that is an integer, SurrealDB will also store it as an integer if possible because they require less space to store and are thus more efficient. But the numbers 9223372036854775808 and -9223372036854775809 are beyond the upper and lower limits of a 64-bit signed integer, and so SurrealDB has no choice but to store them as strings instead.

We can demonstrate that SurrealDB tries to choose an integer if possible when it sees a number in an ID by trying to create two records that look like they have a different ID, but actually do not. The second query won’t work, because SurrealDB treats the number 00005 as a 5.

CREATE person:5;
CREATE person:00005;

Response
-------- Query 1 --------

[
	{
		id: person:5
	}
]

-------- Query 2 --------

'Database record `person:5` already exists'

So if you truly want to create a separate ‘person:00005’, you can enclose it in backticks to force SurrealDB to turn the 00005 into a string.

CREATE person:`00005`;

Response
[
    {
        id: person:⟨00005⟩
    }
]

You can also use SET to set a record’s id . In this case, SurrealDB will join the table name to the id that you chose to set. If you set the id to a string, it will show up inside the ⟨⟩ brackets.

CREATE bird   SET id = "𓅃";
CREATE person SET id = 0;
CREATE person SET id = "0";

Response
-------- Query --------

[
	{
		id: bird:⟨𓅃⟩
	}
]

-------- Query --------

[
	{
		id: person:0
	}
]

-------- Query 3 --------

[
	{
		id: person:⟨0⟩
	}
]

Creating multiple records with CREATE

The CREATE statement is usually used to create a single record, but there are some methods to create more than one at the same time if you prefer.

As we saw in the last chapter, you can separate the table or record names with commas to create more than one record at a time.

CREATE townsperson, townsperson, person SET name = "Just a person";

Response
[
	{
		id: townsperson:n40vh685pw6mr5ynvtpf,
		name: 'Just a person'
	},
	{
		id: townsperson:ihsorngt74vqf69vyju0,
		name: 'Just a person'
	},
	{
		id: person:tlm5i8p839v67uknnrb1,
		name: 'Just a person'
	}
]

CREATE also has a special parallel bar (|| ) syntax used to create more than one record. Inside these bars, you can specify the number of records to create. You can also combine this syntax with the comma method we learned just above.

// Three `townsperson` records with a random id
CREATE |townsperson:3| SET name = "Just a person";
// Two `person` and three `townsperson` records with a random id
CREATE |person:2|, |townsperson:3| SET name = "Just a person";

If you don’t want to see the output for a statement, you can follow it with RETURN NONE . This will be especially useful if you need to create a large number of records, such as through the following statement which creates 1000 person records.

CREATE |person:1000| RETURN NONE;

Response
[]

You can also use SurrealDB’s range operator (.. ) to create more than one record with that ID. This query will not return townsperson records with random IDs; this time, they will show up as townsperson:1, townsperson:2 , all the way up to townsperson:10 .

CREATE |townsperson:1..10|;

This range syntax is useful in a lot of other places that we will encounter throughout this book. In the meantime, let’s move back to statements that return a single record and the ways to make working with them a little more convenient.

Working with single records

We learned in the last chapter that a query will return an array by default, even if only a single item is returned. As the documentation for the SELECT statement says:

By default, SurrealDB returns an array of JSON-like objects called records instead of a tabular structure of rows and columns.

This happens with even the simplest of queries as well when you use statements like SELECT and CREATE . RETURN is the only exception here as it simply returns the value that follows it. Compare the following queries:

RETURN 9;
SELECT * FROM 9;
RETURN [ 9 ]; // You can use [] if you want to emulate the SELECT output

Response
-------- Query --------

9

-------- Query --------

[
	9
]

-------- Query --------

[
	9
]

But sometimes you will find it more convenient to only return a single record, instead of an array that contains a single record. Let’s take a look.

ONLY

For a query that SurrealDB can guarantee will only return one record, you can add the ONLY keyword before the record ID to return just the result instead of an array of results.

CREATE person:1 SET name = 'Aeon';
SELECT name FROM person:1;
SELECT name FROM ONLY person:1;

Response
-------- Query --------

[
	{
		id: person:1,
		name: 'Aeon'
	}
]

-------- Query --------

[
	{
		name: 'Aeon'
	}
]

-------- Query --------

{
	name: 'Aeon'
}

You can insert the ONLY keyword into other statements too, like CREATE .

CREATE person:1;
CREATE ONLY person:2;

Response
-------- Query --------

[
	{
		id: person:1
	}
]

-------- Query --------

{
	id: person:2
}

ONLY with LIMIT 1

Another way to work with a single record at a time is with the LIMIT keyword, followed by the number 1. As the name suggests, you can use the LIMIT keyword to limit the number of results from a query.

While using LIMIT 1 on its own will still return an array of results (with a single record inside), SurrealDB is smart enough in this case to realize that only a single record can be returned, and thus will let you use the ONLY keyword. For example:

CREATE person:1, person:2;

Response
[
    {
        id: person:1
    },
    {
        id: person:2
    }
]

SELECT * FROM ONLY person LIMIT 1;

Response
{
    id: person:1
}

But you can’t trick it into trying to return a single record with ONLY when more than one record might be returned:

SELECT * FROM ONLY person LIMIT 2;

Response
Expected a single result output when using the ONLY keyword

When more than one record is returned using LIMT , their order will be determined by their ID. So if you create the following three person records, and then query for SELECT * FROM person LIMIT 2 , you will see that person:1 and person:2 are returned first.

CREATE person:2;
CREATE person:1;
CREATE person:3;
SELECT * FROM person LIMIT 2;

[
    {
        "id": "person:1"
    },
    {
        "id": "person:2"
    }
]

Here again we see an example of automatic ordering in SurrealDB. This behaviour makes sense, because being able to order by id is one of the main reasons to choose your own id instead of using the random id that SurrealDB creates by default.

There are even more interesting ways to create your own IDs in SurrealDB, but let’s save those for the next chapter so that the new information is not too overwhelming.

VALUE

SurrealDB has another keyword called VALUE that is a bit similar to ONLY in the sense that it produces a simpler output, but instead of returning a single result, VALUE returns only the value(s) that follow a field name - without the field name itself.

CREATE person:1, person:2, person:3;
SELECT id FROM person:1;

Response
[
    {
        id: person:1
    }
]

CREATE person:1, person:2, person:3;
SELECT VALUE id FROM person:1;

Response
[
    person:1
]

CREATE person:1, person:2, person:3;
SELECT VALUE id FROM person;

Response
[
    person:1,
    person:2,
    person:3
]

And since VALUE works to only return values, and ONLY to only return a single record, you can also combine the two. This will result in just a single value from a single object.

CREATE person:1, person:2, person:3;
SELECT VALUE id FROM ONLY person:1;

Response
person:1

Tip: Use Surrealist’s Designer view to visualize your database

We haven’t done anything serious with SurrealDB just yet, but we do know that even a simple query like CREATE person will create a table called person that did not exist before, because you can see this declaration when you type INFO FOR DB :

CREATE person;
INFO FOR DB;

Response
[
    {
        id: person:s5s12n4fxh3svxe56gwf
    }
]

{
        accesses: {},
        analyzers: {},
        functions: {},
        models: {},
        params: {},
        tables: {
                person: 'DEFINE TABLE person TYPE ANY SCHEMALESS PERMISSIONS NONE'
        },
        users: {}
}

As you create more and more tables, you might prefer to have a visual view of the tables and their makeup. You can do that by clicking on the Designer tab in Surrealist.

Try opening up a new Sandbox database inside Surrealist and typing the following simple queries:

CREATE person SET name = 'Billy';
CREATE cat;
CREATE dog SET age = 2;

Once you do that, the Designer view will show you three tables.

The tables won’t show you very much information at all! They will be nothing more than three tables with no defined fields, and have nothing to do with each other. But that is precisely the point, because at the moment we have three tables that are SCHEMALESS, and no fields have been defined despite the fact that we have given name = 'Billy' to our person record and age = 2 to our dog record. Surrealist has no further information from us at this point.

If you click on one of the tables, however, the magic begins. Doing so lets you start defining these tables as you see fit, including schemas, permissions, and so on.

We will start to learn about the details of creating a schema in Chapter 5, and Chapter 14 is devoted to all of the ins and outs of Surrealist. In the meantime, if you are already looking to define a schema for your database and aren’t very familiar with SurrealDB yet, this is the easiest way to start.

Comments

We’ll finish up the chapter with something extremely easy: commenting out your code. SurrealDB lets you comment out code in quite a few ways:

• With -- , // , or # to comment out everything to the right on a single line.
• With /* followed by */ to comment out everything in between, even over multpile lines.

A single example should be enough to demonstrate all of these methods:

CREATE person SET
    name = "Aeon", -- Aeon is the main character
    town = "Toria", // Toria is the main town in the story
    age = "Unknown", # Unknown but pretty young, probably 20-ish
    mood = /* */ "Pretty good",
    other_notes /*
    You can write comments
    over multiple lines too
    this way
    */ = "Nothing at the moment";

Response
[
	{
		age: 'Unknown',
		id: person:5nza6ujqb2j0akeh3ggd,
		mood: 'Pretty good',
		name: 'Aeon',
		other_notes: 'Nothing at the moment',
		town: 'Toria'
	}
]

[
	{
		id: person:1
	}
]

[
	{
		id: person:⟨00001000000000000000000000⟩
	}
]

// Three separate CREATE statements
CREATE person:1;
CREATE person:2;
CREATE person:3;

// A single statement with each record separated by a comma
CREATE person:1, person:2, person:3;

// Range syntax from 1 to 3
CREATE |person:1..3|;

CREATE `📅`:`🎃` SET `👜` = ["🍬", "🍭", "🍓", "🍡", "🍬", "🍭"];

CREATE ONLY person:aeon SET name = "Aeon";