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.
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.
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:
-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:
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.
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:
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:
$ 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:
$ 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:
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.
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:
-- 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:
$ 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:
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:
$ sqlplus -l cj@cjjson_high
[ . . .]
SQL> show user
USER is "CJ"
SQL> quit
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:
// 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:
$ export MYPW=myverysecretpassword
Now run Node.js and the results will be shown:
$ 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.
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 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
Previous Post