Node.js with MySQL

MySQL is one of the most widely used relational databases, and Node.js does not ship with a built-in client for it — you talk to MySQL through a driver package installed from npm. This lesson covers connecting to MySQL from Node with the mysql2 package, running parameterized queries safely, using connection pools the way a real server should, and wrapping multi-step writes in transactions. By the end you will be able to build a Node service that reads and writes MySQL data correctly, securely, and efficiently.

Overview: How Node talks to MySQL

Unlike fs or http, MySQL access is not part of core Node — you install a driver. The most widely used one today is mysql2, a fast, actively maintained package that is largely compatible with the older mysql package’s API but adds native Promise support, prepared statements, and better performance. Under the hood, the driver opens a TCP socket to the MySQL server, performs the MySQL authentication handshake, then sends queries as binary protocol packets and parses the rows that come back into JavaScript values.

This is network I/O, and Node handles it the same way it handles any socket: asynchronously, through libuv’s event-driven socket watchers (backed by epoll/kqueue/IOCP depending on the OS) rather than the fixed-size thread pool that fs uses for file operations. That means a single Node process can have thousands of MySQL queries in flight at once without blocking the event loop — as long as your code awaits them properly and never runs a long synchronous computation in between.

You interact with MySQL in one of two connection shapes:

• A single connection (mysql.createConnection) — fine for a one-off script, a migration, or a CLI tool that opens, does its work, and exits.
• A connection pool (mysql.createPool) — the correct choice for any long-running server. A pool keeps a set of already-authenticated connections open and hands them out to your queries on demand, queuing extra requests when all connections are busy instead of paying the cost of a fresh TCP handshake and login on every request.

mysql2 also distinguishes query from execute. query() sends the SQL text with placeholder values escaped and substituted on the client before sending. execute() sends a real MySQL prepared statement: the SQL is parsed and compiled by the server once, then reused with different parameter values on subsequent calls, which is both faster for repeated queries and immune to SQL injection because parameters are never concatenated into the SQL string at all.

Syntax

The general shape of a mysql2 program is: install the package, create a connection or pool from config, then await queries against it.

npm install mysql2 dotenv

Call	Purpose
mysql.createConnection(config)	Opens one connection; returns a Promise that resolves to a Connection.
mysql.createPool(config)	Creates a reusable pool of connections; returns a Pool immediately (no await needed).
conn.query(sql, params)	Runs SQL with client-side value substitution. Resolves to [rows, fields].
conn.execute(sql, params)	Runs SQL as a server-side prepared statement. Resolves to [rows, fields].
pool.getConnection()	Checks out a single dedicated connection from the pool (needed for transactions).
conn.release()	Returns a checked-out connection back to the pool.
conn.beginTransaction() / commit() / rollback()	Starts, confirms, or undoes a transaction on that connection.
connection.end() / pool.end()	Gracefully closes the connection or pool.

Common config options passed to createConnection/createPool:

Option	Meaning
host, port	Where the MySQL server is listening (port defaults to 3306).
user, password, database	Credentials and the schema to use.
waitForConnections	If true (default), queue callers when the pool is at capacity instead of throwing.
connectionLimit	Max simultaneous connections the pool will open (default 10).
queueLimit	Max queued requests before the pool rejects with an error; 0 means unlimited.

Examples

1. Connecting and running a query

The simplest case: open one connection, run a SELECT, and close it. Never hardcode a real password — read it from an environment variable, loaded via dotenv or Node’s built-in --env-file flag (available since Node 20.6).

import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "app_user",
  password: process.env.DB_PASSWORD,
  database: "shop",
});

try {
  const [rows] = await connection.query(
    "SELECT id, name, price FROM products LIMIT 5"
  );
  console.log(rows);
} catch (err) {
  console.error("Query failed:", err.message);
} finally {
  await connection.end();
}

Output:

[
  { id: 1, name: 'Wireless Mouse', price: 19.99 },
  { id: 2, name: 'USB-C Cable', price: 8.5 }
]

connection.query() resolves to a two-element array: the rows themselves and metadata about the result columns (fields), which we ignore here by only destructuring the first element. The finally block guarantees the connection closes even if the query throws.

2. Parameterized writes (the safe way to build SQL)

Whenever a value comes from outside your code — user input, a form field, a query string — it must go through a placeholder, never through string concatenation. execute() sends the SQL as a prepared statement with ? placeholders and the values in a separate array, so MySQL treats them strictly as data, never as SQL syntax.

import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "app_user",
  password: process.env.DB_PASSWORD,
  database: "shop",
});

async function addProduct(name, price) {
  const [result] = await connection.execute(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    [name, price]
  );
  return result.insertId;
}

try {
  const id = await addProduct("Wireless Mouse", 19.99);
  console.log("Inserted product with id", id);
} finally {
  await connection.end();
}

Output:

Inserted product with id 42

The result object for an INSERT is a ResultSetHeader, not row data — it carries insertId (the new auto-increment id), affectedRows, and similar metadata. Placeholders also handle escaping for you: quotes, backslashes, and other special characters inside name can never break out of the SQL string.

3. A connection pool for a real server

A server handling concurrent requests should create one pool at startup and reuse it for every request — never open a fresh connection per request. pool.query()/pool.execute() transparently borrow a connection, run the query, and return it to the pool for you.

import mysql from "mysql2/promise";

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

export async function getProductById(id) {
  const [rows] = await pool.query(
    "SELECT id, name, price FROM products WHERE id = ?",
    [id]
  );
  return rows[0] ?? null;
}

const product = await getProductById(1);
console.log(product);

Output:

{ id: 1, name: 'Wireless Mouse', price: 19.99 }

In a real app, pool would live in one module and getProductById (and similar functions) would be imported by your route handlers, so every request shares the same small set of already-open connections.

4. Transactions

When several writes must succeed or fail together — moving stock between two products, transferring a balance between accounts — wrap them in a transaction on a single dedicated connection pulled from the pool with getConnection(). pool.query() alone cannot do this, because each call might land on a different underlying connection.

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "localhost",
  user: "app_user",
  password: process.env.DB_PASSWORD,
  database: "shop",
});

async function transferStock(fromId, toId, quantity) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    await conn.execute(
      "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?",
      [quantity, fromId, quantity]
    );
    await conn.execute(
      "UPDATE products SET stock = stock + ? WHERE id = ?",
      [quantity, toId]
    );
    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

try {
  await transferStock(1, 2, 5);
  console.log("Stock transferred");
} catch (err) {
  console.error("Transfer failed:", err.message);
} finally {
  await pool.end();
}

Output:

Stock transferred

The first UPDATE includes AND stock >= ? so it affects zero rows instead of driving stock negative; you would normally check result.affectedRows and roll back manually if it is 0. Note the conn.release() in finally — without it, this connection never goes back to the pool.

How it works step by step

Tracing a single pool.execute(sql, params) call end to end:

• 1. Acquire. The pool looks for an idle connection. If none is free and the pool is under connectionLimit, it opens a new one; if the pool is already full, the call is queued (when waitForConnections is true) until a connection frees up.
• 2. Send. The driver writes a prepared-statement packet to the TCP socket. This is a non-blocking write; your event loop is immediately free to run other code while the packet travels to the server.
• 3. Server work. MySQL parses (or reuses a cached parse of) the statement, executes it against the storage engine, and streams the result rows back over the same socket.
• 4. Receive. libuv’s socket watcher notifies Node when bytes arrive; the driver incrementally parses the binary protocol packets into JavaScript row objects.
• 5. Resolve and release. Once the full result set has arrived, the Promise from pool.execute() resolves with [rows, fields], and the pool automatically returns the underlying connection to the idle pool. Your await resumes on a later microtask tick.

The one exception is pool.getConnection(): that connection is checked out to you explicitly and stays checked out — across a whole transaction, for instance — until you call conn.release() yourself.

Common Mistakes

Building SQL with string concatenation

Concatenating user input directly into SQL text is the single most common way Node apps get SQL-injected.

const name = req.query.name;
const [rows] = await connection.query(
  "SELECT * FROM products WHERE name = '" + name + "'"
);

If a visitor requests ?name=x' OR '1'='1, the query becomes ...WHERE name = 'x' OR '1'='1' and returns every row in the table. Always pass a placeholder and a parameter array instead, as in the earlier examples — connection.query("SELECT * FROM products WHERE name = ?", [name]).

Opening a new connection per request

Creating a connection inside a route handler pays the cost of a TCP handshake and MySQL login on every single request, and under load it can exhaust the database server’s max-connections limit within seconds.

app.get("/products", async (req, res) => {
  const connection = await mysql.createConnection(dbConfig);
  const [rows] = await connection.query("SELECT * FROM products");
  await connection.end();
  res.json(rows);
});

Create one pool at startup (as in Example 3) and reuse it in every handler instead.

Forgetting to release a checked-out connection

Any connection obtained with pool.getConnection() must eventually call release(), in a finally block so it happens even on error. Skip it, and each leaked connection stays permanently checked out until the pool has none left to give — every future query then hangs waiting in the queue.

async function badHandler() {
  const conn = await pool.getConnection();
  const [rows] = await conn.query("SELECT * FROM products");
  return rows;
}

This works the first connectionLimit times it runs, then silently stalls — a classic “works in testing, dies under load” bug. Wrap it exactly like the transaction example: try { ... } finally { conn.release(); }.

Best Practices

• Use a connection pool (mysql.createPool) for any server or long-running process; reserve createConnection for scripts and one-off tasks.
• Always pass values through ? placeholders and a parameter array — never build SQL with template literals or string concatenation.
• Prefer execute() over query() for statements you run repeatedly; the server-side prepared statement is faster and just as safe.
• Load database credentials from environment variables (dotenv or node --env-file=.env), never commit them to source control.
• Use pool.getConnection() plus beginTransaction()/commit()/rollback() for any group of writes that must succeed or fail together.
• Always release() a checked-out connection in a finally block, even when an error is thrown.
• Set connectionLimit to something your MySQL server’s max_connections can actually support across all your app instances combined.
• Call pool.end() during graceful shutdown so the process can exit cleanly instead of hanging on open sockets.
• Wrap every await on a query in try/catch, or handle rejections at a higher level — an unhandled rejection from a failed query will crash a script or terminate the process.

Practice Exercises

• Write a script that creates a pool, runs CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255)), inserts one user with a parameterized INSERT, then selects and prints all rows in the table.
• Extend the transferStock transaction example so it also inserts a row into an audit_log table inside the same transaction. Then deliberately throw an error after the insert and confirm, by querying afterward, that both the stock updates and the audit row were rolled back.
• Write a helper findUserByEmail(email) built on pool.execute() that returns null when no row matches, and call it with an email containing a single quote (like o'brien@example.com) to confirm placeholders handle it safely.

Summary

• Node has no built-in MySQL client; the mysql2 package provides a fast, Promise-based driver.
• Database calls are non-blocking network I/O handled by libuv’s socket watchers, not the fs thread pool.
• Use a single connection for scripts; use mysql.createPool() for any server, and let pool.query()/pool.execute() borrow and return connections automatically.
• Always parameterize SQL with ? placeholders to prevent SQL injection; prefer execute() for repeated statements.
• Transactions require a single dedicated connection from pool.getConnection(), wrapped with beginTransaction()/commit()/rollback(), and must always be release()d.
• The most common production bugs are SQL injection via string concatenation, per-request connections instead of pooling, and leaked connections from a missing release().