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

Parameters in SurrealQL are immutable. The same parameter can be redefined using a LET statement.

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

Output

'Sypha'

Before SurrealDB 3.0, the = on its own was used as syntactic sugar for a LET statement. This has since been deprecated in order to make it clearer that parameters can be redeclared, but not modified.

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";

LET $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.

$access

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

IF $access = "admin" { SELECT * FROM account }
ELSE IF $access = "user" { SELECT * FROM $auth.account }
ELSE {}

$action, $file, $target

These three parameters are used in the context of the permissions of a DEFINE BUCKET statement.

$action represents the type of operation: one of "Put", "Get", "Head", "Delete", "Copy", "Rename", "Exists", and "List".
$file represents the path to the file being accessed.
$target represents the target file ref in copy/rename operations.

$auth

Represents the currently authenticated record user.

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

$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"
            ]
        }
    }
]

$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"
        ]
    }
]

$reference

This parameter represents the reference in question inside an ON DELETE clause for record references.

$request

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::timeout(1s)
        THEN {
            RETURN {
                status: 404,
                body: $request.body,
                headers: {
                    'bla': '123'
                }
            };
        };

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

$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'
);

Improvements to parameters and expressions in statements

Parameters and expressions have traditionally only been available in a limited fashion in SurrealQL statements. As of the alpha versions of SurrealDB 3.0, work is undergoing to allow parameters and expressions to be used in many places that were not possible before.

Some examples of this are:

DEFINE statements

FOR $language IN ["en", "ja", "uk", "ie"] {
    DEFINE TABLE "language_" + $language SCHEMAFULL;
};

(INFO FOR DB).tables;

Output

{
	language_en: 'DEFINE TABLE language_en TYPE NORMAL SCHEMAFULL PERMISSIONS NONE',
	language_ie: 'DEFINE TABLE language_ie TYPE NORMAL SCHEMAFULL PERMISSIONS NONE',
	language_ja: 'DEFINE TABLE language_ja TYPE NORMAL SCHEMAFULL PERMISSIONS NONE',
	language_uk: 'DEFINE TABLE language_uk TYPE NORMAL SCHEMAFULL PERMISSIONS NONE'
}

REMOVE statements

Parameterization in REMOVE statements is particularly useful in the context of testing.

FOR $table IN ["test_user", "test_client"] {
    DEFINE TABLE $table;
    // Do some tests
    REMOVE TABLE $table;
};

The following example shows an example of a test that might be performed using a REMOVE FIELD statement. Here, the INFO FOR TABLE statement is used to dynamically capture the defined fields of a table, followed by the object::keys() function to retrieve each field as a string. The fields can then be removed one by one inside a REMOVE FIELD statement, with the time elapsed logged in a separate table.

DEFINE FIELD string_test ON test TYPE string;
DEFINE FIELD int_test ON test TYPE int;
DEFINE FIELD datetime_test ON test TYPE datetime;

CREATE |test:10000| SET 
    string_test = rand::string(10),
    int_test = rand::int(),
    datetime_test = rand::time()
RETURN NONE;

FOR $field IN (INFO FOR TABLE test).fields.keys() {
    LET $now = time::now();
    REMOVE FIELD $field ON test;
    LET $elapsed = time::now() - $now;
    CREATE log SET results = { field_name: $field, removed_in: $elapsed }
};

The TIMEOUT clause in queries

DEFINE FUNCTION fn::get_timeout() -> duration {
    // Do some HTTP call to get status
    // Simulate the output with rand::enum() function
    rand::enum(100ms, 1s, 5s)
};

SELECT * FROM person TIMEOUT fn::get_timeout();

The OMIT clause in queries

CREATE person SET name = "Galen", surname = "Pathwarden", age = 19;

SELECT * OMIT type::fields(["name", "id"]) FROM person;

Output

[
	{
		age: 19,
		surname: 'Pathwarden'
	}
]