Chapter 4: The security door

Time elapsed: 1w

The technology of the ancient world continues to amaze you. Today, you discovered another room with its own set of computers that have nothing to do with collecting data. These computers are used for security. One of their security features is the ability to close the entrance to the whole compound for a set period of time.

‎

Strangely enough, there is no option to close the security door permanantly. The longest durations that it will accept are up to 24 hours, up to 7 days, up to 52 weeks, up to 100 years…or the maximum value, one “age”. An “age” must be longer than a century, but how long is it actually? You’re curious, but there’s no way that you’ll ever need to select that option.

‎

The shorter durations could be useful if you need to leave the compound for a while and don’t want people to touch the books or computers. After all, only you and your team understand their true value. You often think about how best to present this new knowledge to the world once you have learned to properly use a database. Will the sudden rediscovery of the knowledge of the ancient world please them, or will they be afraid of it?

‎

And maybe the formula in that grey book will come in handy one day? Its “humanity hope ratio” sounds like a good thing to know if you can ever figure it out.

‎

In the meantime, it’s time to gather your courage and learn how to delete records.

The DELETE keyword

DELETE on its own deletes everything

Aeon’s caution about deleting records is well merited, because by default the DELETE keyword followed by a record name will delete each and every record.

Let’s give this a try! First, let’s create a large number of person records without any fields. We’ll go with fifty of them.

CREATE |person:50| RETURN NONE;

Response
[]

We can prove that there are 50 person records with the following query that uses the count() function, which counts the number of items it receives.

RETURN count(SELECT * FROM person);

Response
50

Now let’s try deleting them all! We’ll also do a SELECT after the deletion to make sure that the person records are all gone.

CREATE |person:50| RETURN NONE;
DELETE person;
SELECT * FROM person;

Response
[]

That was quick.

Using WHERE in a DELETE statement

You can use the WHERE clause in a DELETE statement to ensure that you don’t delete each and every record for a table. The syntax is not very surprising. Here are two quick examples:

DELETE person WHERE name = 'Aeon';
DELETE person WHERE name = 'Aeon' AND town = 'Toria';

When deleting large amounts of data, you might want to confirm that your WHERE statement is working as you expect in order to avoid any unpleasant surprises caused by human error. For example, imagine that we created a lot of person records that use the rand::int() function to give each one a random integer.

CREATE |person:50| SET num = rand::int();

Later on, we decide that we want to delete the ones that have a num that is negative. A WHERE num = 0 should do it. But if we have a typo in our DELETE statement…

CREATE |person:50| SET num = rand::int() RETURN NONE;
DELETE person WHERE num;
SELECT * FROM person;

Response to SELECT statement
[]

A DELETE person WHERE num statement evaluates to “delete any person record that has a value at the field num”. In other words, all of them.

A bit of first playing around with the output by using SELECT instead of DELETE will let you delete with confidence.

CREATE |person:50| SET num = rand::int();
// Now see how many there are
RETURN count(SELECT * FROM person);
// Will this delete about half of them?
RETURN count(SELECT * FROM person WHERE num);
// Oops! Try the WHERE clause again
RETURN count(SELECT * FROM person WHERE num < 0);
// Looks good, now change SELECT * FROM to DELETE
DELETE person WHERE num < 0;

One other way to avoid mistakes with DELETE is by returning the records that were deleted.

Returning BEFORE, AFTER, and more

A lot of operations in SurrealDB involve a change of state: using CREATE to make new records, UPDATE to change them, DELETE to remove them, and so on. By default, these operations return the result after the query is completed. And for a DELETE query, that means returning nothing (an empty array).

But sometimes we might want to return information from what happened before a query was completed, especially when using UPDATE and DELETE. SurrealDB offers this convenience via the RETURN and BEFORE (and AFTER) keywords.

Let’s create Aeon again:

CREATE person SET name = 'Aeon';

Response
[
    {
        id: person:7ykci42eb08vszvcur2t,
        name: 'Aeon'
    }
]

Next, we’ll update Aeon with a town but instruct SurrealDB to return the BEFORE state of the query: the person before the update happened.

UPDATE person SET town = 'Toria' RETURN BEFORE;

Response to UPDATE statement
[
    {
        id: person:7ykci42eb08vszvcur2t,
        name: 'Aeon'
    }
]

You can also choose to RETURN DIFF, which returns the changes performed to turn the data from its previous state to its current state. This is one of the nice ways (among others) to keep track of what is going on with your data instead of just using queries to view the final results.

UPDATE person SET age = 20, fears_delete = true RETURN DIFF;
UPDATE person UNSET age RETURN DIFF;

Response to DIFF statements
-------- Query --------

[
	[
		{
			op: 'add',
			path: '/age',
			value: 20
		},
		{
			op: 'add',
			path: '/fears_delete',
			value: true
		}
	]
]

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

[
	[
		{
			op: 'remove',
			path: '/age'
		}
	]
]

This output is in a format known as a JSON patch.

The keywords BEFORE and AFTER are accessed in a pretty interesting way. Learning about how that works will teach us something new about how statements are processed in SurrealDB.

Using $before, $after, and other statement values

The keywords BEFORE and AFTER are a nice convenience, but you can’t use both at the same time.

UPDATE person SET age = 20 RETURN BEFORE, AFTER;

Response
"There was a problem with the database: Parse error: Unexpected token `,`, expected Eof
 --> [1:46]
  |
1 | UPDATE person SET name = 'Aeon' RETURN BEFORE, AFTER;
  |                                              ^ 
"

Or can you? What if we make them lowercase and add a dollar sign in front of them?

UPDATE person SET age = 20 RETURN $before, $after;

Response
[
	{
		after: {
			age: 20,
			id: person:zf8dwubdw0efwnu46bbd,
			name: 'Aeon',
			town: 'Toria'
		},
		before: {
			id: person:zf8dwubdw0efwnu46bbd,
			name: 'Aeon',
			town: 'Toria'
		}
	}
]

So how did that work? It’s not because every keyword has a lowercase dollar sign equivalent. What we’ve seen instead is our first example of SurrealDB’s preset parameters, which it sets for any values that might be relevant to an operation. The BEFORE and AFTER keywords are simply a convenience to access either of these parameters.

You can also make your own parameters. Here are the first two things to know about them:

• Their names are case-sensitive, so you if you type something like $BEFORE or $AfTeR then it will not work.
• You can make your own inside a query by using the LET keyword.

Using the LET keyword to create parameters

Here’s an example of using LET to create a parameter. We can make one called $name and then pass it into a CREATE statement.

LET $name = "Aeon";
CREATE person SET name = $name;

Response
[
    {
        id: person:7i82mzuy19z1uydkmqym,
        name: 'Aeon'
    }
]

If you’re concerned about type safety and code readability, you can add a type declaration after LET. This lets you put SurrealDB to work to ensure that an expected type is correct instead of always needing to rely on your own eyes and mind.

Specifying a type makes it easier to avoid unexpected surprises. Take this parameter for example that we would like to be a string.

CREATE person SET name = "Aeon";
LET $name: string = SELECT name FROM person;

Not quite! Instead of a string, we got an object inside an array which has a field that is a string.

Response
"Found [{ name: 'Aeon' }] for param $name, but expected a string"

We learned the VALUE keyword in a previous chapter. Maybe that will do the trick?

CREATE person SET name = "Aeon";
LET $name: string = SELECT VALUE name FROM person;

Closer, but not quite! We got an array of strings, instead of a single string.

Response
"Found ['Aeon'] for param $name, but expected a string"

We can finally get this LET statement to work by using [0] to access the first item of the output, which is indeed a string.

CREATE person SET name = "Aeon";
LET $name: string = (SELECT VALUE name FROM person)[0];

Now that we have the basics of parameters down, let’s get back to SurrealDB’s preset parameters - also known as reserved statement parameters.

Reserved statement parameters

Unseen to us, SurrealDB sets quite a few other parameters inside every statement. One example of these parameters is one called $session (more on this in Chapter 9) that is always available if you want to see your current session details. Let’s take a look at what it returns inside the sandbox.

RETURN $session;

Response
{
	db: 'sandbox',
	exp: NONE,
	id: NONE,
	ip: NONE,
	ns: 'sandbox',
	or: NONE,
	sc: NONE,
	sd: NONE,
	tk: NONE
}

You can sort of imagine SurrealDB sneaking in a few LET statements every time you do an operation, depending on what is relevant. If irrelevant, a NONE will be returned instead. For example, a SELECT statement doesn’t modify data so $before and $after will always show up as NONE.

CREATE person;
SELECT $before, $after FROM person;

Response
[
	{
		after: NONE,
		before: NONE
	}
]

It’s best to avoid using these parameter names in your own queries, as SurrealDB will point to a different value if the query sets one of these parameters during its operation.

CREATE person:aeon;
LET $before = "Before!";
DELETE person RETURN $before;
RETURN $before;

As DELETE sets $before to the value before deletion, it will not return the string “Before!”, but it will once the operation is over.

-------- Query --------
[
	{
		before: {
			id: person:aeon
		}
	}
]

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

'Before!'

As a SELECT statement does not set the parameter $before, the string “Before!” will show up this time.

CREATE person:aeon;
LET $before = "Before!";
SELECT $before FROM person;

Response
[
	{
		before: 'Before!'
	}
]

And some parameters are outright forbidden as they are expected to be present inside any and all contexts.

LET $session = "Is this thing on?";

Response
"'session' is a protected variable and cannot be set"

In any case, outright avoiding these parameter names is highly recommended.

FOR loops

Knowing what we know from the previous section, let’s insert some towns again in order to practice deleting them.

CREATE town SET name = 'Toria',     population = 20685;
CREATE town SET name = 'Sukh',      population = 2955;
CREATE town SET name = 'Black Bay', population = 4137;

After this we changed our mind: a town should only be for places that have a population of at least 5000. Anything else should be a village. To delete only these records, we can just add WHERE population < 5000 and we are done!

We’ll add the RETURN BEFORE clause to the end to make sure that we can see the records that were deleted.

DELETE town WHERE population < 5000 RETURN BEFORE;

Response
[
    {
        id: town:eqe7g0uaotnbo52e5uie,
        name: 'Black Bay',
        population: 4137
    },
    {
        id: town:or2p68tfy84vujtjesnx,
        name: 'Sukh',
        population: 2955
    }
]

Being able to see the deleted objects is pretty nice, but now we have to manually recreate the town data.

Instead of just deleting these two town records, we could store the data as another record type first, and then delete it. We could do this all at once inside a FOR loop, which takes an array of values and lets us decide what to do with each one.

To practice our first FOR loop, let’s recreate the two towns that we deleted. It will take the following data, an array of objects:

[{ name: "Black Bay", population: 4137 }, { name: "Sukh", population: 2955 }]

We can then tell SurrealDB to create one town for each of these objects.

FOR $data IN [{ name: "Black Bay", population: 4137 }, { name: "Sukh", population: 2955 }] {
    CREATE town SET
      name = $data.name,
      population = $data.population;
}

Next, we want to select the towns with population under 5000, create a village record using their data, and then delete the town records holding their data. The query will be similar to the above, except that the FOR loop is taking the output of a SELECT statement, which returns an array.

FOR $t IN SELECT * FROM town WHERE population < 5000 {
    CREATE village SET
        name = $t.name,
        population = $t.population;
    DELETE $t;
};
SELECT * FROM town, village;

Response
[
	{
		id: town:xo5b77f7xmg00dt010z7,
		name: 'Toria',
		population: 20685
	},
	{
		id: village:pidm8yodrsyueaokda8z,
		name: 'Sukh',
		population: 2955
	},
	{
		id: village:qqlbbm1lta5xac2vgpsd,
		name: 'Black Bay',
		population: 4137
	}
]

So why did we choose the variable name $t instead of $town? Well, we didn’t have to, and could have chosen to use $town. But if we had forgotten to add a $ to our query and written town instead of $town, then the following would have happened:

FOR $town IN (SELECT * FROM town WHERE population < 5000) {
    CREATE village SET
        name = $town.name,
        population = $town.population;
    DELETE town; -- Whoops, deletes all `town` records!
}

This query would have left us with two village records, but no town!

The range syntax that we learned in the last chapter works with FOR loops too. Here is a quick example that creates person records that have a name equal to the number of each step of the loop.

FOR $num IN 1..=10 {
    CREATE person SET name = "Person number " + <string>$num;
}

The records created will have the name “Person number 1”, “Person number 2”, and so on.

DELETE doesn’t delete tables; REMOVE does

Deleting every record for a table doesn’t remove the table itself. It would be most unfortunate if this were the case! You would be forced to make sure that every table defined in your database has at least one record to ensure that it doesn’t get removed.

We can prove this behaviour with three quick queries. One creates a town, the next deletes them all, and the last gets the info for the current database which will hold information on the tables inside.

CREATE town;
DELETE town;
INFO FOR DB;

The output is not too surprising:

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

A REMOVE will do the trick. REMOVE is fairly simple: just the keyword, followed by what you would like to remove (a TABLE, a FIELD, and so on). So this is all you need to remove the town table from the database:

CREATE town;
REMOVE TABLE town;

The INFO FOR DB command will now contain nothing.

Response
{
    accesses: {},
    analyzers: {},
    functions: {},
    models: {},
    params: {},
    tables: {},
    users: {}
}

The syntax is the same when removing a field via a REMOVE FIELD statement, except that you will need to add the word ON and then the table name.

REMOVE FIELD name ON TABLE town;

Record functions to find table names and IDs

Getting back to our data, we can do a quick SELECT to show that these two locations are now villages and not towns:

SELECT * FROM town, village;

Response
[
    {
        id: town:kcpphctxwiktsrscaoof,
        name: 'Toria',
        population: 20685
    },
    {
        id: village:90pgjnbdkrz5ar73vq45,
        name: 'Sukh',
        population: 2955
    },
    {
        id: village:rywz14k0y9x99bz6qd9c,
        name: 'Black Bay',
        population: 4137
    }
]

The output looks pretty good, but how can we make it easier to tell that an object returned is of one type and not another? SurrealDB has a function that can help called record::tb() which returns the table name of a record ID. Let’s give it a try:

SELECT
    name,
    population,
    record::tb(id) AS type
FROM town, village;

Response
[
    {
        name: 'Toria',
        population: 20685,
        type: 'town'
    },
    {
        name: 'Sukh',
        population: 2955,
        type: 'village'
    },
    {
        name: 'Black Bay',
        population: 4137,
        type: 'village'
    }
]

There is also a function called type::thing() that you can pass a table name and record ID into in order to make a record.

CREATE type::thing("settlement", 1);
// id: settlement:1
CREATE type::thing("settlement", "1");
// id: settlement:⟨1⟩

This function can be useful when you have the pieces of a record ID in some other format (like a string) that you want to turn into a record. For example, this query won’t work:

FOR $name IN ["roman_god", "planet"] {
    CREATE $name:"jupiter";
};

It doesn’t work because while SurrealDB is able to parse an input like CREATE roman_god:jupiter, it doesn’t know what to do with a string (“roman_god”) followed by a colon (:), followed by a string (“jupiter”) or a random word (jupiter).

But the type::thing() function is able to take two inputs like these and turn them into a record.

FOR $name IN ["roman_god", "planet"] {
    CREATE type::thing($name, "jupiter");
};

This function is also useful when dealing with dynamic input, like a timestamp. SurrealDB’s time::now() method can be used here to get the current time.

// Won't work:
// CREATE person_made_at:time::now();

// Will work:
CREATE type::thing("person_made_at", time::now());

Response
[
	{
		id: person_made_at:⟨2024-05-20T05:35:00.021Z⟩
	}
]

So how did we know that record::tb() and type::thing() were available to use, and how many other functions are there? Let’s take a look.

Other functions

SurrealDB has an extensive collection of functions, too many for us to all mention in this book. But they are divided quite neatly into their own modules, which makes them easy to find.

Function names in SurrealDB are composed of one or more words joined by ::, with a tendency towards longer function names when further grouping makes sense. This style of grouping also makes function names quite easy to read.

For example, there are a large number of string::is functions whose usage is pretty clear:

string::is::hexadecimal()
string::is::latitude()
string::is::ascii()

Let’s give two of them a try! The string::is::latitude() function should return true for any value between -90 and 90, and string::is::longitude() should do the same for any value between -180 and 180.

RETURN string::is::latitude("87.5519");
RETURN string::is::latitude("97.5519");
RETURN string::is::longitude("97.5519");
RETURN string::is::longitude("187.5519");

Response
true
false
true
false

Some function paths have similar longer paths that allow for greater precision:

array::sort()
array::sort::asc()
array::sort::desc()

rand::uuid()
rand::uuid::v4()
rand::uuid::v7()

Here are some other function names to give you an idea of the variety of functions available:

array::sort()
string::len()
string::semver::set::major()
value::diff()
rand::uuid::v7()

Using method syntax for functions

One of the nice features added in SurrealDB 2.0 is the ability to use the . operator to call these functions as methods on a type, instead of the full paths we just saw.

What that means in practice is that you have the option of calling a function in two ways. You can use the full path, followed by the function argument(s):

-- Call a string method, pass in a string
RETURN string::is::latitude("87.5519");

Or you can use the same function called as a method on the type.

-- Have a string, call a method on it
RETURN "87.5519".is_latitude();

This syntax is particularly nice when it comes to method chaining, namely calling a method on a type and then calling another method on what it returns! The following example in which we push a number to the end of an array and then check its length looks much more readable using the new syntax.

-- Could only do this prior to 2.0
RETURN array::len(array::push([1,2,3], 4));
-- Now you can do this
RETURN [1,2,3].push(4).len();

Make sure to note that the :: in the regular function paths are replaced with _ when using method syntax: "87.5519".is_latitude(), not "87.5519".is::latitude().

Which syntax to use is entirely up to you, and will depend on the situation. You might still prefer the full path of functions like string::len() and array::len() to make it clear to the reader of your code, as the example below shows.

LET $some_data = "Some data";
LET $some_other_data = [1,2,3];
--
-- Lots of lines of code...
--
-- Hmm, which one is the string, and which one is the array?
RETURN [$some_data.len(), $some_other_data.len()];
-- This makes it clear which one is which
RETURN [string::len($some_data), array::len($some_other_data)];

Speaking of functions, the next two types that we will learn have quite a few of their own dedicated functions too.

Datetimes, string prefixes, durations, and sleeping

SurrealDB has two types for working with time: datetime and duration. SurrealDB is able to cast a string into a datetime as long as it matches the ISO 8601 datetime format, which is as follows:

YYYY-MM-DDTHH:MM:SSZ

RETURN <datetime>'2024-06-06T12:00:00Z';

// Wrong format: has a _ between the year and month instead of -
RETURN <datetime>"2024_06-06T12:00:00Z";

Response
d'2024-06-06T12:00:00Z'

"Expected a datetime but cannot convert '2024_06-06T12:00:00Z' into a datetime"

A datetime can also take milliseconds at the end, so this input is valid too.

RETURN <datetime>'2024-06-06T12:00:00.101Z';

Response
d'2024-06-06T12:00:00.101Z'

As a convenience, a string with just a year and month and date can be cast into a datetime. The time will be set to midnight.

RETURN <datetime>"2024-06-06";

Response
d'2024-06-06T00:00:00Z'

SurrealDB has a time::format() function that allows you to output a string from a datetime in any format that you like. Here are two examples of the function in action:

RETURN time::format(<datetime>'2024-06-06', "%Y%m");
RETURN time::format(<datetime>'2024-06-06', "Day %d of month %m of year %y");

Response
'202406'
'Day 06 of month 06 of year 24'

As the examples indicate, a formatter is a string that contains certain letters preceded by % that are used to insert the datetime info at that point. There are far too many types of formatting options you can pass in to this function to mention them all here, but the formatters reference page has them all if you want to take a look.

If you have input that might be a datetime or but might also be a string with a different date format, you could use the time::format() function to change the format of the input if it matches a datetime, or just pass it on as a string otherwise. SurrealDB’s string::is::datetime() function will allow us to see if a string is properly formatted or not.

We don’t know exactly when Toria was founded or what year Aeon is living in, but the people of Aeon’s time certainly have their own calendar to work from, such as “day 15 of month 3, 350 years since the founding of Toria”. A datetime will work for that sort of date too!

RETURN string::is::datetime("0350-03-15");
-- true

Combining the time::format() and “string::is::datetime()` functions might be useful for Aeon, as the world at this point in time now has two types of records:

• Historical records: many are only accurate to the month. Aeon can take these inputs as strings and simply add a “-15” for simplicity to set them to the middle of the month. That will let them be turned into datetimes, which will make it easy to calculate the duration between events.
• Current events: the time can be more accurately specified, maybe to the hour or even the minute.

Here’s an example of a historical event that is only accurate to the month. Thanks to the string::is::datetime() function, no error is generated even when the date is “0003-04”.

-- This event took place long before the tunnel was discovered,
-- so is only accurate down to the month
LET $date = "0003-04";
LET $info = "First ever democratic elections in Toria";

CREATE ONLY event SET
    at = (IF string::is::datetime($date) {
        <datetime>$date
    } ELSE {
        <datetime>($date + "-15")
    }),
    at_historical = time::format(at, "Month %m Day %d, %Y years since Toria's glorious founding"),
    info = $info;

This next event is more accurate, as Aeon can just look at the clock and see that it’s 8 am and use that as the time of day.

LET $date = "0424-04-24T08:00:00Z";
LET $info = "Start of day 50 of the project to restore civilization";

CREATE ONLY event SET
    at = IF string::is::datetime($date) {
        <datetime>$date
    } ELSE {
        <datetime>$date + "-15"
    },
    at_historical = time::format(at, "Month %m Day %d, %Y glorious years since Toria's founding"),
    info = $info;

Aeon could then set the date the next day as a parameter, and then use it to determine how long it has been since each event has passed.

LET $today = <datetime>"0424-04-25";
SELECT $today - at AS time_passed, * FROM event;

Response
[
	{
		at: d'0424-04-24T08:00:00Z',
		at_historical: "Month 04 Day 24, 0424 glorious years since Toria's founding",
		id: event:ef1ro8e65z6omoetayfv,
		info: 'Start of day 50 of the project to restore civilization',
		time_passed: 16h
	},
	{
		at: d'0003-04-15T00:00:00Z',
		at_historical: "Month 04 Day 15, 0003 years since Toria's glorious founding",
		id: event:eazx0ovuem7ivueaz6hy,
		info: 'First ever democratic elections in Toria',
		time_passed: 421y16w1d
	}
]

Another way you can check for valid input is by using “string prefixes”, similar to the dec suffix we learned in Chapter 1 except that they come before the input instead of after. These force SurrealDB to treat the input as one of following types instead of a string, depending on the prefix:

• r: a record ID
• d: a datetime
• u: a UUID

As we learned in Chapter 1, these are instructions to the parser to treat the input as a certain type, as opposed to a cast which tells the database to change a value to another value.

So with a string prefix, a query won’t even work if the input is wrong! Neither Surrealist nor the CLI will even let you run the query below, and will often even know exactly which part of the input is incorrect.

RETURN d'27000024-06-06T12:00:00Z';
RETURN r'person:::::aeon';
RETURN u'01912b31-d67d-704b-b49d-WRONGINPUT';

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

"There was a problem with the database: Parse error: Datetime digits section not the correct length, needs to be 4 characters
  |
1 | RETURN d'27000024-06-06T12:00:00Z';
  |          ^^^^^^^^
"

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

"There was a problem with the database: Parse error: Unexpected token '::' expected :
  |
1 | RETURN r'person:::::aeon';
  |                ^^
"

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

"There was a problem with the database: Parse error: Unexpected token 'a strand' expected UUID hex digits
  |
1 | RETURN u'01912b31-d67d-704b-b49d-WRONGINPUT';
  |                                  ^^^^^^^^^^
"

The d and u prefixes will show up in the output of a query to let you know that the output is a datetime or a uuid, and not a string. Conversely, a Record ID for its part will show up without quotes so that you can tell that it is a Record ID and not a string.

RETURN [
    d'2724-06-06T12:00:00Z',
    u'01912b31-d67d-704b-b49d-f2aed93da1fc',
    r'person:aeon'
];

Response
[
	d'2724-06-06T12:00:00Z',
	u'01912b31-d67d-704b-b49d-f2aed93da1fc',
	person:aeon
]

A duration represents a period of time, and works together with datetime in a way you would expect. You can create a duration to add or subtract to a datetime, or subtract one datetime from another to return a duration, as we did in the query above.

Durations use y for years, w for weeks, d for days, h for hours, m for minutes, s for seconds, ms for milliseconds, us for microseconds, and ns for nanoseconds. You can also use the Greek µs for microseconds, as µ is the Greek letter m. Indeed, us is only used because u is the most similar letter to µ - a letter that most people won’t be able to type on their keyboard.

Months vary from 28 to 31 days in length, so there is no duration for month.

Constructing a datetime is easy: you can just use integers followed by these prefixes.

RETURN 1y1m1s;

Response
1y1m1s

Let’s use one of the numerous type::is functions to make sure that the type returned is indeed a duration.

RETURN type::is::duration(1y1m1s);
1y1m1s.is_duration();

Response
true
true

It is!

SurrealDB is pretty lenient when it comes to input for a duration. Order doesn’t matter, and you can even specify the same unit type as many times as you like.

RETURN 1y1m      + 1m1y;
RETURN 1ms1ms1ms + 1ms1ms1ms;

Response
2y2m
6ms

Now let’s try subtracting one datetime from another to get a duration.

LET $first = time::now();
LET $second = time::now();
RETURN $second - $first;

The output for this will be an incredibly small duration of time, like 48µs or 14µs600ns.

SurrealDB also has a statement called SLEEP which is incredibly easy to use: just type SLEEP and the duration to sleep for.

LET $first = time::now();
SLEEP 1s;
LET $second = time::now();
RETURN $second - $first;

The output for this will be at least a second: 1s1ms966µs300ns.

Putting a query to sleep can be useful in a few limited cases. You can use it to test behaviour, or to simulate a condition. For example, you might want to simulate the latency between regions so that you can get an idea of what it’s like to use your app for someone from a distant part of the world. Or you could use SLEEP to delay a response if you want to prevent users from trying to log in over and over again.

You might also need to delay the database by a little bit to ensure that a record with an ID based on the current time is always unique. The following query shows what happens if you don’t tell the database to wait for a bit:

CREATE person:[time::now()];
CREATE person:[time::now()];
CREATE person:[time::now()];

One or more of the CREATE statements might fail, because these operations are so simple that the current timestamp might not yet have changed once the next CREATE statement starts.

[
	{
		id: person:[
			d'2024-06-24T03:50:48.253Z'
		]
	}
]

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

"Database record `person:[d'2024-06-24T03:50:48.253Z']` already exists"

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

"Database record `person:[d'2024-06-24T03:50:48.253Z']` already exists"

Adding a quick SLEEP 1ms in between these queries is enough to ensure that the timestamps are unique.

Type inference and default values

SurrealDB can be surprisingly flexible at times, returning a successful result when you might expect it to generate an error. This is thanks to type inference.

CREATE counter:my_counter SET value += 1;

Interestingly, the query is successful and returns a counter with its value set to 1.

[
	{
		id: counter:my_counter,
		value: 1
	}
]

The query works because the parser sees a += (the “incrementing operator”) followed by a number. It is able to conclude that value must be a number, and since value has not been set yet, it can be treated as a number at its default value: 0.

Similarly, this query also works.

CREATE city:toria SET nicknames += "The centre of the known world";

Response
[
	{
		id: city:toria,
		nicknames: [
			'The centre of the known world'
		]
	}
]

The += operator doesn’t work for strings, but it does work for arrays by adding an item to the array. SurrealDB therefore concludes that nicknames must be an array<string>. And since the default value of an array is an empty array, it adds the string to the array and returns the array above with a single string inside.

Tip: Check the documentation

SurrealDB has a lot of statements you can use, and each one can be written in a lot of different ways. That means that it won’t be obvious just from looking at examples what the possibilities are for a certain statement. However, each statement does have its own doc page that contains the entire possible syntax, and this is not too difficult to read.

Some of them are incredibly easy, like SLEEP and RETURN:

SLEEP @duration;
RETURN @value;

Others require reading through the syntax step by step. Let’s take the syntax for CREATE as an example. It looks like this:

CREATE [ ONLY ] @targets
	[ CONTENT @value
	  | SET @field = @value ...
	]
	[ RETURN NONE | RETURN BEFORE | RETURN AFTER | RETURN DIFF | RETURN @statement_param, ... ]
	[ TIMEOUT @duration ]
	[ PARALLEL ]
;

To read a statement’s syntax, just go from top to bottom as you keep the following points in mind:

• Anything between [] is optional,
• Anything not between [] is mandatory,
• | is used to list choices,
• … means continuation,
• @ is used to mark a parameter or a value.

So let’s try that with CREATE from top to bottom to see how we can use it.

• You have to start with CREATE.
• You can then add ONLY if you want to return a single value instead of an array of values.
• You then have to choose a @target (like a person or a user).
• To add some values, use either CONTENT for a full object or SET to set one field at a time.
• You can then choose what you want to return once the statement is over. The choices are RETURN NONE, RETURN BEFORE, RETURN AFTER, RETURN DIFF, or RETURN plus one or more parameters.
• Next, you can choose to set a timeout.
• Finally, you can type PARALLEL if you like. We will learn more about this keyword in Chapter 12.

Knowing this, we can create a town with the longest syntax possible for the CREATE statement. We’ll use ONLY, add some CONTENT, return the $after value along with a bunch of other random fields, give it a one second timeout, and finally run the statement in parallel. Here it is!

CREATE ONLY town
    CONTENT {
        name: "Toria",
        population: 3500
    }
    RETURN
        "Nice" AS the_weather_today,
        10,
        15 AS another_number,
        $after
    TIMEOUT 1s
    PARALLEL;

Response
{
	10: 10,
	after: {
		id: town:x3ggms7myoxg426pzax2,
		name: 'Toria',
		population: 3500
	},
	another_number: 15,
	the_weather_today: 'Nice'
}

LET $data = [
    { name: "Toria",     population: 20685  },
    { name: "Sukh",      population: 2955   },
    { name: "Abeston",   population: 1000  }
];

FOR $town_data IN $data {
    CREATE type::thing("town", $town_data.name)
        SET
            name = $town_data.name,
            population = $town_data.population;
};

DELETE town WHERE population > 2000 RETURN BEFORE;
DELETE town WHERE population > 2000 RETURN $before;

SELECT
    "A " + record::tb(id) + " called " + name AS description
    FROM town;

UPDATE town SET location = "Centre of the world" WHERE name = "Toria" TIMEOUT 1s RETURN NONE;

UPDATE [ ONLY ] @targets
	[ CONTENT @value
	  | MERGE @value
	  | PATCH @value
	  | [ SET @field = @value, ... | UNSET @field, ... ]
	]
	[ WHERE @condition ]
	[ RETURN NONE | RETURN BEFORE | RETURN AFTER | RETURN DIFF | RETURN @statement_param, ... ]
	[ TIMEOUT @duration ]
	[ PARALLEL ]
;

LET $now = time::now();
CREATE |town:1000|   RETURN NONE;
CREATE |person:1000| RETURN NONE;
RETURN time::now() - $now;