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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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:
1234curl --remote-name https://jsao.io/wrapper-module-demo-0.1.0.zipunzip wrapper-module-demo-0.1.0.zipcd 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:
1node 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… ๐
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! ๐
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
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.
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
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.
Vollmant,
Good catch! I’ve updated the code.
Regards,
Dan
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!
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
Thanks a lot! It worked like a charm!
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
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
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]
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
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/ ).
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/
That’s Stunning danmcghan! Thanks for sharing the idea!.
Thanks Norman! Your comment made me revisit the post. I added a new update as promise support is now built-in… ๐
Great post!! Thank you very much
Thanks for this module.
i have one query can we pass multiple sql query statement, please explain
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.
This is cool. Using wrapper takes of lot headache on the server side interactions.