Making a Wrapper Module for the Node.js Driver for Oracle Database

The Node.js driver for Oracle Database, node-oracledb, is pretty low level – as a good driver should be! But that doesn’t mean you have to work at that level all the time. Perhaps you’d like to…

use promises rather than callbacks
have a simplified execute method that doesn’t require getting and releasing connections
ensure that certain buildup and teardown procedures are used to keep connections clean and consistent

All of these are great reasons to create a wrapper module for the driver that gives you what you want without having to wait to see if these features are added to the driver itself (which they may never be). I wrote a sample application that uses such a wrapper module that implements all of the functionality listed above.

Update: As of version 1.7 (released 2016/02/29), the driver has a JavaScript layer built-in. Please consider submitting a pull request for features that you feel would benefit the community.

Update: As of version 1.9 (released 2016/04/19), the driver supports Promises with all asynchronous methods.

Update: As of version 1.11 (released 2016/08/19), the driver includes a built-in connection pool cache feature. A separate wrapper for pool storage and retrieval is no longer needed.

Creating the module

The wrapper module, database.js, uses the es6-promise module to expose all of the major methods of the driver classes via promises. The getConnection and releaseConnection methods ensure that any buildup and teardown scripts are executed. Finally, I highlighted the simpleExecute function as it does the most work, exposing a promise based method that handles getting and releasing a connection as well as running buildup and teardown scripts.

var oracledb = require('oracledb');
var Promise = require('es6-promise').Promise;
var async = require('async');
var pool;
var buildupScripts = [];
var teardownScripts = [];

module.exports.OBJECT = oracledb.OBJECT;

function createPool(config) {
    return new Promise(function(resolve, reject) {
        oracledb.createPool(
            config,
            function(err, p) {
                if (err) {
                    return reject(err);
                }

                pool = p;

                resolve(pool);
            }
        );
    });
}

module.exports.createPool = createPool;

function terminatePool() {
    return new Promise(function(resolve, reject) {
        if (pool) {
            pool.terminate(function(err) {
                if (err) {
                    return reject(err);
                }

                resolve();
            });
        } else {
            resolve();
        }
    });
}

module.exports.terminatePool = terminatePool;

function getPool() {
    return pool;
}

module.exports.getPool = getPool;

function addBuildupSql(statement) {
    var stmt = {
        sql: statement.sql,
        binds: statement.binds || {},
        options: statement.options || {}
    };

    buildupScripts.push(stmt);
}

module.exports.addBuildupSql = addBuildupSql;

function addTeardownSql(statement) {
    var stmt = {
        sql: statement.sql,
        binds: statement.binds || {},
        options: statement.options || {}
    };

    teardownScripts.push(stmt);
}

module.exports.addTeardownSql = addTeardownSql;

function getConnection() {
    return new Promise(function(resolve, reject) {
        pool.getConnection(function(err, connection) {
            if (err) {
                return reject(err);
            }

            async.eachSeries(
                buildupScripts,
                function(statement, callback) {
                    connection.execute(statement.sql, statement.binds, statement.options, function(err) {
                        callback(err);
                    });
                },
                function (err) {
                    if (err) {
                        return reject(err);
                    }

                    resolve(connection);
                }
            );
        });
    });
}

module.exports.getConnection = getConnection;

function execute(sql, bindParams, options, connection) {
    return new Promise(function(resolve, reject) {
        connection.execute(sql, bindParams, options, function(err, results) {
            if (err) {
                return reject(err);
            }

            resolve(results);
        });
    });
}

module.exports.execute = execute;

function releaseConnection(connection) {
    async.eachSeries(
        teardownScripts,
        function(statement, callback) {
            connection.execute(statement.sql, statement.binds, statement.options, function(err) {
                callback(err);
            });
        },
        function (err) {
            if (err) {
                console.error(err); //don't return as we still need to release the connection
            }

            connection.release(function(err) {
                if (err) {
                    console.error(err);
                }
            });
        }
    );
}

module.exports.releaseConnection = releaseConnection;

function simpleExecute(sql, bindParams, options) {
    options.isAutoCommit = true;

    return new Promise(function(resolve, reject) {
        getConnection()
            .then(function(connection){
                execute(sql, bindParams, options, connection)
                    .then(function(results) {
                        resolve(results);

                        process.nextTick(function() {
                            releaseConnection(connection);
                        });
                    })
                    .catch(function(err) {
                        reject(err);

                        process.nextTick(function() {
                            releaseConnection(connection);
                        });
                    });
            })
            .catch(function(err) {
                reject(err);
            });
    });
}

module.exports.simpleExecute = simpleExecute;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

var oracledb = require('oracledb');

var Promise = require('es6-promise').Promise;

var async = require('async');

var pool;

var buildupScripts = [];

var teardownScripts = [];

module.exports.OBJECT = oracledb.OBJECT;

function createPool(config) {

return new Promise(function(resolve, reject) {

oracledb.createPool(

config,

function(err, p) {

if (err) {

return reject(err);

}

pool = p;

resolve(pool);

}

);

});

}

module.exports.createPool = createPool;

function terminatePool() {

return new Promise(function(resolve, reject) {

if (pool) {

pool.terminate(function(err) {

if (err) {

return reject(err);

}

resolve();

});

} else {

resolve();

}

});

}

module.exports.terminatePool = terminatePool;

function getPool() {

return pool;

}

module.exports.getPool = getPool;

function addBuildupSql(statement) {

var stmt = {

sql: statement.sql,

binds: statement.binds || {},

options: statement.options || {}

};

buildupScripts.push(stmt);

}

module.exports.addBuildupSql = addBuildupSql;

function addTeardownSql(statement) {

var stmt = {

sql: statement.sql,

binds: statement.binds || {},

options: statement.options || {}

};

teardownScripts.push(stmt);

}

module.exports.addTeardownSql = addTeardownSql;

function getConnection() {

return new Promise(function(resolve, reject) {

pool.getConnection(function(err, connection) {

if (err) {

return reject(err);

}

async.eachSeries(

buildupScripts,

function(statement, callback) {

connection.execute(statement.sql, statement.binds, statement.options, function(err) {

callback(err);

});

function (err) {

if (err) {

return reject(err);

}

resolve(connection);

}

);

});

}

module.exports.getConnection = getConnection;

function execute(sql, bindParams, options, connection) {

return new Promise(function(resolve, reject) {

connection.execute(sql, bindParams, options, function(err, results) {

if (err) {

return reject(err);

}

resolve(results);

});

}

module.exports.execute = execute;

function releaseConnection(connection) {

async.eachSeries(

teardownScripts,

function(statement, callback) {

connection.execute(statement.sql, statement.binds, statement.options, function(err) {

callback(err);

});

function (err) {

if (err) {

console.error(err); //don't return as we still need to release the connection

}

connection.release(function(err) {

if (err) {

console.error(err);

}

});

}

);

}

module.exports.releaseConnection = releaseConnection;

function simpleExecute(sql, bindParams, options) {

options.isAutoCommit = true;

return new Promise(function(resolve, reject) {

getConnection()

.then(function(connection){

execute(sql, bindParams, options, connection)

.then(function(results) {

resolve(results);

process.nextTick(function() {

releaseConnection(connection);

});

})

.catch(function(err) {

reject(err);

process.nextTick(function() {

releaseConnection(connection);

});

})

.catch(function(err) {

reject(err);

});

}

module.exports.simpleExecute = simpleExecute;

Using the module

Here, in the main server.js, we see an example of adding buildup and teardown scripts which set the date format and clear package state respectively. These scripts will be executed on connections when using the getConnection, releaseConnection, and simpleExecute methods of the wrapper module. Also, you can see the promise based API being used to create and terminate the connection pool to the database.

var http = require('http');
var express = require('express');
var morgan = require('morgan');
var dbconfig = require('./server/dbconfig.js');
var database = require('./server/database.js');
var api = require('./server/api.js');
var openHttpConnections = {};
var app;
var httpServer;

process.on('uncaughtException', function(err) {
    console.error('Uncaught exception ', err);

    shutdown();
});

process.on('SIGTERM', function () {
    console.log('Received SIGTERM');

    shutdown();
});

process.on('SIGINT', function () {
    console.log('Received SIGINT');

    shutdown();
});

initApp();

function initApp() {
    app = express();
    httpServer = http.Server(app);

    app.use(morgan('combined')); //logger

    app.use('/api', api.getRouter());

    app.use(handleError);

    httpServer.on('connection', function(conn) {
        var key = conn.remoteAddress + ':' + (conn.remotePort || '');

        openHttpConnections[key] = conn;

        conn.on('close', function() {
            delete openHttpConnections[key];
        });
    });

    database.addBuildupSql({
        sql: "BEGIN EXECUTE IMMEDIATE q'[alter session set NLS_DATE_FORMAT='DD-MM-YYYY']'; END;"
    });

    database.addTeardownSql({
        sql: "BEGIN sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize); END;"
    });

    database.createPool(dbconfig)
        .then(function() {
            httpServer.listen(3000, function() {
                console.log('Webserver listening on localhost:3000');
            });
        })
        .catch(function(err) {
            console.error('Error occurred creating database connection pool', err);
            console.log('Exiting process');
            process.exit(0);
        });
}

function handleError(err, req, res, next) {
    console.error(err);
    res.status(500).send({error: 'An error has occurred, please contact support if the error persists'});
    shutdown();//process would usually be restarted via something like https://github.com/foreverjs/forever
}

function shutdown() {
    console.log('Shutting down');
    console.log('Closing web server');

    httpServer.close(function () {
        console.log('Web server closed');

        database.terminatePool()
            .then(function() {
                console.log('node-oracledb connection pool terminated');
                console.log('Exiting process');
                process.exit(0);
            })
            .catch(function(err) {
                console.error('Error occurred while terminating node-oracledb connection pool', err);
                console.log('Exiting process');
                process.exit(0);
            });
    });

    for (key in openHttpConnections) {
        openHttpConnections[key].destroy();
    }
}

100

101

var http = require('http');

var express = require('express');

var morgan = require('morgan');

var dbconfig = require('./server/dbconfig.js');

var database = require('./server/database.js');

var api = require('./server/api.js');

var openHttpConnections = {};

var app;

var httpServer;

process.on('uncaughtException', function(err) {

console.error('Uncaught exception ', err);

shutdown();

});

process.on('SIGTERM', function () {

console.log('Received SIGTERM');

shutdown();

});

process.on('SIGINT', function () {

console.log('Received SIGINT');

shutdown();

});

initApp();

function initApp() {

app = express();

httpServer = http.Server(app);

app.use(morgan('combined')); //logger

app.use('/api', api.getRouter());

app.use(handleError);

httpServer.on('connection', function(conn) {

var key = conn.remoteAddress + ':' + (conn.remotePort || '');

openHttpConnections[key] = conn;

conn.on('close', function() {

delete openHttpConnections[key];

});

database.addBuildupSql({

sql: "BEGIN EXECUTE IMMEDIATE q'[alter session set NLS_DATE_FORMAT='DD-MM-YYYY']'; END;"

});

database.addTeardownSql({

sql: "BEGIN sys.dbms_session.modify_package_state(sys.dbms_session.reinitialize); END;"

});

database.createPool(dbconfig)

.then(function() {

httpServer.listen(3000, function() {

console.log('Webserver listening on localhost:3000');

});

})

.catch(function(err) {

console.error('Error occurred creating database connection pool', err);

console.log('Exiting process');

process.exit(0);

});

}

function handleError(err, req, res, next) {

console.error(err);

res.status(500).send({error: 'An error has occurred, please contact support if the error persists'});

shutdown();//process would usually be restarted via something like https://github.com/foreverjs/forever

}

function shutdown() {

console.log('Shutting down');

console.log('Closing web server');

httpServer.close(function () {

console.log('Web server closed');

database.terminatePool()

.then(function() {

console.log('node-oracledb connection pool terminated');

console.log('Exiting process');

process.exit(0);

})

.catch(function(err) {

console.error('Error occurred while terminating node-oracledb connection pool', err);

console.log('Exiting process');

process.exit(0);

});

for (key in openHttpConnections) {

openHttpConnections[key].destroy();

}

Using simpleExecute

Perhaps the best part of the module is the simpleExecute method, which abstracts away some of the underlying complexity of getting and releasing connections from the connection pool. Here are some examples that use simpleExecute to fire off some queries on Oracle Database. Note that the hire_date date format will match the format specified in the buildup scripts.

var express = require('express');
var database = require('./database.js');

function getRouter() {
    var router = express.Router();

    router.route('/emps')
        .get(getEmps);

    router.route('/depts')
        .get(getDepts);

    return router;
}

module.exports.getRouter = getRouter;

function getEmps(req, res, next) {
    database.simpleExecute(
        'SELECT employee_id, ' +
        '    first_name, ' +
        '    last_name, ' +
        '    email, ' +
        '    phone_number, ' +
        '    TO_CHAR(hire_date) AS hire_date, ' +
        '    job_id, ' +
        '    salary, ' +
        '    commission_pct, ' +
        '    manager_id, ' +
        '    department_id ' +
        'FROM employees',
        {}, //no binds
        {
            outFormat: database.OBJECT
        }
    )
        .then(function(results) {
            res.send(results);
        })
        .catch(function(err) {
            next(err);
        });
}

function getDepts(req, res, next) {
    database.simpleExecute(
        'SELECT department_id, ' +
        '    department_name, ' +
        '    manager_id, ' +
        '    location_id ' +
        'FROM departments',
        {}, //no binds
        {
            outFormat: database.OBJECT
        }
    )
        .then(function(results) {
            res.send(results);
        })
        .catch(function(err) {
            next(err);
        });
}

var express = require('express');

var database = require('./database.js');

function getRouter() {

var router = express.Router();

router.route('/emps')

.get(getEmps);

router.route('/depts')

.get(getDepts);

return router;

}

module.exports.getRouter = getRouter;

function getEmps(req, res, next) {

database.simpleExecute(

'SELECT employee_id, ' +

' first_name, ' +

' last_name, ' +

' email, ' +

' phone_number, ' +

' TO_CHAR(hire_date) AS hire_date, ' +

' job_id, ' +

' salary, ' +

' commission_pct, ' +

' manager_id, ' +

' department_id ' +

'FROM employees',

{}, //no binds

{

outFormat: database.OBJECT

}

)

.then(function(results) {

res.send(results);

})

.catch(function(err) {

next(err);

});

}

function getDepts(req, res, next) {

database.simpleExecute(

'SELECT department_id, ' +

' department_name, ' +

' manager_id, ' +

' location_id ' +

'FROM departments',

{}, //no binds

{

outFormat: database.OBJECT

}

)

.then(function(results) {

res.send(results);

})

.catch(function(err) {

next(err);

});

}

Installing the demo app

Follow these steps if you’d like to get this sample application up and running in your environment.

Prerequisites

Oracle Database 11g or 12c
Node.js
Oracle Database Node.js Driver installed globally
Access to the HR schema

Checkout Up and Running with Node.js and Oracle if you are interested in getting a VM setup with these requirements.

Application Installation

In the server with Node.js installed, open a terminal, navigate to an appropriate directory to start a new project, and enter the following:

Shell

curl --remote-name https://jsao.io/wrapper-module-demo-0.1.0.zip unzip wrapper-module-demo-0.1.0.zip cd wrapper-module-demo-0.1.0/ npm install

1
2
3
4

curl --remote-name https://jsao.io/wrapper-module-demo-0.1.0.zip
unzip wrapper-module-demo-0.1.0.zip
cd wrapper-module-demo-0.1.0/
npm install
Use your favorite text editor to modify server/dbconfig.js so that it has the correct information to connect to the HR schema in your database.
Return to the terminal in the wrapper-module-demo-0.1.0 directory and use Node.js to start the server:

Shell

node server.js

1

node server.js
From there you should be able to point a browser or curl to http://localhost:3000/api/emps and http://localhost:3000/api/depts to see the data returned from simpleExecute

Final thoughts

Hopefully you’ll agree that it’s not terribly hard to create a wrapper module for the Oracle Database driver for Node.js which can simplify interacting with the database. While writing this wrapper module I started thinking about some interesting ways that it could be extended to do some even cooler stuff! Just need to find some time to experiment… 🙂

21 Comments

Matt says:

April 8, 2015 at 1:18 pm

This is great, thank you. I’ve ended up having to do something very similar, except I used the Bluebird promise library. I’ve toyed with the idea of putting out a module to do this stuff. Would be nice! 🙂

- danmcghan says:
  
  April 8, 2015 at 1:24 pm
  
  Hi Matt,
  
  I’m glad you found it useful. I ended up using the es6 library so that I could later just drop it when Node.js has built in support… Martin dropped this library in OraOpenSource here: https://github.com/OraOpenSource/orawrap. I’ll be curious to see how that develops…
  
  Regards,
  Dan
  
julian says:

April 8, 2015 at 11:39 pm

Nice job! I, too, have spent a number of hours developing a wrapper just so that I can better organize my JS and SQL code. I appreciate that Oracle released their own driver but it does appear that they’re just going to focus on the native library and not a full-fledged ORM. It sounds like Oracle is talking about partnering with groups to develop a comprehensive wrapper, but I wonder exactly who and what they have in mind!

IMO Oracle realized how popular and powerful NodeJS has become over the last two years, and they wanted to develop an official driver before an open source version really took off.

- danmcghan says:
  
  April 10, 2015 at 3:14 pm
  
  Hi Julian,
  
  Thanks, I’m glad you liked it!
  
  I think it makes sense for Oracle to focus on the low level driver as this really serves as the foundation for any higher level tools. Have you seen Sails.js or Knex? Hopefully these will be updated to run on the new driver as they look like great options!
  
  I don’t think Oracle wanted to develop an official driver before an open source version “really took off”. Actually, the Oracle driver IS open source and there have been several community contributions already: https://github.com/oracle/node-oracledb
  
  I believe people within Oracle took note of the popularity of Node.js and wanted to ensure that Oracle customers had a driver option that fully leveraged the many features Oracle Database has to offer. The existing options were written in C++ rather than in C and so they started from scratch to create something that could deliver the highest level of performance possible. There are other examples where Oracle has simply made contributions to existing open source drivers.
  
  In a way, it’s kind of like AngularJS which is open source but was created by Google and has Google’s backing. I think that backing is a big reason AngularJS has been so successful. The open source community is amazing, but at times a bit unpredictable. I think larger enterprises that value stability can appreciate an open source project that has strong backing from one or more organizations.
  
  Regards,
  Dan
  
vollmant says:

May 5, 2015 at 2:10 am

Very nice job on the wrapper. It definitely eased the transition for my first attempt communicating to anything but no sql databases from node. , Just noticed one little bug, In the simpleExecute function, there shouldn’t be a return in front of the reject(err), otherwise the connection never gets released.

danmcghan says:

May 5, 2015 at 11:08 am

Vollmant,

Good catch! I’ve updated the code.

Regards,
Dan

Juan Cancela says:

May 7, 2015 at 3:11 pm

Hi,
Thanks a lot for this work, and congrats!. Anyway, seems that executing queries without returning result (in example, creating an insert instead of a select) seems not to be working. Any ideas why non select operations wont get executed?

Thanks again!

- danmcghan says:
  
  May 7, 2015 at 3:53 pm
  
  Hi Juan,
  
  Thanks for letting me know! I’ve updated the simpleExecute method to set the isAutoCommit property to true. That should fix it for you!
  
  Regards,
  Dan
  
  - Juan Cancela says:
    
    May 7, 2015 at 3:56 pm
    
    Thanks a lot! It worked like a charm!
    
Trung says:

October 14, 2015 at 4:19 am

It’s really greate article and very useful. I have checked the source code in Github https://github.com/OraOpenSource/orawrap. I realized that, the library is initialized (lib/index.js) as singleton.

var Orawrap = require(__dirname + ‘/orawrap.js’);
module.exports = new Orawrap();

So is there any problems for this? I mean some public variables might be changed accidentally with different caller.

Thanks

- danmcghan says:
  
  October 14, 2015 at 1:03 pm
  
  Hi Trung,
  
  Thanks for your comments!
  
  This is an intentional design decision. The idea is that you will have only 1 instance of Orawrap per Node.js application/process. The reason has to do with pool storage and retrieval. Generally, you want to create your connection pools prior to opening up your web server. Then in other files, you’ll include Orawrap and access the pools that were already created.
  
  Regards,
  Dan
  
Vivek says:

February 23, 2016 at 5:29 am

Hi danmcghan,
I have used your database,js file but removed connection pooling as it is not required for my thing. My question is i have modified simpleExecute which releases connection when the resultSet=false , otherwsie it uses that results and uses resultSet methods to retrieve. I am getting issue when i call two different simpleExecute method such that two connection objects are created some times when closing the connections i’m getting the issues as [Error: NJS-003: invalid connection]

- danmcghan says:
  
  March 12, 2016 at 9:16 am
  
  Hi Vivek,
  
  It sounds like you may be “crossing the wires”. Try to ensure the connections are referenced as parameters or using closure so that different users/operations don’t work with the others connection.
  
  Also, keep an eye on the developments of the core driver. It may get to the point that the wrappers are no longer needed…
  https://jsao.io/2016/03/extending-the-javascript-layer/
  
  Regards,
  Dan
  
Valentino says:

April 15, 2016 at 7:11 am

Hey Dan ,
What is the reason for using es6-promises package?
As far as I know promises are shipped with nodejs by default ( ref. https://nodejs.org/en/docs/es6/ ).

- danmcghan says:
  
  April 15, 2016 at 9:34 am
  
  Hi Valentino,
  
  The driver provides support for Node.js going back to version 0.10, before promises were added.
  
  But I wouldn’t worry so much about adding promises to a wrapper module any more, they will soon exist in core:
  https://jsao.io/2016/03/extending-the-javascript-layer/
  
Norman says:

August 8, 2016 at 5:08 pm

That’s Stunning danmcghan! Thanks for sharing the idea!.

- danmcghan says:
  
  August 8, 2016 at 5:15 pm
  
  Thanks Norman! Your comment made me revisit the post. I added a new update as promise support is now built-in… 🙂
  
Lucas Marçoni says:

November 14, 2017 at 10:11 am

Great post!! Thank you very much

vipin says:

January 16, 2018 at 4:29 am

Thanks for this module.
i have one query can we pass multiple sql query statement, please explain

- danmcghan says:
  
  January 18, 2018 at 2:56 pm
  
  Hi Vipin,
  
  Have a look at this post:
  https://jsao.io/2015/04/to-javascript-or-not-to-javascript-that-is-the-question/
  
  Basically, the best thing to do is to ship the statements over to the database for a single round trip. Even better, compile it in the database and then call it as needed from anywhere.
  
Saran says:

April 2, 2018 at 11:05 am

This is cool. Using wrapper takes of lot headache on the server side interactions.

Making a Wrapper Module for the Node.js Driver for Oracle Database

Creating the module

Using the module

Using simpleExecute

Installing the demo app

Final thoughts

21 Comments

Leave a Reply Cancel Reply

@dmcghan on Twitter

Important Links

Archives

FRESH FROM THE BLOG

RECENT COMMENTS

ARCHIVES