First setup of a connection from Node.js to an Oracle Database

In this article I will demonstrate how to make a connection to a remote Oracle database from Node.js running on linux 7. We will be using the node-oracledb module to accomplish this. My colleague Lucas Jellema gave a great explanation about this module in his recent article Running node-oracledb - the Oracle Database Driver for Node.js - in the Pre Built VM for Database Development

As described in that article, the node-oracldb module is depending on the Oracle 11.2 or 12.1 client libraries. So you need to install a full Oracle client, local database or the Oracle Instant Client. I will be using the Oracle Instant Client since it is small and easy to install.

Why Linux 7?

As of Node.js 4 the compiler must support C++11.
This is not included in the default compiler on Linux 6. You can either install another compiler or use Linux 7.

Setup

In this article I will be using the following setup.

Oracle Linux 7 VM on VirtualBox
Node.js 4.4.2 (64-bits)
node-oracledb 1.8
Oracle Instantclient 12.1.0.2.0

OS prerequisites

unzip
libaio
gcc-c++

Use yum to install the OS prerequisites.

yum install unzip libaio gcc-c++

On another VM, I have an Oracle Database 12.1.0.2.0 pluggable database running.

Download the components

node-v4.4.2-linux-x64.tar.xz from nodejs.org
instantclient-basic-linux.x64-12.1.0.2.0.zip from Oracle OTN
instantclient-sdk-linux.x64-12.1.0.2.0.zip from Oracle OTN
node-oracledb will be installed via node package manager, npm

Put the files in the /tmp directory of the VM using any sftp tool you like.


We will remove them when we are done.

Installing components

Logon as root (or use sudo)

Install Node.js

cd /opt
tar -Jxf /tmp/node-v4.4.2-linux-x64.tar.xz

Install Oracle instant client

mkdir /opt/oracle
cd /opt/oracle
unzip -q /tmp/instantclient-basic-linux.x64-12.1.0.2.0.zip
unzip -q /tmp/instantclient-sdk-linux.x64-12.1.0.2.0.zip

Rename the directory so we don't have to tell the installer where to find the OCI libraries, etc...
If you install the Oracle Instant Client in another location, you will have to set two environment variables, OCI_LIB_DIR and OCI_INC_DIR before installing th oracledb module. See INSTALL.md on Github for more details about this.

mv instantclient_12_1 instantclient
cd instantclient
ln -s libclntsh.so.12.1 libclntsh.so

Remove files from /tmp

rm /tmp/instantclient-* node-v4.4.2-linux-x64.tar.xz

Install oracledb module

You can choose to install the module local to the user of global for the system.
If you install it local to the user, you don't need to be a privileged user. You can choose any user you need to run Node.js.
For this demonstration I created a user called nodejs and will install the module local to the user.

Logon as user nodejs

Set environment variables

export PATH=/opt/node-v4.4.2-linux-x64/bin:$PATH
export LD_LIBRARY_PATH=/opt/oracle/instantclient:$LD_LIBRARY_PATH

npm install oracledb

 The module is created in /home/nodejs/node_modules

Test the module

On Github there are several example scripts available for use with the node-oracledb module. See. node-oracledb examples
Download dbconfig.js and select1.js for a test of a db connection.
You can either change the dbconfig.js to match you db connection or set some environment variables.

export=NODE_ORACLEDB_USER=hr
export NODE_ORACLEDB_PASSWORD=hr
export NODE_ORACLEDB_CONNECTIONSTRING=db01.domain.local:1521/fmwdb1.domain.local

Run the select1.js

This will perform a simple query on the departments table from the HR sample schema.

Some remarks

Set the environment variables permanent.

You can set the environment variables permanently for either the specific user or system wide.
Place them in the .bash_profile of the user that will run Node.js or create a .sh file in /etc/profile.d so the environment variables are set at logon for every user.

Install oracledb module global and set additional environment variable.

npm install -g oracledb

Set the environment variable NODE_PATH so Node.js knows where to find the modules.
export NODE_PATH=/opt/node-v4.4.2-linux-x64/lib/node_modules

Sources and references

nodejs.org node-oracledb on Github 
Oracle Instant Client on OTN 
Running node-oracledb - the Oracle Database Driver for Node.js - in the Pre Built VM for Database Development