Record IDs

Note
As of v2.0.0, SurrealDB no longer eagerly converts a string into a record. An implicit r prefix or cast is required instead.

SurrealDB record IDs are composed of a table name and a record identifier separated by a : in between, allowing for a simple and consistent way to reference records across the database. Record IDs are used to uniquely identify records within a table, to query, update, and delete records, and serve as links from one record to another.

Record IDs can be constructed from a number of ways, including alphanumeric text, complex Unicode text and symbols, numbers, arrays, objects, built-in ID generation functions, and a function to generate an ID from values.

All of the following are examples of valid record IDs in SurrealQL.

company:surrealdb
company:w6xb3izpgvz4n0gow6q7
reaction:`🤪`
weather:['London', d'2025-02-14T01:52:50.375Z']
user:{ email: 'me@me.com', since: d'2025-02-14T01:54:46.920Z' }

As all record IDs are unique, trying to create a new record with an existing record ID will return an error. To create a record or modify it if the ID already exists, use an UPSERT statement or an INSERT statement with an ON DUPLICATE KEY UPDATE clause.

Types of Record IDs

Random IDs

When you create a record without specifying the full ID, a random identifier is assigned after the table name. This differs from the traditional default of auto-increment or serial IDs that many developers are used to.

CREATE company;

Output
[
	{
		id: company:ezs644u19mae2p68404j
	}
]

Record IDs can be generated with a number of built-in ID generation functions, which are cryptographically secure and suitable for dispersion across a distributed datastore. These include a 20 digit alphanumeric GUID (the default), sequentially incrementing and temporally sortable ULID Record identifiers, and UUID version 7 Record identifiers.

// Generate a random record ID 20 characters in length
// Charset: `abcdefghijklmnopqrstuvwxyz0123456789`
CREATE temperature:rand() SET time = time::now(), celsius = 37.5;
// Identical to the above CREATE statement, because
// :rand() is the default random ID format
CREATE temperature 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;

Text Record IDs

Text record IDs can contain letters, numbers and _ characters.

CREATE company:surrealdb SET name = 'SurrealDB';
CREATE user_version_2025 SET name = 'Alucard';

To create a record ID with complex characters, use ` (backticks) around the table name and/or record identifier.

CREATE article:`8424486b-85b3-4448-ac8d-5d51083391c7` SET
    time = time::now(),
    author = person:tobie;

CREATE `Artykuł`:100 SET
    author = person:`Lech_Wałęsa`;

The parts of record IDs with complex characters will display enclosed by a ⟨ and ⟩.

Output
-------- Query --------

[
	{
		author: person:tobie,
		id: article:⟨8424486b-85b3-4448-ac8d-5d51083391c7⟩,
		time: d'2025-02-18T01:48:46.364Z'
	}
]

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

[
	{
		author: person:⟨Lech_Wałęsa⟩,
		id: ⟨Artykuł⟩:100
	}
]

As the ⟨ and ⟩ characters are used for the complex parts of a record ID, they can be used directly instead of backticks if preferred. Note that these characters are different from < and > found on standard keyboards.

CREATE ⟨📖⟩ SET
    time = time::now(),
    author = person:tobie;

Output
[
	{
		author: person:tobie,
		id: ⟨📖⟩:svk5taacnhnk5000129r,
		time: d'2025-02-17T05:25:04.932Z'
	}
]

Numeric Record IDs

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;
CREATE article SET id = "10";
CREATE article SET id = "article10";
SELECT VALUE id FROM article;

As the record ID article:10 is different from article:⟨10⟩, no errors are returned when creating and both records turn up in the output of the SELECT statement. Meanwhile, the article with the identifier article10 does not use the ⟨ ⟩ characters as there is no article10 number to differentiate it from.

Output
[
	article:10,
	article:⟨10⟩,
    article:article10
]

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.

Any numeric numbers outside of the range of a signed 64-bit integer will be stored as a string.

CREATE temperature:17493 SET time = time::now(), celsius = 37.5;
CREATE year:29878977097987987979232 SET
    events = [
        "Galactic senate convenes",
        "Mr. Bean still waits in a field"
    ];

Output
-------- Query --------

[
	{
		celsius: 37.5f,
		id: temperature:17493,
		time: d'2025-02-17T06:21:08.911Z'
	}
]

-------- Query s--------

[
	{
		events: [
			'Galactic senate convenes',
			'Mr. Bean still waits in a field'
		],
		id: year:⟨29878977097987987979232⟩
	}
]

Array- and object-based Record IDs

Record IDs can be constructed out of arrays and even objects. This sort of record ID is most used when you have a field or two that will be used to look up records inside a record range, which is extremely performant. This is in contrast to using a WHERE clause to filter, which involves a table scan.

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.

CREATE weather:['London', d'2025-02-13T05:00:00Z'] SET
    temperature = 5.7,
    conditions = "cloudy";

An object can also be used as a record ID. Note that the fields are ordered alphabetically. This is important to know when using an object record ID inside a record range query.

CREATE temperature:{ city: 'London', date: time::now() } SET
    temperature = 23.7,
	location = (0.1276, 51.5072),

Output
[
	{
		id: temperature:{
			city: 'London',
			date: d'2025-02-18T02:23:39.328Z'
		},
		location: (0.1276, 51.5072),
		temperature: 23.7f
	}
]

IDs made with parameters and function calls

Parameters and function calls can be used inside array- and object-based record IDs in the same way as on standalone arrays and objects.

LET $now = time::now();

CREATE weather:['Seoul', $now] SET
    temperature = -2.3,
    conditions = "cloudy";

CREATE weather:['London', time::now()] SET
    temperature = 5.3,
    conditions = "cloudy";

To create a record that uses a parameter or function call as its entire record identifier, the type::thing() function can be used.

LET $now = time::now();

CREATE type::thing("weather", $now) SET city = 'London';

Output
[
	{
		city: 'London',
		id: weather:⟨2025-02-18T02:30:08.563Z⟩
	}
]

Defining record IDs in a schema

The type name of a record ID is record, which by default allows any sort of record. This type can be set inside a DEFINE FIELD statement.

DEFINE FIELD possessions ON TABLE person TYPE option<array<record>>;
DEFINE FIELD friends ON TABLE person TYPE option<array<record<person>>>;

CREATE person SET
    possessions = [ book:one, house:one],
    friends = [ person:one, person:two ];

Be sure to use just record instead of record<any>, as <any> here would imply actual records of a table called any.

DEFINE FIELD possessions ON TABLE person TYPE option<array<record<any>>>;

-- Won't work, 'book' and 'house' are not of table 'any'
CREATE person SET
    possessions = [ book:one, house:one ];

-- Actually expects this, which is probably
-- not what the DEFINE FIELD intended
CREATE person SET
    possessions = [ any:one, any:two ];

Record ranges

SurrealDB supports the ability to query a range of records, using the record ID. Record ID range queries retrieve records using the natural sorting order of the record IDs, making a table scan unnecessary. 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', ..];

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

The following example shows the difference in performance between a regular query that uses a WHERE clause and a record range scan.

FOR $num IN 0..=100000 {
  CREATE person SET id = $num, num = $num  
};

-- Assign the output to an unused parameter
-- to avoid excessive output
LET $_ = SELECT * FROM person WHERE num IN 0..=1000;
LET $_ = SELECT * FROM person:0..=1000;

Tips and best practices for record IDs

Why choose the right record ID format

Choosing an apt record ID format is especially important because record IDs is SurrealQL are immutable. Take the following user records for example:

FOR $i IN 0..5 {
    CREATE user SET user_num = $i, name = "User number " + <string>user_num;
};

Each of these user records will have a random ID, such as user:wvjqjc5ebqvfg3aw7g61. If a decision is made to move away from random IDs to some other form, such as an incrementing number, this will have to be done manually.

FOR $user IN SELECT * FROM user {
    -- Use type::thing to make a record ID
    -- from the user_num field
    CREATE type::thing("user", $user.user_num);
    -- Then delete the old user
    DELETE $user;
};

SELECT * FROM user;

The final query returning just the IDs shows that they have been recreated with new IDs.

Output
[
	{
		id: user:0,
		name: 'User number 0'
	},
	{
		id: user:1,
		name: 'User number 1'
	},
	{
		id: user:2,
		name: 'User number 2'
	},
	{
		id: user:3,
		name: 'User number 3'
	},
	{
		id: user:4,
		name: 'User number 4'
	}
]

However, record IDs are also used as record links and to create graph relations. If this is the case, more work will have to be done in order to recreate the former state.

The following example shows five user records, which each have a 50% chance of liking each of the other users.

FOR $i IN 0..5 {
    CREATE user SET user_num = $i, name = "User number " + <string>user_num;
};

LET $users = SELECT * FROM user;
FOR $user IN $users {
    LET $others = array::complement($users, [$user.id]);
    FOR $counterpart IN $others {
        IF rand::bool() {
            RELATE $user->likes->$counterpart;
        }
    }
};

Finding out the current relational state can be done with a query like the following which shows all of the graph tables in which a record is located at the in or out point. The ? is a wildcard operator, returning any and all tables found at this point of the graph query.

SELECT
    id,
    ->?->? AS did, 
    <-?<-? AS done_to
FROM user;

Output
[
	{
		did: [
			user:zwfnk4by9gmopf6eeqm0
		],
		done_to: [
			user:d6bx6sch5li8qmhq3ljl,
			user:ekovipptanvmgr8f48v6
		],
		id: user:6ycb63zr0k3cpzwel1ga
	},
	{
		did: [
			user:ekovipptanvmgr8f48v6,
			user:6ycb63zr0k3cpzwel1ga,
			user:zk7tpaduzaiuswll58sg
		],
		done_to: [],
		id: user:d6bx6sch5li8qmhq3ljl
	}
    -- and so on..
]

Surrealist’s graph visualization view can help as well.

With this in mind, here are some of the items to keep in mind when deciding what sort of record ID format to use.

Meaningful sortable IDs are faster to query

Records are returned in ascending record ID order by default. As the following query shows, a SELECT statement on a large number of user records with random IDs will show those with record identifiers starting with a large number of zeroes. While the IDs are sortable, the IDs themselves are completely random.

CREATE |user:200000| RETURN NONE;
SELECT VALUE id FROM user LIMIT 4;

Output
[
	user:0001th0nnywnczi7mrvk,
	user:000t5r3y7u8stqtecvht,
	user:000tjk1nbi1it1bedplc,
	user:001dfral92ltbdznypcd
]

For a large number of records, pagination can be used to retrieve a certain amount of records at a time.

-- Returns the same four records as above
SELECT VALUE id FROM user START 0 LIMIT 2;
SELECT VALUE id FROM user START 2 LIMIT 2;

Output
-------- Query --------

[
	user:0001th0nnywnczi7mrvk,
	user:000t5r3y7u8stqtecvht
]

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

[
	user:001dfral92ltbdznypcd,
	user:001hv9g1uzh32nophrpo
]

As record ranges are very performant, consider moving any fields that may be used in a WHERE clause into the ID itself.

In the following example, a number of user records are created using the default random ID, plus a num field that tracks in which order the user was created.

FOR $num IN 0..100 {
    CREATE user SET num = $num;
    sleep(1ms); -- Simulate a bit of time between user creation
};

SELECT * FROM user WHERE num IN 50..=51;
SELECT * FROM user START 50 LIMIT 2;

As the output from the SELECT statements show, a WHERE clause is needed to find two users starting at a num of 50, as START 50 starts based on the user of the record ID, which is entirely random.

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

[
	{
		id: user:pqpeg0edt8kpda907o01,
		num: 50
	},
	{
		id: user:ty6qr7zyob5dh882it08,
		num: 51
	}
]

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

[
	{
		id: user:hvfp5m5ty7n2k95dbamv,
		num: 70
	},
	{
		id: user:hvfumcmmveuolg4e2h26,
		num: 36
	}
]

Using a ULID in this case will allow the IDs to remain random, but still sorted by date of creation.

FOR $num IN 0..100 {
    CREATE user:ulid() SET num = $num;
    sleep(1ms);
};

SELECT * FROM user WHERE num IN 50..=51;
SELECT * FROM user START 50 LIMIT 2;

Not only is the START 50 LIMIT 2 query more performant, but the entire num field could be removed if its only use is to return records by order of creation.

Same record IDs for both queries this time
-------- Query --------

[
	{
		id: user:01JM1AHN7DDN7XM5KZ2RR2YM1S,
		num: 50
	},
	{
		id: user:01JM1AHN7FS4A3B6RNFCF64H90,
		num: 51
	}
]

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

[
	{
		id: user:01JM1AHN7DDN7XM5KZ2RR2YM1S,
		num: 50
	},
	{
		id: user:01JM1AHN7FS4A3B6RNFCF64H90,
		num: 51
	}
]

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;

Record IDs are record links

As a record ID is a pointer to all of the data of a record, a single record ID is enough to access all of a record’s fields. This behaviour is the key to the convenience of record links in SurrealDB, as holding a record ID is all that is needed for one record to have a link to another.

When using a standalone record ID as a record pointer, be sure to use the record ID itself.

CREATE person:1 SET data = {
    some: "demo",
    data: "for",
    demonstration: "purposes"
};

LET $record = SELECT id FROM person:1;
SELECT * FROM $record;

The output of the above query is just the id field on its own, as the $record parameter is an object with an id field, not the id field (the pointer) itself.

Output
[
	{
		id: person:1
	}
]

To rectify this, id.* can be used to follow the pointer to the entire data for the record.

SELECT id.* FROM $record;

Output
[
	{
		id: {
			data: {
				data: 'for',
				demonstration: 'purposes',
				some: 'demo'
			},
			id: person:1
		}
	}
]

Limitations

At present, definitions for a record ID’s value inside a DEFINE FIELD statement are ignored.

DEFINE FIELD id ON user VALUE rand::int(1, 1000000000) READONLY;
CREATE user;

Output
[
	{
		id: user:9ixn3oei6o532c2qyixa
	}
]

To achieve the same behaviour, the id field can be set inside the statement to create the record.

CREATE user SET id = rand::int(1, 1000000000);

Learn more

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

Edit this page on GitHub

Ranges Record links

SurrealQL