NodeJS mysql connection pool with multiple queries

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.

Iterating over items and inserting into database is a bad option! Build an array of objects in a loop and then insert all the items using bulk insert in a single go. This way you need to call getConnection() only one time and then .release() after bulk inserting.

If this is not the case, then in interation process you need to call

1) getConnection () 2) execute the query 3) call .release()

Repeat step 1 to step 3 for all the items.

When it is a final iteration you need to call the callback() function to return the response back to the calling function.