Using Oracle database with Node.js

I'm currently working on a university project which requires the use of an Oracle database. As we need to make a webapp, I chose to do it with node.js. I didn't find much information about how to do it (and which node module I should use) so I spent a little time experimenting. Here is what I found works best.

After some tries, seems to me that "oracle" is the node module which fits best with my needs. It is low level but you can easily connect, execute SQL queries and get the results.

I use OSX as a development plateform and ubuntu for production, but the method is almost the same for both.

First, you need to get Oracle Instant Client "Basic" and "SDK" (OSX download - Linux Download), simply uncompress both in the same folder, I chose to do it in /opt/instantclient.

Then, you need to define some environment variables to tell the compiler where instant client is :

export OCI_LIB_DIR=/opt/instantclient
export OCI_INCLUDE_DIR=/opt/instantclient/sdk/include/
export LD_LIBRARY_PATH=/opt/instantclient

On OSX, you also need to specify the folder where the dyn libs are located :

export DYLD_LIBRARY_PATH=/opt/instantclient

Then you're ready to simply install the Oracle module in your node project :

npm install oracle --save

(The --save option allows to automatically add the module to the packages.json config file)

Then, using the module is really simple, here is a small example from the documentation :

var oracle = require('oracle');

var connectData = {
    hostname: "localhost",
    port: 1521,
    database: "xe", // System ID (SID)
    user: "oracle",
    password: "oracle"
}

oracle.connect(connectData, function(err, connection) {
    if (err) { console.log("Error connecting to db:", err); return; }

    connection.execute("SELECT systimestamp FROM dual", [], function(err, results) {
        if (err) { console.log("Error executing query:", err); return; }

        console.log(results);
        connection.close();
    });
});