Database connection using tedious

Question

What is the recommended way to establish database connection and close it properly in Node.js using tedious?

For each and every request we are creating new connection and processing the request then closing them in callback.

app.get('/getData/:id', function(req, res){
    var id = req.params.id;
    var sqlGet = "exec MyStoreProcedure @Id='" + id + "'";
    var connection = new Connection(config);
    var request = new Request(sqlGet, function(err, result){
        connection.close();
        if(err)
            console.log(err);
        else
            res.send(result);
    });

    connection.on('connect', function(err) {
        if (err) 
        {
            console.log(err)
        }else{
            console.log("Connected");
            connection.execSql(request);
        }
    });
});

Is there any other recommended approach to handle this scenario?

Answer 1

UPDATE (Oct 19, 2020): It appears that tedious-connection-pool is no longer supported/outdated. I've migrated my code to mssql: https://www.npmjs.com/package/mssql

Previous Answer:

You should check out tedious-connection-pool: https://github.com/tediousjs/tedious-connection-pool.

This makes it easy to manage and reuse connections rather than open/close connections continuously.

As part of using connnection pooling, you should extract it out into a separate file so it can be reused across your application.