Connection Management

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

// Connection configuration options
interface ConnectionConfig {
  host?: string;           // MySQL server hostname (default: 'localhost')
  port?: number;           // MySQL server port (default: 3306)
  localAddress?: string;   // Local interface to bind for network connections
  socketPath?: string;     // Path to Unix socket file (alternative to host/port)
  user?: string;           // MySQL username
  password?: string;       // MySQL password
  database?: string;       // Database name to connect to
  charset?: string;        // Connection charset (default: 'UTF8_GENERAL_CI')
  charsetNumber?: number;  // Character set number (internal)
  timezone?: string;       // Timezone for date/time values (default: 'local')
  connectTimeout?: number; // Connection timeout in milliseconds (default: 10000)
  acquireTimeout?: number; // Timeout for acquiring connection (default: 10000)
  timeout?: number;        // Query timeout in milliseconds (default: 0 - no timeout)
  reconnect?: boolean;     // Automatically reconnect when connection is lost (default: true)
  ssl?: boolean | object;  // SSL configuration or boolean to enable SSL
  debug?: boolean | string[]; // Enable debug logging
  trace?: boolean;         // Generate stack traces on errors (default: true)
  insecureAuth?: boolean;  // Allow old authentication method (default: false)
  supportBigNumbers?: boolean;  // Support big numbers (more than 2^53) (default: false)
  bigNumberStrings?: boolean;   // Force big numbers to be returned as strings (default: false)
  dateStrings?: boolean;        // Force date types to be returned as strings (default: false)
  stringifyObjects?: boolean;   // Stringify objects instead of converting to values (default: false)
  multipleStatements?: boolean; // Allow multiple SQL statements per query (default: false)
  localInfile?: boolean;        // Allow LOAD DATA LOCAL INFILE (default: true)
  typeCast?: boolean | function; // Cast field types on receipt (default: true)
  queryFormat?: function;       // Custom query format function
  flags?: string;               // Connection flags
  clientFlags?: number;         // Client capability flags (bitwise OR of CLIENT_* constants)
  maxPacketSize?: number;       // Maximum packet size in bytes (default: 0 - use server default)
  acquireTimeout?: number;      // Timeout for acquiring connection in milliseconds (default: 10000)
  timeout?: number;             // Timeout for all queries in milliseconds (default: 0 - no timeout)
  pingInterval?: number;        // Interval for automatic pings in milliseconds (default: 0 - disabled)
}

const mysql = require('mysql');

// Basic connection
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp'
});

// Connection with SSL
const secureConnection = mysql.createConnection({
  host: 'mysql.example.com',
  user: 'app_user',
  password: 'secure_password',
  database: 'production_db',
  ssl: {
    ca: fs.readFileSync('./server-ca.pem'),
    cert: fs.readFileSync('./client-cert.pem'),
    key: fs.readFileSync('./client-key.pem')
  }
});

// Connection string format
const connection2 = mysql.createConnection('mysql://user:password@host:port/database');

// Advanced connection with extended options
const advancedConnection = mysql.createConnection({
  host: 'mysql.example.com',
  user: 'app_user',
  password: 'secure_password',
  database: 'production_db',
  supportBigNumbers: true,
  bigNumberStrings: true,
  dateStrings: false,
  timezone: 'UTC',
  typeCast: function (field, next) {
    if (field.type === 'TINY' && field.length === 1) {
      return (field.string() === '1'); // Convert TINYINT(1) to boolean
    }
    return next();
  },
  localInfile: false,
  stringifyObjects: false,
  trace: true
});

/**
 * Establishes connection to MySQL server
 * @param {object} [options] - Connection options
 * @param {function} [callback] - Callback function (err)
 */
connection.connect(options, callback);

/**
 * Changes the user for the connection
 * @param {object} options - User change options
 * @param {string} options.user - New username
 * @param {string} options.password - New password
 * @param {string} [options.charset] - Connection charset
 * @param {string} [options.database] - Database to switch to
 * @param {function} [callback] - Callback function (err)
 */
connection.changeUser(options, callback);

/**
 * Executes a SQL query
 * @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);

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

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

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

/**
 * Starts a transaction
 * @param {object} [options] - Transaction options
 * @param {function} [callback] - Callback function (err)
 */
connection.beginTransaction(options, callback);

/**
 * Commits current transaction
 * @param {object} [options] - Commit options
 * @param {function} [callback] - Callback function (err)
 */
connection.commit(options, callback);

/**
 * Rolls back current transaction
 * @param {object} [options] - Rollback options
 * @param {function} [callback] - Callback function (err)
 */
connection.rollback(options, callback);

connection.beginTransaction((err) => {
  if (err) throw err;
  
  connection.query('INSERT INTO users SET ?', {name: 'John', email: 'john@example.com'}, (error, results) => {
    if (error) {
      return connection.rollback(() => {
        throw error;
      });
    }
    
    connection.query('INSERT INTO profiles SET ?', {user_id: results.insertId, bio: 'Developer'}, (error, results) => {
      if (error) {
        return connection.rollback(() => {
          throw error;
        });
      }
      
      connection.commit((err) => {
        if (err) {
          return connection.rollback(() => {
            throw err;
          });
        }
        console.log('Transaction completed!');
      });
    });
  });
});

/**
 * Gracefully closes connection
 * @param {object} [options] - End options
 * @param {function} [callback] - Callback function (err)
 */
connection.end(options, callback);

/**
 * Forcefully closes connection
 */
connection.destroy();

/**
 * Pauses the connection (stops reading from socket)
 */
connection.pause();

/**
 * Resumes the connection (restarts reading from socket)
 */
connection.resume();

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

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

/**
 * Pings the MySQL server
 * @param {object} [options] - Ping options
 * @param {function} [callback] - Callback function (err)
 */
connection.ping(options, callback);

/**
 * Gets server statistics
 * @param {object} [options] - Statistics options
 * @param {function} [callback] - Callback function (err, stats)
 */
connection.statistics(options, callback);

connection.statistics((error, stats) => {
  if (error) throw error;
  console.log('Server statistics:', stats);
  // Stats is a string containing various server metrics
});

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

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

/**
 * Formats SQL with values
 * @param {string} sql - SQL string with placeholders
 * @param {array} values - Values to insert
 * @returns {string} Formatted SQL string
 */
connection.format(sql, values);

// Connection instance properties
interface Connection {
  config: ConnectionConfig;    // Connection configuration
  state: string;              // Connection state: 'disconnected', 'connected', 'authenticated', 'protocol_error'
  threadId: number | null;    // MySQL thread ID for this connection
}

connection.on('connect', () => {
  console.log('Connected to MySQL server');
});

connection.on('error', (err) => {
  console.error('Connection error:', err);
  if (err.code === 'PROTOCOL_CONNECTION_LOST') {
    // Handle connection lost
  }
});

connection.on('end', (err) => {
  console.log('Connection ended');
});

connection.on('drain', () => {
  console.log('Write buffer drained');
});

connection.on('enqueue', (sequence) => {
  console.log('Sequence enqueued:', sequence);
});

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  reconnect: true
});

// Set up event handlers
connection.on('connect', () => {
  console.log('Connected to MySQL server');
});

connection.on('error', (err) => {
  console.error('Database error:', err);
  if (err.code === 'PROTOCOL_CONNECTION_LOST') {
    console.log('Reconnecting...');
  }
});

// Connect and use
connection.connect((err) => {
  if (err) {
    console.error('Error connecting:', err);
    return;
  }
  
  // Perform queries...
  connection.query('SELECT NOW() as now', (error, results) => {
    if (error) throw error;
    console.log('Server time:', results[0].now);
    
    connection.end();
  });
});