JavaScript Functions for Query Reference

reference

Enterprise Edition

This reference guide describes how to write extension functions for SQL++ for Query using the JavaScript language.

Introduction

There are two types of user-defined function in SQL++ for Query:

Inline functions are defined using SQL++ expressions. They enable you to name and reuse complex or repetitive expressions, including subqueries, in order to simplify your queries.
External functions are defined using an external language. They enable you to create functions that may be difficult or impossible to define using built-in SQL++ expressions. The only supported language is JavaScript.

This reference includes details of external functions using JavaScript.

External Libraries

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

You must create the external library and the external function code using the Query Workbench or the SQL++ Functions REST API.

You do not call the external function code directly from SQL++. Instead, when you have created an external library and added a JavaScript function to it, you must define a SQL++ user-defined function to call the JavaScript function.

External libraries, like SQL++ user-defined functions, may be scoped or global. This enables you to keep the code for external functions separate where required.

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.

You can apply access restrictions to scopes, so that only certain groups of users will be able to access collections and libraries within that scope.

Code which is stored in a scoped library is private to users of that scope, and is not visible or available to users of another scope. Code which is stored in a global library is available to users of all scopes.

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 may have a global math library, and a math library in each scope.

Figure 2. Calling a function in a scoped external library

In order to use a SQL++ user-defined function which calls external JavaScript code in a scoped library, you must set the query context to the same bucket and scope as the scoped library.

SQL++ Managed User-Defined Functions

(Introduced in Couchbase Server 7.6)

In Couchbase Server 7.6 and later, you can create the code for an external function and the corresponding SQL++ user-defined function in a single operation. This means that you don’t have to specify an external library and create the code for the external function, before creating the SQL++ user-defined function.

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

Added Constructs

JavaScript functions in SQL++ for Query support most of the language constructs available in ECMAScript, though there are a number of restrictions related to the Couchbase environment. There are also additions that have been made to the language for working specifically with Couchbase.

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, via a 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) {

    var q = SELECT name as airline_name, callsign as airline_callsign 
    FROM `travel-sample`.`inventory`.`airline` 
    WHERE country = $country;  (1)

    var res = [];

    for (const doc of q) {

        var airline = {}
        airline.name = doc.airline_name  (2)
        airline.callsign = doc.airline_callsign  (2)
        res.push(airline);

    }

    return res;

}

1	The SQL++ is written directly into the JavaScript code without having to be used as part of a function call. You can even provide parameters that can be used in the SQL++ statement.
2	A standard JavaScript iterator is used to access the values returned from the SQL++ statement.

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

Unsupported Features

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

Browser Extensions

Because 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 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

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);
}