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

Query Execution

Name: tessl/npm-mysql2
Author: tessl

Comprehensive query execution with support for text queries, prepared statements, parameter binding, and result streaming for optimal performance and security.

Capabilities

Query Method

Execute SQL queries with optional parameter binding and callback handling.

/**
 * Execute SQL query
 * @param sql - SQL query string
 * @param values - Optional parameter values for placeholders
 * @param callback - Optional callback function
 * @returns Query object for chaining or streaming
 */
query(sql: string, callback?: QueryCallback): Query;
query(sql: string, values: any[], callback?: QueryCallback): Query;
query(options: QueryOptions, callback?: QueryCallback): Query;

Usage Examples:

const mysql = require('mysql2');
const connection = mysql.createConnection(config);

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

// Query with parameters
connection.query('SELECT * FROM users WHERE age > ? AND city = ?', [25, 'New York'], (error, results, fields) => {
  if (error) throw error;
  console.log('Matching users:', results);
});

// Query with named parameters
connection.query('SELECT * FROM users WHERE age > :minAge AND city = :city', {
  minAge: 25,
  city: 'New York'
}, (error, results, fields) => {
  if (error) throw error;
  console.log('Matching users:', results);
});

// Query with options object
connection.query({
  sql: 'SELECT * FROM users WHERE created_at > ?',
  values: [new Date('2023-01-01')],
  timeout: 10000,
  typeCast: function(field, next) {
    if (field.type === 'TINY' && field.length === 1) {
      return (field.string() === '1');
    }
    return next();
  }
}, (error, results) => {
  if (error) throw error;
  console.log(results);
});

Execute Method

Execute prepared statements with parameter binding for optimal performance and security.

/**
 * Execute prepared statement
 * @param sql - SQL statement with placeholders
 * @param values - Parameter values for placeholders
 * @param callback - Callback function
 */
execute(sql: string, values?: any[], callback?: ExecuteCallback): void;

Usage Examples:

// Execute prepared statement
connection.execute('SELECT * FROM users WHERE id = ?', [123], (error, results, fields) => {
  if (error) throw error;
  console.log('User:', results[0]);
});

// Insert with prepared statement
connection.execute(
  'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
  ['John Doe', 'john@example.com', 30],
  (error, results, fields) => {
    if (error) throw error;
    console.log('User inserted with ID:', results.insertId);
  }
);

// Update with prepared statement
connection.execute(
  'UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?',
  ['newemail@example.com', 123],
  (error, results, fields) => {
    if (error) throw error;
    console.log('Rows affected:', results.affectedRows);
  }
);

Query Object

Query object returned by query method providing streaming and event handling capabilities.

interface Query extends EventEmitter {
  /** SQL query string */
  sql: string;
  
  /** Parameter values */
  values?: any[];
  
  /** Query timeout */
  timeout?: number;
  
  /** Custom type casting function */
  typeCast?: boolean | TypeCastFunction;
  
  /** Return rows as arrays instead of objects */
  rowsAsArray?: boolean;
  
  /** Nested table handling */
  nestTables?: boolean | string;
  
  /** Stream query results */
  stream(options?: StreamOptions): QueryStream;
}

Query Options

Configuration interface for query execution.

interface QueryOptions {
  /** SQL query string */
  sql: string;
  
  /** Parameter values for placeholders */
  values?: any[];
  
  /** Query timeout in milliseconds */
  timeout?: number;
  
  /** Custom type casting function */
  typeCast?: boolean | TypeCastFunction;
  
  /** Return rows as arrays instead of objects */
  rowsAsArray?: boolean;
  
  /** Nested table handling */
  nestTables?: boolean | string;
  
  /** Use named placeholders */
  namedPlaceholders?: boolean;
  
  /** Return dates as strings */
  dateStrings?: boolean | string[];
  
  /** Support big numbers */
  supportBigNumbers?: boolean;
  
  /** Return big numbers as strings */
  bigNumberStrings?: boolean;
  
  /** Insert ID as number */
  insertIdAsNumber?: boolean;
  
  /** Decimal numbers handling */
  decimalNumbers?: boolean;
}

Type Casting

Custom type casting for converting MySQL types to JavaScript types.

/**
 * Type cast function interface
 * @param field - Field metadata
 * @param next - Default casting function
 * @returns Converted value
 */
type TypeCastFunction = (field: FieldPacket, next: () => any) => any;

Usage Examples:

// Custom type casting
const typeCast = function(field, next) {
  // Convert TINYINT(1) to boolean
  if (field.type === 'TINY' && field.length === 1) {
    return (field.string() === '1');
  }
  
  // Convert DECIMAL to number
  if (field.type === 'NEWDECIMAL') {
    return parseFloat(field.string());
  }
  
  // Convert DATE to custom format
  if (field.type === 'DATE') {
    return new Date(field.string() + 'T00:00:00.000Z');
  }
  
  // Use default casting for other types
  return next();
};

connection.query({
  sql: 'SELECT * FROM products',
  typeCast: typeCast
}, (error, results) => {
  // Results with custom type casting applied
});

Prepared Statements

Manual prepared statement management for advanced usage scenarios.

/**
 * Create prepared statement
 * @param sql - SQL statement with placeholders
 * @param callback - Callback with prepared statement
 */
prepare(sql: string, callback?: (err: Error | null, statement?: PreparedStatement) => void): void;

/**
 * Remove prepared statement from cache
 * @param sql - SQL statement to unprepare
 */
unprepare(sql: string): void;

Usage Examples:

// Prepare statement
connection.prepare('SELECT * FROM users WHERE department = ? AND active = ?', (err, statement) => {
  if (err) throw err;
  
  // Execute multiple times with different parameters
  statement.execute(['Engineering', true], (error, results) => {
    console.log('Engineering users:', results);
  });
  
  statement.execute(['Marketing', true], (error, results) => {
    console.log('Marketing users:', results);
  });
  
  // Close prepared statement
  statement.close();
});

Result Streaming

Stream large result sets to handle memory efficiently.

/**
 * Stream query results
 * @param options - Stream options
 * @returns Readable stream of rows
 */
stream(options?: StreamOptions): QueryStream;

Usage Examples:

// Stream large result set
const query = connection.query('SELECT * FROM large_table');

query.stream({ objectMode: true })
  .on('result', (row) => {
    // Process each row individually
    console.log('Processing row:', row.id);
    
    // Pause stream if needed
    connection.pause();
    
    // Resume after processing
    setTimeout(() => {
      connection.resume();
    }, 100);
  })
  .on('error', (error) => {
    console.error('Stream error:', error);
  })
  .on('end', () => {
    console.log('Stream ended');
  });

Transaction Support

Transaction methods for maintaining data consistency.

/** Begin transaction */
beginTransaction(callback?: (err: Error | null) => void): void;

/** Commit current transaction */
commit(callback?: (err: Error | null) => void): void;

/** Rollback current transaction */
rollback(callback?: (err: Error | null) => void): void;

Usage Examples:

// Transaction example
connection.beginTransaction((err) => {
  if (err) throw err;
  
  connection.query('INSERT INTO accounts (name, balance) VALUES (?, ?)', ['Alice', 1000], (error, results) => {
    if (error) {
      return connection.rollback(() => {
        throw error;
      });
    }
    
    connection.query('INSERT INTO transactions (account_id, amount, type) VALUES (?, ?, ?)', 
      [results.insertId, 1000, 'deposit'], (error, results) => {
      if (error) {
        return connection.rollback(() => {
          throw error;
        });
      }
      
      connection.commit((err) => {
        if (err) {
          return connection.rollback(() => {
            throw err;
          });
        }
        console.log('Transaction completed successfully!');
      });
    });
  });
});

Query Result Types

Select Results

interface RowDataPacket {
  [column: string]: any;
}

// Results array with metadata
type SelectResults = [RowDataPacket[], FieldPacket[]];

Insert/Update/Delete Results

interface OkPacket {
  /** Number of rows affected */
  affectedRows: number;
  
  /** Auto-increment ID of inserted row */
  insertId: number;
  
  /** Server status flags */
  serverStatus: number;
  
  /** Warning count */
  warningCount: number;
  
  /** Info message from server */
  message: string;
  
  /** Protocol version */
  protocol41: boolean;
  
  /** Number of changed rows */
  changedRows: number;
}

// Results for modification queries
type ModificationResults = [OkPacket, FieldPacket[]];

Field Metadata

interface FieldPacket {
  /** Field name */
  name: string;
  
  /** Field type */
  type: number;
  
  /** Field length */
  length: number;
  
  /** Database name */
  db: string;
  
  /** Table name */
  table: string;
  
  /** Original table name */
  orgTable: string;
  
  /** Original field name */
  orgName: string;
  
  /** Character set */
  charsetNr: number;
  
  /** Field flags */
  flags: number;
  
  /** Decimal places */
  decimals: number;
  
  /** Default value */
  default?: any;
  
  /** Zero fill flag */
  zeroFill: boolean;
  
  /** Field protocol version */
  protocol41: boolean;
}

Error Handling

Query operations can encounter various error conditions:

connection.query('SELECT * FROM invalid_table', (error, results, fields) => {
  if (error) {
    console.error('Error code:', error.code);
    console.error('Error number:', error.errno);
    console.error('SQL state:', error.sqlState);
    console.error('SQL message:', error.sqlMessage);
    console.error('Query:', error.sql);
    return;
  }
  
  console.log(results);
});

Common error codes: