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

Connection Pooling

Name: tessl/npm-mysql
Author: tessl

Advanced connection pooling system for managing multiple database connections efficiently. Ideal for applications with concurrent database access requirements and production environments where connection reuse is essential for performance.

Capabilities

Create Pool

Creates a new connection pool with specified configuration.

/**
 * Create a new Pool instance
 * @param {object|string} config - Configuration or connection string for new MySQL connections
 * @returns {Pool} A new MySQL pool
 */
function createPool(config);

// Pool configuration extends ConnectionConfig
interface PoolConfig extends ConnectionConfig {
  connectionLimit?: number;     // Maximum number of connections in pool (default: 10)
  queueLimit?: number;         // Maximum number of queued connection requests (default: 0 - no limit)
  acquireTimeout?: number;     // Timeout for getting connection from pool (default: 10000ms)
  waitForConnections?: boolean; // Whether to queue connection requests when limit reached (default: true)
  reconnect?: boolean;         // Automatically reconnect when connection is lost (default: true)
}

Usage Examples:

const mysql = require('mysql');

// Basic pool configuration
const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  acquireTimeout: 60000,
  reconnect: true
});

// Production pool with SSL
const productionPool = mysql.createPool({
  connectionLimit: 20,
  queueLimit: 0,
  host: 'mysql.example.com',
  user: 'app_user',
  password: 'secure_password',
  database: 'production_db',
  ssl: {
    ca: fs.readFileSync('./server-ca.pem')
  },
  reconnect: true,
  acquireTimeout: 60000
});

Pool Methods

Get Connection

Gets a connection from the pool.

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

Usage Examples:

// Basic connection acquisition
pool.getConnection((err, connection) => {
  if (err) throw err;
  
  connection.query('SELECT * FROM users', (error, results) => {
    // Release connection back to pool
    connection.release();
    
    if (error) throw error;
    console.log(results);
  });
});

// With proper error handling
pool.getConnection((err, connection) => {
  if (err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      console.error('Database connection was closed.');
    }
    if (err.code === 'ER_CON_COUNT_ERROR') {
      console.error('Database has too many connections.');
    }
    if (err.code === 'ECONNREFUSED') {
      console.error('Database connection was refused.');
    }
    return;
  }
  
  // Use connection
  connection.query('SELECT something FROM sometable', (error, results) => {
    connection.release();
    if (error) throw error;
    // Handle results
  });
});

Direct Query

Executes a query using an available connection from the pool.

/**
 * Executes query using pool connection
 * @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
 */
pool.query(sql, values, callback);

Usage Examples:

// Simple pool query
pool.query('SELECT * FROM users', (error, results, fields) => {
  if (error) throw error;
  console.log(results);
});

// Parameterized pool query
pool.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
  if (error) throw error;
  console.log(results[0]);
});

// Event-based pool query
const query = pool.query('SELECT * FROM large_table');
query.on('result', (row) => {
  console.log(row);
});
query.on('end', () => {
  console.log('Query completed');
});

End Pool

Closes all connections in the pool.

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

Usage Example:

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

Pool Connection Methods

Pool connections are extended Connection instances with additional pool-specific methods.

Release Connection

Returns a connection to the pool for reuse.

/**
 * Releases connection back to pool
 */
connection.release();

Destroy Pool Connection

Removes and destroys a connection from the pool.

/**
 * Destroys the connection (removes from pool)
 */
connection.destroy();

Pool Connection Usage:

pool.getConnection((err, connection) => {
  if (err) throw err;
  
  // Check if connection is still good
  connection.ping((err) => {
    if (err) {
      // Connection is bad, destroy it
      connection.destroy();
      return;
    }
    
    // Use connection
    connection.query('SELECT * FROM users', (error, results) => {
      if (error) {
        // Release connection even on error
        connection.release();
        throw error;
      }
      
      // Release connection back to pool
      connection.release();
      console.log(results);
    });
  });
});

Pool Utility Methods

Escape

Escapes a value for safe SQL usage.

/**
 * Escapes a value for SQL
 * @param {*} value - The value to escape
 * @returns {string} Escaped string value
 */
pool.escape(value);

Escape Identifier

Escapes an identifier for SQL.

/**
 * Escapes an identifier for SQL
 * @param {*} value - The value to escape
 * @returns {string} Escaped identifier
 */
pool.escapeId(value);

Pool Properties

// Pool instance properties
interface Pool {
  config: PoolConfig;          // Pool configuration
  _allConnections: Connection[]; // All connections (active + free)
  _freeConnections: Connection[]; // Available connections
  _acquiringConnections: Connection[]; // Connections being acquired
  _connectionQueue: Function[]; // Queue of pending connection requests
  _closed: boolean;           // Whether pool is closed
}

Pool Events

Connection Event

Fired when a new connection is created in the pool.

pool.on('connection', (connection) => {
  console.log('New connection established as id ' + connection.threadId);
});

Acquire Event

Fired when a connection is acquired from the pool.

pool.on('acquire', (connection) => {
  console.log('Connection %d acquired', connection.threadId);
});

Release Event

Fired when a connection is released back to the pool.

pool.on('release', (connection) => {
  console.log('Connection %d released', connection.threadId);
});

Enqueue Event

Fired when a connection request is queued due to pool limits.

pool.on('enqueue', () => {
  console.log('Waiting for available connection slot');
});

Complete Pool Example:

const mysql = require('mysql');

// Create pool
const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  acquireTimeout: 60000,
  reconnect: true
});

// Set up event handlers
pool.on('connection', (connection) => {
  console.log('New connection established as id ' + connection.threadId);
});

pool.on('acquire', (connection) => {
  console.log('Connection %d acquired', connection.threadId);
});

pool.on('release', (connection) => {
  console.log('Connection %d released', connection.threadId);
});

pool.on('enqueue', () => {
  console.log('Waiting for available connection slot');
});

// Use pool for queries
async function getUser(userId) {
  return new Promise((resolve, reject) => {
    pool.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
      if (error) {
        reject(error);
      } else {
        resolve(results[0]);
      }
    });
  });
}

// Use pool with manual connection management
function getUserWithTransaction(userId, callback) {
  pool.getConnection((err, connection) => {
    if (err) return callback(err);
    
    connection.beginTransaction((err) => {
      if (err) {
        connection.release();
        return callback(err);
      }
      
      connection.query('SELECT * FROM users WHERE id = ?', [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[0]);
        });
      });
    });
  });
}

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

Pool Best Practices

Connection Limits: Set appropriate connectionLimit based on your database server capacity and application needs
Error Handling: Always handle connection acquisition errors and release connections properly
Timeouts: Configure acquireTimeout to prevent indefinite waiting for connections
Monitoring: Use pool events to monitor connection usage and detect issues
Graceful Shutdown: Always close pools before application shutdown to prevent resource leaks
Connection Health: Consider implementing connection health checks for long-running applications
Queue Management: Set queueLimit to prevent memory issues during high load