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

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

By March 20, 2015 Uncategorized

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.

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.

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.

Installing the demo app

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

Prerequisites

  1. Oracle Database 11g or 12c
  2. Node.js
  3. Oracle Database Node.js Driver installed globally
  4. 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:
  • 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:
  • 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… đŸ™‚

18 Comments

  • Matt says:

    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! đŸ™‚

  • julian says:

    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:

      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:

    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:

    Vollmant,

    Good catch! I’ve updated the code.

    Regards,
    Dan

  • Juan Cancela says:

    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:

      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

  • Trung says:

    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:

      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:

    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:

      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:

    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/ ).

  • Norman says:

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

    • danmcghan says:

      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:

    Great post!! Thank you very much

Leave a Reply to Juan Cancela Cancel Reply