Use single database connection in entire application?

up vote
6
down vote

favorite

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

edited 2 hours ago

Erik Philips

38.6k685119

asked 2 hours ago

pradip shinde

626316

You may use a mysql connection pool in this case, are you sure you want to use one connection, while there is an option to use a connection pool, so you can reuse each connections and won't block until a sql commit?
â€“Â Janith Kasun
1 hour ago

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
1 hour ago

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
â€“Â Janith Kasun
1 hour ago

And this article
â€“Â Janith Kasun
1 hour ago

1

Here is How to use mssql connection pool. Sorry I didn't see that
â€“Â Janith Kasun
1 hour ago

Â |Â
show 1 more comment

up vote
6
down vote

favorite

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;

edited 2 hours ago

Erik Philips

38.6k685119

asked 2 hours ago

pradip shinde

626316

You may use a mysql connection pool in this case, are you sure you want to use one connection, while there is an option to use a connection pool, so you can reuse each connections and won't block until a sql commit?
â€“Â Janith Kasun
1 hour ago

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
1 hour ago

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
â€“Â Janith Kasun
1 hour ago

And this article
â€“Â Janith Kasun
1 hour ago

1

Here is How to use mssql connection pool. Sorry I didn't see that
â€“Â Janith Kasun
1 hour ago

Â |Â
show 1 more comment

up vote
6
down vote

favorite

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;

edited 2 hours ago

Erik Philips

38.6k685119

asked 2 hours ago

pradip shinde

626316

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;

node.js sql-server npm

edited 2 hours ago

Erik Philips

38.6k685119

asked 2 hours ago

pradip shinde

626316

edited 2 hours ago

Erik Philips

38.6k685119

asked 2 hours ago

pradip shinde

626316

edited 2 hours ago

Erik Philips

38.6k685119

edited 2 hours ago

Erik Philips

38.6k685119

edited 2 hours ago

Erik Philips

38.6k685119

asked 2 hours ago

pradip shinde

626316

asked 2 hours ago

pradip shinde

626316

asked 2 hours ago

pradip shinde

626316

You may use a mysql connection pool in this case, are you sure you want to use one connection, while there is an option to use a connection pool, so you can reuse each connections and won't block until a sql commit?
â€“Â Janith Kasun
1 hour ago

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
1 hour ago

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
â€“Â Janith Kasun
1 hour ago

And this article
â€“Â Janith Kasun
1 hour ago

1

Here is How to use mssql connection pool. Sorry I didn't see that
â€“Â Janith Kasun
1 hour ago

Â |Â
show 1 more comment

You may use a mysql connection pool in this case, are you sure you want to use one connection, while there is an option to use a connection pool, so you can reuse each connections and won't block until a sql commit?
â€“Â Janith Kasun
1 hour ago

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
1 hour ago

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
â€“Â Janith Kasun
1 hour ago

And this article
â€“Â Janith Kasun
1 hour ago

1

Here is How to use mssql connection pool. Sorry I didn't see that
â€“Â Janith Kasun
1 hour ago

You may use a mysql connection pool in this case, are you sure you want to use one connection, while there is an option to use a connection pool, so you can reuse each connections and won't block until a sql commit?
â€“Â Janith Kasun
1 hour ago

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
1 hour ago

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
â€“Â Janith Kasun
1 hour ago

And this article
â€“Â Janith Kasun
1 hour ago

Here is How to use mssql connection pool. Sorry I didn't see that
â€“Â Janith Kasun
1 hour ago

Â |Â
show 1 more comment

2 Answers
2

active

oldest

votes

up vote
3
down vote

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 to the global object and access it from anywhere (typically not a best practice)

Also, you're initiating the connection by calling sql.connect(), but you don't give it a callback for when it's finished connecting, and immediately export it and probably query it before the connection is 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(() => sql)

Then you can retrieve the instance with:

require('./database.js').then(sql => 
 app.sql = sql;
).catch(console.error);

answered 1 hour ago

Brandon D. McKay

296111

add a commentÂ |Â

up vote
1
down vote

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);

answered 2 hours ago

Jitendra virani

1096

add a commentÂ |Â

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52584646%2fuse-single-database-connection-in-entire-application%23new-answer', 'question_page');

);

Post as a guest

Name

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
3
down vote

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 to the global object and access it from anywhere (typically not a best practice)

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(() => sql)

Then you can retrieve the instance with:

require('./database.js').then(sql => 
 app.sql = sql;
).catch(console.error);

answered 1 hour ago

Brandon D. McKay

296111

add a commentÂ |Â

up vote
3
down vote

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 to the global object and access it from anywhere (typically not a best practice)

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(() => sql)

Then you can retrieve the instance with:

require('./database.js').then(sql => 
 app.sql = sql;
).catch(console.error);

answered 1 hour ago

Brandon D. McKay

296111

add a commentÂ |Â

up vote
3
down vote

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 to the global object and access it from anywhere (typically not a best practice)

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(() => sql)

Then you can retrieve the instance with:

require('./database.js').then(sql => 
 app.sql = sql;
).catch(console.error);

answered 1 hour ago

Brandon D. McKay

296111

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 to the global object and access it from anywhere (typically not a best practice)

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(() => sql)

Then you can retrieve the instance with:

require('./database.js').then(sql => 
 app.sql = sql;
).catch(console.error);

answered 1 hour ago

Brandon D. McKay

296111

answered 1 hour ago

Brandon D. McKay

296111

answered 1 hour ago

Brandon D. McKay

296111

answered 1 hour ago

Brandon D. McKay

296111

add a commentÂ |Â

up vote
1
down vote

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);

answered 2 hours ago

Jitendra virani

1096

add a commentÂ |Â

up vote
1
down vote

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);

answered 2 hours ago

Jitendra virani

1096

add a commentÂ |Â

up vote
1
down vote

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);

answered 2 hours ago

Jitendra virani

1096

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);

answered 2 hours ago

Jitendra virani

1096

answered 2 hours ago

Jitendra virani

1096

answered 2 hours ago

Jitendra virani

1096

answered 2 hours ago

Jitendra virani

1096

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky