Connection Pooling and Query Safety

Every database query needs a network connection to the database server, and opening one is expensive: a TCP handshake, then an authentication handshake, then (for TLS) a certificate negotiation — several round trips before a single query even runs. A connection pool opens a batch of connections up front and hands them out to your code on demand, so a typical request reuses an already-authenticated connection instead of paying that setup cost every time. Alongside pooling, query safety means never building SQL out of untrusted string concatenation — using parameterized queries so user input can never change the structure of your SQL. This lesson covers both together, because in Node.js they show up in the same few lines of driver code.

Overview: How Connection Pooling Works

Node.js is single-threaded for your JavaScript, but I/O — including database sockets — is handled asynchronously by libuv’s event loop, using the OS’s non-blocking socket APIs (epoll/kqueue/IOCP), not the libuv thread pool (that pool is reserved for things like DNS lookups and filesystem calls). A database driver such as mysql2 opens a real TCP socket per connection and multiplexes queries over it using events under the hood, but each individual connection can only run one query at a time — MySQL’s wire protocol is not concurrent per connection.

That’s the core reason pools exist: if your HTTP server handles requests concurrently (which Node does naturally), and each request needs a query, a single shared connection would force every request to queue behind the others. A pool keeps a small set of live connections — say 10 — and lends one out to whichever request needs it, queues additional requests when all are busy, and returns each connection to the pool when the query finishes. This gives you real concurrency up to the pool size, while still capping how many simultaneous connections hit the database server (which has its own connection limit, often in the low hundreds).

Pools also handle connection health: a stale or dropped connection is discarded and replaced rather than reused, and idle connections can be recycled after a timeout. You create the pool once, when your process starts, and reuse it for the lifetime of the app — never create a new pool per request.

Query safety in the same breath

The other half of this lesson is what you put inside those queries. If you build SQL text by concatenating user input directly into a string, an attacker who controls that input can change the meaning of the query entirely — this is SQL injection, and it remains one of the most common real-world web vulnerabilities. The fix is parameterized queries: you write the SQL with placeholders (?) and pass the values separately. The driver sends the values through a channel that can never be interpreted as SQL syntax, no matter what characters they contain.

Syntax

Using the popular mysql2 driver’s promise API (works the same way in structure for other drivers like pg):

npm install mysql2 dotenv
  • mysql.createPool(options) — creates the pool once; options.connectionLimit caps concurrent connections, options.waitForConnections queues extra requests instead of throwing.
  • pool.execute(sql, values) — runs a query as a server-side prepared statement; ? placeholders are filled from the values array, in order. Returns a promise resolving to [rows, fields].
  • pool.query(sql, values) — similar, but sends a plain (client-escaped) string rather than a true prepared statement; still safe against injection when you use ? placeholders and pass values.
  • pool.getConnection() — checks out a single connection for multi-statement work (transactions); you must call connection.release() when done.
  • pool.end() — gracefully closes every connection in the pool; call it on process shutdown.
Pool option Meaning
connectionLimit Maximum simultaneous connections (default 10)
waitForConnections If true, callers wait in a queue when the pool is full instead of erroring
queueLimit Maximum queued requests before erroring (0 = unlimited)
enableKeepAlive Sends TCP keep-alive packets so idle connections aren’t silently dropped
idleTimeout Milliseconds an idle connection is kept before being closed

Examples

Example 1: Creating a shared pool module

Create the pool once in its own module and export it, so every part of the app imports the same instance instead of creating new ones.

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.DB_HOST ?? "localhost",
  user: process.env.DB_USER ?? "app_user",
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME ?? "shop",
  connectionLimit: 10,
  waitForConnections: true,
  queueLimit: 0,
  enableKeepAlive: true,
});

export default pool;

Run this with node --env-file=.env app.js (Node 20.6+) so DB_PASSWORD and friends come from a .env file rather than being hardcoded. The pool doesn’t actually open any sockets until the first query is run; connections are created lazily, up to connectionLimit.

Example 2: A parameterized query

import pool from "./db.js";

async function findUserByEmail(email) {
  const [rows] = await pool.execute(
    "SELECT id, email, created_at FROM users WHERE email = ?",
    [email]
  );
  return rows[0] ?? null;
}

async function main() {
  try {
    const user = await findUserByEmail("ada@example.com");
    console.log(user);
  } catch (err) {
    console.error("Query failed:", err.message);
  } finally {
    await pool.end();
  }
}

main();

{ id: 1, email: 'ada@example.com', created_at: 2026-01-10T09:00:00.000Z }

The ? in the SQL string is a placeholder; the single value in the array fills it. mysql2 sends the query text and the value separately using MySQL’s binary prepared-statement protocol, so the value is never parsed as SQL — even if email were the string ' OR '1'='1, it would just fail to match any row, not alter the query’s logic. pool.execute also caches the prepared statement on the connection, so repeated calls with the same SQL shape skip re-parsing on the server.

Example 3: A transaction using a checked-out connection

Pooled one-off queries via pool.execute each get whatever connection is free, which is wrong for a multi-step transaction — all the statements must run on the same connection. Use pool.getConnection() to reserve one:

import pool from "./db.js";

async function transferCredits(fromId, toId, amount) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    const [rows] = await connection.execute(
      "SELECT credits FROM accounts WHERE id = ? FOR UPDATE",
      [fromId]
    );
    const sender = rows[0];
    if (!sender || sender.credits < amount) {
      throw new Error("Insufficient credits");
    }

    await connection.execute(
      "UPDATE accounts SET credits = credits - ? WHERE id = ?",
      [amount, fromId]
    );
    await connection.execute(
      "UPDATE accounts SET credits = credits + ? WHERE id = ?",
      [amount, toId]
    );

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

export default transferCredits;

Every path — success or failure — reaches connection.release() because it's in the finally block. FOR UPDATE locks the selected row until the transaction ends, preventing a second concurrent transfer from reading a stale balance. If anything throws, rollback() undoes both updates so the account balances never end up inconsistent.

Under the Hood: How a Pooled Query Executes

  1. Your code calls pool.execute(sql, values). Synchronously, the pool checks its idle-connection list.
  2. If an idle connection exists, it's handed out immediately (still asynchronously resolved as a microtask). If none is idle and the pool is below connectionLimit, a new TCP connection is opened. If the pool is already at its limit, the call is queued (when waitForConnections is true) until a connection frees up.
  3. The driver writes the prepared-statement request to the socket and returns control to the event loop — no thread is blocked waiting.
  4. When the database responds, libuv's socket-readiness notification fires, the driver parses the binary response into row objects, and the pending promise resolves — this happens on a future turn of the event loop, after any currently running synchronous code and any pending microtasks.
  5. The connection is automatically returned to the pool's idle list once execute() resolves (for a plain query) — you only need to call release() yourself after an explicit getConnection().

None of this blocks the event loop: the wait for the network round-trip is entirely asynchronous, which is why a single Node process can juggle far more concurrent queries than it has pool connections queued behind them.

Common Mistakes

Mistake 1: Building SQL with string concatenation

// DO NOT DO THIS - vulnerable to SQL injection
async function findUserByEmailUnsafe(email) {
  const [rows] = await pool.query(
    "SELECT id, email FROM users WHERE email = '" + email + "'"
  );
  return rows[0] ?? null;
}

If email arrives as ' OR '1'='1, the finished query becomes WHERE email = '' OR '1'='1', which matches every row in the table — an attacker just dumped your entire users table through a login form. The fix is always the same: never interpolate values into the SQL string, use placeholders instead.

async function findUserByEmailSafe(email) {
  const [rows] = await pool.execute(
    "SELECT id, email FROM users WHERE email = ?",
    [email]
  );
  return rows[0] ?? null;
}

Note that ? placeholders only work for values, never for table or column names. If a dynamic identifier is unavoidable (like a sort column chosen by the user), validate it against an allowlist of known-safe strings — don't pass it through a placeholder or concatenate it directly.

Mistake 2: Forgetting to release a checked-out connection

async function leaky() {
  const connection = await pool.getConnection();
  const [rows] = await connection.execute("SELECT NOW() AS now");
  return rows[0];
  // connection.release() is never called - it leaks out of the pool
}

Every call to leaky() permanently removes one connection from the pool's rotation. After connectionLimit calls, every future query hangs forever waiting for a connection that will never come back. Always wrap the work in try/finally and release in the finally block, as shown in Example 3, so the connection is returned even when an error is thrown.

Mistake 3: Creating a new pool per request

Calling mysql.createPool() inside a route handler creates a fresh batch of connections on every request and never reuses or closes the old ones, quickly exhausting the database server's connection limit. Create the pool once at module load time (Example 1) and import that single instance everywhere.

Best Practices

  • Create exactly one pool per process, at startup, and export it from a shared module.
  • Always use ? placeholders with a values array — never template literals or + concatenation — for anything containing user input.
  • Prefer pool.execute() over pool.query() for parameterized queries; it uses true prepared statements and caches them per connection.
  • Set connectionLimit based on your database server's max connections divided across all app instances, not an arbitrarily large number.
  • Always pair getConnection() with a try/finally that calls release().
  • Call pool.end() on graceful shutdown (e.g. on SIGTERM) so the process doesn't hang with open sockets.
  • Never log full query error objects that might contain the connection string or credentials — log err.message and the query name, not raw err.
  • Keep secrets out of code — load them from environment variables via .env or --env-file, never hardcoded.

Practice Exercises

  • Set up a mysql2/promise pool against a local database and write a function listOrdersForUser(userId) that returns all orders for a given user using a parameterized query.
  • Take the unsafe findUserByEmailUnsafe example and rewrite it defensively; then write a short comment explaining what input would have broken the unsafe version.
  • Write a function that transfers credits between two accounts (like Example 3) but add a check that throws if amount is not a positive integer, and confirm via a manual test that a thrown error still results in rollback() being called and the connection being released.

Summary

  • Opening a database connection is expensive (TCP + auth handshakes); a pool reuses a fixed set of connections instead of paying that cost per query.
  • Create one pool per process at startup; never create a pool inside a request handler.
  • pool.execute()/pool.query() hand a connection out and return it automatically; pool.getConnection() reserves one for transactions and must be released manually in a finally block.
  • SQL injection comes from building queries with string concatenation; parameterized queries with ? placeholders eliminate the risk because values are never parsed as SQL syntax.
  • Placeholders work for values only, never for table/column identifiers — validate those against an allowlist instead.
  • None of this blocks the event loop: pooled queries resolve asynchronously as the database responds, letting one process handle many concurrent queries with a small pool.