/api-database-mysql

name: api-database-mysql
description: Direct MySQL database access with mysql2 driver -- connection pools, prepared statements, transactions, streaming, typed queries, error handling

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

**(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