DEFINE FUNCTION

The DEFINE FUNCTION statement allows you to define custom functions that can be reused throughout a database. When using the DEFINE FUNCTION statement, you can define a function that takes one or more arguments and returns a value. You can then call this function in other SurrealQL statements.

Functions can be used to encapsulate logic that you want to reuse in multiple queries. They can also be used to simplify complex queries by breaking them down into smaller, more manageable pieces. They are particularly useful when you have a complex query that you need to run multiple times with different arguments.

Requirements

You must be authenticated as a root owner or editor, namespace owner or editor, or database owner or editor before you can use the DEFINE FUNCTION statement.
You must select your namespace and database before you can use the DEFINE FUNCTION statement.

Statement syntax

SurrealQL Syntax

DEFINE FUNCTION [ OVERWRITE | IF NOT EXISTS ] fn::@name
  ( [ @argument: @type ... ] ) [ -> @type ] {
	[ @query ... ]
	[ RETURN @returned ]
} [ COMMENT @string ] [ PERMISSIONS [ NONE | FULL | WHERE @condition]]

Example usage

Below shows how you can define a custom function using the DEFINE FUNCTION statement, and how to call it.

-- It is necessary to prefix the name of your function with "fn::"
-- This indicates that it's a custom function
DEFINE FUNCTION fn::greet($name: string) {
	"Hello, " + $name + "!"
};

-- Returns: "Hello, Tobie!"
RETURN fn::greet("Tobie");

To showcase a slightly more complex custom function, this will check if a relation between two nodes exists:

-- Define a function that checks if a relation exists between two nodes
DEFINE FUNCTION fn::relation_exists(
	$in: record,
	$tb: string,
	$out: record
) {
	-- Check if a relation exists between the two nodes.
	LET $results = SELECT VALUE id FROM type::table($tb) WHERE in = $in
	  AND out = $out;
	-- Return true if a relation exists, false otherwise
    RETURN array::len($results) > 0;
};

Optional arguments

If one or more ending arguments have the option<T> type, they can be omitted when you invoke the function.

DEFINE FUNCTION fn::last_option($required: number, $optional: option<number>) {
	RETURN {
		required_present: type::is_number($required),
		optional_present: type::is_number($optional),
	}
};

RETURN fn::last_option(1, 2);
//- { required_present: true, optional_present: true }

RETURN fn::last_option(1);
//- { required_present: true, optional_present: false };

Adding a return value

Optionally, the return value of a function can be specified.

For a function that is infallible, a return value is mostly for the sake of readability.

DEFINE FUNCTION fn::greet($name: string) -> string {
	"Hello, " + $name + "!"
};

For a function that is not infallible, specifying a return value can be used to customise error output.

-- Arguments must be of type 'number'
DEFINE FUNCTION fn::combine($one: number, $two: number) -> number {
  $one + $two
};

-- Accepts any value but expects the return type 'number'
DEFINE FUNCTION fn::combine_any($one: any, $two: any) -> number {
  $one + $two
};

fn::combine("one", "two");
fn::combine_any("one", "two");

While both of these return an error, the output of the second function happens only at the point that it attempts to return the combined arguments to the function.

Output

-------- Query 1 --------
"Expected `number` but found `'one'`"

-------- Query 2 --------
"Couldn't coerce return value from function `fn::combine_any`: Expected `number` but found `'onetwo'`"

The return value of a function can even be a literal type. The following function returns such a type by either returning an object of a certain structure, or a string. In this case this output is used in case an application prefers to return an error as a simple string instead of throwing an error or returning a NONE value.

DEFINE FUNCTION fn::age_and_name($user_num: int) -> { age: int, name: string } | string {
    LET $user = type::record("user", $user_num);
    IF $user.exists() {
        $user.{ name, age }
    } ELSE {
        { "Couldn't find user number " + <string>$user_num + "!" }        
    }
};

CREATE user:1 SET name = "Billy", age = 15;

fn::age_and_name(1);
fn::age_and_name(2);

Output

-------- Query 1 --------

{ age: 15, name: 'Billy' }

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

"Couldn't find user number 2!"

Transactional behaviour

A function body runs as a single transaction, without BEGIN or COMMIT appearing in the definition. It commits when the body finishes, and rolls back if anything inside it fails.

An error rolls the whole body back, whether it is a THROW or a statement that fails on its own:

DEFINE FUNCTION fn::place_order($item: record<product>, $quantity: int) -> record<order> {
    LET $order = CREATE ONLY order SET item = $item, quantity = $quantity;
    IF $item.stock < $quantity {
        THROW "Insufficient stock for " + <string>$item;
    };
    UPDATE $item SET stock -= $quantity;
    RETURN $order.id;
};

fn::place_order(product:keyboard, 500);

-- No order exists: the THROW rolled the whole body back, so no order
-- is left behind reserving stock that was never decremented
SELECT VALUE id FROM order;

An early RETURN is not a failure, so the body commits and the work already done is kept:

DEFINE FUNCTION fn::ship_order($order: record<order>) -> record<shipment> {
    LET $shipment = CREATE ONLY shipment SET order = $order, carrier = 'DHL';
    RETURN $shipment.id;                          -- the caller only wants the id
    UPDATE $order SET status = 'shipped';         -- never runs
};

Calling a function from inside a manual transaction makes its statements part of that transaction rather than a nested one, so a failure inside the function aborts the caller's transaction too. See Transactions.

Recursive functions

A function is able to call itself, making it a recursive function. One example of a recursive function is the one below which creates a relation between each and every record passed in.

Consider a situation in which seven person records exist. First, person:1 will need to be related to the rest of the person records, after which there are no more relations to create for it. Following this, the relations for person:2 and all the other records except for person:1 will need to be created, and so on.

This can be done in a recursive function by creating all the relations between the first record and the remaining records, after which the function calls itself by passing in all the records except the first. This continues until the function receives less than two records, in which case it ceases calling itself by doing nothing, thereby ending the recursion.

DEFINE FUNCTION fn::relate_all($records: array<record>) {
  IF $records.len() < 2 {
      -- Don't do anything, ending the recursion
  }  ELSE {
      LET $first = $records[0];
      LET $remainder = $records[1..];
      FOR $counterpart IN $remainder {
          RELATE $first->to->$counterpart;
      };
      fn::relate_all($remainder);
  }
};

CREATE |person:1..8|;

fn::relate_all(SELECT VALUE id FROM person);

SELECT id, ->to->? FROM person;

The last query can be viewed graphically inside SurrealDB Studio, leading to an output showing a seven-pointed star.

An image of a seven-pointed star created visually by relating seven records to each other and displayed inside SurrealDB Studio's graph view.

Permissions

You can set the permissions for a custom function using the PERMISSIONS clause. The PERMISSIONS clause is mostly used to restrict who can access a function and what data they can access. It can be set to NONE, FULL, or WHERE @condition.

FULL: When Full permissions are granted record users have access to the function. This is the default permission when not specified.
NONE: When this permission is granted, record users have no access to the defined function.
WHERE @condition: Permissions are granted to the function based on the specified condition.

Note

The examples below use the Surreal Deal Store dataset.

Using the `FULL` permission

The FULL permission grants all users access to the function. The following example defines a function that fetches all products from the product table and grants the function full permissions to access the data to all users.

Using the `NONE` permission

The NONE permission denies all record users access to the function. The following example defines a function that fetches all products from the product table

-- Define a function that fetches all expiration years from the payment_details table and denies access to all none-admin users
DEFINE FUNCTION fn::fetchAllPaymentDetails() -> array {
	SELECT stored_cards.expiry_year FROM payment_details LIMIT 5
} PERMISSIONS NONE;

RETURN fn::fetchAllPaymentDetails();

Using the `WHERE` clause

The WHERE clause allows you to specify a condition that determines the permissions granted to the function. The condition must evaluate to a boolean value. If the condition evaluates to true, the function is granted permissions. If the condition evaluates to false, the function is not granted permissions.

-- Define a function that fetches all products with the condition that only admin users can access it
DEFINE FUNCTION fn::fetchAllProducts() -> array {
	 SELECT * FROM product LIMIT 10
} PERMISSIONS WHERE $auth.admin = true;

Functions that other definitions require to stay read-only

Available since: v3.3.0

Two places in the schema evaluate an expression that must not modify data: a COMPUTED field body, and a PERMISSIONS FOR select clause. A custom function called from either is held to the same rule. The rule is enforced when the write is reached, not when the field, the clause or the function is defined, so a definition that calls a writing function is stored without complaint and fails when it is evaluated.

A FOR select clause that calls a function which writes is accepted, and each read that evaluates the clause fails with A PERMISSIONS clause cannot contain a statement that modifies data. The FOR create, FOR update and FOR delete clauses may call a function that writes, as described in Writes inside a permission clause.

DEFINE FUNCTION fn::log_access() -> bool { CREATE access_log SET at = time::now(); RETURN true; };

-- Accepted, but a read that evaluates this clause now fails
DEFINE TABLE product PERMISSIONS FOR select WHERE fn::log_access();

The same applies when the function changes rather than the caller. DEFINE FUNCTION OVERWRITE and ALTER FUNCTION accept a body that starts to write while a computed field or a FOR select clause still calls the function. The field or clause then fails the next time it is evaluated - a computed field with A COMPUTED clause cannot contain a statement that modifies data - so check which definitions call a function before changing its body to write.

DEFINE FUNCTION fn::price_with_tax($price: number) -> number { RETURN $price * 1.2; };
DEFINE FIELD gross_price ON product COMPUTED fn::price_with_tax(price);

-- Accepted, but every read of `gross_price` now fails
DEFINE FUNCTION OVERWRITE fn::price_with_tax($price: number) -> number {
	CREATE price_change SET price = $price, at = time::now();
	RETURN $price * 1.2;
};

The check runs where the write is reached, so it applies in the same way to a write several functions deep and to one reached through eval::surql(), a JavaScript function, or a closure that arrives as data.

Using `IF NOT EXISTS` clause

The IF NOT EXISTS clause can be used to define a function only if it does not already exist. You should use the IF NOT EXISTS clause when defining a function in SurrealDB if you want to ensure that the function is only created if it does not already exist. If the function already exists, the DEFINE FUNCTION statement will return an error.

It's particularly useful when you want to safely attempt to define a function without manually checking its existence first.

On the other hand, you should not use the IF NOT EXISTS clause when you want to ensure that the function definition is updated regardless of whether it already exists. In such cases, you might prefer using the OVERWRITE clause, which allows you to define a function and overwrite an existing one if it already exists, ensuring that the latest version of the function definition is always in use.

-- Create a FUNCTION if it does not already exist
DEFINE FUNCTION IF NOT EXISTS fn::example() {};

Using `OVERWRITE` clause

The OVERWRITE clause can be used to define a function and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing function definition. If the function already exists, the DEFINE FUNCTION statement will overwrite the existing definition with the new one.

-- Create a FUNCTION and overwrite if it already exists
DEFINE FUNCTION OVERWRITE fn::example() {};

Functions as custom middleware

Available since: v3.0.0

A DEFINE FUNCTION statement can be used to define a function for use as custom middleware. For more details on defining a custom function in this manner, see the DEFINE API page.