I am referring to the document linked here.

It says –

this long running process to create a pool of database connections for reuse

What does the statement create a pool of database connections mean? Does the library node-mssql create 1000 connection objects (assuming, I have provided maximum pool to be 1000)?

Assuming a simple program flow –

let globalPool;
createConnectionPool()
.then (pool => {
  globalPool = pool
  return pool.query(query)
})
.then (data => {
  const promises = data.map(item =>{
  //manipulate data
  return globalPool.query(anotherQuery)
})
.catch()
.finally(()=>{
  globalPool.close()
})

In this case when createConnectionPool is called, are 1000 connection objects created? When pool.query is called, node-mssql library assigns one of the 1000 objects. In the subsequent then block for each record an unused connection object is assigned.

Issue: I created a typescript program, that was fetching data from table. I created a common sql.ts which could be reused. I played around with connection pool and provided maximum pool to be 512 and minimum to be 256. The first program was not fetching lot of data from table. All good at this point.

I wrote another program, this time, the query is fetching records to the tune of 5-6K rows and then for each of the row there are subsequent queries executed. Except for the first query, which is complicated, rest of the queries are using primary key.

Based on the documentation, I expected that program will wait for connection pool to get freed and then execute. However I received lot of connection pool timeout error. I increased my timeout setting. It reduced the number of timeouts but still not a big improvement. Then I creased my maximum pool to 5000 and my program completed without any issue. I tried another way of keeping the minimum pool requirement to higher number, but it also didnt help.

Let’s assume I write another program (a monthly report). The table returns 50K rows. Do I need to increase my pool count to 50K. This doesn’t seem right. Hence I wanted to know, how connection pool works and how to handle such scenarios. Do I call sqlConnect every time so that if connection pool drops below maximum number and none is available, a new pool is created?