Nodejs mssql global connection

user_8390201 0
Viewed 4183

I used nodejs to connect to a SQL Server. But I failed to create a global connection that is shared among all queries. In the documentation, queries are all wrapped in connection call back function, which means every time I make a query, I have to establish a connection.

Is there a way to keep a single connection alive so I can share it with all controllers? I've done it with mongodb, not sure how SQL Server does this.

I did something like this

connection.js:

const config = require('./config')
const sql = require('mssql')
const pool = sql.ConnectionPool(config).connect(function(err){
    if(err) throw err;
    console.log("connected");
});
module.exports = {
    sql, pool
}

server.js:

const conn = require('./connection.js')
const requst = new conn.sql.Request(conn.pool)
request.query('select * from table', function (err, recordset){
    if(err) console.log(err);
    console.log(recordset);
});

It failed because the connection is closed.

Please share some of your insights, thanks

node.js sql-server
2 Answers

I used MSSQL for one of my projects, to run it globally this is what i did.

server.js:

const sql = require('mssql');
const config = require('./config');
sql.connect(config, (err) => {
if (err) return console.error(err);
  console.log("SQL DATABASE CONNECTED");
});

SomeControllerFile:

const sql = require('mssql');
const request = new sql.Request();
request.multiple = true;

request.query('select * from table') => {
  if(err){
    return console.error(err);
  }
  return res.send(recordset)
});

Hope this helps.

user_74259570

Your first approach would work fine, but you have to account for the asynchronous nature of connect(). What's happening is that connect is being called, the variables are being exported, and you go to use it- and it's not done connecting. I would recommend a fix like this: 

const conn = require('./connection.js'); 
queryDatabase(conn.pool); 

async function queryDatabase(pool_connection){
    let pool = await pool_connection; 
    let request = new conn.sql.Request(pool); 
    request.query('select * from table', function (err, recordset){
         if(err) console.log(err);
         console.log(recordset);
    });
}

In your server js. You could await the connection in your connector file but that defeats the purpose of having non-blocking async functionality in the first place.

user_106270570
Related