Record IDs
In SurrealDB, document record IDs store both the table name, and the record identifier. This allows for a simple and consistent way to reference records across the database. Record IDs are used to uniquely identify records within a table, and are used to query, update, and delete records.
Record IDs are made up of two parts: the table name, and the record identifier. The table name is separated from the record identifier by a : character.
The record identifier can be any string of characters, and can contain complex characters, numbers, and text. They can also be generated using built-in ID generation functions.
An example of a record ID can look like this: table:record_identifier.
Types of Record IDs
Record IDs can be constructed using a number of different types of values, including text, numbers, objects, and arrays. For example, by default when you create a table, create internet, a random id is assigned. This differs from the traditional default of auto-increment or serial IDs you might be used to.
Text Record IDs
Without annotation, text record IDs can contain letters, numbers and _ characters.
CREATE company:surrealdb SET name = 'SurrealDB';
Record IDs can contain complex characters, surrounded by the ` character.
CREATE article:`8424486b-85b3-4448-ac8d-5d51083391c7` SET time = time::now(), author = person:tobie;
Alternatively complex characters within record IDs can be surrounded by the ⟨ and ⟩ characters.
CREATE article:⟨8424486b-85b3-4448-ac8d-5d51083391c7⟩ SET time = time::now(), author = person:tobie;
If you create a record ID with a number as a string, it will be stored with the ⟨ ⟩ characters to differentiate it from a number.
CREATE article SET id = "10";
-- becomes: article:⟨10⟩
Numeric Record IDs
If a numeric value is specified without any decimal point suffix and is within the range -9223372036854775808 to 9223372036854775807 then the value will be parsed, stored, and treated as a 64-bit signed integer.
If the numeric number is outside the range of a signed 64-bit integer it will be treated as a string.
CREATE temperature:17493 SET time = time::now(), celsius = 37.5;
Object-based Record IDs
Complex record IDs support dynamic expressions, allowing parameters, and function expressions to be used as values within the IDs. This is useful in a timeseries context, or for ensuring locality between specific records in a table. All object keys in SurrealDB are sorted alphabetically
// Set a new parameter
LET $now = time::now();
// Create a record with a complex ID using an object
CREATE temperature:{ location: 'London', date: $now } SET
location = 'London',
date = $now,
temperature = 23.7
;
Array-based Record IDs
Similar to object-based record IDs, records in SurrealDB can store arrays of values, with no limit to the depth of the arrays. Arrays can store any value stored within them, and can store different value types within the same array. Retrieving records with RecordIDs which are object is the same as how it is done with RecordIDs which are arrays.
// Set a new parameter
LET $now = time::now();
// Create a record with a complex ID using an array
CREATE temperature:['London', $now] SET
location = 'London',
date = $now,
temperature = 23.7
;
Generating Record IDs
Record IDs can be generated with a number of built-in ID generation functions. These allow for record IDs to be generated using cryptographically-secure randomly-generated identifiers (which are suitable for dispersion across a distributed datastore), sequentially incrementing ULID Record identifiers, and UUID version 7 Record idenfitifiers.
// Generate a random record ID
CREATE temperature:rand() SET time = time::now(), celsius = 37.5;
// Generate a ULID-based record ID
CREATE temperature:ulid() SET time = time::now(), celsius = 37.5;
// Generate a UUIDv7-based record ID
CREATE temperature:uuid() SET time = time::now(), celsius = 37.5;
Record ranges
SurrealDB supports the ability to query a range of records, using the record ID. The record ID ranges, retrieve records using the natural sorting order of the record IDs. These range queries can be used to query a range of records in a timeseries context.
-- Select all person records with IDs between the given range
SELECT * FROM person:1..1000;
-- Select all records for a particular location, inclusive
SELECT * FROM temperature:['London', NONE]..=['London', time::now()];
-- Select all temperature records with IDs less than a maximum value
SELECT * FROM temperature:..['London', '2022-08-29T08:09:31'];
-- Select all temperature records with IDs greater than a minimum value
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..;
-- Select all temperature records with IDs between the specified range
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', '2022-08-29T08:09:31'];
Field names
Valid field names
Similar to record IDs, field names can be constructed from ASCII characters, underscores, and numbers:
--Query
CREATE user SET my_name_1 = 'name';
--Output
[
{
"id": "user:sklds4e7lqkewtddgijt",
"my_name_1": "name"
}
]
To create a field name with complex characters, use backticks:
--Query
CREATE user SET `mi_nómine😊` = 'name';
--Output
[
{
"id": "user:tkwse1j5o0anqjxonvzx",
"mi_nómine😊": "name"
}
]
Inside an object, non-ASCII field names can simply be set by using a string:
--Query
SELECT * FROM {
"mi_nómine": "name"
};
--Output
[
{
"mi_nómine": "name"
}
]
Automatically generated field names
A field created from an operation will have a field name that represents the operation(s) used to construct it.
--Query
SELECT
math::mean(temps),
[ math::min(temps), math::max(temps) ]
FROM { temps: [-5, 8, 9] };
--Output
[
{
"[math::min(temps), math::max(temps)]": [
-5,
9
],
"math::mean": 4
}
]
Using AS allows these automatically calculated field names to be replaced with custom names:
--Query
SELECT
math::mean(temps) AS mean_temps,
[ math::min(temps), math::max(temps) ] AS avg_temps
FROM { temps: [-5, 8, 9] };
--Output
[
{
"avg_temps": [
-5,
9
],
"mean_temps": 4
}
]
Learn more
Learn more about Record IDs in this blogpost and on this youtube video.