How to connect to your FREE Autonomous Database using a Polyglot Node.js from Cloud Shell?

Published in

db-one

5 min readOct 21, 2020

In a previous post, I presented a quick way to do this using Python. Now is the time to make JavaScript or TypeScript developers happy as well!

To get started with your Always Free Autonomous Database, please read Todd’s post.

Then, click on the Cloud Shell icon in the upper right corner of your OCI (Oracle Cloud Infrastructure) web console. This opens a Linux shell right inside your browser! The first run might take more than one minute in order to configure it. You’ll have up to 5 GB of storage and numerous tools pre-installed…

Cloud Shell open, Linux inside your browser!

Now the fun part… In order to run a test program that will display the date and time, you’ll need to download and unzip the Autonomous Database wallet.

$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.eu-frankfurt-1.abtheljtcwfhy5ohok66arn6ngrpqdigvq7bzcpygtfnkbldvgtk4rv3xhtq --file wallet.zip --password My_Strong_Pa55word$ unzip wallet.zip

You’ll also need to set up the environment (TNS_ADMIN environment variable) so that the driver can find the connection string stored inside the tnsnames.ora file.

$ pwd$ export TNS_ADMIN=/home/loic_lefev$ sed -i 's/?\/network\/admin/$TNS_ADMIN/' sqlnet.ora

But what about Node.js?

That’s a very good question since by default (and as of today), we get a v10.21.0 version installed.

To install the desired version, you can use the nvm command:

The interesting commands are:

$ nvm ls-remote --lts

The first command lists the available versions including the Long-Term Support.

$ nvm install 15.0.0 --latest-npm

This second command installs a given version (here 15.0.0) and upgrade npm to the latest version as well:

Install latest (as of today) available version of Node.js: 15.0.0 using nvm

Now that we have the wanted version, let’s continue by using npm this time to install the oracledb Node.js driver:

$ npm install oracledb

Install of the oracledb driver release 5.0.0

The following program can be copied and pasted right into a file named select.js:

'use strict';const oracledb = require('oracledb');async function run() {
 let connection;
 
 try {
    connection = await oracledb.getConnection({
     user: process.env.NODE_ORACLEDB_USER || "USERNAME",
     password: process.env.NODE_ORACLEDB_PASSWORD || "PASSWORD",
     connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING || "CONNECTIONSTRING"
   });
      let result = await connection.execute('select sysdate from dual');console.log(result.rows[0]);
 } catch (err) {
   console.error(err);
 } finally {
   if (connection) {
     try {
    await connection.close();
     } catch (err) {
    console.error(err);
     }
   }
 }
}
 
run();

Then to run it, I would need to set the environment variables:

$ export NODE_ORACLEDB_USER=dragon
$ export NODE_ORACLEDB_PASSWORD=My_Strong_Pa55word
$ export NODE_ORACLEDB_CONNECTIONSTRING=dragon_tp

And then run the program:

$ node select.js

Running a node.js program connecting to an Always Free Autonomous Database \o/

Nice, but what is this Polyglot Node.js you mentioned in the title?

Glad you asked :D

In fact, there is another cool way to run Node.js applications: using the wonderful GraalVM that provides polyglot capabilities.

In the next example, we’ll run Java code from this Node.js application, a very basic example.

First, let’s install the GraalVM open source Community Edition on OCI Cloud Shell:

$ wget https://github.com/graalvm/graalvm-ce-builds/releases/download/vm-20.2.0/graalvm-ce-java8-linux-amd64-20.2.0.tar.gz$ tar -xvf graalvm-ce-java8-linux-amd64-20.2.0.tar.gz$ export PATH=/home/loic_lefev/graalvm-ce-java8-20.2.0/bin:$PATH

The code above download the current version of the GraalVM CE and add the bin directory to the path.

Now you’ll see that the Node.js version has changed:

We are indeed not in the latest release but we are effectively using the GraalVM when invoking the node command now.

Next, let’s do some Java coding (save this content in a file named JavaQueryProvider.java):

public class JavaQueryProvider {
    public static String getCurrentDateTime() {
        return "select sysdate from dual";
    }
}

This very basic class has a static method returning the SQL query we’ll use in our Node.js script. The GraalVM will need a Java Archive to run it, so let’s build a sqlqueries.jar:

$ javac JavaQueryProvider.java$ ls *.class$ jar -cvf sqlqueries.jar JavaQueryProvider.class

In the JavaScript program, we’ll need to make a small change (highlighted in bold):

'use strict';const oracledb = require('oracledb');async function run() {
 let connection;
 
 try {
    connection = await oracledb.getConnection({
     user: process.env.NODE_ORACLEDB_USER || "USERNAME",
     password: process.env.NODE_ORACLEDB_PASSWORD || "PASSWORD",
     connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING || "CONNECTIONSTRING"
   });
   let result = await connection.execute( Java.type('JavaQueryProvider').getCurrentDateTime() );
   console.log(result.rows[0]);
 } catch (err) {
   console.error(err);
 } finally {
   if (connection) {
     try {
    await connection.close();
     } catch (err) {
    console.error(err);
     }
   }
 }
}
 
run();

This code will ask the GraalVM to run the Java code we just compiled and then return a String containing the actual SQL code to run.

Will that work?…… Of course! :D

$ node --vm.cp sqlqueries.jar select.js

Neat! So what’s next? How about discovering all the JSON innovations Oracle has brought to the database world?

Autonomous JSON Database (AJD) blog post
Oracle AJD launch video for developers
Details of the native binary JSON format Oracle uses (making MongoDB’s BSON kind of a legacy technology…)

How to connect to your FREE Autonomous Database using a Polyglot Node.js from Cloud Shell?

Written by Loïc Lefèvre