title: Node.js mssql data input layer with connection pool
body: <p>This question relates to a <strong>Node.js mssql API</strong>.</p>
  
  <p>I've recently updated my code to use a <strong><em>SQL.ConnectionPool</em></strong> instead of <strong>sql.connect</strong> which when combined with an async / await function allowed me to get around <strong><em>connection.close()</em></strong> errors.</p>
  
  <p>In my previous (<strong>OLD</strong>) <strong><em>executeQuery</em></strong> function, I was able to pass an array which I could push values into to use with "<strong>request.input(name, value)</strong>"</p>
  
  <p><strong>Function call example:</strong></p>
  
  <pre><code>app.get('/api/route/:id', function (req, res) {
  var id = req.params.id;
  let x = []
  if (id != null &amp;&amp; id != NaN) {
      x.push({
          Name: 'id', Value: id
      })
      var query = `SELECT * from [Table] where ID = @id`
      executeQuery(res, query, arr);
  } else {
      res.send(500)
  }
  </code></pre>
  
  <p>})</p>
  
  <p><strong>OLD Function</strong>:</p>
  
  <pre><code>var executeQuery = function (res, query, arr) {
  sql.connect(dbConfig, function (err) {
      if (err) {
          console.log('Error while connecting to the database: ' + err)
          res.send(err)
      } else {
          // Create the request object
          var request = new sql.Request();
          if (arr != null) {
              if (arr.length &gt; 0) {
                  for (var obj of arr) {
                      request.input(obj.Name, obj.Value)
                  }
              }
          }
          request.query(query, function (err, rs) {
              if (err) {
                  sql.close();
                  console.log('Error while querying the database : ' + err);
                  res.send(err);
              } else {
                  sql.close();
                  console.log(rs)
                  res.send(rs)
              }
          })
      }
  })
  </code></pre>
  
  <p>}</p>
  
  <p><strong>NEW Function</strong>: </p>
  
  <pre><code>var executeQuery = async function(res, query, arr){
  const pool = new sql.ConnectionPool(dbConfig);
  pool.on('error', err =&gt; {
      console.log('sql errors ', err);
  });
  
  try {
      await pool.connect();
      let result = await pool.request().query(query);
      console.log('success')
      res.send(result);
      return {success: result};
  } catch (err) {
      console.log('error')
      console.log(err)
      res.send(err);
      return {err: err};
  } finally {
      pool.close();
  }
  </code></pre>
  
  <p>}</p>
  
  <p><strong>Question</strong></p>
  
  <p>How do I go about achieving the same request.input process with a ConnectionPool as I did with my previous function ( like the below )</p>
  
  <pre><code>var request = new sql.Request();
      if (arr != null) {
          if (arr.length &gt; 0) {
              for (var obj of arr) {
                  request.input(obj.Name, obj.Value)
              }
          }
      }
  </code></pre>
  
  <p>Thank you.</p>