mysql2

What it is

mysql2 is the dominant pure-JavaScript MySQL / MariaDB client for Node. It is a drop-in faster successor to the older mysql package, with a built-in Promise interface, support for prepared statements (the MySQL binary protocol), and a Pool / PoolCluster API. It also speaks MariaDB and tolerates most of its protocol extensions.

Reach for mysql2 when you want low-level SQL against MySQL / MariaDB with predictable performance and full access to driver knobs (connection limits, SSL, JSON parsing, multi-statement queries). Layer kysely, drizzle-orm, prisma, or sequelize on top when you want a query builder or ORM.

Install

npm install mysql2

Output: added mysql2 to dependencies

pnpm add mysql2

Output: added 1 package, linked from store

yarn add mysql2

Output: added mysql2

bun add mysql2

Output: installed mysql2

Types ship in-tree — no @types/mysql2 install needed.

Versioning & Node support

The current line is mysql2@3.x.

• mysql2@3 — Node 14+. Promise API moved out of /promise import (still works for backwards-compat). Stricter type definitions. AbortController support.
• mysql2@2 — Node 8+. Historical; still works but missing recent driver fixes.
• mysql2@1 — Legacy.

mysql2 follows semver carefully and is on a slow major cadence. Pin minor in production ("mysql2": "3.x").

Package metadata

• Maintainer: sidorares + community (sidorares/node-mysql2)
• Project home: github.com/sidorares/node-mysql2
• Docs: sidorares.github.io/node-mysql2
• npm: npmjs.com/package/mysql2
• License: MIT
• First released: 2014
• Downloads: ~5 million+ weekly downloads — has surpassed the legacy mysql package on most projects.

Peer dependencies & extras

No peer deps. Optional companions:

• mysql2/promise — the Promise API (same package, different import path)
• mariadb — alternative driver from MariaDB Corp; separate package with its own API
• mssql, pg, sqlite3 — sibling drivers for other databases
• kysely, drizzle-orm, prisma, sequelize, typeorm, mikro-orm — ORM / query-builder layers

The package bundles its own connection pool — no mysql2-pool separate install.

Alternatives

Real-world recipes

Pool + Promise API

The Promise-based pool is the right default. Import from mysql2/promise to skip the util.promisify ritual.

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DB,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

const [rows] = await pool.query("SELECT NOW() AS now");
console.log(rows[0].now);

await pool.end();

Output: prints the server's current DATETIME. Pool drains on end().

The [rows] destructure unwraps the tuple [rows, fields] — fields is column metadata, usually ignored.

Prepared statements

Use execute instead of query to take the prepared-statement path — MySQL caches the plan, and parameters are sent over the binary protocol (no string interpolation).

import mysql from "mysql2/promise";

const pool = mysql.createPool(/* … */);

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

console.log(await findUser("alice@example.com"));

Output: { id: 'u-123', email: 'alice@example.com' } or null. execute reuses the plan on subsequent calls with the same SQL text.

Transaction

Transactions need a dedicated connection from the pool. Always release the connection in finally.

import mysql from "mysql2/promise";

const pool = mysql.createPool(/* … */);

async function transfer(from, to, amount) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    await conn.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from]);
    await conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to]);
    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

Output: both updates apply atomically; rollback discards both on failure.

JSON column

MySQL 5.7+ and MariaDB 10.2+ support a native JSON column. mysql2 parses and serializes automatically.

import mysql from "mysql2/promise";

const pool = mysql.createPool(/* … */);

await pool.execute(
  "INSERT INTO documents (id, payload) VALUES (?, ?)",
  ["doc-1", JSON.stringify({ tags: ["draft"], score: 0.92 })]
);

const [rows] = await pool.execute(
  "SELECT id, payload, JSON_EXTRACT(payload, '$.score') AS score FROM documents WHERE id = ?",
  ["doc-1"]
);
console.log(rows[0]);

Output: { id: 'doc-1', payload: { tags: ['draft'], score: 0.92 }, score: 0.92 }. mysql2 parses the JSON column on read.

Stringify on write (mysql2 does not auto-stringify). Use the typeCast option if you need a different parsing strategy.

Streaming a large result set

For wide queries you don't want to materialize in memory, mysql2 supports streaming row-by-row.

import mysql from "mysql2";

const conn = mysql.createConnection({ /* … */ });

const stream = conn.query("SELECT id, name FROM big_table");

stream
  .on("result", (row) => console.log(row.id))
  .on("end", () => conn.end());

Output: rows print as they arrive; memory stays bounded; suitable for ETL jobs reading millions of rows.

The streaming API uses the callback driver, not mysql2/promise. Mix freely in the same project.

Production deployment

Pool sizing

connectionLimit should match server capacity divided by app replicas. MySQL's default max_connections is 151 — a single 10-replica cluster with connectionLimit: 10 saturates it immediately. Either raise max_connections (MySQL config) or front with ProxySQL.

const pool = mysql.createPool({
  connectionLimit: 10,
  waitForConnections: true, // queue when exhausted, don't throw
  queueLimit: 100,          // bound the queue; reject beyond this
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});

Output: stable connections under load; rejects clients fast when overwhelmed instead of holding them open.

ProxySQL or PlanetScale

For horizontal scaling, run ProxySQL in front of MySQL and connect mysql2 to ProxySQL. PlanetScale's Vitess proxy is similar — it presents a MySQL endpoint and shards behind the scenes.

ProxySQL multiplexes thousands of incoming connections onto a small backend pool — analogous to PgBouncer for Postgres.

TLS

Production MySQL should require SSL/TLS:

import fs from "node:fs";

const pool = mysql.createPool({
  ssl: {
    ca: fs.readFileSync(process.env.MYSQL_CA),
    rejectUnauthorized: true,
  },
});

Output: TLS handshake fails if the server cert doesn't chain to the supplied CA.

For managed services (RDS, PlanetScale, Cloud SQL), download the CA bundle from the provider and pin it.

Graceful shutdown

for (const sig of ["SIGTERM", "SIGINT"]) {
  process.on(sig, async () => {
    await pool.end();
    process.exit(0);
  });
}

Output: drains in-flight queries; rejects new checkouts; clean exit.

Performance tuning

• Use execute not query. execute uses the binary protocol; query uses text. Binary saves CPU on both ends.
• rowsAsArray: true. Returns each row as an array instead of an object. Twice as fast for wide rows you never name-lookup.
• decimalNumbers: true if you accept floating-point loss — by default mysql2 returns DECIMAL as strings to preserve precision.
• Tune connectionLimit. More isn't better; measure with pool.pool.threadId or expose pool.config.
• bigNumberStrings: true to keep BIGINT as strings (JS Number only holds 53 bits safely).
• Prepared-statement cache. MySQL caches plans per connection; long-lived pool connections benefit. Avoid creating new connections per request.
• multipleStatements: false by default — keep it off in production. Multi-statement queries are an SQL-injection multiplier.
• JSON aggregation. For wide-fan-out reads, use JSON_OBJECT / JSON_ARRAYAGG server-side and parse once on the client.

Version migration guide

From mysql to mysql2

mysql2 is API-compatible with the legacy mysql package for most calls — query, escape, pool methods. The main migrations:

1. require('mysql') → require('mysql2') or import mysql from "mysql2".
2. To get the Promise API, switch to mysql2/promise.
3. escapeId and escape still exist; ? and ?? placeholders work identically.
4. mysql2's execute is new — adopt it for prepared statements.
5. mysql2 returns RowDataPacket[] objects with a hidden prototype; if you JSON.stringify(row) you get the data, but row instanceof Object is true with extra metadata. Avoid Object.keys if you need exact own-property enumeration.

npm uninstall mysql && npm install mysql2

Output: dep swap; in source, search-and-replace 'mysql' → 'mysql2'.

From mysql2@2 to mysql2@3

1. Node 14+ floor.
2. Type definitions tightened — TypeScript projects may surface new errors.
3. Some deprecated callback APIs removed.
4. Pool option defaults adjusted; verify connectionLimit is explicit.

Security considerations

• Always use ? placeholders. Never interpolate user input into SQL strings. mysql2 binds via the binary protocol when you use execute.
• multipleStatements: false. Enable only when running migrations or scripts where the SQL is fully under your control.
• TLS required in production. ssl: { rejectUnauthorized: true, ca: ... }. Self-signed acceptance defeats TLS.
• Least-privilege role. App uses a role with SELECT/INSERT/UPDATE/DELETE on its tables — never GRANT ALL.
• identifier interpolation. mysql2's ?? placeholder safely quotes identifiers. Use it instead of building names by string concat.
• Don't expose errno / sqlMessage to clients. Wrap errors and emit a sanitized envelope; raw messages leak schema details.
• Audit LOAD DATA LOCAL INFILE. Disabled by default in mysql2 v3; verify flags doesn't enable it on connections you don't control.
• Bound bigNumberStrings carefully. Mixing string-mode BIGINTs with arithmetic gives wrong results silently.

Testing & CI integration

For unit tests, options are: real MySQL in Docker, in-memory sql.js (SQLite-compatible only), or service containers in CI.

testcontainers — best fidelity:

import { GenericContainer } from "testcontainers";

const container = await new GenericContainer("mysql:8")
  .withEnvironment({
    MYSQL_ROOT_PASSWORD: "test",
    MYSQL_DATABASE: "test",
  })
  .withExposedPorts(3306)
  .start();

Output: ephemeral MySQL; full feature parity; tears down after the suite.

GitHub Actions service container:

services:
  mysql:
    image: mysql:8
    env:
      MYSQL_ROOT_PASSWORD: test
      MYSQL_DATABASE: test
    ports: ["3306:3306"]
    options: >-
      --health-cmd "mysqladmin ping -ptest" --health-interval 10s
      --health-timeout 5s --health-retries 10

For pure-unit tests, mock at the mysql2 boundary with sinon or use a small interface in your data-access layer.

Ecosystem integrations

Troubleshooting common errors

ER_NOT_SUPPORTED_AUTH_MODE — MySQL 8 default auth is caching_sha2_password; older mysql2 versions didn't support it. Upgrade mysql2 to current 3.x, or change the MySQL user to mysql_native_password.

PROTOCOL_CONNECTION_LOST — connection idle-killed by the server (wait_timeout). The pool replaces it on next checkout. For long-running scripts using a single connection, set enableKeepAlive: true.

ER_ACCESS_DENIED_ERROR — wrong credentials or host mismatch. MySQL ties users to host: user@'%' is different from user@'localhost'.

ER_BAD_FIELD_ERROR — column doesn't exist. Often a typo; MySQL is case-insensitive on Linux only if lower_case_table_names = 1.

ER_DUP_ENTRY — unique constraint violation. Catch and translate to a domain error.

ER_LOCK_WAIT_TIMEOUT / ER_LOCK_DEADLOCK — deadlock or lock-wait timeout. Retry with backoff for idempotent operations.

ER_DATA_TOO_LONG — value exceeds column size. Validate before insert or widen the column.

Pool is closed — using a pool after end(). Audit lifecycle; tests often hit this.

Hanging queries / pool exhausted — connections not released. Audit finally { conn.release() } blocks.

ER_CON_COUNT_ERROR: too many connections — pool max summed across replicas exceeds max_connections. Lower connectionLimit or raise the server limit.

When NOT to use this

• Edge runtimes. mysql2 uses Node's net module — won't run on Cloudflare Workers / Vercel Edge directly. Use PlanetScale's HTTP-based serverless driver (@planetscale/database) instead.
• You want an ORM, not a driver. Reach for Prisma / Drizzle / Sequelize and let them wrap mysql2.
• MariaDB-specific features. The official mariadb driver supports protocol features mysql2 doesn't (PIPELINE, MARIADB SkySQL extensions). Most apps don't need them.
• Sharded scale-out. Vitess / PlanetScale provide MySQL-compatible endpoints with sharding built in. mysql2 talks to them, but you may want their tooling on top.
• You only need SQLite-like ergonomics. For small apps, better-sqlite3 is far simpler.

See also

• JavaScript: Node runtime — async I/O, process model
• Concept: async — promise pipelines, backpressure
• Concept: api — driver vs ORM boundaries