How does connectionLimit work in node mysql?

Question

I quite don't understand how connectionLimit in mysql module works.

I have created a connectionPool in following way:

  var connPool = mysql.createPool({
        host: config.get('database.host'),
        user: config.get('database.user'),
        password: config.get('database.password'),
        database: config.get('database.dbname'),
        connectionLimit: 5
  });

Now, what does limit 5 do here?

Here's what I think:

Suppose there are 5 users using my REST API for selecting some data from a table. Now in my API, I get a connection from pool, do stuff and then release the connection.

So now, suppose there are 12 users who send request to this API concurrently. So first 5 of them should get access to the data and other 7 will get error / no response. They will have to request again to get the data.

Is this like so or something else?

Answer 1

Is this like so or something else?

It's something else.

If connectionLimit is 10, the maximum number of connections that the driver will make to the database is 10.

Assuming the default driver options, if all of those connections are active (that is, they have been acquired from the pool to perform a query, but they haven't yet been released), then the next request for a connection (pool.getConnection()) will be queued: the driver will wait until one of the active connections gets released back into the pool. Your user will only notice a bit of a delay, but they won't get an error or no response; just a delayed response.

All this can be fine-tuned using the options documented here. For instance, if you prefer that instead of waiting, the driver returns an error, set waitForConnections : false.

Or, if you want the driver to return an error if, say, there are 25 requests queued to receive a connection, set queueLimit : 25.