api-database-mysql

MySQL Patterns (mysql2)

Quick Guide: Use mysql2/promise for all new code -- it provides async/await support over the mysql2 callback API. Always use createPool() (never createConnection() in production) with execute() for parameterized queries (prepared statements, LRU-cached). Type query results with RowDataPacket generics for SELECTs and ResultSetHeader for INSERT/UPDATE/DELETE. For transactions, acquire a dedicated connection with pool.getConnection(), wrap in try/finally to guarantee connection.release(). Never interpolate user input into SQL strings -- always use ? placeholders. Handle ER_DUP_ENTRY and ER_LOCK_DEADLOCK explicitly in catch blocks.

<critical_requirements>

CRITICAL: Before Using This Skill

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST use execute() with ? placeholders for ALL queries containing user input -- NEVER interpolate values into SQL strings with template literals or string concatenation)

(You MUST use pool.getConnection() for transactions and release the connection in a finally block -- pool convenience methods (pool.execute()) use a different connection per call and cannot maintain transaction state)

(You MUST always import from mysql2/promise for async/await code -- the base mysql2 module returns callback-based objects that do not support await)

(You MUST handle the pool error event -- unhandled connection errors crash the Node.js process)

</critical_requirements>

Examples

• [Core Patterns](examples/core.md) -- Pool setup, typed queries, prepared statements, connection lifecycle
• [Transactions](examples/transactions.md) -- Manual transactions, savepoints, deadlock retry, nested operations
• [Streaming & Batch](examples/streaming.md) -- Streaming large result sets, batch inserts, multiple statements
• [Error Handling](examples/error-handling.md) -- MySQL error codes, connection errors, retry strategies, graceful degradation
• [Configuration](examples/configuration.md) -- SSL/TLS, named placeholders, pool tuning, monitoring events

Additional resources:

• [reference.md](reference.md) -- Type cheat sheet, pool options, error codes, production checklist

Auto-detection: MySQL, mysql2, mysql2/promise, createPool, createConnection, RowDataPacket, ResultSetHeader, execute, prepared statement, pool.getConnection, beginTransaction, commit, rollback, ER_DUP_ENTRY, ER_LOCK_DEADLOCK, connectionLimit, SHOW TABLES, mysqldump, InnoDB, MariaDB

When to use:

• Direct SQL queries against MySQL or MariaDB databases
• Connection pool management for server applications
• Transactions requiring atomicity across multiple queries
• Streaming large result sets without loading all rows into memory
• Typed query results with TypeScript generics
• Batch inserts or multi-statement operations

Key patterns covered:

• Pool creation with mysql2/promise and proper configuration
• Prepared statements via execute() with ? placeholders
• TypeScript generics with RowDataPacket and ResultSetHeader
• Transaction lifecycle: getConnection -> beginTransaction -> commit/rollback -> release
• Streaming with connection.query().stream() on the non-promise API
• Error handling for ER_DUP_ENTRY, ER_LOCK_DEADLOCK, connection failures
• Pool events (acquire, release, enqueue) for monitoring
• SSL/TLS and named placeholders configuration

When NOT to use:

• When your project already uses an ORM or query builder for MySQL -- use that tool's skill instead
• For in-memory caching or key-value storage (use a dedicated caching solution)
• For document databases or graph queries (wrong database type)
• For one-off CLI scripts where a single connection suffices and pool overhead is unnecessary

<philosophy>

Philosophy

mysql2 is a low-level MySQL driver -- it sends SQL to MySQL and returns typed results. It does not generate SQL, manage migrations, or handle schema changes.

Core principles:

1. Pools, not connections -- Production applications should always use createPool(). Pools manage connection lifecycle, handle reconnection, and prevent connection exhaustion. createConnection() is only appropriate for one-off scripts.
2. Prepared statements always -- execute() sends parameterized queries to MySQL's prepared statement protocol. The driver caches prepared statements in an LRU cache, so repeated queries skip the preparation step. Never use query() with string interpolation.
3. Type your results -- MySQL2's TypeScript generics (RowDataPacket, ResultSetHeader) eliminate any from query results. Define interfaces extending RowDataPacket for each table shape.
4. Transactions need dedicated connections -- Pool convenience methods (pool.execute(), pool.query()) may use different connections for each call. Transactions require pool.getConnection() to pin a single connection, with connection.release() in a finally block.
5. Fail explicitly -- MySQL errors carry structured code fields (ER_DUP_ENTRY, ER_LOCK_DEADLOCK). Check error.code in catch blocks rather than parsing message strings.

</philosophy>

<patterns>

Core Patterns

Pattern 1: Pool Setup with mysql2/promise

Create a connection pool with environment-based configuration and error handling. See [examples/core.md](examples/core.md) for the complete setup pattern.

// Good Example - Production pool setup
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

const DEFAULT_CONNECTION_LIMIT = 10;
const DEFAULT_IDLE_TIMEOUT_MS = 60_000;

function createDatabasePool(): Pool {
  const url = process.env.DATABASE_URL;
  if (!url) {
    throw new Error("DATABASE_URL environment variable is required");
  }

  return mysql.createPool({
    uri: url,
    waitForConnections: true,
    connectionLimit: DEFAULT_CONNECTION_LIMIT,
    maxIdle: DEFAULT_CONNECTION_LIMIT,
    idleTimeout: DEFAULT_IDLE_TIMEOUT_MS,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0,
  });
}

export { createDatabasePool };

Why good: Environment variable validation, named constants for limits, waitForConnections: true queues requests instead of throwing, enableKeepAlive prevents stale connections

// Bad Example - Hardcoded single connection
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password123",
  database: "mydb",
});
// Hardcoded credentials, single connection exhausts under load, no pool

Why bad: Hardcoded credentials leak in version control, single connection cannot handle concurrent requests, no automatic reconnection

Pattern 2: Typed Queries with Generics

Use RowDataPacket for SELECTs and ResultSetHeader for mutations. See [examples/core.md](examples/core.md) for all type patterns.

// Good Example - Typed SELECT and INSERT
import type { Pool, RowDataPacket, ResultSetHeader } from "mysql2/promise";

interface UserRow extends RowDataPacket {
  id: number;
  email: string;
  name: string;
  created_at: Date;
}

async function getUserById(
  pool: Pool,
  userId: number,
): Promise<UserRow | null> {
  const [rows] = await pool.execute<UserRow[]>(
    "SELECT id, email, name, created_at FROM users WHERE id = ?",
    [userId],
  );
  return rows[0] ?? null;
}

async function createUser(
  pool: Pool,
  email: string,
  name: string,
): Promise<number> {
  const [result] = await pool.execute<ResultSetHeader>(
    "INSERT INTO users (email, name) VALUES (?, ?)",
    [email, name],
  );
  return result.insertId;
}

Why good: Interface extends RowDataPacket for type safety, execute() uses prepared statements, destructured [rows] skips field metadata, null check for missing rows

// Bad Example - Untyped