tessl/npm-mysql

A comprehensive MySQL database driver for Node.js providing connection management, query execution, connection pooling, and cluster support.

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Pending

The risk profile of this skill

Overview

Eval results

Files

Cluster Management

Name: tessl/npm-mysql
Author: tessl

Pool clustering functionality with load balancing and failover support. Perfect for distributed applications requiring high availability and horizontal scaling across multiple MySQL servers.

Capabilities

Create Pool Cluster

Creates a new pool cluster for managing multiple connection pools with load balancing and failover capabilities.

/**
 * Create a new PoolCluster instance
 * @param {object} [config] - Configuration for pool cluster
 * @returns {PoolCluster} New MySQL pool cluster
 */
function createPoolCluster(config);

// Cluster configuration
interface ClusterConfig {
  canRetry?: boolean;           // Whether to retry failed connections (default: true)
  removeNodeErrorCount?: number; // Number of errors before removing node (default: 5)
  restoreNodeTimeout?: number;  // Timeout to restore offline nodes in ms (default: 0 - never restore)
  defaultSelector?: string;     // Default load balancing selector (default: 'RR' - round robin)
}

Usage Examples:

const mysql = require('mysql');

// Basic cluster
const cluster = mysql.createPoolCluster();

// Cluster with custom configuration
const cluster = mysql.createPoolCluster({
  canRetry: true,
  removeNodeErrorCount: 3,
  restoreNodeTimeout: 60000, // 1 minute
  defaultSelector: 'RR'
});

Cluster Methods

Add Node

Adds a pool to the cluster with a unique identifier.

/**
 * Adds a pool to cluster
 * @param {string|object} id - Node identifier or configuration object
 * @param {object} [config] - Pool configuration (if id is string)
 */
cluster.add(id, config);

Usage Examples:

// Add named nodes
cluster.add('MASTER', {
  host: 'mysql-master.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

cluster.add('SLAVE1', {
  host: 'mysql-slave1.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

cluster.add('SLAVE2', {
  host: 'mysql-slave2.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

// Add anonymous node (auto-generated ID)
cluster.add({
  host: 'mysql-replica.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

Remove Node

Removes pools matching a pattern from the cluster.

/**
 * Removes pools matching pattern
 * @param {string} pattern - Pattern to match node IDs
 */
cluster.remove(pattern);

Usage Examples:

// Remove specific node
cluster.remove('SLAVE1');

// Remove multiple nodes with pattern
cluster.remove('SLAVE*'); // Removes SLAVE1, SLAVE2, etc.

// Remove all nodes
cluster.remove('*');

Get Connection

Gets a connection from the cluster using load balancing.

/**
 * Gets connection from cluster
 * @param {string|function} [pattern] - Node pattern or callback function
 * @param {string|function} [selector] - Load balancing selector or callback function
 * @param {function} callback - Callback function (err, connection)
 */
cluster.getConnection(pattern, selector, callback);

Load Balancing Selectors:

'RR' - Round Robin (default)
'RANDOM' - Random selection
'ORDER' - Use nodes in order of addition

Usage Examples:

// Get connection from any node (round robin)
cluster.getConnection((err, connection) => {
  if (err) throw err;
  
  connection.query('SELECT * FROM users', (error, results) => {
    connection.release();
    if (error) throw error;
    console.log(results);
  });
});

// Get connection from specific pattern with selector
cluster.getConnection('SLAVE*', 'RANDOM', (err, connection) => {
  if (err) throw err;
  
  // This connection is from a random slave node
  connection.query('SELECT * FROM products', (error, results) => {
    connection.release();
    if (error) throw error;
    console.log(results);
  });
});

// Get connection from master for writes
cluster.getConnection('MASTER', (err, connection) => {
  if (err) throw err;
  
  connection.query('INSERT INTO logs SET ?', {message: 'Hello'}, (error, results) => {
    connection.release();
    if (error) throw error;
    console.log('Inserted with ID:', results.insertId);
  });
});

Create Namespace

Creates a namespace for easier pool selection and management.

/**
 * Creates namespace for pool selection
 * @param {string} [pattern] - Node pattern to include in namespace (default: '*')
 * @param {string} [selector] - Default selector for namespace (default: cluster default)
 * @returns {PoolNamespace} New pool namespace
 */
cluster.of(pattern, selector);

Usage Examples:

// Create namespace for read operations (slaves only)
const readNamespace = cluster.of('SLAVE*', 'RANDOM');

// Create namespace for write operations (master only)
const writeNamespace = cluster.of('MASTER');

// Use namespaces
readNamespace.query('SELECT * FROM products', (error, results) => {
  if (error) throw error;
  console.log(results);
});

writeNamespace.query('INSERT INTO logs SET ?', {message: 'Hello'}, (error, results) => {
  if (error) throw error;
  console.log('Inserted:', results.insertId);
});

End Cluster

Closes all pools in the cluster.

/**
 * Closes all pools in cluster
 * @param {function} [callback] - Callback function (err)
 */
cluster.end(callback);

Pool Namespace

Pool namespaces provide a convenient way to work with subsets of cluster nodes. They act as a filtered view of the cluster, allowing operations on specific node patterns with consistent load balancing.

// PoolNamespace interface
interface PoolNamespace {
  pattern: string;    // Node selection pattern
  selector: string;   // Load balancing selector
}

Namespace Methods

/**
 * Gets connection from namespace
 * @param {function} callback - Callback function (err, connection)
 */
namespace.getConnection(callback);

/**
 * Executes query using namespace
 * @param {string|object} sql - SQL query string or query object
 * @param {array} [values] - Parameter values for prepared statements
 * @param {function} [callback] - Callback function (err, results, fields)
 * @returns {Query} Query object for event-based processing
 */
namespace.query(sql, values, callback);

Cluster Events

Online Event

Fired when a node comes online.

cluster.on('online', (nodeId) => {
  console.log('Node %s is online', nodeId);
});

Offline Event

Fired when a node goes offline.

cluster.on('offline', (nodeId) => {
  console.log('Node %s is offline', nodeId);
});

Remove Event

Fired when a node is removed from the cluster.

cluster.on('remove', (nodeId) => {
  console.log('Node %s removed from cluster', nodeId);
});

Complete Cluster Example:

const mysql = require('mysql');

// Create cluster
const cluster = mysql.createPoolCluster({
  canRetry: true,
  removeNodeErrorCount: 3,
  restoreNodeTimeout: 60000,
  defaultSelector: 'RR'
});

// Set up event handlers
cluster.on('online', (nodeId) => {
  console.log('Node %s is online', nodeId);
});

cluster.on('offline', (nodeId) => {
  console.log('Node %s is offline', nodeId);
});

cluster.on('remove', (nodeId) => {
  console.log('Node %s removed from cluster', nodeId);
});

// Add nodes
cluster.add('MASTER', {
  connectionLimit: 10,
  host: 'mysql-master.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

cluster.add('SLAVE1', {
  connectionLimit: 10,
  host: 'mysql-slave1.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

cluster.add('SLAVE2', {
  connectionLimit: 10,
  host: 'mysql-slave2.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

// Create namespaces
const readPool = cluster.of('SLAVE*', 'RANDOM');
const writePool = cluster.of('MASTER');

// Application functions
function readUser(userId, callback) {
  readPool.query('SELECT * FROM users WHERE id = ?', [userId], callback);
}

function createUser(userData, callback) {
  writePool.query('INSERT INTO users SET ?', userData, callback);
}

function updateUser(userId, updates, callback) {
  writePool.getConnection((err, connection) => {
    if (err) return callback(err);
    
    connection.beginTransaction((err) => {
      if (err) {
        connection.release();
        return callback(err);
      }
      
      connection.query('UPDATE users SET ? WHERE id = ?', [updates, userId], (error, results) => {
        if (error) {
          return connection.rollback(() => {
            connection.release();
            callback(error);
          });
        }
        
        connection.commit((err) => {
          if (err) {
            return connection.rollback(() => {
              connection.release();
              callback(err);
            });
          }
          
          connection.release();
          callback(null, results);
        });
      });
    });
  });
}

// Graceful shutdown
process.on('SIGINT', () => {
  console.log('Closing cluster...');
  cluster.end(() => {
    console.log('Cluster closed');
    process.exit(0);
  });
});

// Usage examples
readUser(1, (error, results) => {
  if (error) throw error;
  console.log('User:', results[0]);
});

createUser({name: 'John', email: 'john@example.com'}, (error, results) => {
  if (error) throw error;
  console.log('Created user with ID:', results.insertId);
});

Cluster Best Practices

Node Identification: Use meaningful node IDs that reflect their role (MASTER, SLAVE1, etc.)
Error Handling: Configure appropriate removeNodeErrorCount to handle temporary network issues
Load Balancing: Choose selectors based on your application needs (RR for even distribution, RANDOM for unpredictability)
Namespaces: Use namespaces to separate read and write operations for better performance
Monitoring: Implement event handlers to monitor cluster health and node status
Failover: Plan for master failover scenarios in your application logic
Connection Limits: Set appropriate connection limits for each node based on server capacity
Health Checks: Consider implementing application-level health checks for critical operations