Parameters

Parameters can be used like variables to store a value which can then be used in subsequent queries. To define a parameter in SurrealQL, use the LET statement. The name of the parameter should begin with a $ character.

Defining parameters within SurrealQL

-- Define the parameter
LET $suffix = "Morgan Hitchcock";
-- Use the parameter
CREATE person SET name = "Tobie " + $suffix;
-- (Another way to do the same)
CREATE person SET name = string::join(" ", "Jaime", $suffix);

Response
[
    {
        "id": "person:3vs17lb9eso9m7gd8mml",
        "name": "Tobie Morgan Hitchcock"
    }
]

[
    {
        "id": "person:xh4zbns5mgmywe6bo1pi",
        "name": "Jaime Morgan Hitchcock"
    }
]

A parameter can store any value, including the result of a query.

-- Assuming the CREATE statements from the previous example
LET $founders = (SELECT * FROM person);
RETURN $founders.name;

Response
[
    "Tobie Morgan Hitchcock",
    "Jaime Morgan Hitchcock"
]

Parameters persist across the current connection, and thus can be reused between different namespaces and databases. In the example below, a created person record assigned to a parameter is reused in a query in a completely different namespace and database.

LET $billy = CREATE ONLY person:billy SET name = "Billy";
-- Fails as `person:billy` already exists
CREATE person CONTENT $billy;

USE NAMESPACE other_namespace;
USE DATABASE other_database;
-- Succeeds as `person:billy` does not yet exist in this namespace and database
CREATE person CONTENT $billy;

Parameters can be defined using SurrealQL as shown above, or can be passed in using the client libraries as request variables.

Redefining and shadowing parameters

The value of a parameter can be redefined by using the = operator, which is syntactic sugar for a LET statement.

LET $my_name = "Alucard";
$my_name = "Sypha";
RETURN $my_name;

Output
'Sypha'

If the parameter is redefined inside another scope, the original value will be shadowed. Shadowing refers to when a value is temporarily obstructed by a new value of the same name until the new scope has completed.

LET $nums = [
    [1,2],
    [3,4]
];

{
    LET $nums = $nums.flatten();
    -- Flattened into a single array,
    -- so $nums is shadowed as [1,2,3,4]
    RETURN $nums;
};

-- Returns original unflattened $nums:
-- [[1,2], [3,4]]
RETURN $nums;

Even a parameter defined using a DEFINE PARAM statement can be shadowed.

DEFINE PARAM $USERNAME VALUE "user@user.com";

$USERNAME = "some other email";

However, the parameter $USERNAME in this case is still defined as its original value, as can be seen via an INFO FOR DB statement.

{
	accesses: {},
	analyzers: {},
	apis: {},
	configs: {},
	functions: {},
	models: {},
	params: {
		USERNAME: "DEFINE PARAM $USERNAME VALUE 'user@user.com' PERMISSIONS FULL"
	},
	tables: {},
	users: {}
}

As the shadowed $USERNAME parameter will persist over the length of the connection, the parameter $USERNAME will once again show up as its original defined value if the connection is discontinued and restarted.

Defining parameters within client libraries

SurrealDB’s client libraries allow parameters to be passed in as JSON values, which are then converted to SurrealDB data types when the query is run. The following example show a variable being used within a SurrealQL query from the JavaScript library.

let people = await surreal.query("SELECT * FROM article WHERE status INSIDE $status", {
	status: ["live", "draft"],
});

Reserved variable names

SurrealDB automatically predefines certain variables depending on the type of operation being performed. For example, $this and $parent are automatically predefined for subqueries so that the fields of one can be compared to another if necessary. In addition, the predefined variables $access, $auth, $token, and $session are protected variables used to give access to parts of the current database configuration and can never be overwritten.

LET $access = true;
LET $auth = 10;
LET $token = "Mytoken";
LET $session = rand::int(0, 100);

Output
-------- Query 1 --------

"'access' is a protected variable and cannot be set"

-------- Query 2 --------

"'auth' is a protected variable and cannot be set"

-------- Query 3 --------

"'token' is a protected variable and cannot be set"

-------- Query 4 --------

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

Other predefined variables listed below are not specifically protected, but should not be used in order to avoid unexpected behaviour.

$before, $after

Represent the values before and after a mutation on a field.

CREATE cat SET name = "Mr. Meow", nicknames = ["Mr. Cuddlebun"];
UPDATE cat SET nicknames += "Snuggles" WHERE name = "Mr. Meow" RETURN $before, $after;

Response
[
    {
        "after": {
            "id": "cat:6p71csv2zqianixf0dkz",
            "name": "Mr. Meow",
            "nicknames": [
                "Mr. Cuddlebun",
                "Snuggles"
            ]
        },
        "before": {
            "id": "cat:6p71csv2zqianixf0dkz",
            "name": "Mr. Meow",
            "nicknames": [
                "Mr. Cuddlebun"
            ]
        }
    }
]

$auth

Represents the currently authenticated record user.

DEFINE TABLE user SCHEMAFULL
    PERMISSIONS
        FOR select, update, delete WHERE id = $auth.id;

$event

Represents the type of table event triggered on an event. This parameter will be one of either "CREATE", "UPDATE", or "DELETE".

DEFINE EVENT user_created ON TABLE user WHEN $event = "CREATE" THEN (
    CREATE log SET table = "user", event = $event, created_at = time::now()
);

$input

Represents the initially inputted value in a field definition, as the value clause could have modified the $value variable.

CREATE city:london SET
    population = 8900000,
    year = 2019,
    historical_data = [];

INSERT INTO city [
    { id: "london", population: 9600000, year: 2023 }
]
ON DUPLICATE KEY UPDATE
-- Stick old data into historical_data
historical_data += {
    year: year,
    population: population
},
-- Then update current record with the new input using $input
population = $input.population,
year = $input.year;

[
    {
        "historical_data": [
            {
                "population": 8900000,
                "year": 2019
            }
        ],
        "id": "city:london",
        "population": 9600000,
        "year": 2023
    }
]

$parent, $this

$this represents the current record in a subquery, and $parent its parent.

CREATE user SET name = "User1", member_of = "group1";
CREATE user SET name = "User2", member_of = "group1";
CREATE user SET name = "User3", member_of = "group1";
SELECT name, 
    (SELECT VALUE name FROM user WHERE member_of = $parent.member_of)
    AS group_members
    FROM user
    WHERE name = "User1";

Response
[
    {
        "group_members": [
            "User1",
            "User3",
            "User2"
        ],
        "name": "User1"
    }
]

INSERT INTO person (name) VALUES ("John Doe"), ("John Doe"), ("Jane Doe");
SELECT 
    *,
    (SELECT VALUE id FROM person WHERE $this.name = $parent.name) AS 
    people_with_same_name
    FROM person;

Response
[
    {
        "id": "person:hwffcckiv61ylwiw43yf",
        "name": "John Doe",
        "people_with_same_name": [
            "person:hwffcckiv61ylwiw43yf",
            "person:tmscoy7bjj20xki0fld5"
        ]
    },
    {
        "id": "person:tmscoy7bjj20xki0fld5",
        "name": "John Doe",
        "people_with_same_name": [
            "person:hwffcckiv61ylwiw43yf",
            "person:tmscoy7bjj20xki0fld5"
        ]
    },
    {
        "id": "person:y7mdf3912rf5gynvxc7q",
        "name": "Jane Doe",
        "people_with_same_name": [
            "person:y7mdf3912rf5gynvxc7q"
        ]
    }
]

$access

Represents the name of the access method used to authenticate the current session.

IF $access = "admin" THEN
    ( SELECT * FROM account )
ELSE IF $access = "user" THEN
    ( SELECT * FROM $auth.account )
ELSE
    []
END

$session

Represents values from the session functions as an object.

You can learn more about those values from the security parameters section.

CREATE user SET 
    name = "Some User",
    on_database = $session.db;

Response
[
    {
        "id": "user:wa3ajflozlqoyurc4i4v",
        "name": "Some User",
        "on_database": "database"
    }
]

$token

Represents values held inside the JWT token used for the current session.

You can learn more about those values from the security parameters section.

DEFINE TABLE user SCHEMAFULL
  PERMISSIONS FOR select, update, delete, create
  WHERE $access = "users"
  AND email = $token.email;

$value

Represents the value after a mutation on a field (identical to $after in the case of an event).

DEFINE EVENT email ON TABLE user WHEN $before.email != $after.email THEN (
    CREATE event SET 
        user = $value.id,
        time = time::now(),
        value = $after.email,
        action = 'email_changed'
);

$request

Available since: v2.0.0

This parameter represents the value of a request to a custom API defined using the DEFINE API statement.

DEFINE API OVERWRITE "/test"
    FOR get, post 
        MIDDLEWARE
            api::req::raw_body(false)
        THEN {
            RETURN {
                status: 404,
                body: $request.body,
                headers: {
                    'bla': '123'
                }
            };
        };

The $request parameter may contain values at the following fields: body, headers, method, query, and params.