Using MySQL with mysql2

MySQL and its drop-in fork MariaDB power a huge share of the web, and mysql2 is the de facto Node.js driver for talking to them. It is faster than the older mysql package, supports the modern authentication plugins MySQL 8 uses by default, and — crucially — ships a first-class Promise API alongside true server-side prepared statements. This page covers connecting, pooling, parameterizing queries, transactions, and streaming large result sets so you can use it confidently in production.

Install it from npm:

npm install mysql2

Single connections and the promise API

The callback-based core lives at mysql2, but for modern code you almost always want mysql2/promise, which returns promises you can await. A single connection is fine for scripts and one-off tasks; for servers, reach for a pool (covered below).

import mysql from 'mysql2/promise';

const conn = await mysql.createConnection({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'shop',
});

const [rows] = await conn.query('SELECT id, email FROM users WHERE active = ?', [1]);
console.log(`Found ${rows.length} active users`);
console.log(rows[0]);

await conn.end();

Output:

Found 2 active users
{ id: 1, email: '[email protected]' }

Note the destructuring: query() resolves to a [rows, fields] tuple, so const [rows] = ... grabs just the result set. With CommonJS the import is const mysql = require('mysql2/promise'); — everything else is identical.

Connection pools

Opening a connection involves a TCP handshake and authentication, so doing it per request would cripple a busy server. A pool keeps a set of reusable connections that queries borrow and return automatically. Create one pool at startup and share it across your whole app.

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'shop',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

// pool.query checks out a connection, runs the query, and returns it.
const [rows] = await pool.query('SELECT NOW() AS ts');
console.log(`Server time: ${rows[0].ts.toISOString()}`);

Output:

Server time: 2026-06-14T10:42:18.000Z

Option	Default	Purpose
connectionLimit	10	Maximum concurrent connections in the pool
waitForConnections	true	Queue requests when the pool is exhausted instead of erroring
queueLimit	0	Max queued requests (0 = unlimited)
enableKeepAlive	true	Send TCP keep-alive so idle connections survive
namedPlaceholders	false	Allow :name placeholders instead of ?

Call pool.end() during graceful shutdown to drain in-flight queries and close sockets cleanly. Never call it per request — the pool is meant to live for the entire process.

Parameterized and prepared statements

Never build SQL by concatenating user input — it invites SQL injection. mysql2 gives you two safe options. query() uses placeholder substitution: it escapes values and sends a single SQL string. execute() uses real prepared statements: the SQL template and the values travel separately, the server parses the statement once, and mysql2 caches it for reuse.

const email = "robert'); DROP TABLE users;--";

// Safe: the value is escaped, never interpreted as SQL.
const [byQuery] = await pool.query(
  'SELECT id FROM users WHERE email = ?',
  [email],
);

// Safer + faster on repeated calls: a server-side prepared statement.
const [byExecute] = await pool.execute(
  'SELECT id, email FROM users WHERE created_at > ? LIMIT ?',
  ['2026-01-01', 50],
);
console.log(`${byExecute.length} recent users`);

Output:

12 recent users

Prefer execute() for queries you run repeatedly with different values; the prepared-statement cache pays off. With namedPlaceholders: true you can pass an object instead of an array:

const [rows] = await pool.execute(
  'SELECT id FROM users WHERE country = :country AND active = :active',
  { country: 'CA', active: 1 },
);

Transactions

A transaction groups several statements so they either all commit or all roll back. Check out a single connection from the pool with getConnection() so every statement runs on the same session, then always release it in a finally block.

const conn = await pool.getConnection();
try {
  await conn.beginTransaction();

  await conn.execute(
    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
    [100, 1],
  );
  await conn.execute(
    'UPDATE accounts SET balance = balance + ? WHERE id = ?',
    [100, 2],
  );

  await conn.commit();
  console.log('Transfer committed');
} catch (err) {
  await conn.rollback();
  console.error('Transfer rolled back:', err.message);
  throw err;
} finally {
  conn.release(); // returns the connection to the pool
}

Output:

Transfer committed

Streaming large result sets

Loading millions of rows into memory at once can exhaust your heap. Instead of await-ing the full array, use the callback connection’s .stream() to get a Node Readable and process rows as they arrive. Backpressure is handled for you — the driver pauses fetching when the consumer is slow.

import mysql from 'mysql2';

const conn = mysql.createConnection({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'shop',
});

const stream = conn.query('SELECT id, total FROM orders').stream();

let count = 0;
let revenue = 0;
for await (const row of stream) {
  count += 1;
  revenue += row.total;
}
console.log(`Processed ${count} orders, total revenue ${revenue}`);

conn.end();

Output:

Processed 1284 orders, total revenue 98430.5

The for await...of loop works because the stream is an async iterable, so you get clean async/await syntax with constant memory usage regardless of result size.

Best Practices

• Use mysql2/promise and async/await for application code; reach for the callback API only when you need .stream().
• Create one pool at startup, share it everywhere, and call pool.end() on shutdown — never connect per request.
• Always pass values as ? placeholders; prefer execute() for repeated queries to benefit from server-side prepared statements.
• Run multi-statement transactions on a single getConnection() and release it in a finally block, even on error.
• Stream large SELECTs with .stream() and for await...of to keep memory flat.
• Keep credentials in environment variables (node --env-file=.env app.js), never in source control.
• Pin to an active LTS release (Node 20 or 22) so the driver’s async APIs stay stable.

Related Topics

• Database Connectivity in Node.js
• PostgreSQL with Node.js
• Connection pooling
• Transactions