The previous blog introduced to you new features in JavaScript user-defined functions.
We are now going to explore them in more detail.
We start with a basic mechanism to execute N1QL statements inside JavaScript code.
N1QL in JavaScript
There are two ways to execute N1QL statements inside a JavaScript function: either calling directly a function aptly named N1QL(), or by letting the JavaScript compiler detect embedded N1QL statements and produce corresponding N1QL() calls.
Each method is examined below.
Example pre-requisites
The following sections contain a number of examples.
These are fully functional but in order to execute them you will need:
-
- a Couchbase cluster, version 7.1 or newer
- a bucket named b1
- a primary index on b1
- to create each function in a library called udfblog, and
- to create the corresponding N1QL UDF
You can use the UDF tab in the web console to create the function and the UDF.
N1QL() function
The N1QL() function takes a statement in the form of a string, and a second optional parameter containing placeholder values.
All it takes to execute a N1QL statement that does not take parameters and does not return values is the following:Â
and the corresponding UDF:
|
1 |
CREATE FUNCTION doInsert() LANGUAGE JAVASCRIPT AS "doInsert" AT "udfblog" |
(Forget for a second that the function itself does not return any value. Also, apologies for the code snippets shown as images, WordPress did not like rendering these as text).
When the N1QL() function is called, the statement passed in the first parameter gets parsed, planned, and executed.
If the statement returns no value, by the time the N1QL function returns, the statement has completed execution: in this particular case, assuming that bucket b1 exists and a key k1 does not already exist, a new document will be inserted.
If you are familiar with ESQL/C’s or PL/SQL’s EXECUTE IMMEDIATE statement, the N1QL() function is the JavaScript UDF equivalent.Â
When values are returned, the return value of N1QL() is a JavaScript iterator, which can be used to collect values one at a time, as we will see later on.Â
Dynamic N1QL
Of course, the other use of the N1QL() function is to build a statement dynamically by concatenating strings, for example:
While this is perfectly legal, it’s probably best not to employ such a technique given the risk of N1QL (or more generally SQL) injection that it carries.
Use placeholders instead.
JavaScript Transpiler
The second way to execute N1QL is to instead embed the N1QL statement in the function body, and let the transpiler (a component of the JavaScript compiler) detect it and build the correct N1QL calls behind the scenes:
This is very convenient to execute static statements immediately, and the function suddenly becomes much more readable.
N1QL statement placeholders and placeholder values
The best way to avoid the risks associated with dynamic N1QL generation is to have static statements using either named or positional placeholders, and passing placeholder values at execution time.
N1QL() function
When using the N1QL() function, this can be done by passing the values in the second optional argument like the following.
Positional parameters
This case is covered by passing an array of values:
|
1 2 3 |
function doInsert() { Â Â Â Â var q = N1QL("insert into b1 values(\"k4\", {\"f1\": $1})", [4]); } |
Named parameters
In this case, just pass an object whose field name matches the parameter names:
|
1 2 3 |
function doInsert() { Â Â Â Â var q = N1QL("insert into b1 values(\"k5\", {\"f1\": $p1})", { p1: 5}); } |
It is not possible to mix positional and named parameters, the N1QL() function only accepts one of either an array or an object.
Note that the values passed to N1QL() don’t have to be literals, you can construct arrays and objects from variables, or can pass an array or object variable:
|
1 2 3 4 |
function doInsert() { Â Â Â Â let p1 = 6; Â Â Â Â var q = N1QL("insert into b1 values(\"k6\", {\"f1\": $1})", [p1]); } |
or
|
1 2 3 4 |
function doInsert() { Â Â Â Â let p1 = [7]; Â Â Â Â var q = N1QL("insert into b1 values(\"k7\", {\"f1\": $1})", p1); } |
Transpiler
With the transpiler, only named parameters are possible – however, using them is as simple as using already declared variables:
|
1 2 3 4 |
function doInsert() { Â Â Â Â let p1 = 8; Â Â Â Â var q = insert into b1 values("k8", {"f1": $p1}); } |
Of course, it is possible to use function parameters directly:
|
1 2 3 |
function doInsert(p1) { Â Â Â Â var q = insert into b1 values("k9", {"k1": $p1}); } |
And the corresponding N1QL UDF:
|
1 |
CREATE FUNCTION doInsert(p1) LANGUAGE JAVASCRIPT AS "doInsert" AT "udfblog" |
Conclusion
We have covered basic examples on how to use N1QL inside javascript functions, both using the N1QL() function call and embedding the N1QL statement directly into JavaScript, and show how to use named and positional parameters.
The next blog post will cover iterator and data manipulation statements.
