My understanding of a connection pool is that multiple connections to the database hang around and a client application can use them in order to send queries to the database, without having to go through the cost of setting up a new TCP connection (handshake, etc). I want to understand a little bit more in depth about the limitations of using a singular connection in a multithreaded application, and in something like nodeJS.

In a multithreaded application if we have a connection to a database, we are able to just access that connection from both threads and send and receive data from it, correct? My understanding is that instead of being exactly in parallel, the queries to the database follow one after another in this case. Can we send multiple queries before receiving data, or does the socket block until data is received from the first query? If it is the latter, than is there any risk of accidentally reading the data from the other query that was sent?

In a single threaded application like nodeJS, I’m struggling to even see why we would need a connection pool in the first place. For an example like so

    let connection = msql.Connection()

    const getPerson = async () => {
        connection.Query('SELECT * from People where name = 'test')
    }

    let arr = Array.from(Array(1_000_000)).keys())

    await promise.all(arr.map(x => {
      getPerson()
    })

Wouldn’t getPerson() simply call connection.Query() synchronously on ever call and then the c++ apis would send the request off to the database, and then the loop can continue and we can call getPerson() again and send another query to the database? Or is there some limitation that I am missing?

Any clarification on database connection pooling is extremely appreciated, thanks! (Mainly about why using one single connection for multiple queries doesn’t work in the examples provided?)