Why do we need to release connection when using connection pool in mysql?

Question

I am trying to implement nodejs mysql database following this tutorial. I know that

pool.query() is a shortcut for pool.getConnection() + connection.query() + connection.release().

In the article the database is configed as:

var mysql = require('mysql')
var pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'matt',
    password: 'password',
    database: 'my_database'
})
pool.getConnection((err, connection) => {
    if (err) {
        if (err.code === 'PROTOCOL_CONNECTION_LOST') {
            console.error('Database connection was closed.')
        }
        if (err.code === 'ER_CON_COUNT_ERROR') {
            console.error('Database has too many connections.')
        }
        if (err.code === 'ECONNREFUSED') {
            console.error('Database connection was refused.')
        }
    }
    if (connection) connection.release()
    return
})
module.exports = pool

This is can be used as:

pool.query('SELECT * FROM users', function (err, result, fields) {
    if (err) throw new Error(err)
    // Do something with result.
})

However, I really do not understand the point of

if (connection) connection.release()

Why do we need this if using pool releases the connection automatically?

Answer 1

Once you do pool.getConnection(), you are removing a connection from the pool which you can then use and nobody else can get access to that connection from the pool. When you are done with it, you put it back in the pool so others can use it.

So, when not using pool.query() (which as you know puts it back in the pool automatically), you have to get a connection, do whatever you want with it and then put it back into the pool yourself.

If all you need to do is a single query, then use pool.query() and let it automatically get a connection from the pool, run the query, then release it back to the pool. But, if you have multiple things you want to do with the connection, such as multiple queries or multiple inserts to the database, then get the connection, do your multiple operations with it and then release it back to the pool. Getting a connection manually from the pool also allows you to build up state on that connection and share that state among several operations. Two successive calls to pool.query() may actually use different connections from the pool. They might even run in parallel.

However, I really do not understand the point of

if (connection) connection.release()

Why do we need this if using pool releases the connection automatically?

If you manually get a connection from the pool, then you have to manually put it back in the pool when you're done with it with connection.release(). Otherwise, the pool will soon be empty of connections and you'll have a bunch of idle connections that can't be used by anyone.

If you use the automatic methods like pool.query(), then it will handle putting it back into the pool after the single query operation.

Think of it like an automatic mode vs. a manual mode. The manual mode gives you finer grain control over how you do things, but when the automatic mode lines up with your needs, it's easier to use. When the automatic mode (pool.query()) doesn't do exactly what you want, then manually get a connection from the pool, use it and the put it back.