Use a bounded connection pool

A pool avoids repeated connection setup and queues work when all its connections are busy.

Count the pool size across every application process. Ten replicas with a pool of ten can open one hundred connections, before workers, migrations, and administration.

A transaction must stay on one checked-out connection:

const connection = await pool.getConnection()

try {
  await connection.beginTransaction()

  const [note] = await connection.execute(
    'INSERT INTO notes (title) VALUES (?)',
    ['Plan the week']
  )

  await connection.execute(
    'INSERT INTO note_tags (note_id, tag_id) VALUES (?, ?)',
    [note.insertId, 1]
  )

  await connection.commit()
} catch (error) {
  await connection.rollback()
  throw error
} finally {
  connection.release()
}

Do not call pool.execute() inside that transaction. The pool may choose another connection. Always roll back after an error and release in finally.