CtrlK
BlogDocsLog inGet started
Tessl Logo

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

SecuritybySnyk

Pending

The risk profile of this skill

Overview
Eval results
Files

queries.mddocs/

Query Execution

Comprehensive query execution system supporting parameterized queries, streaming results, and event-driven processing. Includes full transaction support, prepared statements, and advanced query options for optimal performance and security.

Capabilities

Create Query

Creates a new query object for execution.

/**
 * Create a new Query instance
 * @param {string} sql - The SQL for the query
 * @param {array} [values] - Any values to insert into placeholders in sql
 * @param {function} [callback] - The callback to use when query is complete
 * @returns {Query} New query object
 */
function createQuery(sql, values, callback);

// Query options object
interface QueryOptions {
  sql: string;                    // SQL query string
  values?: any[];                 // Parameter values for placeholders
  timeout?: number;               // Query timeout in milliseconds
  nestTables?: boolean | string;  // Nest result tables by table name
  typeCast?: boolean | function;  // Enable/disable or custom type casting
}

Usage Examples:

const mysql = require('mysql');

// Create query with SQL string
const query1 = mysql.createQuery('SELECT * FROM users');

// Create query with parameters
const query2 = mysql.createQuery('SELECT * FROM users WHERE id = ?', [1]);

// Create query with callback
const query3 = mysql.createQuery('SELECT * FROM users', (error, results) => {
  if (error) throw error;
  console.log(results);
});

// Create query with options object
const query4 = mysql.createQuery({
  sql: 'SELECT * FROM users WHERE active = ?',
  values: [true],
  timeout: 30000,
  nestTables: true
});

Query Execution Methods

Queries can be executed using connections, pools, or directly on query objects.

Connection Query

Execute query on a specific connection.

/**
 * Executes a SQL query on 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
 */
connection.query(sql, values, callback);

Pool Query

Execute query using pool connection.

/**
 * 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);

Basic Query Examples:

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

// Parameterized query (prevents SQL injection)
connection.query('SELECT * FROM users WHERE id = ? AND active = ?', [userId, true], (error, results) => {
  if (error) throw error;
  console.log('User:', results[0]);
});

// INSERT query
connection.query('INSERT INTO users SET ?', {
  name: 'John Doe',
  email: 'john@example.com',
  active: true
}, (error, results) => {
  if (error) throw error;
  console.log('Inserted user with ID:', results.insertId);
});

// UPDATE query
connection.query('UPDATE users SET ? WHERE id = ?', [{name: 'Jane Doe'}, userId], (error, results) => {
  if (error) throw error;
  console.log('Updated rows:', results.affectedRows);
});

// DELETE query
connection.query('DELETE FROM users WHERE id = ?', [userId], (error, results) => {
  if (error) throw error;
  console.log('Deleted rows:', results.affectedRows);
});

Advanced Query Options

Nested Tables

Organize results by table name when joining multiple tables.

// Query with nested tables
connection.query({
  sql: 'SELECT u.*, p.* FROM users u LEFT JOIN profiles p ON u.id = p.user_id',
  nestTables: true
}, (error, results) => {
  if (error) throw error;
  
  results.forEach(row => {
    console.log('User:', row.u);      // User table data
    console.log('Profile:', row.p);   // Profile table data
  });
});

// Nested tables with separator
connection.query({
  sql: 'SELECT u.*, p.* FROM users u LEFT JOIN profiles p ON u.id = p.user_id',
  nestTables: '_'
}, (error, results) => {
  if (error) throw error;
  
  results.forEach(row => {
    // Fields will be named like: u_id, u_name, p_bio, etc.
    console.log(row);
  });
});

Type Casting

Control how MySQL types are converted to JavaScript types.

// Disable type casting (return raw buffer data)
connection.query({
  sql: 'SELECT * FROM users',
  typeCast: false
}, (error, results) => {
  if (error) throw error;
  console.log(results); // Raw buffer data
});

// Custom type casting function
connection.query({
  sql: 'SELECT * FROM users',
  typeCast: function (field, next) {
    if (field.type === 'TINY' && field.length === 1) {
      return (field.string() === '1'); // Convert TINYINT(1) to boolean
    }
    if (field.type === 'DATETIME') {
      return new Date(field.string()); // Convert to Date object
    }
    return next(); // Use default type casting
  }
}, (error, results) => {
  if (error) throw error;
  console.log(results);
});

Query Timeout

Set timeout for individual queries.

connection.query({
  sql: 'SELECT * FROM large_table',
  timeout: 30000 // 30 seconds
}, (error, results) => {
  if (error) {
    if (error.code === 'PROTOCOL_SEQUENCE_TIMEOUT') {
      console.error('Query timed out');
    }
    throw error;
  }
  console.log(results);
});

Event-Driven Query Processing

Queries return EventEmitter objects for advanced result handling.

Query Events

// Query event handlers
const query = connection.query('SELECT * FROM users');

query.on('result', (row, index) => {
  console.log('Row %d:', index, row);
});

query.on('fields', (fields, index) => {
  console.log('Fields for result set %d:', index, fields);
});

query.on('error', (err) => {
  console.error('Query error:', err);
});

query.on('end', () => {
  console.log('Query completed');
});

Large Result Set Handling:

const query = connection.query('SELECT * FROM large_table');

let rowCount = 0;

query.on('result', (row) => {
  // Pause connection to prevent memory overflow
  connection.pause();
  
  rowCount++;
  
  // Process row asynchronously
  processRow(row, () => {
    // Resume connection after processing
    connection.resume();
  });
});

query.on('end', () => {
  console.log('Processed %d rows', rowCount);
});

function processRow(row, callback) {
  // Simulate async processing
  setTimeout(() => {
    console.log('Processed row:', row.id);
    callback();
  }, 10);
}

Streaming Query Results

Convert query results to readable streams for memory-efficient processing.

/**
 * Returns query results as a readable stream
 * @param {object} [options] - Stream options
 * @param {number} [options.highWaterMark] - Stream buffer size (default: 16)
 * @param {string} [options.encoding] - Stream encoding (default: null)
 * @param {boolean} [options.objectMode] - Object mode (default: true for MySQL streams)
 * @returns {ReadableStream} Readable stream of query results
 */
query.stream(options);

// Stream options interface
interface StreamOptions {
  highWaterMark?: number;  // Buffer size for stream backpressure control
  encoding?: string;       // Stream encoding (usually null for object streams)
  objectMode?: boolean;    // Whether stream operates in object mode
}

Streaming Example:

const query = connection.query('SELECT * FROM large_table');
const stream = query.stream({highWaterMark: 5});

stream.on('data', (row) => {
  console.log('Stream row:', row);
});

stream.on('end', () => {
  console.log('Stream ended');
});

stream.on('error', (err) => {
  console.error('Stream error:', err);
});

Prepared Statements

MySQL package supports prepared statements through parameterized queries.

// Prepared statement with single parameter
const stmt1 = 'SELECT * FROM users WHERE id = ?';
connection.query(stmt1, [userId], callback);

// Prepared statement with multiple parameters
const stmt2 = 'SELECT * FROM users WHERE age > ? AND city = ?';
connection.query(stmt2, [25, 'New York'], callback);

// Prepared statement with object parameters
const stmt3 = 'INSERT INTO users SET ?';
connection.query(stmt3, [{name: 'John', email: 'john@example.com'}], callback);

// Prepared statement with mixed parameters
const stmt4 = 'UPDATE users SET ? WHERE id = ?';
connection.query(stmt4, [{name: 'Jane'}, userId], callback);

Multiple Statements

Execute multiple SQL statements in a single query (requires multipleStatements: true).

// Enable multiple statements in connection config
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  multipleStatements: true
});

// Execute multiple statements
connection.query('SELECT 1; SELECT 2; SELECT 3', (error, results) => {
  if (error) throw error;
  
  console.log('First result:', results[0]);  // SELECT 1 results
  console.log('Second result:', results[1]); // SELECT 2 results  
  console.log('Third result:', results[2]);  // SELECT 3 results
});

Query Properties and Methods

// Query instance properties
interface Query extends EventEmitter {
  sql: string;           // SQL query string
  values: any[];         // Parameter values
  
  // Methods
  stream(options?: StreamOptions): ReadableStream;
}

// Query result structure
interface QueryResult {
  // For SELECT queries
  results?: any[];       // Result rows
  fields?: FieldInfo[];  // Field metadata
  
  // For INSERT queries
  insertId?: number;     // Auto-increment ID of inserted row
  affectedRows?: number; // Number of affected rows
  
  // For UPDATE/DELETE queries
  affectedRows?: number; // Number of affected rows
  changedRows?: number;  // Number of actually changed rows
  
  // General properties
  fieldCount?: number;   // Number of fields
  serverStatus?: number; // Server status flags
  warningCount?: number; // Number of warnings
  message?: string;      // Server message
}

// Field information
interface FieldInfo {
  catalog: string;       // Catalog name
  db: string;           // Database name
  table: string;        // Table name
  orgTable: string;     // Original table name
  name: string;         // Column name
  orgName: string;      // Original column name
  charsetNr: number;    // Character set number
  length: number;       // Column length
  type: number;         // Column type (see Types constants)
  flags: number;        // Column flags
  decimals: number;     // Decimal places
  default?: any;        // Default value
  zeroFill: boolean;    // Zero fill flag
  protocol41: boolean;  // Protocol 4.1 flag
}

Transaction Queries

Queries within transactions follow the same patterns but require proper transaction management.

function performTransaction(callback) {
  connection.beginTransaction((err) => {
    if (err) return callback(err);
    
    // First query
    connection.query('INSERT INTO users SET ?', {name: 'John'}, (error, results) => {
      if (error) {
        return connection.rollback(() => {
          callback(error);
        });
      }
      
      const userId = results.insertId;
      
      // Second query
      connection.query('INSERT INTO profiles SET ?', {user_id: userId, bio: 'Developer'}, (error, results) => {
        if (error) {
          return connection.rollback(() => {
            callback(error);
          });
        }
        
        // Commit transaction
        connection.commit((err) => {
          if (err) {
            return connection.rollback(() => {
              callback(err);
            });
          }
          
          callback(null, {userId, profileId: results.insertId});
        });
      });
    });
  });
}

Query Best Practices

  1. Parameterized Queries: Always use parameterized queries to prevent SQL injection
  2. Error Handling: Implement comprehensive error handling for all query types
  3. Connection Management: Release connections promptly after query completion
  4. Large Results: Use streaming or pagination for large result sets
  5. Timeouts: Set appropriate query timeouts for long-running operations
  6. Type Casting: Use custom type casting for application-specific data transformations
  7. Transaction Management: Always handle transaction rollback scenarios
  8. Memory Management: Pause connections when processing large result sets to prevent memory issues

docs

clustering.md

connection.md

index.md

pooling.md

queries.md

security.md

tile.json