tessl/npm-mysql2

Fast MySQL client for Node.js with support for prepared statements, SSL, compression, and promise-based APIs

—

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 Pools

Name: tessl/npm-mysql2
Author: tessl

Connection pooling for scalable database access with automatic connection management, load balancing, and efficient resource utilization for high-throughput applications.

Capabilities

Create Pool

Creates a connection pool with the specified configuration.

/**
 * Creates a connection pool
 * @param config - Pool configuration object or connection string
 * @returns Pool instance
 */
function createPool(config: PoolOptions | string): Pool;

Usage Examples:

const mysql = require('mysql2');

// Create a pool with configuration
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'testdb',
  connectionLimit: 10,
  waitForConnections: true,
  queueLimit: 0
});

// Execute queries directly on pool
pool.query('SELECT * FROM users', (error, results) => {
  if (error) throw error;
  console.log(results);
});

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

Pool Class

Main pool class providing connection management and query execution methods.

interface Pool extends EventEmitter {
  /** Pool configuration */
  config: PoolOptions;
  
  /** Get connection from pool */
  getConnection(callback: (err: Error | null, connection?: PoolConnection) => void): void;
  
  /** Release connection back to pool */
  releaseConnection(connection: PoolConnection): void;
  
  /** Execute SQL query using pool connection */
  query(sql: string, callback?: QueryCallback): Query;
  query(sql: string, values: any[], callback?: QueryCallback): Query;
  query(options: QueryOptions, callback?: QueryCallback): Query;
  
  /** Execute prepared statement using pool connection */
  execute(sql: string, values?: any[], callback?: ExecuteCallback): void;
  
  /** Close all connections in pool */
  end(callback?: (err: Error | null) => void): void;
  
  /** Escape SQL value */
  escape(value: any): string;
  
  /** Escape SQL identifier */
  escapeId(value: string | string[]): string;
  
  /** Format SQL query with values */
  format(sql: string, values?: any[]): string;
}

Pool Connection

Connection instance obtained from a pool with additional pool-specific methods.

interface PoolConnection extends Connection {
  /** Release connection back to pool */
  release(): void;
  
  /** Destroy connection without returning to pool */
  destroy(): void;
  
  /** Underlying connection object */
  connection: Connection;
}

Pool Options

Configuration interface for connection pools extending ConnectionOptions.

interface PoolOptions extends ConnectionOptions {
  /** Maximum number of connections in pool */
  connectionLimit?: number;
  
  /** Wait for available connection when limit reached */
  waitForConnections?: boolean;
  
  /** Maximum number of queued connection requests */
  queueLimit?: number;
  
  /** Maximum number of idle connections */
  maxIdle?: number;
  
  /** Idle connection timeout in milliseconds */
  idleTimeout?: number;
  
  /** Acquire connection timeout in milliseconds */
  acquireTimeout?: number;
  
  /** Enable connection queue timeout */
  enableKeepAlive?: boolean;
  
  /** Keep alive initial delay */
  keepAliveInitialDelay?: number;
}

Pool Events

Pools emit events for connection lifecycle management.

// Event: 'connection' - New connection added to pool
pool.on('connection', (connection) => {
  console.log('New connection added as id ' + connection.threadId);
});

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

// Event: 'release' - Connection released back to pool
pool.on('release', (connection) => {
  console.log('Connection %d released', connection.threadId);
});

// Event: 'enqueue' - Connection request queued
pool.on('enqueue', () => {
  console.log('Waiting for available connection slot');
});

// Event: 'error' - Pool error occurred
pool.on('error', (error) => {
  console.error('Pool error:', error);
});

Advanced Pool Usage

Transaction Management with Pools:

pool.getConnection((err, connection) => {
  if (err) throw err;
  
  connection.beginTransaction((err) => {
    if (err) {
      connection.release();
      throw err;
    }
    
    connection.query('INSERT INTO users SET ?', userData, (error, results) => {
      if (error) {
        return connection.rollback(() => {
          connection.release();
          throw error;
        });
      }
      
      connection.query('INSERT INTO profiles SET ?', profileData, (error, results) => {
        if (error) {
          return connection.rollback(() => {
            connection.release();
            throw error;
          });
        }
        
        connection.commit((err) => {
          if (err) {
            return connection.rollback(() => {
              connection.release();
              throw err;
            });
          }
          
          console.log('Transaction completed successfully!');
          connection.release();
        });
      });
    });
  });
});

Pool Monitoring:

// Monitor pool status
setInterval(() => {
  console.log({
    totalConnections: pool._allConnections.length,
    freeConnections: pool._freeConnections.length,
    connectionQueue: pool._connectionQueue.length
  });
}, 5000);

Pool Management Best Practices

Connection Limits

const pool = mysql.createPool({
  // Set reasonable connection limit based on database capacity
  connectionLimit: 10,
  
  // Don't let connection requests wait indefinitely
  acquireTimeout: 30000,
  
  // Limit queue size to prevent memory issues
  queueLimit: 50,
  
  // Clean up idle connections
  maxIdle: 5,
  idleTimeout: 60000
});

Graceful Shutdown

process.on('SIGINT', async () => {
  console.log('Closing connection pool...');
  
  pool.end((err) => {
    if (err) {
      console.error('Error closing pool:', err);
      process.exit(1);
    }
    console.log('Pool closed successfully');
    process.exit(0);
  });
});

Error Handling

Pool operations can encounter various error conditions:

pool.getConnection((err, connection) => {
  if (err) {
    if (err.code === 'POOL_CLOSED') {
      console.error('Pool has been closed');
    } else if (err.code === 'POOL_CONNLIMIT') {
      console.error('Connection limit reached');
    } else if (err.code === 'POOL_ENQUEUELIMIT') {
      console.error('Queue limit reached');
    }
    return;
  }
  
  // Use connection...
  connection.release();
});