MySQL2 Node.js Client

3.22.1 · active · verified Sat Apr 18

mysql2 is a fast MySQL driver for Node.js, implementing the core protocol, prepared statements, SSL, and compression in native JavaScript. It offers an API that is largely compatible with the popular `node-mysql` library but provides enhanced performance and additional features, including native promise support. The current stable version is 3.22.1. The project maintains an active development cycle, regularly releasing patch and minor versions.

Common errors

```
ER_ACCESS_DENIED_ERROR: Access denied for user 'user'@'host' (using password: YES)
```
cause Incorrect username, password, or host specified in connection options, or the MySQL user lacks necessary permissions.

fix Double-check your `user`, `password`, and `host` in `mysql.createConnection()` options. Verify user permissions in MySQL using `GRANT` statements.
```
Error: connect ECONNREFUSED
```
cause The MySQL server is not running, or it's not accessible at the specified host and port, possibly due to a firewall.

fix Ensure your MySQL server is running. Verify the `host` and `port` in your connection options are correct and that no firewall is blocking the connection.
```
Error: Packets out of order. Got: 1 Expected: 4
```
cause This typically indicates a connection issue, such as the MySQL server closing the connection unexpectedly, an invalid query causing a server error, or a network problem.

fix Review recent queries for syntax errors. Ensure the MySQL server is stable. Consider increasing the `wait_timeout` on the MySQL server or implementing a connection pool with `acquireTimeout` in your `mysql2` client.
```
Cannot read properties of undefined (reading 'fieldCount')
```
cause This error often occurs when a query fails or returns an unexpected response, leading to `rows` or `fields` being `undefined` or not in the expected format. It can also happen if the connection drops before a query completes.

fix Ensure your query is valid and the connection is stable. Add robust error handling around `await connection.execute()` to catch and log specific database errors.

Warnings

gotcha The `mysql_clear_password` authentication plugin is disabled by default for security reasons.
Fix: If required for legacy systems, explicitly enable it in your connection options: `{ authPlugins: { mysql_clear_password: () => true } }`. Consider using more secure authentication methods where possible.
gotcha Large integer values (e.g., `BIGINT`) from MySQL can lose precision when converted to standard JavaScript numbers.
Fix: Use `supportBigNumbers: true` and `bigNumberStrings: true` in your connection options to receive large numbers as strings, preventing precision loss: `{ supportBigNumbers: true, bigNumberStrings: true }`.
gotcha MySQL `DATETIME` or `TIMESTAMP` columns are converted to JavaScript `Date` objects, which might lead to timezone issues or unexpected formatting without proper configuration.
Fix: Use `dateStrings: true` in connection options to retrieve dates as MySQL string format directly, or specify a `timezone` (e.g., `'Z'` for UTC) for consistent `Date` object conversion: `{ dateStrings: true }` or `{ timezone: 'Z' }`.
gotcha `mysql2` provides both callback-based and promise-based APIs. Mixing them or incorrectly using `async/await` with callbacks can lead to unhandled errors or unexpected behavior.
Fix: For modern Node.js applications, prefer the promise-based API by importing `mysql2/promise`. Ensure all database operations are `await`ed within `async` functions or handled with `.then().catch()`.

Install

npm install mysql2 npm
yarn add mysql2 yarn
pnpm add mysql2 pnpm

Imports

mysql
```
import mysql from 'mysql2';
```
For callback-based API and general utilities.
mysql
```
import mysql from 'mysql2/promise';
```
For the promise-based API, recommended for modern applications.

Quickstart

Connects to a MySQL database, executes a simple arithmetic query and a parameterized query using prepared statements, then logs the results. Uses environment variables for sensitive connection details.

import mysql from 'mysql2/promise';

async function connectAndQuery() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST ?? 'localhost',
    user: process.env.DB_USER ?? 'root',
    password: process.env.DB_PASSWORD ?? 'password',
    database: process.env.DB_DATABASE ?? 'testdb'
  });

  try {
    const [rows, fields] = await connection.execute('SELECT 1 + 1 AS solution');
    console.log('The solution is: ', rows[0].solution);

    const [users] = await connection.execute('SELECT id, name FROM users WHERE age > ?', [30]);
    console.log('Users over 30:', users);
  } catch (error) {
    console.error('Error executing query:', error);
  } finally {
    await connection.end();
  }
}

connectAndQuery();

view raw JSON →