Publish data over REST with Node.js

Of course the best way to expose database data over REST is with Oracle REST Data Services.  If you haven't read over the Statement of Direction, it's worth the couple minutes it takes.  The auto table enablement and filtering is quite nice.

For anyone interested in node.js and oracle, this is a very quick example of publishing the emp table over REST for use by anyone that would prefer REST over sql*net.  For example from javascript,php,perl,...



The current best driver for node.js to access oracle is node-oracle which is found here: https://github.com/joeferner/node-oracle


Here's a short set of steps to get going.

1. Install node, http://nodejs.org/download/

2. Setup the environment's oracle variables

export PATH=$PATH:.  
export ORACLE_HOME=/path/to/my/oracle/product/11.2.0/dbhome_1
export OCI_LIB_DIR=$ORACLE_HOME/lib
export OCI_INCLUDE_DIR=$ORACLE_HOME/rdbms/public
export OCI_VERSION=11 #Integer. Optional, defaults to '11'
export NLS_LANG=.UTF8 # Optional, but required to support international characters
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

3. run npm install oracle

That's all there is to getting then env setup for oracle.  The other node package I use in this example is express.

4. To install that just type:  npm install express To learn more about express, it's home is here http://expressjs.com/

var express = require('express');
var app = express();
// This make a uri end point for the REST call
// http://myhost/emp will return the data
app.get('/emp', function(req, res){
  empList(req,res)
  }
);

var oracle = require('oracle');
var conn;

var connectData = {
    hostname: "127.0.0.1",
    port: 1521,
    database: "XE", 
    user: "klrice",
    password: "fortim"
}

// connect
//var conn="";
oracle.connect(connectData, function(err, connection) {
    if (err) { console.log("Error connecting to db:", err); return; }
    console.log('connected');
    conn =connection;
});

// function registered in the /emp declaration
function empList(req,res){
     var empList = "select * from emp ";
    conn.execute(empList, [], function(err, results) {
            if (err) { console.log("Error executing query:", err); return; }

            // use the JSON function to format the results and print
            res.write(JSON.stringify(results));
            res.end();
    });
}

// listen on port 8000
app.listen(8000);

The result is that the node script is listening on port 8000 for /emp and will return this JSON.