CtrlK
BlogDocsLog inGet started
Tessl Logo

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

SecuritybySnyk

Pending

The risk profile of this skill

Overview
Eval results
Files

server.mddocs/

Server Creation

MySQL server implementation for creating custom MySQL protocol servers, handling client connections, and building MySQL-compatible database services.

Capabilities

Create Server

Creates a MySQL protocol server that can accept client connections and handle MySQL commands.

/**
 * Create MySQL protocol server
 * @param handler - Optional connection handler function
 * @returns Server instance
 */
function createServer(handler?: (connection: Connection) => void): Server;

Usage Examples:

const mysql = require('mysql2');

// Create basic server
const server = mysql.createServer();

// Handle new connections
server.on('connection', (conn) => {
  console.log('New connection from:', conn.remoteAddress);
  
  // Handle queries from client
  conn.on('query', (sql) => {
    console.log('Received query:', sql);
    
    // Send mock response
    if (sql === 'SELECT 1') {
      conn.writeTextResult([{ '1': 1 }], [{ name: '1', type: 'LONG' }]);
    } else {
      conn.writeError({ message: 'Unknown query', code: 1064 });
    }
  });
});

// Start server
server.listen(3307, () => {
  console.log('MySQL server listening on port 3307');
});

// Create server with connection handler
const server2 = mysql.createServer((conn) => {
  console.log('Connection established');
  
  // Set connection attributes
  conn.serverHandshake({
    protocolVersion: 10,
    serverVersion: '5.7.0-mock',
    connectionId: Math.floor(Math.random() * 1000000),
    statusFlags: 2,
    characterSet: 8,
    capabilityFlags: 0xffffff
  });
});

Server Class

MySQL protocol server class extending Node.js net.Server.

interface Server extends EventEmitter {
  /** Start listening for connections */
  listen(port: number, callback?: () => void): void;
  listen(port: number, hostname: string, callback?: () => void): void;
  listen(options: { port: number; host?: string; backlog?: number }, callback?: () => void): void;
  
  /** Close server and stop accepting connections */
  close(callback?: (err?: Error) => void): void;
  
  /** Get server address information */
  address(): { port: number; family: string; address: string } | string | null;
  
  /** Maximum number of concurrent connections */
  maxConnections: number;
  
  /** Current number of connections */
  connections: number;
  
  /** Whether server is listening */
  listening: boolean;
}

Server Events

MySQL servers emit events for connection management and error handling.

// Event: 'connection' - New client connection
server.on('connection', (connection) => {
  console.log('Client connected:', connection.connectionId);
});

// Event: 'listening' - Server started listening
server.on('listening', () => {
  console.log('Server is listening');
});

// Event: 'close' - Server closed
server.on('close', () => {
  console.log('Server closed');
});

// Event: 'error' - Server error
server.on('error', (error) => {
  console.error('Server error:', error);
});

Connection Handling

Server connections provide methods for handling MySQL protocol communications.

interface ServerConnection extends EventEmitter {
  /** Connection ID */
  connectionId: number;
  
  /** Client remote address */
  remoteAddress: string;
  
  /** Client remote port */
  remotePort: number;
  
  /** Connection state */
  state: string;
  
  /** Send handshake to client */
  serverHandshake(options: HandshakeOptions): void;
  
  /** Write OK packet to client */
  writeOk(options?: OkPacketOptions): void;
  
  /** Write error packet to client */
  writeError(options: ErrorPacketOptions): void;
  
  /** Write result set to client */
  writeTextResult(rows: any[], fields: FieldDefinition[]): void;
  
  /** Write prepared statement response */
  writePreparedStatement(id: number, fields: FieldDefinition[], params: FieldDefinition[]): void;
  
  /** Write binary result set */
  writeBinaryResult(rows: any[], fields: FieldDefinition[]): void;
  
  /** Write EOF packet */
  writeEof(options?: EofPacketOptions): void;
  
  /** Close connection */
  close(): void;
  
  /** Destroy connection */
  destroy(): void;
}

Connection Events

Server connections emit events for handling MySQL protocol commands.

// Event: 'query' - Text query received
connection.on('query', (sql) => {
  console.log('Query:', sql);
  
  // Process query and send response
  if (sql.toUpperCase().startsWith('SELECT')) {
    // Handle SELECT
    connection.writeTextResult(rows, fields);
  } else if (sql.toUpperCase().startsWith('INSERT')) {
    // Handle INSERT
    connection.writeOk({ affectedRows: 1, insertId: 123 });
  } else {
    connection.writeError({ message: 'Unsupported query', code: 1064 });
  }
});

// Event: 'prepare' - Prepared statement creation
connection.on('prepare', (sql) => {
  console.log('Prepare:', sql);
  
  // Create prepared statement
  const statementId = Math.floor(Math.random() * 1000000);
  connection.writePreparedStatement(statementId, fields, params);
});

// Event: 'execute' - Prepared statement execution
connection.on('execute', (statementId, parameters) => {
  console.log('Execute statement:', statementId, 'with params:', parameters);
  
  // Execute prepared statement
  connection.writeBinaryResult(rows, fields);
});

// Event: 'close' - Connection closed
connection.on('close', () => {
  console.log('Connection closed');
});

// Event: 'error' - Connection error
connection.on('error', (error) => {
  console.error('Connection error:', error);
});

Server Configuration Interfaces

Handshake Options

Configuration for MySQL server handshake.

interface HandshakeOptions {
  /** Protocol version */
  protocolVersion?: number;
  
  /** Server version string */
  serverVersion?: string;
  
  /** Connection ID */
  connectionId?: number;
  
  /** Authentication plugin name */
  authPluginName?: string;
  
  /** Server capabilities */
  capabilityFlags?: number;
  
  /** Character set */
  characterSet?: number;
  
  /** Server status flags */
  statusFlags?: number;
  
  /** Authentication plugin data */
  authPluginData?: Buffer;
}

Packet Options

Options for various MySQL protocol packets.

interface OkPacketOptions {
  /** Number of affected rows */
  affectedRows?: number;
  
  /** Last insert ID */
  insertId?: number;
  
  /** Server status flags */
  serverStatus?: number;
  
  /** Warning count */
  warningCount?: number;
  
  /** Status message */
  message?: string;
}

interface ErrorPacketOptions {
  /** Error message */
  message: string;
  
  /** Error code */
  code?: number;
  
  /** SQL state */
  sqlState?: string;
}

interface EofPacketOptions {
  /** Warning count */
  warningCount?: number;
  
  /** Server status flags */
  statusFlags?: number;
}

Field Definition

MySQL field metadata for result sets.

interface FieldDefinition {
  /** Field name */
  name: string;
  
  /** Field type */
  type: number;
  
  /** Field length */
  length?: number;
  
  /** Field flags */
  flags?: number;
  
  /** Decimal places */
  decimals?: number;
  
  /** Database name */
  db?: string;
  
  /** Table name */
  table?: string;
  
  /** Original table name */
  orgTable?: string;
  
  /** Original field name */
  orgName?: string;
  
  /** Character set */
  charsetNr?: number;
}

Advanced Server Implementation

Database Proxy Server

const mysql = require('mysql2');
const backendPool = mysql.createPool({
  host: 'backend-db.example.com',
  user: 'proxy_user',
  password: 'proxy_password',
  database: 'production'
});

const proxyServer = mysql.createServer((conn) => {
  console.log(`Proxy connection ${conn.connectionId} from ${conn.remoteAddress}`);
  
  // Handle queries by forwarding to backend
  conn.on('query', async (sql) => {
    try {
      const [rows, fields] = await backendPool.execute(sql);
      
      if (Array.isArray(rows)) {
        // SELECT result
        conn.writeTextResult(rows, fields);
      } else {
        // INSERT/UPDATE/DELETE result
        conn.writeOk({
          affectedRows: rows.affectedRows,
          insertId: rows.insertId,
          message: rows.message
        });
      }
    } catch (error) {
      conn.writeError({
        message: error.message,
        code: error.errno || 1064,
        sqlState: error.sqlState || 'HY000'
      });
    }
  });
  
  // Send initial handshake
  conn.serverHandshake({
    protocolVersion: 10,
    serverVersion: '5.7.0-proxy',
    connectionId: conn.connectionId,
    characterSet: 8,
    capabilityFlags: 0xffffff
  });
});

proxyServer.listen(3308, () => {
  console.log('Database proxy server listening on port 3308');
});

Mock Database Server

const mockData = {
  users: [
    { id: 1, name: 'John Doe', email: 'john@example.com', active: 1 },
    { id: 2, name: 'Jane Smith', email: 'jane@example.com', active: 1 },
    { id: 3, name: 'Bob Johnson', email: 'bob@example.com', active: 0 }
  ]
};

const mockServer = mysql.createServer((conn) => {
  console.log(`Mock connection ${conn.connectionId}`);
  
  conn.on('query', (sql) => {
    console.log('Mock query:', sql);
    
    const upperSql = sql.toUpperCase().trim();
    
    if (upperSql === 'SELECT * FROM users') {
      // Return all users
      conn.writeTextResult(mockData.users, [
        { name: 'id', type: mysql.Types.LONG },
        { name: 'name', type: mysql.Types.VAR_STRING },
        { name: 'email', type: mysql.Types.VAR_STRING },
        { name: 'active', type: mysql.Types.TINY }
      ]);
    } else if (upperSql.startsWith('SELECT * FROM users WHERE')) {
      // Simple WHERE clause parsing
      const activeMatch = sql.match(/active\s*=\s*(\d+)/i);
      if (activeMatch) {
        const active = parseInt(activeMatch[1]);
        const filteredUsers = mockData.users.filter(user => user.active === active);
        conn.writeTextResult(filteredUsers, [
          { name: 'id', type: mysql.Types.LONG },
          { name: 'name', type: mysql.Types.VAR_STRING },
          { name: 'email', type: mysql.Types.VAR_STRING },
          { name: 'active', type: mysql.Types.TINY }
        ]);
      } else {
        conn.writeError({ message: 'Unsupported WHERE clause', code: 1064 });
      }
    } else if (upperSql === 'SELECT 1') {
      // Ping query
      conn.writeTextResult([{ '1': 1 }], [{ name: '1', type: mysql.Types.LONG }]);
    } else {
      conn.writeError({ message: 'Unsupported query', code: 1064 });
    }
  });
  
  // Initial handshake
  conn.serverHandshake({
    protocolVersion: 10,
    serverVersion: '5.7.0-mock',
    connectionId: conn.connectionId,
    characterSet: 33, // UTF8_GENERAL_CI
    capabilityFlags: 0xffffff
  });
});

mockServer.listen(3309, () => {
  console.log('Mock database server listening on port 3309');
});

Connection Authentication

const authenticatedServer = mysql.createServer((conn) => {
  let authenticated = false;
  
  conn.on('authenticate', (user, password, database) => {
    console.log(`Authentication attempt: ${user}@${database}`);
    
    // Simple authentication logic
    if (user === 'testuser' && password === 'testpass') {
      authenticated = true;
      conn.writeOk({ message: 'Authentication successful' });
    } else {
      conn.writeError({
        message: 'Access denied',
        code: 1045,
        sqlState: '28000'
      });
      conn.close();
    }
  });
  
  conn.on('query', (sql) => {
    if (!authenticated) {
      conn.writeError({
        message: 'Not authenticated',
        code: 1045,
        sqlState: '28000'
      });
      return;
    }
    
    // Handle authenticated queries...
    conn.writeOk({ message: 'Query executed' });
  });
  
  // Custom handshake with authentication
  conn.serverHandshake({
    protocolVersion: 10,
    serverVersion: '5.7.0-auth',
    connectionId: conn.connectionId,
    authPluginName: 'mysql_native_password',
    characterSet: 33,
    capabilityFlags: 0xffffff
  });
});

authenticatedServer.listen(3310, () => {
  console.log('Authenticated server listening on port 3310');
});

Server Lifecycle Management

Graceful Shutdown

const server = mysql.createServer();
const connections = new Set();

server.on('connection', (conn) => {
  connections.add(conn);
  
  conn.on('close', () => {
    connections.delete(conn);
  });
});

// Graceful shutdown
process.on('SIGTERM', () => {
  console.log('Shutting down server...');
  
  server.close(() => {
    console.log('Server closed');
    
    // Close all connections
    connections.forEach(conn => conn.close());
    
    process.exit(0);
  });
});

server.listen(3307);

Error Handling

const server = mysql.createServer();

server.on('error', (error) => {
  if (error.code === 'EADDRINUSE') {
    console.error('Port already in use');
  } else if (error.code === 'EACCES') {
    console.error('Permission denied');
  } else {
    console.error('Server error:', error);
  }
});

server.on('connection', (conn) => {
  conn.on('error', (error) => {
    console.error(`Connection ${conn.connectionId} error:`, error);
  });
});

docs

connections.md

index.md

pool-clusters.md

pools.md

promises.md

queries.md

server.md

sql-utilities.md

tile.json