or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

clustering.mdconnection.mdindex.mdpooling.mdqueries.mdsecurity.md
tile.json

tessl/npm-mysql

A comprehensive MySQL database driver for Node.js providing connection management, query execution, connection pooling, and cluster support.

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
npmpkg:npm/mysql@2.18.x

To install, run

npx @tessl/cli install tessl/npm-mysql@2.18.0

index.mddocs/

MySQL

MySQL is a comprehensive Node.js driver for MySQL databases providing connection management, query execution, connection pooling, and cluster support. It offers both simple connection interfaces for basic applications and advanced features like connection pooling, clustering, and streaming for enterprise applications.

Package Information

  • Package Name: mysql
  • Package Type: npm
  • Language: JavaScript
  • Installation: npm install mysql

Core Imports

const mysql = require('mysql');

For ES6 modules:

import * as mysql from 'mysql';

Basic Usage

const mysql = require('mysql');

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

// Connect and query
connection.connect();

connection.query('SELECT * FROM users WHERE id = ?', [1], (error, results, fields) => {
  if (error) throw error;
  console.log(results);
});

connection.end();

Architecture

MySQL is built around several key components:

  • Connection Management: Individual database connections with lifecycle management and event-driven architecture
  • Connection Pooling: Efficient connection pooling with configurable limits, queuing, and automatic connection recovery
  • Cluster Support: Pool clustering with load balancing, failover capabilities, and namespace-based routing
  • Query Engine: Support for parameterized queries, prepared statements, transactions, and streaming results
  • Security Layer: Built-in SQL injection protection with escaping utilities and prepared statement support
  • Type System: Complete MySQL type constants and field metadata support

Capabilities

Connection Management

Core database connection functionality providing direct MySQL server connectivity with full lifecycle management, transaction support, and event-driven architecture.

function createConnection(config);

// Connection configuration
interface ConnectionConfig {
  host?: string;
  port?: number;
  user?: string;
  password?: string;
  database?: string;
  charset?: string;
  timezone?: string;
  connectTimeout?: number;
  acquireTimeout?: number;
  timeout?: number;
  reconnect?: boolean;
  ssl?: boolean | object;
}

Connection Management

Connection Pooling

Advanced connection pooling system for managing multiple database connections efficiently. Ideal for applications with concurrent database access requirements and production environments.

function createPool(config);

// Pool configuration extends ConnectionConfig
interface PoolConfig extends ConnectionConfig {
  connectionLimit?: number;
  queueLimit?: number;
  acquireTimeout?: number;
  reconnect?: boolean;
}

Connection Pooling

Cluster Management

Pool clustering functionality with load balancing and failover support. Perfect for distributed applications requiring high availability and horizontal scaling across multiple MySQL servers.

function createPoolCluster(config);

// Cluster configuration
interface ClusterConfig {
  canRetry?: boolean;
  removeNodeErrorCount?: number;
  restoreNodeTimeout?: number;
  defaultSelector?: string;
}

Cluster Management

Query Execution

Comprehensive query execution system supporting parameterized queries, streaming results, and event-driven processing. Includes full transaction support and prepared statements.

function createQuery(sql, values, callback);

// Query options
interface QueryOptions {
  sql: string;
  values?: any[];
  timeout?: number;
  nestTables?: boolean | string;
  typeCast?: boolean | function;
}

Query Execution

Security & Utilities

SQL injection protection and utility functions for safe database interactions. Includes escaping functions, raw SQL wrappers, and SQL formatting utilities.

function escape(value, stringifyObjects, timeZone);
function escapeId(value, forbidQualified);
function format(sql, values, stringifyObjects, timeZone);
function raw(sql): object;

Security & Utilities

Types

MySQL Field Types

Complete MySQL field type constants for handling database metadata and type-specific operations.

const Types = {
  DECIMAL: 0,
  TINY: 1,
  SHORT: 2,
  LONG: 3,
  FLOAT: 4,
  DOUBLE: 5,
  NULL: 6,
  TIMESTAMP: 7,
  LONGLONG: 8,
  INT24: 9,
  DATE: 10,
  TIME: 11,
  DATETIME: 12,
  YEAR: 13,
  NEWDATE: 14,
  VARCHAR: 15,
  BIT: 16,
  TIMESTAMP2: 17,
  DATETIME2: 18,
  TIME2: 19,
  JSON: 245,
  NEWDECIMAL: 246,
  ENUM: 247,
  SET: 248,
  TINY_BLOB: 249,
  MEDIUM_BLOB: 250,
  LONG_BLOB: 251,
  BLOB: 252,
  VAR_STRING: 253,
  STRING: 254,
  GEOMETRY: 255
};

// Client capability flags
const CLIENT_FLAGS = {
  CLIENT_LONG_PASSWORD: 1,     // Use the improved version of Old Password Authentication
  CLIENT_FOUND_ROWS: 2,        // Send found rows instead of affected rows
  CLIENT_LONG_FLAG: 4,         // Allow more column flags
  CLIENT_CONNECT_WITH_DB: 8,   // Database (schema) name can be specified on connect
  CLIENT_NO_SCHEMA: 16,        // Don't allow database.table.column syntax
  CLIENT_COMPRESS: 32,         // Use compression protocol
  CLIENT_ODBC: 64,             // ODBC client
  CLIENT_LOCAL_FILES: 128,     // Enable LOAD DATA LOCAL INFILE
  CLIENT_IGNORE_SPACE: 256,    // Parser can ignore spaces before '('
  CLIENT_PROTOCOL_41: 512,     // New 4.1 protocol
  CLIENT_INTERACTIVE: 1024,    // This is an interactive client
  CLIENT_SSL: 2048,            // Switch to SSL after handshake
  CLIENT_IGNORE_SIGPIPE: 4096, // IGNORE sigpipes
  CLIENT_TRANSACTIONS: 8192,   // Client knows about transactions
  CLIENT_RESERVED: 16384,      // Old flag for 4.1 protocol
  CLIENT_SECURE_CONNECTION: 32768, // New 4.1 authentication
  CLIENT_MULTI_STATEMENTS: 65536,  // Enable/disable multi-stmt support
  CLIENT_MULTI_RESULTS: 131072,    // Enable/disable multi-results
  CLIENT_PS_MULTI_RESULTS: 262144, // Multi-results in PS-protocol
  CLIENT_PLUGIN_AUTH: 524288,      // Client supports plugin authentication
  CLIENT_CONNECT_ATTRS: 1048576,   // Client supports connection attributes
  CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA: 2097152, // Enable authentication response packet
  CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS: 4194304,   // Can handle server requests for expired password
  CLIENT_SESSION_TRACK: 8388608,   // Client supports session tracking
  CLIENT_DEPRECATE_EOF: 16777216   // Client no longer needs EOF packet
};

Common Interfaces

// Query result structure
interface QueryResult {
  results: any[];
  fields: FieldInfo[];
}

// Field metadata
interface FieldInfo {
  catalog: string;
  db: string;
  table: string;
  orgTable: string;
  name: string;
  orgName: string;
  charsetNr: number;
  length: number;
  type: number;
  flags: number;
  decimals: number;
  default?: any;
  zeroFill: boolean;
  protocol41: boolean;
}

// Error structure
interface MysqlError extends Error {
  code: string;           // Error code (e.g., 'ER_DUP_ENTRY', 'PROTOCOL_CONNECTION_LOST')
  errno: number;          // MySQL error number
  sqlState?: string;      // SQL state code (5-character string)
  sqlMessage?: string;    // MySQL server error message
  sql?: string;           // SQL query that caused the error
  fatal: boolean;         // Whether the error is fatal (connection lost)
  fieldCount?: number;    // Number of fields (for result errors)
  index?: number;         // Parameter index (for parameter errors)
}