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

promises.mddocs/

Promise Integration

Promise-based wrappers providing modern async/await support for all database operations with full compatibility for contemporary JavaScript development patterns.

Capabilities

Promise Module Import

Import the promise-based version of MySQL2 for async/await support.

// CommonJS
const mysql = require('mysql2/promise');

// ES Modules
import mysql from 'mysql2/promise';
import { createConnection, createPool, createPoolCluster } from 'mysql2/promise';

Promise Connection Creation

Creates a promise-based database connection that resolves when the connection is established.

/**
 * Create promise-based connection
 * @param config - Connection configuration or connection string
 * @returns Promise resolving to PromiseConnection
 */
function createConnection(config: ConnectionOptions | string): Promise<PromiseConnection>;

Usage Examples:

const mysql = require('mysql2/promise');

async function main() {
  try {
    // Create connection with async/await
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'testdb'
    });
    
    console.log('Connected to database');
    
    // Use connection...
    await connection.end();
  } catch (error) {
    console.error('Connection failed:', error);
  }
}

// Using connection string
async function connectWithUri() {
  const connection = await mysql.createConnection('mysql://user:password@localhost:3306/database');
  return connection;
}

Promise Pool Creation

Creates a promise-based connection pool for scalable database operations.

/**
 * Create promise-based connection pool
 * @param config - Pool configuration or connection string
 * @returns PromisePool instance
 */
function createPool(config: PoolOptions | string): PromisePool;

Usage Examples:

// Create promise pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'testdb',
  connectionLimit: 10,
  waitForConnections: true
});

// Use pool directly with async/await
async function queryUsers() {
  try {
    const [rows, fields] = await pool.execute('SELECT * FROM users WHERE active = ?', [true]);
    return rows;
  } catch (error) {
    console.error('Query failed:', error);
    throw error;
  }
}

// Get individual connection from pool
async function usePoolConnection() {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    const [result] = await connection.execute('INSERT INTO users (name) VALUES (?)', ['John']);
    await connection.execute('INSERT INTO logs (user_id, action) VALUES (?, ?)', [result.insertId, 'created']);
    
    await connection.commit();
    console.log('Transaction completed');
  } catch (error) {
    await connection.rollback();
    console.error('Transaction failed:', error);
    throw error;
  } finally {
    connection.release();
  }
}

Promise Pool Cluster Creation

Creates a promise-based pool cluster for multi-database operations.

/**
 * Create promise-based pool cluster
 * @param config - Pool cluster configuration options
 * @returns PromisePoolCluster instance
 */
function createPoolCluster(config?: PoolClusterOptions): PromisePoolCluster;

Usage Examples:

// Create promise-based pool cluster
const cluster = mysql.createPoolCluster({
  canRetry: true,
  removeNodeErrorCount: 5,
  restoreNodeTimeout: 50000
});

// Add pools to cluster
cluster.add('MASTER', { host: 'master.db.com', user: 'app', password: 'secret', database: 'prod' });
cluster.add('SLAVE1', { host: 'slave1.db.com', user: 'app', password: 'secret', database: 'prod' });
cluster.add('SLAVE2', { host: 'slave2.db.com', user: 'app', password: 'secret', database: 'prod' });

// Use cluster with async/await
async function readFromSlaves() {
  try {
    const [rows] = await cluster.execute('SLAVE*', 'SELECT * FROM products ORDER BY created_at DESC LIMIT 10');
    return rows;
  } catch (error) {
    console.error('Cluster query failed:', error);
    throw error;
  }
}

async function writeToMaster() {
  const connection = await cluster.getConnection('MASTER');
  try {
    const [result] = await connection.execute('INSERT INTO products (name, price) VALUES (?, ?)', ['Widget', 29.99]);
    return result.insertId;
  } finally {
    connection.release();
  }
}

Promise Connection Interface

PromiseConnection Class

Promise-based connection providing async/await methods for all database operations.

interface PromiseConnection extends EventEmitter {
  /** Connection configuration */
  config: ConnectionOptions;
  
  /** Connection thread ID */
  threadId: number;
  
  /** Execute SQL query with promise */
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  query(options: QueryOptions): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  
  /** Execute prepared statement with promise */
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
  
  /** Create prepared statement with promise */
  prepare(sql: string): Promise<PreparedStatementInfo>;
  
  /** Begin transaction with promise */
  beginTransaction(): Promise<void>;
  
  /** Commit transaction with promise */
  commit(): Promise<void>;
  
  /** Rollback transaction with promise */
  rollback(): Promise<void>;
  
  /** Change user with promise */
  changeUser(options: ConnectionOptions): Promise<void>;
  
  /** Ping server with promise */
  ping(): Promise<void>;
  
  /** End connection with promise */
  end(): Promise<void>;
  
  /** Force close connection */
  destroy(): void;
  
  /** Pause connection */
  pause(): void;
  
  /** Resume connection */
  resume(): void;
  
  /** Escape SQL value */
  escape(value: any): string;
  
  /** Escape SQL identifier */
  escapeId(value: string | string[]): string;
  
  /** Format SQL query */
  format(sql: string, values?: any[]): string;
}

PreparedStatementInfo Interface

Promise-based prepared statement interface.

interface PreparedStatementInfo {
  /** Execute prepared statement with parameters */
  execute(parameters?: any | any[] | { [param: string]: any }): Promise<[
    RowDataPacket[][] | RowDataPacket[] | OkPacket | OkPacket[] | ResultSetHeader,
    FieldPacket[]
  ]>;
  
  /** Close prepared statement */
  close(): Promise<void>;
}

Promise Pool Interface

PromisePool Class

Promise-based pool providing async connection management and query execution.

interface PromisePool extends EventEmitter {
  /** Get connection from pool with promise */
  getConnection(): Promise<PromisePoolConnection>;
  
  /** Release connection back to pool */
  releaseConnection(connection: PromisePoolConnection): void;
  
  /** Execute query on pool with promise */
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  
  /** Execute prepared statement on pool with promise */
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
  
  /** Close pool with promise */
  end(): Promise<void>;
  
  /** Escape SQL value */
  escape(value: any): string;
  
  /** Escape SQL identifier */
  escapeId(value: string | string[]): string;
  
  /** Format SQL query */
  format(sql: string, values?: any[]): string;
  
  /** Underlying pool object */
  pool: Pool;
}

PromisePoolConnection Interface

Promise-based pool connection with release method.

interface PromisePoolConnection extends PromiseConnection {
  /** Release connection back to pool */
  release(): void;
  
  /** Underlying connection object */
  connection: PromiseConnection;
}

Promise Pool Cluster Interface

PromisePoolCluster Class

Promise-based pool cluster for multi-database operations.

interface PromisePoolCluster extends EventEmitter {
  /** Cluster configuration */
  config: PoolClusterOptions;
  
  /** Add pool to cluster */
  add(config: PoolOptions): void;
  add(group: string, config: PoolOptions): void;
  add(group: string, connectionUri: string): void;
  
  /** Remove pool from cluster */
  remove(pattern?: string): void;
  
  /** Get connection from cluster with promise */
  getConnection(): Promise<PromisePoolConnection>;
  getConnection(group: string): Promise<PromisePoolConnection>;
  getConnection(group: string, selector: string): Promise<PromisePoolConnection>;
  
  /** Get pool namespace for pattern */
  of(pattern: string, selector?: string): PromisePoolNamespace;
  
  /** Execute query on cluster with promise */
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  
  /** Execute prepared statement on cluster with promise */
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
  
  /** Close cluster with promise */
  end(): Promise<void>;
}

PromisePoolNamespace Interface

Promise-based pool namespace for pattern-based operations.

interface PromisePoolNamespace {
  /** Get connection from namespace with promise */
  getConnection(): Promise<PromisePoolConnection>;
  
  /** Execute query on namespace with promise */
  query(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  query(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]>;
  
  /** Execute prepared statement on namespace with promise */
  execute(sql: string): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
  execute(sql: string, values: any[]): Promise<[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader, FieldPacket[]]>;
}

Advanced Promise Patterns

Connection Pooling with Async/Await

const mysql = require('mysql2/promise');

class DatabaseService {
  constructor() {
    this.pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      connectionLimit: 10,
      acquireTimeout: 30000,
      waitForConnections: true
    });
  }
  
  async getUser(id) {
    const [rows] = await this.pool.execute('SELECT * FROM users WHERE id = ?', [id]);
    return rows[0];
  }
  
  async createUser(userData) {
    const [result] = await this.pool.execute(
      'INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())',
      [userData.name, userData.email]
    );
    return result.insertId;
  }
  
  async updateUser(id, updates) {
    const fields = Object.keys(updates).map(key => `${key} = ?`).join(', ');
    const values = [...Object.values(updates), id];
    
    const [result] = await this.pool.execute(
      `UPDATE users SET ${fields} WHERE id = ?`,
      values
    );
    return result.affectedRows > 0;
  }
  
  async close() {
    await this.pool.end();
  }
}

Transaction Management with Promises

async function transferFunds(fromAccountId, toAccountId, amount) {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    // Check source account balance
    const [fromAccount] = await connection.execute(
      'SELECT balance FROM accounts WHERE id = ? FOR UPDATE',
      [fromAccountId]
    );
    
    if (fromAccount[0].balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    // Debit source account
    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [amount, fromAccountId]
    );
    
    // Credit destination account
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toAccountId]
    );
    
    // Record transaction
    await connection.execute(
      'INSERT INTO transactions (from_account, to_account, amount, created_at) VALUES (?, ?, ?, NOW())',
      [fromAccountId, toAccountId, amount]
    );
    
    await connection.commit();
    console.log('Transfer completed successfully');
    
  } catch (error) {
    await connection.rollback();
    console.error('Transfer failed:', error.message);
    throw error;
  } finally {
    connection.release();
  }
}

Prepared Statements with Promises

async function optimizedUserQueries() {
  const connection = await mysql.createConnection(config);
  
  try {
    // Prepare statement once
    const getUserStatement = await connection.prepare('SELECT * FROM users WHERE department = ? AND active = ?');
    
    // Execute multiple times with different parameters
    const [engineering] = await getUserStatement.execute(['Engineering', true]);
    const [marketing] = await getUserStatement.execute(['Marketing', true]);
    const [sales] = await getUserStatement.execute(['Sales', true]);
    
    console.log('Engineering users:', engineering.length);
    console.log('Marketing users:', marketing.length);
    console.log('Sales users:', sales.length);
    
    // Close prepared statement
    await getUserStatement.close();
    
  } finally {
    await connection.end();
  }
}

Error Handling with Async/Await

async function robustDatabaseOperation() {
  let connection;
  
  try {
    connection = await mysql.createConnection(config);
    
    const [results] = await connection.execute('SELECT * FROM users WHERE active = ?', [true]);
    
    return results.map(user => ({
      id: user.id,
      name: user.name,
      email: user.email
    }));
    
  } catch (error) {
    if (error.code === 'ER_NO_SUCH_TABLE') {
      console.error('Table does not exist');
    } else if (error.code === 'ECONNREFUSED') {
      console.error('Database connection refused');
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      console.error('Database access denied');
    } else {
      console.error('Unexpected database error:', error);
    }
    
    throw error;
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

Utility Functions

The promise module includes the same utility functions as the callback module:

/** Escape SQL value */
const escape = mysql.escape;

/** Escape SQL identifier */
const escapeId = mysql.escapeId;

/** Format SQL query */
const format = mysql.format;

/** Create raw SQL object */
const raw = mysql.raw;

/** Set parser cache size */
const setMaxParserCache = mysql.setMaxParserCache;

/** Clear parser cache */
const clearParserCache = mysql.clearParserCache;

Constants

All constants are available in the promise module:

/** MySQL data types */
const Types = mysql.Types;

/** MySQL charsets */
const Charsets = mysql.Charsets;

/** Charset to encoding mappings */
const CharsetToEncoding = mysql.CharsetToEncoding;

docs

connections.md

index.md

pool-clusters.md

pools.md

promises.md

queries.md

server.md

sql-utilities.md

tile.json