Error: Global connection already exists. Call sql.close() first

Question

Hi I am creating node js restful api by using sqlserver database , i prepare get api when i am using that api output is shown in json format, while refreshing that browser gain its shows "Error: Global connection already exists. Call sql.close() first."error . I am adding code

var express = require("express");

var sql = require("mssql");
var app = express();

//Initiallising connection string
var dbConfig = {
    user: 'sa',
    password: 'India123',
    server: 'localhost',
    database: 'sample'

  
};

app.get('/login', function (req, res) {

    // connect to your database
    
    var data = {

        "user": ""
    };
    sql.connect(dbConfig, function (err) {

        if (err) console.log(err);

 
        var request = new sql.Request();
   
      
        request.query('select * from Login', function (err, result) {

            if (err) console.log(err)

            // send data as a response
            //res.send(result.recordset);
            data["user"] = result.recordset;
            res.send(data);
          
      
        });
    }); 

});

var server = app.listen(5000, function () {
    console.log('Server is running..');
});

Please correct me code . thanks advance

Answer 1

// db.js 
var mssql = require("mssql"); 
var dbConfig = {
    user: 'sa',
    password: 'India123',
    server: 'localhost',
    database: 'sample'   
};

var connection = mssql.connect(dbConfig, function (err) {
    if (err)
        throw err; 
});

module.exports = connection; 

// app.js 
var db = require("db"); 
var express = require("express"); 
var app = express();

app.get('/login', function (req, res, next) {
    var request = new db.Request();
    request.query('select * from Login', function (err, result) {
        if (err) 
            return next(err);

        var data = {};
        data["user"] = result.recordset;
        res.send(data);      
    }); 
}); 

var server = app.listen(5000, function () {
    console.log('Server is running..'); 
});

Answer 2

Don't use sql.Connection() any more, instead use sql.ConnectionPool()

Connections

Internally, each ConnectionPool instance is a separate pool of TDS connections. Once you create a new Request/Transaction/Prepared Statement, a new TDS connection is acquired from the pool and reserved for desired action. Once the action is complete, connection is released back to the pool. Connection health check is built-in so once the dead connection is discovered, it is immediately replaced with a new one.

IMPORTANT: Always attach an error listener to created connection. Whenever something goes wrong with the connection it will emit an error and if there is no listener it will crash your application with an uncaught error.

Create pool and use connection.

const pool = new sql.ConnectionPool({ /* config */ })

Entire Article how to use pool and close pool.

https://www.npmjs.com/package/mssql

var sql = require("mssql");

const pool = new sql.ConnectionPool({
    user: 'sa',
    password: 'Pass@123',
    server: 'SAI-PC',
    database: 'Demo'
})


var conn = pool;

conn.connect().then(function () {
    var req = new sql.Request(conn);
    req.query("SELECT * FROM Product").then(function (recordset) {
        console.log(recordset);
        conn.close();
    })
        .catch(function (err) {
            console.log(err);
            conn.close();
        });
})
    .catch(function (err) {
        console.log(err);
    });