NodeJS mysql connection pool with multiple queries

Question

I'm fairly new to NodeJS (from a PHP background). I have a basic understanding of the asyncronous nature of NodeJS compared to the blocking nature of PHP. I've created a few apps Node that connect to mysql and got those working.

My current understanding of the best way to execute queries in Node is this:

• create a connection pool
• When you need to execute a query:
• getConnection() from pool
• execute query
• .release() connection

I'm in a situation where I need to iterate of a large number of items and insert each to the database. Doing the above didn't work. My thought was that since each query is executed in parallel, the next item in the list was trying to acquire a connection, but none were available since the previous queries may not have completed yet. No errors were thrown, it just seemed like items weren't being put in. Through logging I found that it was never getting to the point of running the pool.getConnection() callback.

So I thought I could revise the connection flow:

• create a connection pool
• When you need to execute many queries:
• getConnection() from pool
• iterate over items that need queries
• execute queries
• .release() connection

The issue with this is, how do I know when it's safe to .release() my connection? I can't just do it in my .query() callback because there may be more queries after it. But I also can't not do it in a query callback because I know I need to wait for all queries to finish. Am I responsible for creating my own logic to figure out when all queries (and all possible future queries, even if they aren't started yet) are done? Or is this just something that isn't done?

In PHP this would be trivial because of it's blocking nature. I chose to use NodeJS for this application because it does need to be asynchronous at times. But it forces asynchronicity on me in places my app doesn't need it (an in some cases shouldn't use it).

I'm hesitant to post my code because it's a bit complicated so I didn't want to distract from this specific topic.

Answer 1

Your connection flow is fine. If you only do 1 query, you don't actually need to do any of the steps and you can just call .query() on the pool directly. (Which does the rest under the hood).

The situation where you need to do multiple queries in 1 transaction is mostly, but I would add transaction support:

• create a connection pool
• When you need to execute many queries:
• getConnection() from pool
• start transaction
• iterate over items that need queries
• execute queries
• if any fails, stop and roll back
• if all succeeded, commit
• .release() connection

You know when to release the connection if all the queries are 'done'. How do you know it's done? There's more than one way. I would recommend you use the promise API, so it might look like this:

async function doStuff(items) {

  try { 
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    for(const item of items) {
      await connection.query('....');
    }
    await connection.commit();
  } catch (e) {
    await connection.rollback();
    throw e;
  } finally {
    await connection.release();
  }

}

This pattern has a few advantages:

1. It will correctly report errors
2. It will release the connection on success and on error.
3. It either completely fails or completely succeeds. No half-assing here.

If you don't care about transactions, this can be simplified:

async function doStuff(items) {

  try { 
    const connection = await pool.getConnection();
    for(const item of items) {
      await connection.query('....');
    }
  } finally {
    await connection.release();
  }

}

The issue with this is that you can get partial successes, which is often not desirable, especially for APIs. However, if it's good enough for you, it's good enough.

And if you are comfortable not having transactions, you can in theory completely skip the getConnection step:

async function doStuff(items) {
  for(const item of items) {
    await pool.query('....');
  }
}

The implication is that all your queries might execute on different connections. This may give you worse performance, but makes for simpler code.

How to get promises enabled?

Well this is a bit controversial. To do this, you might need to switch mysql packages. There is a mysql2 package that has a mysql2/promise import that's really excellent (and actually shares code with the more popular mysql package). It's super recommended to switch to this.

What if I don't want to switch packages?

Well, the callback version of this is a lot more painful. I would in this case recommend still using promises but converting your callbacks to a promise pattern, perhaps using 'promisify'.

If you really don't want promises anywhere, you basically have to convert my examples to callback-based ones, which is going to look a lot more painful.