From N1QL to JavaScript and Back – Part 2: Basic Usage

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:

(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:

Named parameters

In this case, just pass an object whose field name matches the parameter names:

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:

or

Transpiler

With the transpiler, only named parameters are possible – however, using them is as simple as using already declared variables:

Of course, it is possible to use function parameters directly:

And the corresponding N1QL UDF:

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.