node mssql concurrent request resulting "Connection is closed"

Question

I am using mssql package of node js and each route i am creating a new connection and performing the transactions, when i call two apis parallelly from client end the mssql throws "Connection is closed"

Example: when this api called concurrently the "Connection is closed" is coming up

router.get('/', async function (req, res, next) {
   let result = {};
   try {
       let pool = await sql.connect(db);
       try {
          let sqlResponse = await pool.request().execute('p_getAllData');
          result = {
             status: true,
             data: sqlResponse.recordsets[0]
          }
          pool.close();
      } catch (e) {
          pool.close();
          throw e;
      }
   } catch (e) {
       result = {
          status: false,
          data: e.message
       }
       console.log(e);
   } 
   res.send(result);
});

Answer 1

I wouldn't create the pool for each request and close it with every request. It is very inefficient. You should create the connection pool once probably when you start the server and reuse that across.

A method like this

let pool;

app.listen(port, async () => {
   console.log(`Example app listening on port ${port}!`);
pool = await sql.connect("connectionstring");
});

In your route you basically use pool the way you normall do.

A more appropriate way to do is to create a dbOperation file which has all the relevant methods and use them in your routes.