Node js database connection in a separate module

Question

I made node.js app that includes some REST services. Those services connect to a database (for example Oracle or DB2) to execute some query.

Since I'm a beginner in node.js programming, I have a question about my case: What's the right way to access to a database? Is it better to have one connection reference while the app is running and use the same connection instance when REST services are called?

I found some examples that includes database connection in a separate module and use that module in app, something like that:

db2.js:

var db2 = require('ibm_db');

var db2ConnSettings = "DRIVER={DB2};DATABASE=mydb;HOSTNAME=localhost;UID=db2test;PWD=db2test;PORT=50000;PROTOCOL=TCPIP";

var db2Conn = db2.open(db2ConnSettings, function(err, conn) {
        if (err)
            return console.log(err);
    });

module.exports = db2Conn;

server.js:

var express = require('express');
var app = express();
var db2Connection = require('./db2.js');

app.get('/data', function(req, res) {
    console.log(db2Connection );
    // make some query
});

When this service is called, db2connection is undefined. How come? How should I retrieve a db2 connection from db2.js file?

the problem is, that your database connection is set up asynchronously. You get the actual connection as the conn parameter in the callback and not as synchronous return value of .open(). — Sirko
– Sirko, Commented Sep 2, 2016 at 8:03
I'm not familiar with ibm_db, but I'd suggest some basic debugging to figure out what's going on. Add some console.logs to different places, like in db2.js to see if it's loaded in the first place, then log out dbConn to see if db2.open returned a value. — Whothehellisthat
– Whothehellisthat, Commented Sep 2, 2016 at 8:03
@Whothehellisthat I already did that and it's undefined in db2.js, too. So I believe that it really has something to do with callback, but I can't really make it work in server.js file. — peterremec
– peterremec, Commented Sep 2, 2016 at 8:18
You said "db2connection is undefined" which is after db2.js is required, in server.js only. See my answer below. — Whothehellisthat
– Whothehellisthat, Commented Sep 2, 2016 at 8:21

Whothehellisthat · Accepted Answer · 2016-09-02 11:25:33Z

2

As said by @Sirko:

db2.js

var db2 = require('ibm_db');
var db2ConnSettings = "DRIVER={DB2};DATABASE=mydb;HOSTNAME=localhost;UID=db2test;PWD=db2test;PORT=50000;PROTOCOL=TCPIP";

var err, conn;
var callbacks = [];

module.exports = function(callback) {
  // db2 module is called

  if (err || conn) {
    // connection has already been established
    //   (results of db2.open have been stored)
    // callback immediately
    callback(err, conn);
  }
  else {
    // connection has not been established
    // store the callback for when db connects
    callbacks.push(callback);
  }
};

db2.open(db2ConnSettings, function(_err, _conn){
  // db has connected

  err = _err; conn = _conn; // store results
  var next_callback;

  // array.pop() removed the last item from the array
  // and returns it. if no items are left, returns null.
  // so this loops through all stored callbacks.
  while(next_callback = callbacks.pop()) {
    // the removed item is stored in next_callback
    next_callback(err, conn); // send connection results to callback
  }

  // no more items in callbacks to trigger
});

server.js

var express = require('express');
var app = express();
var db2Connection = require('./db2.js')(function(err, conn) {
  // triggered if the connection has already been established
  // or as soon as it HAS been established
  app.get('/data', function(req, res) {
      console.log(conn);
      // ...
  });
});

edited Sep 2, 2016 at 11:25

answered Sep 2, 2016 at 8:06

Whothehellisthat

2,1521 gold badge15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sirko Over a year ago

I'm not sure, if the solution is that simple as here each time the function is called a new database connection is established. So depending on the rest of the code, you can end up with LOTS of connections.

Whothehellisthat Over a year ago

Fair enough. I've updated it with a simple promises-like system.

peterremec Over a year ago

@Whothehellisthat thanks, it worked. Would you mind adding some comment to that code? I'd like to understand what it does...

Whothehellisthat Over a year ago

Does that help? I've changed some variable names to be clearer also.

Christopher Jones · Accepted Answer · 2016-09-06 02:05:52Z

2

For Oracle with node-oracledb it's simple to create and use a connection pool. Your app would just get a free connection from the pool whenever it handles an HTTP REST request. Look at webapp.js and webapppromises.js in the examples. Node-oracledb has a 'connection pool queue' (see doc) which handles connection load spikes. It also has a 'connection pool cache' (also see the doc) which makes it easy to access a pool created in a different module.

answered Sep 6, 2016 at 2:05

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

3 Comments

peterremec Over a year ago

I guess if an app uses just a few connections in a short period, there's no need to create a connection pool, right? What's the most common approach in that case? If I have multiple REST services with database access, should I create a new connection each time a service is called or should I use the same connection reference?

Christopher Jones Over a year ago

I'd probably go with a small pool.

peterremec Over a year ago

Thanks for a suggestion!

Collectives™ on Stack Overflow

Node js database connection in a separate module

2 Answers 2

db2.js

server.js

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

db2.js

server.js

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related