The Personal Blog of Todd Sharp

Oracle Functions - Connecting To ATP With Node.JS

Posted By: Todd Sharp on 8/2/2019 8:00 GMT

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:

Note: You should always encrypt any configuration variables that contain sensitive information. Check my guide to using Key Management in OCI to learn how!

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.

Photo by Paul Esch-Laurent on Unsplash

Related Posts

Querying Autonomous Database from an Oracle Function (The Quick, Easy & Completely Secure Way)

Querying Autonomous Database from an Oracle Function (The Quick, Easy & Completely Secure Way)

I've written many blog posts about connecting to an Autonomous DB instance in the past. Best practices evolve as tools, services, and frameworks become...

Sending Email With OCI Email Delivery From Micronaut

Sending Email With OCI Email Delivery From Micronaut

Email delivery is a critical function of most web applications in the world today. I've managed an email server in the past - and trust me - it's not fun...

Brain to the Cloud - Part III - Examining the Relationship Between Brain Activity and Video Game Performance

Brain to the Cloud - Part III - Examining the Relationship Between Brain Activity and Video Game Performance

In my last post, we looked at the technical aspects of my Brain to the Cloud project including much of the code that was used to collect and analyze the...

Note: Comments are currently closed on this blog. Disqus is simply too bloated to justify its use with the low volume of comments on this blog. Please visit my contact page if you have something to say!