agents-inc/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 -- Pool setup, typed queries, prepared statements, connection lifecycle
• Transactions -- Manual transactions, savepoints, deadlock retry, nested operations
• Streaming & Batch -- Streaming large result sets, batch inserts, multiple statements
• Error Handling -- MySQL error codes, connection errors, retry strategies, graceful degradation
• Configuration -- SSL/TLS, named placeholders, pool tuning, monitoring events

Additional resources:

• 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 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 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 query with interpolation
const [rows] = await pool.query(`SELECT * FROM users WHERE id = ${userId}`);
// SQL injection vulnerability, untyped results, query() skips prepared statements

Why bad: SQL injection via string interpolation, any-typed results, query() does not use prepared statement protocol

---

Pattern 3: Transaction with Dedicated Connection

Transactions require a single connection from the pool. See examples/transactions.md for savepoints, deadlock retry, and nested operations.

// Good Example - Transfer with transaction
async function transferFunds(
  pool: Pool,
  fromId: number,
  toId: number,
  amount: number,
): Promise<void> {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    await connection.execute(
      "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
      [amount, fromId, amount],
    );
    await connection.execute(
      "UPDATE accounts SET balance = balance + ? WHERE id = ?",
      [amount, toId],
    );

    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

Why good: getConnection() pins one connection, finally guarantees release even on error, rollback() in catch prevents partial commits, balance check in SQL prevents overdraft

---

Pattern 4: Streaming Large Result Sets

Use the callback-based API for streaming -- the promise API does not support .stream(). See examples/streaming.md for backpressure handling and transform streams.

// Good Example - Stream rows without loading all into memory
import mysql from "mysql2";

function streamUsers(
  pool: ReturnType<typeof mysql.createPool>,
): NodeJS.ReadableStream {
  return pool
    .query("SELECT * FROM users WHERE active = 1")
    .stream({ highWaterMark: 100 });
}

Why good: highWaterMark controls buffer size, rows are emitted one at a time via Node.js stream interface, constant memory usage regardless of result set size

When to use: Result sets with 10K+ rows, ETL pipelines, CSV exports, data migrations

---

Pattern 5: Error Code Handling

MySQL errors carry structured code and errno fields. See examples/error-handling.md for retry strategies and connection error handling.

// Good Example - Structured error handling
import type { Pool, ResultSetHeader } from "mysql2/promise";

const MYSQL_ER_DUP_ENTRY = "ER_DUP_ENTRY";

interface MysqlError extends Error {
  code: string;
  errno: number;
  sqlState: string;
  sqlMessage: string;
}

function isMysqlError(error: unknown): error is MysqlError {
  return error instanceof Error && "code" in error && "errno" in error;
}

async function createUserSafe(
  pool: Pool,
  email: string,
  name: string,
): Promise<{ insertId: number } | { duplicate: true }> {
  try {
    const [result] = await pool.execute<ResultSetHeader>(
      "INSERT INTO users (email, name) VALUES (?, ?)",
      [email, name],
    );
    return { insertId: result.insertId };
  } catch (error) {
    if (isMysqlError(error) && error.code === MYSQL_ER_DUP_ENTRY) {
      return { duplicate: true };
    }
    throw error;
  }
}

Why good: Type guard for MySQL errors, named constant for error code, returns discriminated union instead of throwing on expected errors, re-throws unexpected errors

</patterns>

---

<performance>

Performance Optimization

execute() vs query()

execute() uses MySQL's binary prepared statement protocol with an LRU cache. The first call prepares the statement; subsequent calls with the same SQL reuse the cached preparation, skipping the parse step. Use execute() for all parameterized queries.

query() sends the full SQL text each time. Use query() only for dynamic SQL where the statement text itself changes (e.g., dynamic column lists), or when streaming (.stream() is not available on the promise API's execute).

Pool Sizing

connectionLimit = (number of CPU cores * 2) + number of disk spindles

For cloud databases, start with connectionLimit: 10 and increase under load testing. The MySQL server's max_connections must accommodate all application instances' pools combined.

enableKeepAlive

Set enableKeepAlive: true to prevent firewalls and load balancers from dropping idle connections. Without this, connections that idle for longer than the intermediary's timeout are silently dropped, causing ECONNRESET errors on the next query.

Batch Inserts

For inserting many rows, use a single INSERT ... VALUES (...), (...), (...) statement instead of individual inserts -- see examples/streaming.md.

</performance>

---

<decision_framework>

Decision Framework

Pool vs Connection

What am I building?
-- Production server handling concurrent requests? -> createPool()
-- One-off CLI script or migration? -> createConnection() is acceptable
-- Serverless function (Lambda, Vercel)? -> createPool() with connectionLimit: 1

execute() vs query()

Does the SQL have user-provided parameters?
-- YES -> execute() with ? placeholders (ALWAYS)
-- NO, but same SQL runs repeatedly? -> execute() (benefits from LRU cache)
-- NO, SQL text itself is dynamic? -> query() (cannot prepare dynamic SQL)
-- Need to stream results? -> query().stream() on the callback API

Pool method vs getConnection()

Is this a single query?
-- YES -> pool.execute() or pool.query() (auto-acquires and releases)
-- NO, multiple queries needing same connection? -> pool.getConnection()
-- Transaction? -> pool.getConnection() (REQUIRED)

Error Handling Strategy

What MySQL error did I get?
-- ER_DUP_ENTRY (1062) -> Handle as business logic (return conflict, not throw)
-- ER_LOCK_DEADLOCK (1213) -> Retry the entire transaction (MySQL rolled it back)
-- ER_LOCK_WAIT_TIMEOUT (1205) -> Retry or fail with timeout message
-- ECONNREFUSED / PROTOCOL_CONNECTION_LOST -> Connection issue, pool will reconnect
-- ER_ACCESS_DENIED_ERROR (1045) -> Configuration error, fail fast

</decision_framework>

---

<red_flags>

RED FLAGS

High Priority Issues:

• Interpolating user input into SQL strings (\SELECT * FROM users WHERE id = ${id}`) -- SQL injection vulnerability, always use ?placeholders withexecute()`
• Using pool.execute() or pool.query() for transactions -- each call may use a different connection, breaking transaction isolation; use pool.getConnection()
• Importing from mysql2 instead of mysql2/promise for async/await code -- the base module returns callback-based objects, await will not work as expected
• Not releasing connections acquired with pool.getConnection() -- connection leak exhausts the pool; always release in a finally block

Medium Priority Issues:

• Using query() instead of execute() for parameterized queries -- misses prepared statement caching and binary protocol efficiency
• Missing pool error event handler -- unhandled connection errors crash the Node.js process
• Setting connectionLimit too high -- each MySQL connection uses ~10 MB of server memory; 10-20 is usually sufficient
• Not setting enableKeepAlive: true -- idle connections get dropped by firewalls/load balancers causing ECONNRESET

Common Mistakes:

• Expecting pool.end() to wait for active queries -- it immediately destroys all connections; drain queries first
• Using rows.length to check if an UPDATE affected rows -- use result.affectedRows from ResultSetHeader instead
• Assuming insertId is always the auto-increment value -- for INSERT ... ON DUPLICATE KEY UPDATE, insertId is 0 if the existing row was updated, not inserted
• Calling connection.release() after connection.destroy() -- destroy removes the connection from the pool entirely; release returns it
• Treating null and undefined as interchangeable in parameter arrays -- mysql2 converts null to SQL NULL but undefined causes a protocol error

Gotchas & Edge Cases:

• DECIMAL and BIGINT columns are returned as strings by default to avoid JavaScript floating-point precision loss -- parse explicitly if you need numbers
• DATE columns return JavaScript Date objects, but DATETIME precision beyond milliseconds is truncated -- MySQL supports microsecond precision, JavaScript Date does not
• execute() with named placeholders requires namedPlaceholders: true on the pool/connection config -- the default is unnamed ? only
• multipleStatements: true is a security risk -- it enables SQL injection via ; in user input if combined with query(); only enable when needed and never with user-provided SQL
• Pool waitForConnections: false throws immediately when all connections are in use instead of queuing -- the default true is almost always what you want
• ResultSetHeader.warningStatus indicates server warnings -- check it after DDL operations (the deprecated OkPacket type had a separate warningCount field; ResultSetHeader has always used warningStatus)

</red_flags>

---

<critical_reminders>

CRITICAL REMINDERS

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)

Failure to follow these rules will cause SQL injection vulnerabilities, transaction corruption, connection pool exhaustion, and application crashes.

</critical_reminders>