Promise MySQL Wrapper

Common errors

• Cannot find module 'mysql'

  cause The `promise-mysql` package is a wrapper and depends on the `mysql` package (specifically `mysqljs/mysql`) as a peer dependency. This error occurs if `mysql` is not installed alongside `promise-mysql`.
  fix Install the underlying MySQL driver: `npm install mysql` or `yarn add mysql`.

• Error: Connection lost: The server closed the connection.

  cause This error often indicates that the MySQL server unexpectedly closed the connection, possibly due to inactivity timeouts, server restarts, or network issues. While `promise-mysql` has a `reconnect` option (default `true`), persistent connection loss indicates deeper issues.
  fix Ensure your MySQL server is stable and accessible. Check server logs for connection errors. For long-running applications, utilize connection pools (`mysql.createPool`) rather than individual connections (`mysql.createConnection`), as pools inherently handle connection re-establishment and rotation. If `reconnect` is explicitly set to `false`, consider enabling it.

• TypeError: connection.query is not a function

  cause This usually happens if you're trying to call `query` on an object that isn't a valid `promise-mysql` connection or pool object, or if `connection` itself is `undefined` because `createConnection` or `getConnection` failed or wasn't awaited.
  fix Verify that `mysql.createConnection()` or `pool.getConnection()` were successfully `await`ed and resolved to a valid connection object. Ensure you are calling `query` on the object returned by these methods. Also, avoid mixing `promise-mysql` with the callback-based `mysqljs/mysql` API directly.

Warnings

• gotcha This package wraps `mysqljs/mysql` (v2.x), which is largely unmaintained (last update over 4 years ago). For new projects or better performance, consider `mysql2/promise` (v3.x or later) which offers native promise support, prepared statements, and active development.
  Fix: For new projects, evaluate `mysql2` which has built-in promise support and is actively maintained. If migrating an existing `promise-mysql` project, be aware of potential API differences with `mysql2`.
• gotcha Promise-mysql uses the Bluebird promise library internally. While functional, modern Node.js versions have highly optimized native Promises. Relying on Bluebird adds an extra dependency and can occasionally lead to subtle interop issues or differences in promise behavior compared to native Promises.
  Fix: Ensure consistent promise usage across your codebase. If using `async/await`, the integration is usually seamless, but be mindful of Bluebird-specific promise methods if you delve into advanced promise patterns.
• breaking Upgrading from `promise-mysql` v3 to v4 changed `mysql.createPool` to return a `Promise` directly, rather than the pool object itself. This was a significant shift in the API's asynchronous behavior.
  Fix: Ensure that calls to `mysql.createPool()` are `await`ed or chained with `.then()`, as they now return a Promise that resolves to the pool object. For example, `const pool = await mysql.createPool(options);`.
• gotcha Connections from a pool *must* be explicitly released back to the pool using `connection.release()` when they are no longer needed. Failing to do so will lead to connection leaks, eventually exhausting the pool and causing 'Too many connections' errors or connection timeouts.
  Fix: Always use a `try...finally` block when acquiring a connection from a pool to ensure `connection.release()` is called, regardless of whether operations succeed or fail. Example: `let connection; try { connection = await pool.getConnection(); /* ... */ } finally { if (connection) connection.release(); }`.

Install

• npm install promise-mysql npm
• yarn add promise-mysql yarn
• pnpm add promise-mysql pnpm

Imports

• createConnection
  wrong:

  import { createConnection } from 'promise-mysql';

  correct:

  import mysql from 'promise-mysql';
  const connection = await mysql.createConnection(options);

  The library exports a default object containing all methods. Direct named imports are incorrect. As a CommonJS module, `require` is the canonical way to import.
• createPool
  wrong:

  import { createPool } from 'promise-mysql';

  correct:

  const mysql = require('promise-mysql');
  const pool = await mysql.createPool(options);

  This is a CommonJS module, so `require` is the standard import mechanism. Named imports with `import { ... }` will not work directly.
• query
  wrong:

  connection.query('SELECT * FROM users', (err, rows) => {});

  correct:

  const [rows, fields] = await connection.query('SELECT * FROM users');

  The primary benefit of `promise-mysql` is its promise-based API. While the underlying `mysql` driver supports callbacks, directly using callbacks with `promise-mysql` defeats its purpose and can lead to unhandled promise rejections if errors occur.

Quickstart

Demonstrates connecting to a MySQL database, performing DDL and DML operations (create table, insert, select, delete), and properly closing the connection using async/await with promise-mysql. Environment variables are used for credentials for security.

const mysql = require('promise-mysql');

async function runDbOperations() {
  let connection; // Declare connection outside try block for finally
  try {
    connection = await mysql.createConnection({
      host: process.env.DB_HOST ?? 'localhost',
      user: process.env.DB_USER ?? 'root',
      password: process.env.DB_PASSWORD ?? 'password',
      database: process.env.DB_NAME ?? 'test_db'
    });

    console.log('Database connected successfully!');

    // Create a table if it doesn't exist
    await connection.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL
      );
    `);
    console.log('Users table ensured.');

    // Insert a new user
    const insertResult = await connection.query('INSERT INTO users (name, email) VALUES (?, ?)', ['Alice', 'alice@example.com']);
    console.log('Inserted user:', insertResult.insertId);

    // Select all users
    const users = await connection.query('SELECT * FROM users');
    console.log('Users:', users);

    // Clean up (optional: delete the user for idempotency)
    await connection.query('DELETE FROM users WHERE email = ?', ['alice@example.com']);
    console.log('Cleaned up user.');

  } catch (err) {
    console.error('Database operation failed:', err);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Connection closed.');
    }
  }
}

runDbOperations();