MySQL connection pools in Node.js (mysql2)

Question

I am struggling to find documentation that describes how so called "Connection Pools" work in Node.js applications, running with the mysql2 package from NPM. I only managed to find some short examples of it on their NPM page, but there is a lack of explanation of how it actually works.

I've got this code in my express app:

/main.js:

const db = require('./modules/databaseFunctions');

app.get('/test', async (req, res) => {
    const worlds = await db.getWorlds();
    res.render('test', { worlds: worlds });
});

/modules/databaseFunctions.js:

const db = require('./databaseConnection');

async function getWorlds() {
    const sql = "SELECT * FROM worlds";
    const [rows] = await db.connection.query(sql);
    return rows;
}

exports.getWorlds = getWorlds;

/modules/databaseConnection.js:

const mysql = require('mysql2/promise');

// Establish connection to database
const connection = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    waitForConnections: true,
    connectionLimit: 10,
    maxIdle: 10,
    idleTimeout: 60000,
    queueLimit: 0
});

exports.connection = connection;

So if we take a look at the "databaseConnection.js" file where I create a connection pool to the database, I have set it to a max limit of 10 connections in the pool. But what would happen if 50 users would be on my website and attempt to load /test page? Would only 10 of them go through and what happens to the others? And how would I manage a lot of users on the website overall? Any recommendations for queuelimits and connectionlimits?

What happens if only 2 users visit my site? Will there be 8 empty connections? I don't see how this is more beneficial for the performance in that case.

Is there any way to redirect users to another page if all connections are taken?

Can anyone explain how this all works? I am struggling to understand how it all works, how the limits work, will it create more connection pools if the first pool (10 connections) are taken?

Thanks in advance!

Answer 1

Connection pooling is a technique that can help improve the performance of Node.js applications that need to make frequent database requests. Instead of opening a new connection for every request, connection pooling allows the application to reuse existing connections from a pool, which reduces the overhead of establishing new connections.

If more than the maximum number of connections in the pool (in this case, 10) are needed, the connection pool will queue the requests and process them as connections become available. By default, the queue is unlimited, so it can grow as needed.

If the maximum number of connections is not reached, the connection pool will maintain idle connections in the pool that can be reused for subsequent requests, which can help improve the application's performance.

If all connections in the pool are taken and a new request comes in, the connection pool will queue the request until a connection becomes available. If the queue limit is reached and a new request is made, an error will be thrown. To avoid this, you can increase the queue limit or connection limit of the pool.