MySQL Connection Pooling in Node.js with mysql2: Sizing, Transactions and Leaks

Most Node.js + MySQL performance problems I've debugged weren't slow queries. They were connection problems: requests hanging for no obvious reason, Too many connections errors after a deploy, or random PROTOCOL_CONNECTION_LOST crashes at 3 a.m.

Nearly all of them come back to the connection pool: how it's configured, how big it is, and whether the code returns connections to it. This guide covers all three, using mysql2, the most widely used MySQL driver for Node.js.

Why you need a pool#

Opening a MySQL connection is expensive: a TCP handshake, often a TLS handshake, then MySQL's authentication exchange. That's several network round trips before your query even starts. Doing it on every request adds latency, and under load it can overwhelm the server: MySQL has a hard cap on simultaneous connections (max_connections, 151 by default).

A pool opens connections once and reuses them:

1. A request asks the pool for a connection.
2. If an idle one exists, it's handed over immediately. If not, and the pool is below its limit, a new connection is opened.
3. If the pool is at its limit, the request waits in a queue until another request releases one.
4. When the query finishes, the connection goes back to the pool, still open, ready for the next request.

Setting up the pool#

npm install mysql2

import mysql from "mysql2/promise";

export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT ?? 3306),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,

  connectionLimit: 10,       // max open connections from THIS process
  waitForConnections: true,  // queue requests when all are busy (instead of erroring)
  queueLimit: 0,             // 0 = unlimited queue (see below)
  maxIdle: 10,               // max idle connections kept open
  idleTimeout: 60_000,       // close idle connections after 60s
  enableKeepAlive: true,     // TCP keep-alive so dead connections are detected
  keepAliveInitialDelay: 0,
});

Create the pool once per process, at module level, and import it everywhere. Creating a pool inside a request handler is one of the most common mistakes: every request then makes a brand-new pool, which defeats the purpose and leaks connections.

What each option does in practice:

• connectionLimit: the pool's upper bound. This is the number you'll size carefully below.
• waitForConnections: true: when all connections are busy, requests wait their turn. With false, they fail immediately, which is rarely what you want for a web API.
• queueLimit: how many requests may wait. 0 means unlimited, which can hide problems: if the database slows down, the queue grows silently and requests just get slower. A finite limit (say 100) makes the pool fail fast with an error you can see and alert on.
• maxIdle + idleTimeout: after a traffic spike, close connections you no longer need instead of holding them forever.
• enableKeepAlive: firewalls, NAT gateways and load balancers silently drop idle TCP connections. Keep-alive probes stop the pool from handing you a connection that's already dead.

Querying: execute vs query#

import { pool } from "./db.js";

// execute(): prepared statement, values sent separately from the SQL
const [rows] = await pool.execute(
  "SELECT id, name, price FROM products WHERE category_id = ? AND price < ?",
  [categoryId, maxPrice],
);

// query(): values are escaped client-side and inlined into the SQL
const [result] = await pool.query("UPDATE products SET views = views + 1 WHERE id = ?", [id]);

Both methods take a connection from the pool, run the query, and release the connection automatically. For single queries, always use pool.execute() / pool.query() directly; you never have to think about releasing.

Both are safe from SQL injection as long as you use ? placeholders. Never build SQL with template strings:

// ❌ SQL injection: whatever is in `email` becomes part of the SQL
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

// ✅ placeholder: `email` is always treated as a value
await pool.execute("SELECT * FROM users WHERE email = ?", [email]);

Which to prefer? execute() uses real prepared statements, and mysql2 caches them per connection, so a query you run often is parsed once by MySQL rather than every time. I default to execute(). One known gotcha: with execute() on MySQL 8.0.22+, passing a JavaScript number to LIMIT ? can fail with Incorrect arguments to mysqld_stmt_execute. Pass the value as a string (String(limit)), or use query() for that statement.

Transactions: the bug almost everyone writes once#

Here's code that looks correct and isn't:

// ❌ BROKEN: each pool.query() may run on a DIFFERENT connection
await pool.query("START TRANSACTION");
await pool.query("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, fromId]);
await pool.query("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, toId]);
await pool.query("COMMIT");

A transaction belongs to one connection. pool.query() picks any free connection each time, so START TRANSACTION might run on connection A, the updates on B and C, and COMMIT on D. Nothing is actually transactional, and connection A is left with an open transaction that the next unlucky request inherits.

For transactions, check out one connection and use it for every statement:

import { pool } from "./db.js";

export async function transferStock(fromWarehouse, toWarehouse, productId, qty) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    // Lock the source row so two transfers can't both spend the same stock
    const [rows] = await conn.execute(
      "SELECT quantity FROM stock WHERE warehouse_id = ? AND product_id = ? FOR UPDATE",
      [fromWarehouse, productId],
    );
    if (!rows.length || rows[0].quantity < qty) {
      throw new Error("Insufficient stock");
    }

    await conn.execute(
      "UPDATE stock SET quantity = quantity - ? WHERE warehouse_id = ? AND product_id = ?",
      [qty, fromWarehouse, productId],
    );
    await conn.execute(
      "UPDATE stock SET quantity = quantity + ? WHERE warehouse_id = ? AND product_id = ?",
      [qty, toWarehouse, productId],
    );

    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release(); // ALWAYS: success, failure or thrown error
  }
}

The finally { conn.release() } is the most important line in the file. Since you'll write this shape often, wrap it once:

export async function withTransaction(work) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    const result = await work(conn);
    await conn.commit();
    return result;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

// Usage
await withTransaction(async (conn) => {
  await conn.execute("INSERT INTO orders (customer_id, total) VALUES (?, ?)", [customerId, total]);
  await conn.execute("UPDATE customers SET order_count = order_count + 1 WHERE id = ?", [customerId]);
});

Now nobody on the team can forget to commit, roll back or release.

Connection leaks: when requests "just hang"#

A leak is a connection taken with getConnection() and never released. The pool thinks it's still in use, forever. Leak ten and with connectionLimit: 10 the pool is empty: every new request joins the queue and waits for a connection that will never come back. No error, no crash, just requests that hang until they time out.

Typical causes:

• An early return between getConnection() and release()
• An exception thrown before release() without a finally
• getConnection() used for a single query that pool.execute() would have handled

Two habits catch leaks early:

1. Log when the pool is exhausted. mysql2's pool emits an enqueue event when a request has to wait:

pool.on("enqueue", () => {
  console.warn("MySQL pool exhausted: a request is waiting for a free connection");
});

An occasional warning under a traffic spike is normal. A steady stream, or warnings at low traffic, means a leak or a pool that's too small.

2. Search the code. Every getConnection() should have a matching release() in a finally block. Better still, allow getConnection() only inside a helper like withTransaction, and use pool.execute() everywhere else.

Sizing the pool: bigger is not faster#

It's tempting to set connectionLimit: 100 "to be safe." Don't. A MySQL server can only execute about as many queries at once as it has CPU cores; past that, connections just wait inside MySQL instead of inside your pool, while each one costs memory on the server. A widely used starting point (from the HikariCP pool sizing guide) for the total connections across all your app instances is:

connections ≈ (database CPU cores × 2) + effective disk spindles

For a 4-core database with SSDs, that's roughly 10, in total. Treat it as a starting point and measure, but it shows why small pools often outperform big ones.

The multiplication problem#

Here's where most production Too many connections errors come from. connectionLimit is per pool, and you have one pool per process:

total = connectionLimit × workers per container × containers (or servers)

This is why the Node.js clustering guide and clustering in Docker both warn about databases: scaling your app multiplies connections. Work backwards instead:

1. Decide the total your database should handle, comfortably under max_connections (leave room for admin tools, migrations and deploy overlap).
2. Divide by the total number of processes at peak, including during deploys.
3. That's your connectionLimit. For clustered apps it's often just 2–5 per process.

Check what the server actually sees:

mysql -e "SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections';"

Max_used_connections is the high-water mark since the server started. If it's close to max_connections, you're one traffic spike away from errors.

Stale connections and PROTOCOL_CONNECTION_LOST#

MySQL closes connections that sit idle longer than wait_timeout (8 hours by default). Network devices are less patient: NAT gateways and load balancers can drop idle TCP connections after just a few minutes, often without telling either side. The pool then hands your request a connection that's already dead, and it fails with PROTOCOL_CONNECTION_LOST or ECONNRESET.

The defence is the options from the setup section:

• idleTimeout shorter than every idle limit in the path (MySQL's wait_timeout, your load balancer, your NAT gateway), so the pool closes idle connections before anything else does
• enableKeepAlive: true, so the OS detects dead peers

Graceful shutdown#

On shutdown, close the pool so in-flight queries finish and MySQL isn't left holding abandoned connections:

process.on("SIGTERM", async () => {
  server.close();     // stop accepting HTTP requests
  await pool.end();   // wait for active queries, then close every connection
  process.exit(0);
});

In a clustered app, do this in each worker's shutdown path; the clustering guide shows where.

Special cases#

Next.js and other hot-reloading dev servers. Every reload re-runs db.js, creating a new pool while the old one stays open, until MySQL runs out of connections. Cache the pool on globalThis in development:

import mysql, { type Pool } from "mysql2/promise";

const globalForDb = globalThis as unknown as { mysqlPool?: Pool };

export const pool =
  globalForDb.mysqlPool ??
  mysql.createPool({ uri: process.env.DATABASE_URL, connectionLimit: 5 });

if (process.env.NODE_ENV !== "production") globalForDb.mysqlPool = pool;

Serverless (AWS Lambda, Vercel functions). Each function instance gets its own pool, and a traffic burst can start hundreds of instances, each opening connections. Keep connectionLimit at 1–2 per instance and put a connection proxy such as Amazon RDS Proxy in between, which pools connections on the database side across all instances.

Checklist#

• ✅ One pool per process, created at module level
• ✅ pool.execute() with ? placeholders for everyday queries
• ✅ Transactions on a single connection from getConnection(), released in finally, ideally via a withTransaction helper
• ✅ A finite queueLimit and an enqueue warning, so exhaustion is visible
• ✅ connectionLimit × processes × instances stays well under max_connections, including during deploys
• ✅ idleTimeout shorter than every idle timeout in the network path, plus enableKeepAlive
• ✅ await pool.end() on shutdown

A connection pool is a small piece of configuration that decides whether your app degrades gracefully or falls over under load. Get the size right, always release, and keep transactions on one connection, and most of those 3 a.m. database errors never happen.