Skip to main content

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 weather:{ 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, 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 weather:['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 identifiers.

// Generate a random record ID. Example: x4q9u1noyik9my4lta3s
// Note: equivalent to `CREATE temperature:rand()`
CREATE temperature SET time = time::now(), celsius = 37.5;
// Generate a ULID-based record ID. Example: 01HWPK2R0P1B5TPNZ70R2GGKCJ
CREATE temperature:ulid() SET time = time::now(), celsius = 37.5;
// Generate a UUIDv7-based record ID. Example: 018f2d31-6016-7498-a3c7-91738596576d
CREATE temperature:uuid() SET time = time::now(), celsius = 37.5;

Record ranges and querying complex IDs

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'];

Nested fields inside object-based Record IDs can be queried in the same way as a regular SurrealDB record.

INSERT INTO weather [
{
id: {
date: '2024-04-30T03:21:04Z',
location: 'London'
},
temperature: 18.2f
},
{
id: {
date: '2024-05-01T03:21:04Z',
location: 'London'
},
temperature: 20f
},
{
id: {
date: '2024-05-01T03:21:04Z',
location: 'Pärnu'
},
temperature: 18.1f
}
];

SELECT id.date AS date, temperature FROM weather WHERE id.location = 'London';
Response
[
{
date: '2024-04-30T03:21:04Z',
temperature: 18.2f
},
{
date: '2024-05-01T03:21:04Z',
temperature: 20
}
]

Record ranges queries can be used for object-based record IDs as well.

INSERT INTO weather [
{
id: {
date: '2024-04-30T03:21:04Z',
location: 'London'
},
temperature: 18.2f
},
{
id: {
date: '2024-05-01T03:21:04Z',
location: 'London'
},
temperature: 20f
},
{
id: {
date: '2024-05-01T03:21:04Z',
location: 'Pärnu'
},
temperature: 18.1f
}
];

SELECT * FROM weather:{ date: '2024-05-01T00:00:00Z' }..={ date: '2024-05-03T00:02:00Z'};
Response
[
{
id: weather:{
date: '2024-05-01T03:21:04Z',
location: 'London'
},
temperature: 20
},
{
id: weather:{
date: '2024-05-01T03:21:04Z',
location: 'Pärnu'
},
temperature: 18.1f
}
]

For more on sorting and filtering complex record IDs, see the respective pages for objects and arrays.

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.