User Functions

ArcadeDB can be extended with user-defined functions written in SQL, JavaScript, OpenCypher, or Java. Functions defined in any language are callable from ALL query languages (SQL, Cypher, Gremlin, GraphQL, MongoDB).

Overview

User functions provide a powerful way to encapsulate business logic and reuse it across different query languages:

  • SQL Functions: Use SQL queries as function bodies

  • JavaScript Functions: Use JavaScript for complex calculations

  • OpenCypher Functions: Use Cypher queries for graph operations

  • Java Functions: Use Java for maximum performance

Universal Function Registry

Functions are stored in a global registry and are accessible from any query language:

// Define once in SQL
DEFINE FUNCTION math.sum "SELECT :a + :b" PARAMETERS [a,b] LANGUAGE sql

// Call from SQL
SELECT `math.sum`(3, 5) AS result

// Call from Cypher
RETURN math.sum(3, 5) AS result

// Call from Gremlin, GraphQL, MongoDB queries...

Database’s Functions

Look at the Database Interface page for built-in database functions.

Defining User Functions

User functions can be defined via SQL using the following command:

DEFINE FUNCTION <library>.<name> "<body>" [PARAMETERS [<parameter>,*]] LANGUAGE <language>

  • <library> - A namespace to group related functions

  • <name> - The function’s name

  • <body> - The function’s body as a string in the chosen language’s syntax

  • [<parameter>,*] - A list of parameter identifiers used in the function body. Omit the PARAMETERS [] block for functions without parameters

  • <language> - One of: sql, js (JavaScript), or opencypher (alias cypher). Java is not a valid LANGUAGE value: Java functions are registered programmatically (see User Functions in Java below)

SQL Language Functions

SQL functions execute SQL queries and return the result of the first row’s projection.

The return value in a SQL function is determined by the projection named 'result' or the first column if only one column is returned.

Examples

// Simple arithmetic function
DEFINE FUNCTION math.add "SELECT :a + :b AS result" PARAMETERS [a,b] LANGUAGE sql;
SELECT `math.add`(10, 5) AS sum;  // Returns 15

// Function returning a string
DEFINE FUNCTION the.answer 'SELECT "forty-two" AS result' LANGUAGE sql;
SELECT `the.answer`();  // Returns "forty-two"

// Function with no parameters
DEFINE FUNCTION utils.currentYear 'SELECT date_format(sysdate(), "yyyy") AS result' LANGUAGE sql;
SELECT `utils.currentYear`();

JavaScript Language Functions

JavaScript functions use GraalVM’s JavaScript engine for complex calculations and data transformations.

Examples

// Fused multiply-add function
DEFINE FUNCTION my.fma 'return a + b * c' PARAMETERS [a,b,c] LANGUAGE js;
SELECT `my.fma`(1, 2, 3);  // Returns 7

// String manipulation
DEFINE FUNCTION text.greet 'return "Hello, " + name + "!"' PARAMETERS [name] LANGUAGE js;
SELECT `text.greet`('World');  // Returns "Hello, World!"

// Complex JSON processing
DEFINE FUNCTION json.extract 'return JSON.parse(data).users.length' PARAMETERS [data] LANGUAGE js;
SELECT `json.extract`('{"users":[1,2,3]}');  // Returns 3

OpenCypher Language Functions

OpenCypher functions execute Cypher queries, perfect for graph traversals and pattern matching.

You can use either opencypher or cypher as the language identifier (they are aliases).

Examples

// Simple calculation using Cypher
DEFINE FUNCTION cypher.double "RETURN $x * 2" PARAMETERS [x] LANGUAGE opencypher;
SELECT `cypher.double`(5);  // Returns 10

// Graph query function
DEFINE FUNCTION graph.countNeighbors
  "MATCH (n) WHERE id(n) = $nodeId MATCH (n)-[]->() RETURN count(*) AS cnt"
  PARAMETERS [nodeId] LANGUAGE cypher;

// Use in SQL query
SELECT `graph.countNeighbors`('#1:0') AS neighbors;

// Use in Cypher query
MATCH (n:Person) RETURN n.name, graph.countNeighbors(id(n)) AS friendCount;

// Complex graph pattern function
DEFINE FUNCTION graph.findCommonFriends
  "MATCH (a)-[:KNOWS]->(common)<-[:KNOWS]-(b)
   WHERE id(a) = $id1 AND id(b) = $id2
   RETURN count(DISTINCT common) AS cnt"
  PARAMETERS [id1, id2] LANGUAGE opencypher;

Calling User Functions

Functions can be called from any supported query language:

Language Syntax Example

SQL

library.function(args)`

SELECT `math.sum(3, 5)

Cypher

library.function(args)

RETURN math.sum(3, 5)

Cypher CALL

CALL library.function(args) YIELD result

CALL math.sum(3, 5) YIELD result RETURN result

Gremlin

Standard function call

Access via SQL bridge

GraphQL

In query fields

Access via SQL bridge

Cross-Language Function Calls

One of ArcadeDB’s unique features is the ability to define a function in one language and call it from any other:

// Define in SQL
DEFINE FUNCTION sql.concat "SELECT :a || :b" PARAMETERS [a,b] LANGUAGE sql;

// Define in JavaScript
DEFINE FUNCTION js.power "return Math.pow(x, y)" PARAMETERS [x,y] LANGUAGE js;

// Define in Cypher
DEFINE FUNCTION cypher.shortestPath
  "MATCH path=shortestPath((a)-[*]-(b))
   WHERE id(a)=$from AND id(b)=$to
   RETURN length(path)"
  PARAMETERS [from, to] LANGUAGE opencypher;

// Call all from Cypher
RETURN sql.concat('Hello', ' World') AS greeting,
       js.power(2, 10) AS power,
       cypher.shortestPath('#1:0', '#1:100') AS pathLen;

// Call all from SQL
SELECT `sql.concat`('foo', 'bar') AS str,
       `js.power`(3, 3) AS cube,
       `cypher.shortestPath`('#1:0', '#1:100') AS dist;

Deleting User Functions

A user function can be deleted via SQL using the following command:

DELETE FUNCTION <library>.<name>

Example

DELETE FUNCTION extra.tsum;
DELETE FUNCTION math.obsolete;

User Functions in Java

Java functions provide maximum performance and access to the full Java ecosystem. Unlike the SQL, JavaScript and OpenCypher functions described above, Java functions cannot be created with DEFINE FUNCTION …​ LANGUAGE java: they must be registered programmatically through the Java API. There is no auto-discovery (no ServiceLoader, no annotation or classpath scan) for user functions: registration is always an explicit API call.

Where does the JAR go, and when is the function registered?

  1. Deploy the JAR. Copy the JAR containing your function classes into the lib/ directory of the ArcadeDB installation ($ARCADEDB_HOME/lib/). The server start script (server.sh / server.bat) puts the whole lib/ folder on the classpath (-cp "$ARCADEDB_HOME/lib/*"), so your classes become loadable with Class.forName(…​).

  2. Register the function. Because there is no auto-discovery, you must call the registration API yourself. When and where depends on how you run ArcadeDB:

    • Embedded (ArcadeDB as a library inside your application): register right after opening the database, before running queries that use the function.

    • Standalone server: registration has to run inside the server JVM. Package the registration logic as a Server Plugin and register the function from its startService() method (see the server example below). Drop the same JAR in lib/plugins/ and enable it with the arcadedb.server.plugins setting.

There are two independent ways to expose Java code as functions.

Option A: Register a Java class or method as a function library

A function library registered on the schema is callable from all query languages, exactly like the SQL/JavaScript/Cypher functions above. Use JavaClassFunctionLibraryDefinition to expose every public method of a class, or JavaMethodFunctionLibraryDefinition to expose a single method. The library name becomes the namespace and each method name becomes a function in it.

Given this class deployed in a JAR under lib/:

package com.example;

public class MathFunctions {
  // Instance method: invoked on a new instance created via the no-arg constructor
  public int sum(final int a, final int b) {
    return a + b;
  }

  // Static method: invoked directly, no instance needed
  public static double max(final double a, final double b) {
    return Math.max(a, b);
  }
}

Register it on the database schema (embedded usage shown):

// Register the whole class as a library named "math".
// Every public method (sum, max, ...) becomes a function in that library.
database.getSchema().registerFunctionLibrary(
    new JavaClassFunctionLibraryDefinition("math", "com.example.MathFunctions"));

// ...or pass the Class directly:
database.getSchema().registerFunctionLibrary(
    new JavaClassFunctionLibraryDefinition("math", com.example.MathFunctions.class));

// ...or register a single method as a one-function library:
database.getSchema().registerFunctionLibrary(
    new JavaMethodFunctionLibraryDefinition("math",
        com.example.MathFunctions.class.getMethod("sum", Integer.TYPE, Integer.TYPE)));
A library name can be registered only once. Re-registering the same name throws IllegalArgumentException. Remove it first with database.getSchema().unregisterFunctionLibrary("math").

Once registered, call the function from any query language using the library.name form:

// Direct invocation through the API
Object r = database.getSchema().getFunction("math", "sum").execute(20, 7);  // 27

// From SQL (backtick-quote the library.function reference)
database.command("sql", "SELECT `math.sum`(20, 7) AS result");

// From Cypher
database.query("opencypher", "RETURN math.sum(20, 7) AS result");

Option B: The dedicated "java" query engine

The java query engine invokes a Java method directly as a command, without going through the schema. For security, every class or method must be added to an allow-list with registerFunctions(…​) before it can be called; calling anything that was not registered throws a SecurityException.

// Allow-list a single method (Class::method) or a whole class
database.getQueryEngine("java").registerFunctions("com.example.MathFunctions::sum");
database.getQueryEngine("java").registerFunctions("com.example.MathFunctions"); // all methods

// Invoke it (positional parameters follow the command string)
ResultSet result = database.command("java", "com.example.MathFunctions::sum", 5, 3);
int value = (int) result.next().getProperty("value");  // 8

// Remove the allow-list when done
database.getQueryEngine("java").unregisterFunctions();

The java engine supports only command(…​) (not query(…​)) and positional parameters; it matches overloaded methods by argument count and type.

Registering Java functions on a standalone server

On a standalone server there is no application code to call registerFunctionLibrary(…​), so wrap the registration in a Server Plugin. Build a JAR containing both the function class and a plugin like the one below, place it in lib/plugins/, and enable it.

package com.example;

import com.arcadedb.ContextConfiguration;
import com.arcadedb.function.java.JavaClassFunctionLibraryDefinition;
import com.arcadedb.server.ArcadeDBServer;
import com.arcadedb.server.ServerPlugin;

public class MathFunctionsPlugin implements ServerPlugin {
  private ArcadeDBServer server;

  @Override
  public void configure(final ArcadeDBServer arcadeDBServer, final ContextConfiguration configuration) {
    this.server = arcadeDBServer;
  }

  @Override
  // Run after the databases are open so they are available for registration
  public PluginInstallationPriority getInstallationPriority() {
    return PluginInstallationPriority.AFTER_DATABASES_OPEN;
  }

  @Override
  public void startService() {
    // Register the library on every database that needs it
    server.getDatabase("mydb").getSchema().registerFunctionLibrary(
        new JavaClassFunctionLibraryDefinition("math", "com.example.MathFunctions"));
  }
}

Enable the plugin with the arcadedb.server.plugins setting, using the <pluginName>:<pluginFullClassName> format (comma-separate multiple plugins):

-Darcadedb.server.plugins=Math:com.example.MathFunctionsPlugin

After the server starts, the functions are available from SQL, Cypher and the other query languages (for example SELECT `math.sum(20, 7)`), including over the HTTP/REST API.

Best Practices

  1. Choose the Right Language:

    • Use SQL for data transformations and aggregations

    • Use JavaScript for complex business logic and calculations

    • Use OpenCypher for graph traversals and pattern matching

    • Use Java for maximum performance

  2. Namespace Organization: Group related functions under the same library namespace [source,sql] ---- DEFINE FUNCTION math.sum …​ DEFINE FUNCTION math.avg …​ DEFINE FUNCTION utils.formatDate …​ DEFINE FUNCTION graph.shortestPath …​ ----

  3. Function Naming: Use descriptive names that indicate what the function does

  4. Performance Considerations:

    • User functions are evaluated for each row/result

    • For heavy operations, consider caching or materialized views

    • Java functions provide the best performance

  5. Error Handling: Functions should handle null inputs gracefully

Advantages Over Neo4j

Unlike Neo4j which only supports Java-based user-defined procedures requiring compilation and deployment:

✅ Dynamic Definition: Define functions at runtime without restart
✅ 4 Languages: SQL, JavaScript, OpenCypher, and Java
✅ Universal Access: Call from any query language
✅ No Compilation: Interpreted languages work immediately
✅ Easy Management: Simple SQL commands to define/delete functions