CODEWITHSUNDEEP
node.jsexpress
Dylan
Dylan

Reputation: 37

Multiple concurrent connections in node to mssql

I'm using node with express and mssql and querying the database works fine. However, if I pass 2 concurrent requests at exactly the same time, I get:

UnhandledPromiseRejectionWarning: ConnectionError: Database is already connected! Call close before connecting to different database.

var express = require('express'); // Web Framework
var app = express();
var sql = require('mssql/msnodesqlv8');



    const pool = new sql.ConnectionPool({
        database: 'db123',
        server: 'server1',
        driver: 'msnodesqlv8',
        options: {
            trustedConnection: true
        },
    })

    var conn = pool;


var server = app.listen(5001, function () {
    var host = server.address().address
    var port = server.address().port
    console.log("app listening at http://%s:%s", host, port)
});



// GET: SQL Stored procedure return time codes
app.get('/codes/:userid/:showclosed', function (req, res) {
    conn.connect().then(function () {
        var request = new sql.Request(conn);
        request.input('userid', req.params.userid);
        request.input('showclosed', parseInt(req.params.showclosed));
        request.execute('sel_new', function(err, recordsets, returnValue, affected) {
            if(err) console.log(err);
            res.end(JSON.stringify(recordsets)); // Result in JSON format
            conn.close();
        })
    })
    })

EDIT:

Here is what I ended up with:

app.get('/codes/:userid/:showclosed', function (req, res) {
    //const pool1 = new sql.ConnectionPool(config);
   (async function () {
    try {
        let pool = await conn;
        // Stored procedure       
        let result2 = await pool.request()
            .input('userid', req.params.userid)
            .input('showclosed', parseInt(req.params.showclosed))
            .execute('StoredProcedure1', function(err, recordsets, returnValue, affected) {
                if(err) console.log(err);
                res.end(JSON.stringify(recordsets)); // Result in JSON format
                //sql.close();
            })
    } catch (err) {
        console.log(err);
    }
})()

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

Upvotes: 1

Views: 5763

Answers (1)

Elliot Blackburn
Elliot Blackburn

Reputation: 4164

You shouldn't be using conn.connect() on every request, this initialises the connection pool and should be used when your service starts up.

So you want conn.connect() to happen straight after var conn = pool. This will instruct the connection pool to open a connection to the database which your application can then use on-demand in your various endpoints. You can see an example of this in the mssql library documentation. The code opens a connection with sql.connect() and then goes on to use the sql object to create and execute SQL queries. In your example you'd use conn rather than sql. I've not used the library myself but that's what the documentation seems to suggest.

You also shouldn't close the connection after making a call into the database, as you might have no guessed this will close the entire pool of connections. The idea is to open a pool of connections and leave them around for as long as the service is running, you should definetely run conn.close() during the shutdown phase though.

Upvotes: 5

Related Questions