Use single database connection in entire application?

Question

I am creating a application that will communicate over Udp protocol in node js. Also i am using sql server as a database so in order to connect this database i am using mssql npm liabrary. Basically what i am doing i have one separate module for dbcon as shown below

const sql = require('mssql')
const config = {
    user: 'sa',
    password: '123',
    server: '192.168.1.164', // You can use 'localhost\\instance' to connect to named instance
    database: 'SBM-EMCURE',

    options: {
        encrypt: false // Use this if you're on Windows Azure
    }
}
 sql.connect(config, err => {

 })

sql.on('error', err => {
    console.log('error on sql.on()');
})
module.exports.sql = sql;

And i am using this exported sql object to run my queries outside dbcon module but it gives me different behavior sometimes like query executes before databse connection, is there is any way to use single database connection for entire application?. Using single database connection is useful or it will slow down my process
Thanks in advance

yeah but i am using mssql when i try to reconnect the database it giving warning like please close previous connection then create new one so what i done i connect to database once and store its instance one variable. and i am using this variable in entire application and it is works like charm — pradip shinde, Oct 01 '18 at 05:48
Yes, pradip that because you are using a one connection to the mysql database, so util the changes are committed, your sql connection cannot be used. Even if you used one connection to retrieve data that will fail on high frequency data retrieve. Let me give you a board answer. For now please follow this [question](https://stackoverflow.com/questions/18496540/node-js-mysql-connection-pooling) — Janith, Oct 01 '18 at 05:58
And this [article](http://www.madhur.co.in/blog/2016/09/05/nodejs-connection-pooling.html) — Janith, Oct 01 '18 at 06:02
i am using mssql not mysql is this pool concepts support mssql i dont know much about it — pradip shinde, Oct 01 '18 at 06:13
Here is How to use [mssql connection pool](https://stackoverflow.com/questions/30356148/how-can-i-use-a-single-mssql-connection-pool-across-several-routes-in-an-express). Sorry I didn't see that — Janith, Oct 01 '18 at 06:27

score 2 · Answer 1 · answered Oct 01 '18 at 04:59

first you should create file database.js:

var mysql = require('mysql');
var connection = mysql.createConnection({
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database : 'event'
});

connection.connect(function(err) {
    if (err) throw err;
});

module.exports = connection;

Then you can use this connection in server.js or any other file.

var express = require('express');
var app = express();
var dbcon = require('./database');

app.get('/getEvent',function(req,res){
    dbcon.query('SELECT * FROM eventinfo',function(err, result) {
      if (err) throw err;
    });
});

app.listen(3000);

score 2 · Accepted Answer · 2018-10-03T23:02:11.280

You could:

Pass the instance into each router and use it there when you set them up
Set the instance as a property of your app object and access it from req.app.sql or res.app.sql within your middleware functions
Set the instance as a property of the global object and access it from anywhere (typically not a best practice though)

Also, in your example code, you're initiating the connection by calling sql.connect(), but you don't give it a callback for when it's finished connecting. This is causing it to be immediately exported and probably queried before the connection is actually established. Do this:

const util = require('util');
const sql = require('mssql');

const config = {
    user: 'sa',
    password: '123',
    server: '192.168.1.164', 
    database: 'SBM-EMCURE',

    options: {
        encrypt: false 
    }
};

module.exports = util.promisify(sql.connect)(config);

Then you can retrieve the instance with:

const sql = await require('./database.js');

score 0 · Answer 3 · answered Aug 15 '23 at 12:21

node js require returns the same instance of a module every time you do require,From the [Node.js docs]

"every call to require('foo') will get exactly the same object returned, if it would resolve to the same file."

let's create a db wrapper file called dbconn.js

    var mysql = require('mysql');
    var connection = mysql.createConnection({
      host     : '127.0.0.1',
      user     : 'root',
      password : '',
      database : 'event'
    });

   connection.connect(function(err) {
     if (err) throw err;
   });

   module.exports = connection;

in any file you can just do require of database wrapper js and get the same connection

var dbcon = require('./dbconn');
dbcon.query('SELECT * FROM eventinfo',function(err, result) {
      if (err) throw err;
});

Use single database connection in entire application?

3 Answers3