exporting a module for creating Connection creates multiple connection in nodejs

Question

I am newbie in nodejs and api development, I have written following code for creating connection to database and using them by calling connection.query.

Here is code for create Connection module and call them in my model class. I have to pass database dynamically as in my project there could be multiple databases.

It is creating multiple database connection. How can I change it to single database connection which should be used in whole app ,quickly without making much changes in code.

db.js:

var mysql = require('mysql');
module.exports = dbConnection = (dbname) => {
  console.log('mysql inside dynamic db...');
  return connection = mysql.createConnection({

      host: process.env.DBHost || 'localhost',
      port: process.env.DBPort || 3306,
      user: process.env.DBUser || 'root',
      password: process.env.DBPassword || '',
      database: process.env.DBName || dbname
  });

};

for query :

//get financial year
Employee.getYear = function getYear(req_decoded, result) {
    var myPromise = dbname(req_decoded);
    myPromise.then(function(myDBName) {
        new_conn(myDBName).query("Select id,name from " + myDBName + ".financial_year", function(err, res) {
            if (err) {
                //console.log("error: ", err);
                result(null, err);
            } else {
                result(null, JSON.stringify({
                    "statusCode": 200,
                    "status": "success",
                    "data": res
                }));
            }
        });
    });
};

I am getting the database name from another database and using promise to get the database name.

Any help would be appreciated.

Sandeep Patel · Accepted Answer · 2019-09-10 13:40:13Z

Don't create Connection multiple times, create connection something as below :

 // connection.js
    var mysql = require('mysql');
    module.exports = connection = mysql.createConnection({

          host: process.env.DBHost || 'localhost',
          port: process.env.DBPort || 3306,
          user: process.env.DBUser || 'root',
          password: process.env.DBPassword || '',

      });

};

if you are using only one db then you can pass database details as well into connection options.

Connect to database server in main file let say server.js :

var connection =  require('./connection.js');
connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }


  });

Once connected you can import same connection object other places also and run query like this:

connection.query("Select id,name from " + myDBName + ".financial_year", function(err, res) {});

But this approach is not highly recommended when number of read and write are high and queries are complex. I would recommend you to read this also: When to close MySQL connection using node-mysql?

Collectives™ on Stack Overflow

exporting a module for creating Connection creates multiple connection in nodejs

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related