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 many developers are used to.

Note

As of v2.0.0, SurrealDB no longer eagerly converts a string into a record. An implicit r prefix or cast using <record> (or <record<recordname>>) is required instead.

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
// Charset: `abcdefghijklmnopqrstuvwxyz0123456789`
// ID Length: 20 characters long
CREATE temperature SET time = time::now(), celsius = 37.5;
// :rand() is the default random ID format, so this
// is identical to the above CREATE statement
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
    }
]

Auto-incrementing IDs

While SurrealDB does not use auto-incrementing IDs by default, this behaviour can be achieved in a number of ways. One is to use the record::id() function on the latest record, which returns the latter part of a record ID (the ‘1’ in the record ID person:1). This can then be followed up with the type::thing() function to create a new record ID.

-- Create records from person:1 to person:10
CREATE |person:1..10|;
LET $latest = SELECT VALUE id FROM ONLY person ORDER BY id DESC LIMIT 1;
CREATE type::thing("person", $latest.id() + 1);

Output
[
	{
		id: person:11
	}
]

When dealing with a large number of records, a more performant option is to use a separate record that holds a single value representing the latest ID. An UPSERT statement is best here, which will allow the counter to be initialized if it does not yet exist, and updated otherwise. This is best done inside a manual transaction so that the latest ID will be rolled back if any failures occur when creating the next record.

BEGIN TRANSACTION;
UPSERT person_id:counter SET num += 1;
-- Creates a person:1
CREATE type::thing("person", person_id:counter.num);
COMMIT TRANSACTION;

BEGIN TRANSACTION;
-- Latest ID is now 2
UPSERT person_id:counter SET num += 1;
-- Whoops, invalid datetime format
-- Transaction fails and all changes are rolled back
CREATE type::thing("person", person_id:counter.num) SET created_at = <datetime>'2025_01+01';
COMMIT TRANSACTION;

-- Latest ID is still 1
RETURN person_id:counter.num;

Learn more

Learn more about Record IDs in this blogpost and on this youtube video.