recursive.codes


The Personal Blog of Todd Sharp

Oracle Functions - Connecting To ATP With Node.JS

Posted By: Todd Sharp on 8/2/2019 9:01 UTC
Tagged: Node

In my last post we looked at connecting up to ATP with a Java based serverless function. That worked out great, but I wanted to show you how you could accomplish the same task via a Node.JS based function. In this post we'll do just that, but with a slight twist on how we store the data.

To get started, create a new Fn application via the Fn CLI. This time we'll pass our config values in directly with the create app call:

Now create our first function, but instead of using an init-image like we did last time, we'll just use the node runtime and modify things ourselves:

Open up the generated func.yaml file and change the value of runtime to docker. Add the value of format, memory, timeout and idle_timeout as shown below:

Now create a Dockerfile in the root of the function. We'll need our wallet as before (download it and put it in the root of the function), but this time we'll also need the Oracle Instant Client so that Node can make the proper connection. and we'll grab that dependency and install it in our Dockerfile. The entire Dockerfile looks like this:

In this Dockerfile we're using Oracle Linux as a base image and installing the instant client dependencies via the Oracle yum repository instead of manually installing it (which makes life much easier!). We're also copying in our wallet files so they can be used to make the connection. Finally, we set node func.js as our ENTRYPOINT

Next, run npm install oracledb and npm install dateformat from the function root. This will install the Oracle Node package and a date formatting library so that we can use them for our queries. We'll need to create the table that we'll use for reading/writing with this demo. This time we'll take advantage of the JSON column support in ATP, so our table will be pretty simple:

Now on to our function. Open func.js and populate it like this:

Here we pull in our required libraries (the FDK for Node and the Oracle DB package) and create a single method that will be invoked when our function is called. Within that function we establish a connection to the DB, query for all of the records and return them as an array of objects. We can deploy and invoke to test it out, but obviously our result will be an empty array at this point, so let's also create a function that will handle inserts. You can copy the entire project above, paste it and then change the function name within func.yaml to create an insert function, but change func.js to handle an insert instead:

Deploy this function and now we can invoke it, passing a JSON object containing the data that we want to persist. The JSON object can be anything, as long as it is valid JSON:

At this point we can invoke our read function and see the results of our previous insert(s):

As an alternative to the above examples, your function might also use Simple Oracle Data Access (SODA) to handle your data in a style more familiar to other NoSQL implementations. SODA does not require a predefined table or schema, rather it works with document collections and a simplified API instead of traditional SQL (though you are not limited to using only the API - you can of course still query your collection using traditional SQL if you need to). An example of a function that uses SODA is shown below:

If we were to invoke the SODA function as follows:

We would end up with two records in a table called soda_collection.  We could query this table using the SODA API in a "query by example" fashion (see the SODA Node docs for more info), but we could also query the table with SQL like so (note that it is necessary to cast the JSON document to varchar2 since it is stored as a BLOB):

Another option is to transform the documents into a traditional table format that can be used in views or joined to relational tables using json_table:

In my next post, we'll look at invoking Oracle Functions with the OCI Java SDK.