#mysql2: connections and pools
Addedmysql2 is the standard MySQL driver for Node (version 3.24 here). It speaks MySQL's wire protocol over a TCP socket, has a promise API (mysql2/promise), and is what Knex, Sequelize, Drizzle and TypeORM use underneath. It gives you two ways to talk to the server: a single connection (createConnection) and a pool (createPool).
A connection is one socket and one server thread, and it runs one query at a time: send a query, wait for the result, send the next. Opening one costs a TCP handshake, authentication and often a TLS handshake. A pool keeps up to connectionLimit connections open (10 by default) and lends them out. pool.query() borrows a free connection, runs the query, and puts it back; if none is free, the request waits in a queue in your process. The mental model from Connection pooling applies: a taxi rank, where a few cabs serve a long queue because each ride is short.
In a web server you create one pool per process at startup, share it across all requests, and call pool.end() only on shutdown (see Graceful shutdown on the Node site).
// A new connection per query vs a pool, and how a pool queues work beyond its size.
import mysql from "mysql2/promise";
const config = { host: "127.0.0.1", port: 3307, user: "root", password: "study", database: "ch11_orm" };
// 1. Open, query, close — 200 times.
let t0 = performance.now();
for (let i = 0; i < 200; i++) {
const conn = await mysql.createConnection(config);
await conn.query("SELECT name FROM customers WHERE id = ?", [1 + (i % 6)]);
await conn.end();
}
console.log(`new connection per query: ${((performance.now() - t0) / 200).toFixed(2)} ms per query`);
// 1b. The same with TLS, as you'd use across a network (self-signed sandbox cert, so no verification here).
t0 = performance.now();
for (let i = 0; i < 200; i++) {
const conn = await mysql.createConnection({ ...config, ssl: { rejectUnauthorized: false } });
await conn.query("SELECT name FROM customers WHERE id = ?", [1 + (i % 6)]);
await conn.end();
}
console.log(`new TLS connection each: ${((performance.now() - t0) / 200).toFixed(2)} ms per query`);
// 2. The same 200 queries through a pool.
const pool = mysql.createPool({ ...config, connectionLimit: 2 });
t0 = performance.now();
for (let i = 0; i < 200; i++) await pool.query("SELECT name FROM customers WHERE id = ?", [1 + (i % 6)]);
console.log(`pooled: ${((performance.now() - t0) / 200).toFixed(2)} ms per query`);
// 3. Six slow queries at once on a pool of two connections.
t0 = performance.now();
const at = () => `${Math.round(performance.now() - t0)} ms`;
await Promise.all([1, 2, 3, 4, 5, 6].map(async (n) => {
const [[row]] = await pool.query("SELECT CONNECTION_ID() AS conn, SLEEP(0.2) AS s");
console.log(`query ${n} finished at ${at()} on connection ${row.conn}`);
}));
const [[{ open }]] = await pool.query(
"SELECT COUNT(*) AS open FROM performance_schema.processlist WHERE USER = 'root' AND DB = 'ch11_orm'");
console.log("connections the pool holds open:", open);
await pool.end();
// new connection per query: 0.78 ms per query
// new TLS connection each: 1.85 ms per query
// pooled: 0.12 ms per query
// query 1 finished at 203 ms on connection 2294
// query 2 finished at 203 ms on connection 2295
// query 3 finished at 405 ms on connection 2294
// query 4 finished at 405 ms on connection 2295
// query 6 finished at 607 ms on connection 2295
// query 5 finished at 607 ms on connection 2294
// connections the pool holds open: 2- Opening a fresh connection for every query costs 0.78 ms per query on localhost, and 1.85 ms with TLS. The query itself is a tiny primary-key lookup; nearly all of that time is the handshake. Across a real network, each round trip of the handshake costs a full network latency, so the gap grows to many milliseconds.
- The pool opens its connection(s) once and reuses them: 0.12 ms per query, 6 to 15 times faster, and the server isn't creating and destroying a thread for every request.
- Six
SLEEP(0.2)queries start at once on a pool withconnectionLimit: 2. Two run immediately on connections 2294 and 2295; the other four wait inside Node, in the pool's queue. They finish in pairs at 203, 405 and 607 ms: the pool limits concurrency to its size, and the rest queue rather than fail (waitForConnections: trueis the default;queueLimit: 0means the queue is unbounded). CONNECTION_ID()shows the same two server connections alternating. Eachpool.query()borrows one connection, and returns it when the result arrives, so consecutive queries from the same code can run on different connections. That's fine for independent queries and a disaster for transactions, as Transactions from Node shows.performance_schema.processlistconfirms the server sees exactly 2 connections from this pool.pool.end()closes them; without it, the open sockets keep the Node process alive after the script finishes.
query() and execute() resolve to a two-element array, which is why the examples destructure const [rows] = …:
- For a
SELECT,[rows, fields]:rowsis an array of plain objects keyed by column name (or arrays withrowsAsArray: true),fieldsdescribes the columns' types. - For
INSERT/UPDATE/DELETE,[result, undefined], whereresultis aResultSetHeaderwithaffectedRows,insertIdandchangedRows. MySQL has noRETURNING, soinsertIdis how you learn a new row'sAUTO_INCREMENTid. - For a
CALLof a stored procedure,rowsis an array with one entry per result set, followed by aResultSetHeaderfor the call itself (see Stored procedures). pool.getConnection()gives you a connection to use for several statements (a transaction); you must callconn.release()to return it.