mysql Node.js Driver

A PoolCluster allows you to manage multiple connection pools across different hosts, supporting grouping, retries, and selection strategies (e.g., for Master/Slave setups).

Adding and Removing Nodes

Nodes can be added with an automatic name or a specific identifier. They can be removed by ID or by a pattern (using wildcards).

Selecting Nodes

You can retrieve connections from specific groups, using patterns (wildcards or Regular Expressions), or using the of() method to create a sub-pool with a specific selector.

PoolCluster Options

• canRetry: If true, attempts to reconnect when a connection fails. (Default: true)
• removeNodeErrorCount: Number of failures allowed before a node is removed from the cluster. (Default: 5)
• restoreNodeTimeout: Milliseconds to wait before attempting to reconnect to a failed node. If 0, the node is removed and never re-used. (Default: 0)
• defaultSelector: The strategy used to pick a node. Options: RR (Round-Robin), RANDOM, or ORDER (first available).

var poolCluster = mysql.createPoolCluster();

// Add nodes
poolCluster.add('MASTER', masterConfig);
poolCluster.add('SLAVE1', slave1Config);
poolCluster.add('SLAVE2', slave2Config);

// Get connection from a specific group (Selector: Round-Robin)
poolCluster.getConnection('SLAVE*', function (err, connection) {});

// Get connection using a pattern and specific selector
poolCluster.getConnection(/^SLAVE[12]$/, 'ORDER', function (err, connection) {});

// Create a sub-pool with a specific selector
var pool = poolCluster.of('SLAVE*', 'RANDOM');
pool.query('SELECT 1');

// Close the cluster
poolCluster.end(function (err) {});

To prevent SQL Injection, always escape user-provided data. This library performs client-side escaping.

Methods for escaping:

• connection.escape(value)
• pool.escape(value)
• mysql.escape(value)
• Using ? placeholders in .query() (recommended).

Escaping behavior by type:

• Numbers: Left untouched.
• Booleans: Converted to true / false.
• Dates: Converted to 'YYYY-mm-dd HH:ii:ss' strings.
• Buffers: Converted to hex strings (e.g., X'0fa5').
• Strings: Safely escaped.
• Arrays: Converted to lists (e.g., ['a', 'b'] $\rightarrow$ 'a', 'b').
• Nested Arrays: Converted to grouped lists for bulk inserts (e.g., [['a', 'b'], ['c', 'd']] $\rightarrow$ ('a', 'b'), ('c', 'd')).
• Objects: Converted to key = 'val' pairs for each enumerable property. If a property is a function, it is skipped; if it is an object, toString() is used.
• undefined / null: Converted to NULL.
• NaN / Infinity: Left as-is (Note: MySQL does not support these and may throw errors).

Special Object Handling:

• Objects with a .toSqlString() method will use that method's return value as the raw SQL.
• mysql.raw(string) creates an object that bypasses all escaping, useful for dynamic SQL functions like CURRENT_TIMESTAMP().

// Using placeholders (Best Practice)
connection.query('SELECT * FROM users WHERE id = ?', [userId], function (error, results, fields) {
  // ...
});

// Using mysql.raw for SQL functions
var CURRENT_TIMESTAMP = mysql.raw('CURRENT_TIMESTAMP()');
connection.query('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42], function (error, results) {
  // ...
});

// Bulk insert using nested arrays
connection.query('INSERT INTO table (col1, col2) VALUES ?', [['val1', 'val2'], ['val3', 'val4']], function (error, results) {
  // ...
});

The module uses standard JavaScript Error objects with additional properties to provide context about MySQL or Node.js errors.

Error Properties

• err.code: String. Contains the MySQL server error symbol (e.g., 'ER_ACCESS_DENIED_ERROR'), a Node.js error code (e.g., 'ECONNREFUSED'), or an internal error code (e.g., 'PROTOCOL_CONNECTION_LOST').
• err.errno: Number. The MySQL server error number (only for MySQL server errors).
• err.fatal: Boolean. Indicates if the error is terminal to the connection object. This is not defined for non-protocol errors.
• err.sql: String. The full SQL of the failed query.
• err.sqlState: String. The five-character SQLSTATE value (only for MySQL server errors).
• err.sqlMessage: String. A textual description of the error (only for MySQL server errors).

Error Propagation Behavior

• Fatal Errors: Propagated to all pending callbacks. If no callbacks are pending, the error is emitted as an 'error' event on the connection object.
• Normal Errors: Only delegated to the specific callback belonging to the failed operation.
• Unhandled Errors: If a normal error occurs with no callback, or a fatal error occurs with no listeners, it is emitted as an 'error' event. In Node.js, unhandled 'error' events will print a stack trace and kill the process. Always provide a callback or an 'error' listener to prevent silent failures or process crashes.

var connection = require('mysql').createConnection({
  port: 1 // example blocked port
});

connection.connect(function(err) {
  console.log(err.code); // 'ECONNREFUSED'
  console.log(err.fatal); // true
});

connection.query('SELECT 1', function (error, results, fields) {
  console.log(error.code); // 'ECONNREFUSED'
  console.log(error.fatal); // true
});

To process large quantities of rows without buffering them all into memory, use the event-based streaming approach.

Important requirements:

• You MUST NOT provide a callback to the query() method when streaming.
• The 'result' event fires for both data rows and OK packets (for INSERT/UPDATE).
• Use connection.pause() and connection.resume() to throttle processing if your row handling involves I/O. This prevents the internal buffer from overflowing.
• Be careful not to leave the connection paused for too long, or you may encounter Error: Connection lost: The server closed the connection. due to the MySQL net_write_timeout setting.

Available events:

• 'error': Emitted on error. An 'end' event will follow.
• 'fields': Emitted with the field packets for the upcoming rows.
• 'result': Emitted for each row (or OK packet).
• 'end': Emitted when all rows have been received.

var query = connection.query('SELECT * FROM posts');
query
  .on('error', function(err) {
    // Handle error, an 'end' event will be emitted after this as well
  })
  .on('fields', function(fields) {
    // the field packets for the rows to follow
  })
  .on('result', function(row) {
    // Pausing the connection is useful if your processing involves I/O
    connection.pause();

    processRow(row, function() {
      connection.resume();
    });
  })
  .on('end', function() {
    // all rows have been received
  });

Instead of managing individual connections, use mysql.createPool(config) to manage a cache of connections. This improves performance and simplifies connection management.

Shortcut Method

Use pool.query() for single, one-off queries. This automatically handles acquiring and releasing a connection from the pool.

Manual Connection Management

If you need to share connection state (like session variables or transactions) across multiple queries, use pool.getConnection(). You must call connection.release() when finished to return the connection to the pool. If you want to permanently remove a connection from the pool, use connection.destroy() instead.

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

// Shortcut: automatically acquires and releases
pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

// Manual: useful for sharing state
pool.getConnection(function(err, connection) {
  if (err) throw err;

  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // Release the connection back to the pool
    connection.release();

    if (error) throw error;
  });
});

You can establish a connection in two ways:

Explicit Connection

Use connection.connect(callback) to handle connection errors (such as handshake or network issues) via a callback. This is the recommended approach.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

Implicit Connection

Invoking a query via connection.query() will implicitly establish the connection if it is not already connected.

var mysql      = require('mysql');
var connection = mysql.createConnection(...);

connection.query('SELECT 1', function (error, results, fields) {
  if (error) throw error;
  // connected!
});

Warning: Any connection error (handshake or network) is considered a fatal error.

By default, multiple statements are disabled for security. To enable them, set multipleStatements: true in your connection configuration.

Standard Querying: When executing multiple statements, results is an array where each element corresponds to a statement in the query.

Streaming Multiple Statements: When streaming, the 'fields' and 'result' events provide an index argument (starting at 0) indicating which statement the data belongs to. If a statement causes an error, the error object will contain an err.index property. Note that MySQL stops executing remaining statements if one fails.

// Enable the feature
var connection = mysql.createConnection({multipleStatements: true});

// Execute multiple statements
connection.query('SELECT 1; SELECT 2', function (error, results, fields) {
  if (error) throw error;
  console.log(results[0]); // [{1: 1}]
  console.log(results[1]); // [{2: 2}]
});

// Stream multiple statements
var query = connection.query('SELECT 1; SELECT 2');
query
  .on('fields', function(fields, index) {
    // index refers to the statement index
  })
  .on('result', function(row, index) {
    // index refers to the statement index
  });