node-mysql2 Documentation

Running Tests Copy Chapter

npm run test

npm run lintcheck

npm run typecheck

npm run clear
npm run build

npm run lint

File: website/docs/documentation/00-index.mdx Copy Chapter

slug: /documentation title: Introduction Copy Chapter

import { PageTitle } from '@site/src/components/PageTitle';

Documentation

MySQL2 aims to be a drop in replacement for Node MySQL.

:::note
If you see any API incompatibilities with Node MySQL, please report via github issue.
:::

Not only MySQL2 offers better performance over Node MySQL, we also support these additional features:

• Prepared Statements
• Query Attributes
• MariaDB Data Types
• Reset Connection
• Promise Wrapper
• Authentication Switch
• More Features
• MySQL Server
• Pooling
• SSL
• Tracing Channels
• MySQL Compression
• Binary Log Protocol Client

Examples Copy Chapter

Please check these examples for MySQL2.

Known incompatibilities with [Node MySQL][node-mysql] Copy Chapter

• zeroFill flag is ignored in type conversion.
  You need to check corresponding field's zeroFill flag and convert to string manually if this is of importance to you.

• DECIMAL and NEWDECIMAL types always returned as string unless you pass this config option:

{
  decimalNumbers: true,
}

:::note
This option could lose precision on the number as Javascript Number is a Float!
:::

• By default, the JSON type is always returned parsed into an object. However, you can modify this behavior by specifying the following configuration:

{
  jsonStrings: true,
}

Other Resources Copy Chapter

• Wire protocol documentation
• Node MySQL - Most popular node.js mysql client library
• node-mariasql - Bindings to libmariasql. One of the fastest clients
• node-libmysqlclient - Bindings to libmysqlclient
• go-mysql - MySQL Go client (prepared statements, binlog protocol, server)

Benchmarks Copy Chapter

• https://gist.github.com/sidorares/ffe9ee9c423f763e3b6b
• npm run benchmarks
• node-mysql-benchmarks
• try to run example benchmarks on your system

File: website/docs/documentation/authentication-switch.mdx Copy Chapter

Authentication Switch Request

During the connection phase the server may ask the client to switch to a different auth method.
If the authPlugins connection config option is set, it must be an object where each key
is the name of a potential authentication plugin requested by the server, and the corresponding
value must be a function that optionally receives the connection config options and returns
another function, which in turn, optionally receives the switch request data.

The plugin is loaded with a ({user,password,...}) signature, and each call has a (pluginData)
signature. Each call should make the plugin return any additional authentication data (Buffer)
that should be sent back to the server, either synchronously or asynchronously using a Promise,
or should yield an error accordingly.

Example: (imaginary ssh-key-auth plugin) pseudo code

const conn = mysql.createConnection({
  user: 'test_user',
  password: 'test',
  database: 'test_database',
  authPlugins: {
    'ssh-key-auth': function ({ password }) {
      return function (pluginData) {
        return getPrivate(key)
          .then((key) => {
            const response = encrypt(key, password, pluginData);
            // continue handshake by sending response data
            return response;
          })
          .catch((err) => {
            // throw error to propagate error to connect/changeUser handlers
          });
      };
    },
  },
});

There is also a deprecated API where if a authSwitchHandler connection config option is set
it must be a function that receives switch request data and responds via a callback. In this case,
the first invocation always has a ({pluginName, pluginData}) signature, following calls - ({pluginData}).
The client replies with an opaque blob matching the requested plugin via callback(null, data: Buffer).

const conn = mysql.createConnection({
  user: 'test_user',
  password: 'test',
  database: 'test_database',
  authSwitchHandler: function ({ pluginName, pluginData }, cb) {
    if (pluginName === 'ssh-key-auth') {
      getPrivateKey((key) => {
        const response = encrypt(key, pluginData);
        // continue handshake by sending response data
        // respond with error to propagate error to connect/changeUser handlers
        cb(null, response);
      });
    } else {
      const err = new Error(
        `Unknown AuthSwitchRequest plugin name ${pluginName}`
      );
      err.fatal = true;
      cb(err);
    }
  },
});

The initial handshake is always performed using mysql_native_password plugin. This will be possible to override in future versions.

Note that if the mysql_native_password method is requested it will be handled internally according
to Authentication::Native41
and no authPlugins function or the authSwitchHandler will be invoked.

These MAY be called multiple times if the plugin algorithm requires multiple roundtrips of data
exchange between client and server.

Cleartext Authentication Copy Chapter

The mysql_clear_password authentication plugin sends the password to the server in plaintext.
This is required by some authentication schemes (e.g. PAM, LDAP, or AWS RDS IAM), but poses a
security risk if the connection is not encrypted — a malicious server or man-in-the-middle could
capture credentials.

For this reason, mysql_clear_password is disabled by default. If the server requests it and
the client has not explicitly opted in, the connection will fail with a
MYSQL_CLEAR_PASSWORD_NOT_ENABLED error.

Enabling cleartext authentication Copy Section

Set enableCleartextPlugin: true in your connection options:

const conn = mysql.createConnection({
  host: 'example.com',
  user: 'my_user',
  password: 'my_password',
  ssl: { rejectUnauthorized: true },
  enableCleartextPlugin: true,
});

:::caution
Only enable this over a secure connection (TLS/SSL or a Unix domain socket). Never enable it over
an unencrypted network connection.
:::

Alternatively, providing a custom mysql_clear_password function via the authPlugins option
implicitly enables cleartext authentication without the enableCleartextPlugin flag:

const conn = mysql.createConnection({
  host: 'example.com',
  user: 'my_user',
  ssl: { rejectUnauthorized: true },
  authPlugins: {
    mysql_clear_password: () => () => Buffer.from(password + '\0'),
  },
});

Multi-factor authentication Copy Chapter

If the user requires multi-factor authentication in the server, the client will receive a AuthNextFactor
request, which is similar in structure to the regular authentication switch request and contains the name
and possible initial data for the additional authentication factor plugin (up to 3). Additional passwords
can be provided using the connection config options - password2 and password3. Again, for each
authentication factor, multiple roundtrips of data exchange can be required by the plugin algorithm.

const conn = mysql.createConnection({
  user: 'test_user',
  password: 'secret1',
  password2: 'secret2',
  password3: 'secret3',
  database: 'test_database',
  authPlugins: {
    // password1 === password
    'auth-plugin1': function ({ password1 }) {
      return function (serverPluginData) {
        return clientPluginData(password1, serverPluginData);
      };
    },
    'auth-plugin2': function ({ password2 }) {
      return function (serverPluginData) {
        return clientPluginData(password2, serverPluginData);
      };
    },
    'auth-plugin3': function ({ password3 }) {
      return function (serverPluginData) {
        return clientPluginData(password3, serverPluginData);
      };
    },
  },
});

File: website/docs/documentation/connect-on-cloudflare.mdx Copy Chapter

import { History } from '@site/src/components/History';

Cloudflare Workers

<History
records={[
{
version: '3.13.0',
changes: [
<>
Support for non-eval parsers by using{' '}
disableEval option.
</>,
],
},
]}
/>

Prerequisites Copy Chapter

• A Cloudflare account.
• Wrangler installed.
• MySQL2 version 3.13.0 or higher.

To learn how to create a Cloudflare Worker project, please refer to Cloudflare Workers Documentation.

Setup Copy Chapter

Wrangler Copy Section

MySQL2 relies on Node.js modules, such as net, events, stream, tls, etc. You can enable Node.js compatibility for Cloudflare Workers by using the "nodejs_compat" flag through the wrangler.jsonc file:

{
  "compatibility_flags": ["nodejs_compat"]
}

:::important
The minimum compatibility date is 2025-02-24, for example:

{
  "compatibility_date": "2025-02-24",
  "compatibility_flags": ["nodejs_compat"]
}

:::

MySQL2 connection Copy Section

MySQL2 uses a code generation-based parser for performance by default, but since Cloudflare Workers don't offer support for evaluations, you can disable it by using the disableEval option:

import { createConnection } from 'mysql2/promise';

export default {
  async fetch(): Promise<Response> {
    const conn = await createConnection({
      host: 'localhost',
      user: 'root',
      database: 'test',
      // highlight-start
      disableEval: true,
      // highlight-end
    });

    const [results] = await conn.query('SHOW TABLES;');

    return new Response(JSON.stringify(results));
  },
} satisfies ExportedHandler<Env>;

:::tip
For required SSL connections, it is recommended to use the Cloudflare Hyperdrive connection pool.
:::

File: website/docs/documentation/extras.mdx Copy Chapter

Extra Features

Named placeholders Copy Chapter

You can use named placeholders for parameters by setting namedPlaceholders config value or query/execute time option. Named placeholders are converted to unnamed ? on the client (mysql protocol does not support named parameters). If you reference parameter multiple times under the same name it is sent to server multiple times. Unnamed placeholders can still be used by providing the values as an array instead of an object.

connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', { x: 1, y: 2 }, (err, rows) => {
  // statement prepared as "select ? + ? as z" and executed with [1,2] values
  // rows returned: [ { z: 3 } ]
});

connection.execute('select :x + :x as z', { x: 1 }, (err, rows) => {
  // select ? + ? as z, execute with [1, 1]
});

connection.query('select :x + :x as z', { x: 1 }, (err, rows) => {
  // query select 1 + 1 as z
});

// unnamed placeholders are still valid if the values are provided in an array
connection.query('select ? + ? as z', [1, 1], (err, rows) => {
  // query select 1 + 1 as z
});

Receiving rows as array of columns instead of hash with column name as key: Copy Chapter

const options = { sql: 'select A,B,C,D from foo', rowsAsArray: true };
connection.query(options, (err, results) => {
  /* results will be an array of arrays like this now:
  [[
     'field A value',
     'field B value',
     'field C value',
     'field D value',
  ], ...]
  */
});

Sending tabular data with 'load infile' and local stream: Copy Chapter

In addition to sending local fs files you can send any stream using infileStreamFactory query option. If set, it has to be a function that return a readable stream. It gets file path from query as a parameter.

Note: starting from version 2.0 infileStreamFactory is required parameter for LOAD DATA LOCAL INFILE. Response from server indicates that it wants access to a local file and no infileStreamFactory option is provided the query ends with error.

// local file
connection.query(
  'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test FIELDS TERMINATED BY ? (id, title)',
  onInserted1
);
// local stream
const sql =
  'LOAD DATA LOCAL INFILE "mystream" INTO TABLE test FIELDS TERMINATED BY ? (id, title)';
connection.query(
  {
    sql: sql,
    infileStreamFactory: function (path) {
      return getStream();
    },
  },
  onInserted2
);

The infileStreamFactory option may also be set at a connection-level:

const fs = require('fs');
const mysql = require('mysql2');

const connection = mysql.createConnection({
  user: 'test',
  database: 'test',
  infileStreamFactory: (path) => {
    // Validate file path
    const validPaths = ['/tmp/data.csv'];
    if (!validPaths.includes(path)) {
      throw new Error(
        `invalid file path: ${path}: expected to be one of ${validPaths.join(
          ','
        )}`
      );
    }
    return fs.createReadStream(path);
  },
});

connection.query(
  'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test',
  onInserted
);

Connecting using custom stream: Copy Chapter

const net = require('net');
const mysql = require('mysql2');
const shape = require('shaper');
const connection = mysql.createConnection({
  user: 'test',
  database: 'test',
  stream: net.connect('/tmp/mysql.sock').pipe(shape(10)), // emulate 10 bytes/sec link
});
connection.query('SELECT 1+1 as test1', console.log);

stream also can be a function. In that case function result has to be duplex stream, and it is used for connection transport. This is required if you connect pool using custom transport as new pooled connection needs new stream. Below is an updated SOCKS5 proxy example using the modern API, where stream returns a stream or a Promise resolving to a stream.

const mysql = require('mysql2');
const { SocksClient } = require('socks');

const pool = mysql.createPool({
  database: 'test',
  user: 'foo',
  password: 'bar',

  // modern usage: stream returns a Duplex or a Promise that resolves to one
  stream: async () => {
    const { socket } = await SocksClient.createConnection({
      proxy: {
        host: 'localhost',
        port: 1080,
        type: 5,
      },
      destination: {
        host: 'remote.host',
        port: 3306,
      },
    });
    return socket; // must be a Duplex stream
  },
});

Note:

Older documentation used a callback-style API:

stream: function (cb) {
  cb(null, stream);
}

This callback style no longer works on current versions of mysql2 and causes:

TypeError: cb is not a function

Use the modern return-style function shown above.

In addition to password `createConnection()`, `createPool()` and `changeUser()` accept `passwordSha1` option. Copy Chapter

This is useful when implementing proxies as plaintext password might be not available.

Graceful pool connection end Copy Chapter

const pool = mysql.createPool({
  gracefulEnd: true,
});

By default, calling end on a pool connection will result in a warning being emitted and the connection being released back to the pool. This is for compatibility with mysqljs/mysql.
By providing gracefulEnd: true when creating the pool, calling end on a pooled connection will actually close the connection instead of releasing it back to the pool.

File: website/docs/documentation/mysql-server.mdx Copy Chapter

MySQL Server API

Server Copy Chapter

• createServer() - creates server instance
• Server.listen - listen port / unix socket (same arguments as net.Server.listen)

Events Copy Section

• connect
  • new incoming connection.

Connection Copy Chapter

• serverHandshake({ serverVersion, protocolVersion, connectionId, statusFlags, characterSet, capabilityFlags })
  • send server handshake initialisation packet, wait handshake response and start listening for commands
  • capabilityFlags controls which protocol features are advertised. Client flags are masked against these capabilities when parsing the handshake response, so the server only honors mutually-supported features.
• writeOk({ affectedRows: num, insertId: num })
  • send OK packet to client
• writeEof(warnings, statusFlags)
  • send EOF packet
• writeTextResult(rows, fields)
  • write query result to client. Rows and fields are in the same format as in connection.query callback.
• writeColumns(fields)
  • write fields + EOF packets.
• writeTextRow(row)
  • write array (not hash!) of values as result row
• TODO: binary protocol

Events Copy Section

Every command packet received by the server will be emitted as a packet event with the parameters:

• packet: Packet
  • The packet itself
• knownCommand: boolean
  • is this command known to the server
• *commandCode: number
  • the parsed command code (first byte)

In addition special events are emitted for commands received from the client. If no listener is present a fallback behavior will be invoked.

• quit()
  • Default: close the connection
• init_db(schemaName: string)
  • Default: return OK
• query(sql: string)
  • Please attach a listener to this. Default: return HA_ERR_INTERNAL_ERROR
• field_list(table: string, fields: string)
  • Default: return ER_WARN_DEPRECATED_SYNTAX
• ping() - Default: return OK

File: website/docs/documentation/prepared-statements.mdx Copy Chapter

tags: [Prepared Statements, execute] Copy Chapter

Prepared Statements

Automatic creation, cached and re-used by connection Copy Chapter

Similar to connection.query().

connection.execute('select 1 + ? + ? as result', [5, 6], (err, rows) => {
  // rows: [ { result: 12 } ]
  // internally 'select 1 + ? + ? as result' is prepared first. On subsequent calls cached statement is re-used
});

// close cached statement for 'select 1 + ? + ? as result'. noop if not in cache
connection.unprepare('select 1 + ? + ? as result');

Note that connection.execute() will cache the prepared statement for better performance, remove the cache with connection.unprepare() when you're done.

Manual prepare / execute Copy Chapter

Manually prepared statements doesn't comes with LRU cache and SHOULD be closed using statement.close() instead of connection.unprepare().

connection.prepare('select ? + ? as tests', (err, statement) => {
  // statement.parameters - array of column definitions, length === number of params, here 2
  // statement.columns - array of result column definitions. Can be empty if result schema is dynamic / not known
  // statement.id
  // statement.query

  statement.execute([1, 2], (err, rows, columns) => {
    // -> [ { tests: 3 } ]
  });

  // don't use connection.unprepare(), it won't work!
  // note that there is no callback here. There is no statement close ack at protocol level.
  statement.close();
});

Note that you should not use statement after connection reset (changeUser(), reset() or disconnect). Statement scope is connection, you need to prepare statement for each new connection in order to use it.

Configuration Copy Chapter

maxPreparedStatements : We keep the cached statements in a lru-cache. Default size is 16000 but you can use this option to override it. Any statements that are dropped from cache will be closed.

Serialization of bind parameters Copy Chapter

The bind parameter values passed to execute are serialized JS -> MySQL as:

• null -> NULL
• number -> DOUBLE
• boolean -> TINY (0 for false, 1 for true)
• object -> depending on prototype:
  • Date -> DATETIME
  • JSON like object - JSON
  • Buffer -> VAR_STRING
• Other -> VAR_STRING

Passing in undefined or a function will result in an error.

Prepared Statements Helper Copy Chapter

MySQL2 provides execute helper which will prepare and query the statement. You can also manually prepare / unprepare statement with prepare / unprepare methods.

connection.execute(
  'select ?+1 as qqq, ? as rrr, ? as yyy',
  [1, null, 3],
  (err, rows, fields) => {
    console.log(err, rows, fields);
    connection.execute(
      'select ?+1 as qqq, ? as rrr, ? as yyy',
      [3, null, 3],
      (err, rows, fields) => {
        console.log(err, rows, fields);
        connection.unprepare('select ?+1 as qqq, ? as rrr, ? as yyy');
        connection.execute(
          'select ?+1 as qqq, ? as rrr, ? as yyy',
          [3, null, 3],
          (err, rows, fields) => {
            console.log(err, rows, fields);
          }
        );
      }
    );
  }
);

Examples Copy Chapter

For Prepared Statements examples, please see here.

Parameter types Copy Chapter

The MySQL type of each bind parameter is inferred from the JavaScript value.
See Typed Parameters for how to state the type
explicitly, and why it matters for large integers, unsigned values, binary data
and index lookups.

File: website/docs/documentation/promise-wrapper.mdx Copy Chapter

Promise Wrappers

In addition to errback interface there is thin wrapper to expose Promise-based api

Basic Promise Copy Chapter

const mysql = require('mysql2/promise'); // or require('mysql2').createConnectionPromise
mysql
  .createConnection({/* same parameters as for non-promise createConnection */})
  .then((conn) => conn.query('select foo from bar'))
  .then(([rows, fields]) => console.log(rows[0].foo));

const pool = require('mysql2/promise').createPool({}); // or require('mysql2').createPoolPromise({}) or require('mysql2').createPool({}).promise()
pool
  .getConnection()
  .then((conn) => {
    const res = conn.query('select foo from bar');
    conn.release();
    return res;
  })
  .then((result) => {
    console.log(result[0][0].foo);
  })
  .catch((err) => {
    console.log(err); // any of connection time or query time errors from above
  });

ES7 Async Await Copy Chapter

async function example1() {
  const mysql = require('mysql2/promise');
  const conn = await mysql.createConnection({ database: test });
  const [rows, fields] = await conn.execute('select ?+? as sum', [2, 2]);
  await conn.end();
}

async function example2() {
  const mysql = require('mysql2/promise');
  const pool = mysql.createPool({ database: test });
  // execute in parallel, next console.log in 3 seconds
  await Promise.all([
    pool.query('select sleep(2)'),
    pool.query('select sleep(3)'),
  ]);
  console.log('3 seconds after');
  await pool.end();
}

Explicit Resource Management (`await using`) Copy Chapter

With Explicit Resource Management, connections and pools are automatically cleaned up when they go out of scope — no need to manually call .end() or .release().

import mysql from 'mysql2/promise';

{
  await using conn = await mysql.createConnection({ database: test });
  const [rows, fields] = await conn.execute('select ?+? as sum', [2, 2]);
  // conn.end() is called automatically when leaving the scope
}

import mysql from 'mysql2/promise';

{
  await using pool = mysql.createPool({ database: test });

  // execute in parallel, next console.log in 3 seconds
  await Promise.all([
    pool.query('select sleep(2)'),
    pool.query('select sleep(3)'),
  ]);
  console.log('3 seconds after');
  // pool.end() is called automatically when leaving the scope
}

import mysql from 'mysql2/promise';

const pool = mysql.createPool({});

{
  await using conn = await pool.getConnection();
  const res = await conn.query('select foo from bar');
  console.log(res[0][0].foo);
  // conn.release() is called automatically when leaving the scope
}

:::tip
await using and using leverage Explicit Resource Management to automatically call .end() or .release() when the variable goes out of scope, so you never forget to clean up connections.
:::

With [CO](https://github.com/tj/co) Copy Chapter

const mysql = require('mysql2');
const co = require('co');
co(function* () {
  const c = yield mysql.createConnectionPromise({
    user: 'root',
    namedPlaceholders: true,
  });
  const rows = yield c.query('show databases');
  console.log(rows);
  console.log(yield c.execute('select 1+:toAdd as qqq', { toAdd: 10 }));
  yield c.end();
});

Examples in /examples/promise-co-await