JavaScript Functions for Query Reference

Capella Operational

reference

You can write extension functions for SQL++ for Query in Couchbase Capella, using the JavaScript programming language.

About User-Defined Functions in SQL++ for Query

Capella supports 2 types of user-defined function in SQL++ for Query:

Inline functions are defined using SQL++ or JavaScript expressions. Use an inline function to reuse complex or repetitive expressions, including subqueries, and simplify your SQL++ queries.
External functions are defined using an external language and stored in a user-defined function (UDF) library. External functions can be collectively managed through their UDF library. Capella supports defining external functions using JavaScript.

You can use inline or external JavaScript functions to run expressions or queries that may be difficult or impossible to define using built-in SQL++ expressions.

External Libraries

You can store JavaScript functions in a user-defined function (UDF) library. This enables you to share external function code for use in more than one SQL++ user-defined function. A library can contain 1 or more JavaScript functions.

For more information about how to create a UDF library, see Create a User-Defined Function Library.

You cannot call external function code directly from SQL++. If you want to manage your external function code with a library, you must:

Create a UDF library and add a JavaScript function or functions to that library.
Create a user-defined function to call a specific JavaScript function from that library.

UDF libraries, like SQL++ user-defined functions, may be scoped or global. Set a UDF library or user-defined function as Scoped to keep the code for external functions separate.

Figure 1. Global and scoped external libraries

A global library is created within a namespace, at the same level as the buckets within the namespace.
A scoped library is created within a scope, at the same level as the collections within a scope.

Any code that you store in a global library is available to all users with read and write permissions on your operational cluster.

A global library may have the same name as a scoped library, and scoped libraries may have the same name as each other. For example, you can have a global math library, and a math library in each scope.

Figure 2. Calling a function in a scoped external library

When you want to use a Scoped user-defined function from the Query Tab, you must set your query context to the same bucket and scope as the library.

SQL++ Managed User-Defined Functions

Couchbase Server 7.6

For operational clusters using Couchbase Server 7.6 and later, you can create the code for a JavaScript function and the corresponding SQL++ user-defined function in a single operation in the Query Tab or cbq. You do not have to create a UDF library before creating a SQL++ user-defined function.

With a SQL++ managed user-defined function, the JavaScript function code is stored inline, along with the SQL++ user-defined function. You cannot share this JavaScript function code with other user-defined functions, or access it from a UDF library.

Added Language Constructs

User-defined functions in SQL++ for Query support most of the language constructs available in ECMAScript. Couchbase’s implementation makes specific changes to support working with JavaScript through SQL++.

SQL++ Embedded Statements

Top level SQL++ keywords, such as SELECT, UPDATE, INSERT and DELETE, are available as inline keywords in functions. Operations that return values such as SELECT are accessible through a returned iterable handle. SQL++ Query results, through SELECT, are streamed in batches to the iterable handle as the iteration progresses through the result set.

Example 1. JavaScript code with embedded SQL++ statements

function selectAirline(country) {

    // SQL++ is written directly into the JavaScript code without the need
    // for a function call. 
    var q = SELECT name as airline_name, callsign as airline_callsign 
    FROM `travel-sample`.`inventory`.`airline` 
    WHERE country = $country;

    var res = [];

    for (const doc of q) {

        var airline = {}
        // Use a standard JavaScript iterator to access the values
        // from the SQL++ statement
        airline.name = doc.airline_name
        airline.callsign = doc.airline_callsign
        res.push(airline);

    }

    return res;

}

You can even provide parameters in your JavaScript code that can be used in the SQL++ statement.

For more information, see Calling SQL++ from JavaScript.

Unsupported JavaScript Features

The following features are not supported in JavaScript functions for Query:

Browser Extensions
Global State
Logging

Browser Extensions

JavaScript functions in SQL++ for Query do not execute in the context of a browser. The extensions that browsers add to the core language, such as window methods, DOM events, and so on, are not available.

Global State

All variables must be local to the function. Global state is not permitted.

Example 2. JavaScript code with global variable

var count = 0;                         // Not allowed - global variable.
function increment() {
    count++;
}

Along with global state, global arrow functions are not supported. Arrow functions local to individual JavaScript functions are supported.

Logging

Logging using the console.log(..) function is not supported.

Restricted JavaScript Features

Couchbase Server 7.6.2

The following features are restricted in JavaScript functions for Query in Couchbase Server 7.6.2 and later:

Code Injection
Date Granularity

Code Injection

JavaScript constructs that may allow for code injection have been removed:

The eval symbol has been removed.
The Function construct has been removed.

Example 3. JavaScript code with the eval symbol

The following example does not compile as it uses the eval symbol.

function evaluate() {
    var q = select jscode from <bucket> where meta().id = <docid>;
    let iter = q[Symbol.iterator]();
    let code = iter.next();
    let result = eval(code);
}

Example 4. JavaScript code with the Function construct

The following example does not compile as it uses the Function construct.

function dynamicfunction() {
var q = select jscode from <bucket> where meta().id = <docid>;
    let iter = q[Symbol.iterator]();
    let code = iter.next();
  return new Function("inject", code);
}

function evaluate() {
    dynamicfunction();
}

Date Granularity

The granularity of the Date object has been reduced to 1 second. This is to prevent a potential attacker from easily measuring the difference between a CPU cache miss and cache hit, hence taking advantage of side-channel attacks or speculative execution attacks.

Example 5. JavaScript code with timestamp

The following example executes a SQL++ query to insert a document with a field containing the current timestamp. The timestamp is returned to the last second, rather than the most recent millisecond.

function addOrder() {
    let curr = Date.now();
    N1QL('INSERT INTO orders VALUES (uuid(),{"time":'+ curr +'})')
}

Example 6. JavaScript code with date comparison

The following example simulates sleep by blocking execution by the number of milliseconds passed as a function parameter. Since the Date.Now() function does not return the current time with millisecond granularity, the function may not work as expected.

function sleep(milliseconds) {
  let init = Date.now();
  let curr = null;
  do {
     curr = Date.now();
   } while (curr - init < milliseconds);
}