Writing Node.js apps for Oracle Autonomous JSON Database

August 17, 2020 | 7 minute read
Christopher Jones
Senior Principal Product Manager
Text Size 100%:

The launch of the great new Oracle Autonomous JSON Database cloud service was widely covered, for example in the detailed blog post Introducing Oracle Autonomous JSON Database for application developers by my colleague Beda Hammerschmidt. To quote his post: "Today, Oracle announced the availability of Autonomous JSON Database — a new cloud service built for developers who are looking for an easy to use, cost-effective JSON database with simple NoSQL API's. Autonomous JSON Database provides all the core capabilities of MongoDB along with high performance, simple elasticity, full ACID support and complete SQL functionality."

Wow!

Beda shows the few steps needed to create a database. He then gives examples using the web SQL Developer query tool. I want to continue on from this and show how to connect to the JSON database service from your own computer using Oracle's popular Node.js node-oracledb module.

Create an Oracle Autonomous JSON Database

Follow the steps in Beda's blog to create an AJD database service. The AJD service was also made available as part of the Oracle Database Free tier, so if you don't have an Oracle cloud account and want to try it yourself, you can quickly get up and running for no cost.

During creation the 'Allow secure access from everywhere' option is selected. This gives easy access the database from a laptop.

During creation you'll also set a password for the database ADMIN user. This is the privileged account for database access. It will be used later to create a 'normal' (un-privileged) application user.

Download the Oracle Database wallet files

To connect to the database remotely, you need some 'wallet' files on the computer where you will run Node.js. The wallet enforces network security.

To get the wallet, navigate to the database console page and select 'DB Connection':

Then select 'Download Wallet':

This will prompt for a wallet password. (For Node.js, Python, PHP, Go, Ruby and anything else that internally uses Oracle's "Oracle Call Interface" libraries, this wallet password won't actually be used at runtime).

Once the wallet ZIP file has been downloaded, extract the cwallet.sso, sqlnet.ora, and tnsnames.ora files. Unlike some other Oracle wallets, this one doesn't contain database username and password credentials but you should still keep the wallet and files in a secure location. I put the wallet in the directory /Users/cjones/q/Cloud/ATP-S-CJJSON and extracted just the three files needed:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
-rw-r--r--@  1 cjones  staff  20531 15 Aug 10:12 Wallet_CJJSON.zip
  -rw-r--r--@  1 cjones  staff   6725 15 Aug 00:12 cwallet.sso
  -rw-r--r--   1 cjones  staff    134 15 Aug 10:13 sqlnet.ora
  -rw-r--r--@  1 cjones  staff   1801 15 Aug 00:12 tnsnames.ora

The other files in the ZIP would be used, for example, if you were going to connect via JDBC.

Now here's the important step: Edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/cjones/q/Cloud/ATP-S-CJJSON")))
SSL_SERVER_DN_MATCH=yes

Sidebar: if you have Oracle Client libraries 19c or later, you can simplify the steps above and use an 'Easy Connect Plus' connection string. See How to connect to Oracle Autonomous Cloud Databases.

Check you can connect

To check you can connect to the database. I recommend using SQL*Plus. This uses the Oracle Call Interface libraries just like the Node.js node-oracledb module does. So if SQL*Plus can connect, then you know that your Node.js apps will also. SQL Developer commonly uses a JDBC stack and configuration is different. If you don't already have it, you can get SQL*Plus from the free Oracle Instant Client ZIP packages.

View the tnsnames.ora file. It will contain some network service names (to the left of the "=" signs) that you will use in your applications, for example:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
cjjson_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=xxxxxxxxxxxxxxx_cjjson_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

There are several network service name entries in the file. The differences are described here.

Setting the TNS_ADMIN environment variable is standard for Oracle tools. It lets them locate the tnsnames.ora and sqlnet.ora files:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ export TNS_ADMIN=/Users/cjones/q/Cloud/ATP-S-CJJSON

Now you can run SQL*Plus as the ADMIN user and enter the ADMIN user password that you set when the database was originally created:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ sqlplus -l admin@cjjson_high

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 15 10:22:55 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL>

Success!

If you are behind a firewall, you can copy one of the tnsnames.ora entries, give it a new alias, and add https_proxy and https_proxy_port settings. For example:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
cjjson_high_proxy = (description= (retry_count=20)(retry_delay=3)(address=(https_proxy=your-proxy.example.com)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=xxxxxxxxxxxxxxx_cjjson_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

Then cjjson_high_proxy would be used where I show cjjson_high. Note this is not recommended for production use due to the overheads.

Create an application user

First I want to create a 'normal' database user for general application access. I have a hack script createuser.sql that I tend to use. You can customize it for your needs:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
-- createuser.sql
--
-- Creates a DB user for quick testing

define USERNAME = &1

-- Uncomment if you want to clean up a previous user
-- begin execute immediate 'drop user &USERNAME cascade'; exception when others then if sqlcode <> -1918 then raise; end if; end;
-- /

create user &USERNAME;

alter user &USERNAME
      default tablespace data
      temporary tablespace temp
      account unlock
      quota unlimited on data;

grant  connect
     , resource
     to &USERNAME;

grant  create session
     , create table
     , create procedure
     , create sequence
     , create trigger
     , create view
     , create synonym
     , alter  session
     , create type
     , soda_app
     to &USERNAME;

password &USERNAME

Start SQL*Plus as the ADMIN user:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ sqlplus -l admin@cjjson_high

Then run the createuser.sql script. Pass the name of your desired user as the script argument. I used cj. The script will prompt for the password to set for this new user:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> @createuser.sql cj
old   1: create user &USERNAME
new   1: create user cj

User created.

old   1: alter user &USERNAME
new   1: alter user cj

User altered.

old   3:      to &USERNAME
new   3:      to cj

Grant succeeded.

old  11:      to &USERNAME
new  11:      to cj

Grant succeeded.

Changing password for cj
New password:
Retype new password:
Password changed

SQL> quit

Now you can check that connection succeeds:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ sqlplus -l cj@cjjson_high

[ . . .]

SQL> show user
USER is "CJ"

SQL> quit

Run a Node.js application

First, install Node.js and node-oracledb. The instructions are here.

Create a test file, such as test.js. This uses Oracle's great SODA NoSQL-style API that lets you access Oracle Autonomous JSON Database. SODA stands for 'Simple Oracle Document Access'. It gives you a simple document-store interface to store JSON documents in collections:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
// test.js

const oracledb = require('oracledb');

if (process.platform === 'darwin') {
  oracledb.initOracleClient({
    libDir: '/Users/cjones/instantclient_19_3',  // On Windows use double backslashes for each dir separator: 'c:\\instantclient_19_8'
    configDir: '/Users/cjones/q/Cloud/ATP-S-CJJSON'
  });
}

// The general recommendation for simple SODA usage is to enable autocommit
oracledb.autoCommit = true;

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(
      {
        user: 'cj',
        password: process.env.MYPW,
        connectString: 'cjjson_high'
      });

    // Create the parent object for SODA
    const soda = connection.getSodaDatabase();

    // Create a new SODA collection
    // This will open an existing collection, if the name is already in use.
    collection = await soda.createCollection("mycollection");

    // Insert a document.  A system generated key is created by default.
    content = {name: "Matilda", address: {city: "Melbourne"}};
    doc = await collection.insertOneAndGet(content);
    const key = doc.key;
    console.log("The key of the new SODA document is: ", key);

    // Fetch the document back
    doc = await collection.find().fetchArraySize(0).key(key).getOne(); // A SodaDocument
    content = doc.getContent();                                        // A JavaScript object
    console.log(content);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();

You can see I used the node-oracledb 5.0 oracledb.initOracleClient() function to set the Oracle Client library directory, and to give the directory containing the wallet network configuration files. Technically the latter isn't necessary because TNS_ADMIN was already set, but it is handy to use in general apps. The initOracleClient() function is most useful for Windows and macOS. On Linux you will still need to set the system library search path to the Oracle Client library directory before you run Node.js. For more information on initOracleClient(), see my blog post here and the documentation here.

SODA has extensive functionality. The test.js script simply creates a collection (collections can hold zero or more documents), inserts one document, and then retrieves that document. When using Oracle Client libraries 19.5 or later, you can increase the fetchArraySize() value to get better performance when selecting multiple records.

The script accesses the database password from an environment variable, so set this to the password you entered when you created the application user:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ export MYPW=myverysecretpassword

Now run Node.js and the results will be shown:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ node test.js
The key of the new SODA document is:  7DA3104D6CF84FD9BF59832FA070BC99
{ name: 'Matilda', address: { city: 'Melbourne' } }

Another example is in examples/soda1.js; this shows SODA functionality such as how to search JSON documents using query-by-example (QBE) methods.

Summary

The new Oracle Autonomous JSON Database service makes a great database platform for developers who want to take advantage of document storage. Simple Oracle Document Access (SODA) is a powerful NoSQL-style API available in Node.js, Python, Java, PL/SQL, C and via REST. The combination gives developers great access to modern technology.

Documentation on AJD is here. Documentation on using SODA in Node.js is here. (For Python users who have read this far, SODA documentation is here).

If you want to learn more about AJD and Oracle Database, join one of Beda's excellent JSON in Oracle Database Office Hours sessions.

Christopher Jones

Senior Principal Product Manager

Christopher is a Senior Principal Product Manager working with the Oracle Database Data Access team primarily on APIs for scripting languages including Node.js, Python, PHP and the newly launched ODPI-C library for Oracle Database driver writers.  He has responsibilities for Oracle Call Interface (OCI) and related Oracle Database APIs; for Oracle Database clients such as Oracle Instant Client; and for general database adoption by developers, including championing Oracle Database XE.  He remains a lead maintainer of PHP's OCI8 extension for Oracle Database.  He co-released the popular book "The Underground PHP and Oracle Manual" and is the author of a number of technical articles.  Christopher has presented at big and small conferences around the world including the O'Reilly Open Source Convention and Oracle OpenWorld.  

Twitter: @ghrd

Email: christopher.jones@oracle.com

Blog: http://blogs.oracle.com/opal/

Show more

Previous Post

Avoiding the DPI-1047 error with Node.js node-oracledb 5 on macOS and Windows

Christopher Jones | 3 min read

Next Post


Installing XAMPP on macOS for PHP and Oracle Database

Christopher Jones | 3 min read