MySQL Connection Pools: Why They Matter and How They Work

[HTTP Clients]
      |
      v
+------------------+
|  Express Routes  |
+------------------+
      |
      v  acquire()                    release()
+------------------+   in use list   -----------+
|  MySQL Pool      | <-------------- | Conn #3  |
|------------------|                 +----------+
| Free: [#1][#2]   |  getConnection() -> gives #2
| InUse:[#3][#4]   |  (if none free & < max -> create new)
| WaitQ: [R5,R6]   |  (if at max -> enqueue requester)
+------------------+
      |
      v (execute query over persistent TCP/TLS)
+------------------+
|     MySQL DB     |
+------------------+

                          +--------------------+
                          |      HTTP Clients  |
                          |  (Browsers/Mobile) |
                          +----------+---------+
                                     |
                                     | HTTP requests (burst)
                                     v
        +-------------------+   acquire()    +----------------------------------+
        |    Node.js API    +--------------->|        Connection Pool           |
        |  (Express Routes) |                |----------------------------------|
        +----------+--------+                | Free:  [c7][c8][c9]              |
                   ^                         | InUse: [c1][c2][c3][c4]          |
                   |                         | WaitQ: (req17, req18, req19...)  |
                   | release()/rows          | Max: 40  Idle Timeout: 5m        |
                   |                         +-----------+----------------------+
                   |                                     |
                   |                         reuse socket|query
                   |                                     v
                                                +------------------+
                                                |   MySQL Server   |
                                                | (Threads = Conns)|
                                                +------------------+

Flow (left to right):
1. Clients send bursts of requests.
2. Routes call `pool.getConnection()`.
3. If a free connection exists: hand it out.
4. Else if total < max: create new, add to InUse.
5. Else: enqueue request in WaitQ (backpressure).
6. Query executes over persistent connection.
7. Results returned; connection released back to Free (or to next waiter).

Legend:
[ci] = live TCP/TLS + authenticated MySQL session
Free = idle, ready
InUse = currently executing
WaitQ = queued callers waiting for a slot

Return paths (results / release) flow right -> left (omitted arrows to reduce clutter).

// Bad!
import express from 'express'
import mysql from 'mysql2/promise'

const app = express()

app.get('/users/:id', async (req, res) => {
  const conn = await mysql.createConnection({
    host: 'localhost',
    user: 'app',
    password: 'pwd',
    database: 'appdb',
  })
  try {
    const [rows] = await conn.execute('SELECT * FROM users WHERE id = ?', [req.params.id])
    res.json(rows)
  } finally {
    // Costly connection teardown.
    // CPU 📈. Performance 📉
    await conn.end()
  }
})

app.listen(3000)

// pool.js start
import mysql from 'mysql2/promise'

export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 40, // Tune
  queueLimit: 0, // 0 = unlimited queue (consider a cap)
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000,
})
// pool.js end

// index.js start
import express from 'express'
import { pool } from './db/pool'

const app = express()

app.get('/users/:id', async (req, res, next) => {
  let conn
  try {
    conn = await pool.getConnection() // Acquire
    // Removed explicit transaction for a simple SELECT
    const [rows] = await conn.execute('SELECT * FROM users WHERE id = ?', [req.params.id])
    res.json(rows)
  } catch (e) {
    next(e)
  } finally {
    conn?.release() // Critical: return to pool
  }
})

app.get('/health/db', async (_req, res) => {
  const [row] = await pool.query('SELECT 1 AS OK')
  res.json({ db: row ? 'up' : 'down' })
})

app.listen(3000)
// index.js end

import { pool } from './pool'

export async function getUser(id: string) {
  const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [id])

  return rows
}

import { pool } from '../db/pool'

export function logPoolStats() {
  const anyPool = pool
  // NOTE: These are internal properties. Test them in your environment before releasing to production.
  console.log({
    inUse: anyPool._allConnections.length - anyPool._freeConnections.length,
    free: anyPool._freeConnections.length,
    total: anyPool._allConnections.length,
    pending: anyPool._acquiringConnections.length,
  })
}

import express from 'express'
import mysql from 'mysql2/promise'
import http from 'http'

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 20,
})

const app = express()

let activeRequests = 0
let shuttingDown = false

// Track in-flight requests & block new ones during shutdown
app.use((req, res, next) => {
  if (shuttingDown) {
    res.status(503).json({ error: 'Server shutting down' })

    return
  }

  activeRequests++

  res.on('finish', () => activeRequests--)

  next()
})

// Demo route using the pool
app.get('/users/:id', async (req, res, next) => {
  let conn
  try {
    conn = await pool.getConnection()
    const [rows] = await conn.execute('SELECT * FROM users WHERE id = ?', [req.params.id])
    res.json(rows)
  } catch (e) {
    next(e)
  } finally {
    conn?.release()
  }
})

const server = http.createServer(app)

server.listen(3000, () => {
  console.log('Listening on :3000')
})

// Graceful shutdown logic
async function shutdown(signal: string) {
  if (shuttingDown) {
    return
  }

  shuttingDown = true

  console.log(`${signal} received: initiating graceful shutdown`)

  // 1. Stop accepting new connections
  server.close(() => {
    console.log('HTTP server closed')
  })

  const deadline = Date.now() + 10000 // 10s max

  // 2. Wait for in-flight requests (or timeout)
  while (activeRequests > 0 && Date.now() < deadline) {
    console.log(`Waiting: ${activeRequests} active request(s)...`)

    await new Promise((r) => setTimeout(r, 250))
  }

  if (activeRequests > 0) {
    console.warn('Forcing shutdown with active requests remaining')
  }

  // 3. Drain pool (reject further getConnection calls)
  try {
    await pool.end()

    console.log('Connection pool drained')
  } catch (e) {
    console.error('Error draining pool', e)
  }

  process.exit(0)
}

;['SIGTERM', 'SIGINT'].forEach((sig) => process.on(sig as NodeJS.Signals, () => shutdown(sig)))

process
  .on('uncaughtException', (err) => {
    console.error('uncaughtException', err)
    shutdown('uncaughtException')
  })
  .on('unhandledRejection', (reason, promise) => {
    console.error('unhandledRejection', { reason, promise })
    shutdown('unhandledRejection')
  })