Executing N1QL statements from Javascript functions.
Introduction
As well as being able to call JavaScript functions from N1QL, you can also call N1QL statements from inside your Javascript functions.
Calling N1QL statements inline
You can embed a N1QL statement directly in your Javascript code:
function addAirline() {
var q = insert into airport values("1200",
{"id": 1200,
"type": "airline",
"name": "Couch Air",
"ca1": "Q5", "icao": "MLA",
"callsign": "COUCH-AIR",
"country": "United States"});
}Executing N1QL statements using the N1QL() call
In addition, you can also execute a N1QL statement by calling it from the N1QL(…) function.
function addAirline() {
var q = N1QL("insert into airport values(1200, " +
"{\"id\": 1200," +
" \"type\": \"airline\"," +
" \"name\": \"Couch Air\"," +
" \"ca1\": \"Q5\"," +
" \"icao\": \"MLA\"," +
" \"callsign\": \"COUCH-AIR\"," +
" \"country\": \"United States\"})")
}
Behind the scenes, the inline call method will generate the equivalent N1QL call, so whichever you choose to use will come down to personal preference.
|
Side Effects
Functions executed as part of expressions cannot have side effects that will change data stored by the Couchbase engine. For example, this statement:
SELECT "true" AS response WHERE AddAirline() = "missing";will generate an error because the AddAirline() function will attempt to alter data, which the caller may be unaware of.
Functions that change data must be called using the EXECUTE FUNCTION statement.
|
Returning values from N1QL statements
As shown in the examples above, embedded N1QL statements return values which can be used later on in your code.
The values returned from the statement calls are Javascript iterators: lists of values or documents returned from the database.
In the next example, we’re going to retrieve a list of the hotels stored in the travel-sample database:
function selectHotels() {
var q = SELECT * FROM `travel-sample`.`inventory`.`hotel`; (1)
var res = [];
for (const doc of q) { (2)
res.push(doc); (3)
}
return res; (4)
}| 1 | The N1QL statement returns an iterator containing the items retrieved by the query. |
| 2 | Using the standard Javascript iterator pattern to loop through the items returned in q. |
| 3 | Add the current document from the iterator to the result array res. |
| 4 | Once all the items have been retrieved, return the result array. |
|
If an inline statement/N1QL call does not return a value, then the associated N1QL statement is executed as part of a synchronous operation. i.e. the runtime will wait until the statement completes before moving on to the next line of Javascript. If the inline statement/N1QL call returns a value then it is executed asynchronously: execution continues before the iterator is returned. Each document is fetched from the bucket as it requested by the iterator. 
|
Passing Parameters to N1QL statements
You can pass parameters from your Javascript to your N1QL statements. Parameters can either be positional or named.
- Positional
-
The parameters are applied to the statement in the order they appear in the list.
function addAirlineWithPositionalParameters(name, callsign, country) { var q = N1QL("insert into `travel-sample`.`inventory`.`airport` values(\"1600\", " + "{\"id\": $1, " + "\"type\": $2, " + "\"name\": $3, " + "\"ca1\": $4, " + "\"icao\": $5, " + "\"callsign\": $6, " + "\"country\": $7})", [1600, "airline", name, "Q5", "MLA", callsign, country]) } - Named
-
The parameters are given a mnemonic name attached to the value, so they can be included directly in the N1QL statement.
function addAirlineWithNamedParameters(name, callsign, country) { var q = insert into `travel-sample`.`inventory`.`airport` values("1700", {"id": 1700, "type": "airline", "name": $name, "ca1": "Q5", "icao": "MLA", "callsign": $callsign, "country": $country}); }The names of the parameters passed into the Javascript function are used in the N1QL statement without any need to assign the parameters in a separate step.
Transactions
Transactions are supported from N1QL statements called from Javascript functions.
-
The function can run statements in a transaction that was started before the function was executed.
-
The function can run a statement that starts the transaction.
-
The function can run a statement that rolls back a transaction.
|
A N1QL statement and its corresponding iterator must live entirely within the scope of a transaction. If a transaction is started during the iteration process, then the transaction cannot be rolled back entirely. 
|
Role-Based Access Control
In order to execute N1QL statements as part of a Javascript function, the user executing the function must have the appropriate privileges to perform the action on any objects referenced in the N1QL statement.
Executing N1QL statements that call functions
It is often the case that Javascript function will call a N1QL statement that may itself call another Javascript function. However, it is important to be aware that each Javascript function call executed from a parent call will use a new Javascript worker process to run. The deeper the calls are nested, the fewer Javascript workers are available to run, so the calling chain will eventually fail and throw an error. This can be demonstrated using a recursive call sequence as shown below:
function doRecursion() {
var q = N1QL("EXECUTE FUNCTION doRecursion()")
}Then executing the function:
EXECUTE FUNCTION doRecursion();returns the following result:
[
{
"code": 10109,
"msg": "Error executing function 'doRecursion' (my-library:doRecursion)",
"reason": {
"details": {
"Code": " var q = N1QL(\"EXECUTE FUNCTION doRecursion()\")",
"Exception": {
"caller": "javascript:133",
"code": 10112,
"key": "function.nested.error",
"message": "Error executing function 'doRecursion': 10 nested javascript calls" (1)
},
"Location": "functions/my-library.js:30",
"Stack": " at doRecursion (functions/my-library.js:30:13)"
},
"type": "Exceptions from JS code"
}
}
]| 1 | The call failed after 10 nested call, which exhausted the number of Javascript workers available during the call sequence. |
|
The JavaScript workers are created when the Couchbase server is started up. \(Number\:of\:JavaScript\:workers = 4 \times number\:of\:CPUs\) The service will automatically prevent recursive calls if there are less than 50% javascript workers available |