# MySQL (mysql2) -- Configuration Examples

> SSL/TLS, named placeholders, pool tuning, and monitoring events. See [core.md](core.md) for basic pool setup.

**Related examples:**

- [core.md](core.md) -- Pool setup, connection lifecycle
- [error-handling.md](error-handling.md) -- Connection error handling

---

## Named Placeholders

Named placeholders use `:name` syntax instead of positional `?`. Enable with `namedPlaceholders: true`.

```typescript
import mysql from "mysql2/promise";
import type { Pool, RowDataPacket } from "mysql2/promise";

interface ProductRow extends RowDataPacket {
  id: number;
  name: string;
  price: number;
  category: string;
}

function createPoolWithNamedPlaceholders(): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    namedPlaceholders: true,
  });
}

async function searchProducts(
  pool: Pool,
  filters: { category: string; minPrice: number; maxPrice: number },
): Promise<ProductRow[]> {
  const [rows] = await pool.execute<ProductRow[]>(
    "SELECT id, name, price, category FROM products WHERE category = :category AND price BETWEEN :minPrice AND :maxPrice",
    filters, // Object keys match :placeholder names
  );
  return rows;
}

export { createPoolWithNamedPlaceholders, searchProducts };
```

**Why good:** Named placeholders are self-documenting for queries with many parameters, object keys match placeholder names -- no positional confusion

**When to use:** Queries with 4+ parameters where positional `?` becomes hard to track

**Gotcha:** Named placeholders are converted to positional `?` on the client side -- the MySQL protocol does not support them natively. This means the SQL sent to the server still uses `?`, and the conversion happens in the mysql2 driver.

---

## SSL/TLS Configuration

```typescript
import mysql from "mysql2/promise";
import { readFileSync } from "node:fs";
import type { Pool } from "mysql2/promise";

// Option 1: Cloud databases (PlanetScale, AWS RDS, etc.)
// Most cloud providers' CA certs are already trusted by the system
function createCloudPool(): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    ssl: {},
  });
}

// Option 2: Custom CA certificate
function createPoolWithCA(caCertPath: string): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    ssl: {
      ca: readFileSync(caCertPath),
      rejectUnauthorized: true,
    },
  });
}

// Option 3: Mutual TLS (client certificate authentication)
function createPoolWithMTLS(
  caCertPath: string,
  clientCertPath: string,
  clientKeyPath: string,
): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    ssl: {
      ca: readFileSync(caCertPath),
      cert: readFileSync(clientCertPath),
      key: readFileSync(clientKeyPath),
      rejectUnauthorized: true,
    },
  });
}

export { createCloudPool, createPoolWithCA, createPoolWithMTLS };
```

**Why good:** Three common TLS patterns covered, `rejectUnauthorized: true` prevents MITM attacks

**Gotcha:** Setting `ssl: {}` (empty object) enables SSL with system CA trust -- this is sufficient for most cloud databases. Setting `rejectUnauthorized: false` disables certificate validation entirely and should NEVER be used in production.

---

## Pool Event Monitoring

```typescript
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

function createMonitoredPool(): Pool {
  const pool = mysql.createPool({
    uri: process.env.DATABASE_URL,
    waitForConnections: true,
  });

  pool.on("acquire", (connection) => {
    console.log("Connection %d acquired from pool", connection.threadId);
  });

  pool.on("release", (connection) => {
    console.log("Connection %d released to pool", connection.threadId);
  });

  pool.on("enqueue", () => {
    // This fires when the pool has no available connections and a request is queued
    // If you see this frequently, increase connectionLimit
    console.warn("Waiting for available connection -- pool may be exhausted");
  });

  pool.on("error", (err) => {
    console.error("Pool error:", err.message);
  });

  return pool;
}

export { createMonitoredPool };
```

**Why good:** `enqueue` event is an early warning for pool exhaustion, `acquire`/`release` track connection lifecycle

**When to use:** Development debugging, production monitoring integration, pool sizing validation

---

## Pool Size Tuning

```typescript
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

// Standard server application
const STANDARD_POOL_LIMIT = 10;
const STANDARD_IDLE_TIMEOUT_MS = 60_000;

// Serverless function (Lambda, Vercel)
const SERVERLESS_POOL_LIMIT = 1;
const SERVERLESS_IDLE_TIMEOUT_MS = 10_000;

// High-throughput batch processing
const BATCH_POOL_LIMIT = 25;
const BATCH_IDLE_TIMEOUT_MS = 120_000;

function createServerPool(): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    connectionLimit: STANDARD_POOL_LIMIT,
    maxIdle: STANDARD_POOL_LIMIT,
    idleTimeout: STANDARD_IDLE_TIMEOUT_MS,
    waitForConnections: true,
    queueLimit: 0,
  });
}

function createServerlessPool(): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    connectionLimit: SERVERLESS_POOL_LIMIT,
    maxIdle: SERVERLESS_POOL_LIMIT,
    idleTimeout: SERVERLESS_IDLE_TIMEOUT_MS,
    waitForConnections: true,
  });
}

function createBatchPool(): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    connectionLimit: BATCH_POOL_LIMIT,
    maxIdle: STANDARD_POOL_LIMIT, // Keep fewer idle connections
    idleTimeout: BATCH_IDLE_TIMEOUT_MS,
    waitForConnections: true,
    queueLimit: 0,
  });
}

export { createServerPool, createServerlessPool, createBatchPool };
```

**Why good:** Named constants for each environment, `maxIdle` different from `connectionLimit` for batch (scales down when idle), serverless uses `connectionLimit: 1` to avoid connection exhaustion

**Key insight:** Each MySQL connection uses ~10 MB of server memory. With 5 application instances at `connectionLimit: 10` each, the MySQL server needs capacity for 50 connections. Check `SHOW VARIABLES LIKE 'max_connections'` to verify.

---

## Date and Timezone Configuration

```typescript
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

// Option 1: Return dates as JavaScript Date objects (default)
function createPoolWithDates(): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    timezone: "+00:00", // Store and retrieve in UTC
  });
}

// Option 2: Return dates as strings (avoids timezone conversion issues)
function createPoolWithDateStrings(): Pool {
  return mysql.createPool({
    uri: process.env.DATABASE_URL,
    dateStrings: true, // DATE -> "2025-01-15", DATETIME -> "2025-01-15 10:30:00"
  });
}
```

**When to use `dateStrings: true`:** When you need exact date/time values without JavaScript's timezone conversion, or when passing dates to APIs that expect ISO strings.

**Gotcha:** With `timezone: "local"` (default), JavaScript `Date` objects are converted using the local timezone of the Node.js process. This causes inconsistencies between environments. Use `timezone: "+00:00"` and store all dates in UTC.

---

## Testing Configuration

```typescript
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

const TEST_POOL_LIMIT = 2;

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

  return mysql.createPool({
    uri: url,
    connectionLimit: TEST_POOL_LIMIT,
    waitForConnections: true,
  });
}

// Setup/teardown for test suites
async function setupTestDatabase(pool: Pool): Promise<void> {
  // Truncate tables in dependency order (children first)
  await pool.execute("SET FOREIGN_KEY_CHECKS = 0");
  await pool.execute("TRUNCATE TABLE order_items");
  await pool.execute("TRUNCATE TABLE orders");
  await pool.execute("TRUNCATE TABLE users");
  await pool.execute("SET FOREIGN_KEY_CHECKS = 1");
}

async function teardownTestDatabase(pool: Pool): Promise<void> {
  await pool.end();
}

export { createTestPool, setupTestDatabase, teardownTestDatabase };
```

**Why good:** Separate env var for test database, low connection limit for tests, `FOREIGN_KEY_CHECKS = 0` allows truncation regardless of FK constraints, cleanup in dependency order

**Gotcha:** Run tests with `--runInBand` (or equivalent serial mode) when sharing a test database -- parallel tests will clobber each other's data. Alternatively, use unique database names per test worker.

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_

# MySQL (mysql2) -- Core Examples

> Pool setup, typed queries, prepared statements, and connection lifecycle. Reference from [SKILL.md](../SKILL.md).

**Related examples:**

- [transactions.md](transactions.md) -- Manual transactions, savepoints, deadlock retry
- [streaming.md](streaming.md) -- Streaming large result sets, batch inserts
- [error-handling.md](error-handling.md) -- MySQL error codes, retry strategies
- [configuration.md](configuration.md) -- SSL/TLS, named placeholders, pool tuning

---

## Pool Setup

```typescript
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");
  }

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

  return pool;
}

export { createDatabasePool };
```

**Why good:** Environment variable validation, named constants, `waitForConnections: true` queues requests instead of throwing, `enableKeepAlive` prevents stale connections from firewall timeouts

```typescript
// ❌ Bad Example - Hardcoded credentials, single connection
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password123",
  database: "mydb",
});
// Hardcoded credentials leak in version control
// Single connection cannot handle concurrent requests
// No automatic reconnection on failure
```

**Why bad:** Hardcoded credentials, single connection exhausts under concurrent load, no reconnection strategy

---

## Typed SELECT with RowDataPacket

```typescript
import type { Pool, RowDataPacket } 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 getUsersByStatus(
  pool: Pool,
  status: string,
  limit: number,
): Promise<UserRow[]> {
  const [rows] = await pool.execute<UserRow[]>(
    "SELECT id, email, name, created_at FROM users WHERE status = ? LIMIT ?",
    [status, limit],
  );
  return rows;
}

export { getUserById, getUsersByStatus };
export type { UserRow };
```

**Why good:** Interface extends `RowDataPacket` for type-safe destructuring, `execute()` uses prepared statements with LRU cache, explicit column list avoids `SELECT *`, null-safe return for missing rows

```typescript
// ❌ Bad Example - Untyped, SELECT *, string interpolation
const [rows] = await pool.query(`SELECT * FROM users WHERE id = ${userId}`);
const user = rows[0]; // type: any
```

**Why bad:** SQL injection via template literal, `any`-typed results, `SELECT *` pulls unnecessary columns and breaks on schema changes

---

## INSERT with ResultSetHeader

```typescript
import type { Pool, ResultSetHeader } from "mysql2/promise";

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;
}

export { createUser };
```

**Why good:** `ResultSetHeader` provides typed access to `insertId` and `affectedRows`

---

## UPDATE and DELETE with Affected Rows

```typescript
import type { Pool, ResultSetHeader } from "mysql2/promise";

async function updateUserName(
  pool: Pool,
  userId: number,
  newName: string,
): Promise<boolean> {
  const [result] = await pool.execute<ResultSetHeader>(
    "UPDATE users SET name = ? WHERE id = ?",
    [newName, userId],
  );
  // affectedRows: rows matching WHERE clause
  // changedRows is deprecated -- use affectedRows instead
  return result.affectedRows > 0;
}

async function deleteUser(pool: Pool, userId: number): Promise<boolean> {
  const [result] = await pool.execute<ResultSetHeader>(
    "DELETE FROM users WHERE id = ?",
    [userId],
  );
  return result.affectedRows > 0;
}

export { updateUserName, deleteUser };
```

**Why good:** Returns boolean indicating whether the operation found a matching row, uses `affectedRows` (not deprecated `changedRows`)

**Gotcha:** `affectedRows` counts rows matching the WHERE clause. The deprecated `changedRows` used to count rows where values actually changed -- use `affectedRows` instead and check `result.info` if you need the distinction (it contains `"Rows matched: 1  Changed: 0"` for unchanged UPDATEs).

---

## Connection Lifecycle

```typescript
import type { Pool, PoolConnection } from "mysql2/promise";

// Pattern 1: Pool convenience method (auto-acquires and releases)
async function simpleQuery(pool: Pool): Promise<void> {
  // Connection is acquired, query runs, connection is released automatically
  const [rows] = await pool.execute("SELECT 1");
}

// Pattern 2: Manual connection for multiple operations on same connection
async function multiStepOperation(pool: Pool): Promise<void> {
  const connection = await pool.getConnection();
  try {
    // All queries run on the SAME connection
    await connection.execute("SET @var = 1");
    const [rows] = await connection.execute("SELECT @var AS result");
    // Session variables, temporary tables, and locks persist across queries
  } finally {
    connection.release(); // ALWAYS release in finally
  }
}
```

**Why good:** Pattern 1 for simple queries -- zero boilerplate. Pattern 2 for when you need session state, temp tables, or transactions. `finally` block guarantees the connection is returned to the pool.

```typescript
// ❌ Bad Example - Leaked connection
const connection = await pool.getConnection();
await connection.execute("SELECT 1");
// If execute() throws, connection is never released
// Pool eventually exhausts all connections
connection.release(); // Never reached on error
```

**Why bad:** No `finally` block means the connection leaks on any error, eventually exhausting the pool

---

## Graceful Shutdown

```typescript
import type { Pool } from "mysql2/promise";

async function shutdownPool(pool: Pool): Promise<void> {
  try {
    await pool.end();
  } catch (error) {
    // Pool may already be closed or connections may be in use
    console.error("Error closing pool:", (error as Error).message);
  }
}

// Usage with process signals
process.on("SIGTERM", async () => {
  await shutdownPool(pool);
  process.exit(0);
});
```

**Why good:** Graceful shutdown closes all idle connections, `SIGTERM` handler for container orchestrators

**Gotcha:** `pool.end()` immediately destroys all connections, including ones with in-flight queries. In production, drain requests before calling `pool.end()`.

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_

# MySQL (mysql2) -- Error Handling Examples

> MySQL error codes, connection errors, retry strategies, and graceful degradation. See [core.md](core.md) for pool setup and [transactions.md](transactions.md) for deadlock retry.

**Related examples:**

- [core.md](core.md) -- Pool setup, typed queries
- [transactions.md](transactions.md) -- Transaction patterns, deadlock retry
- [configuration.md](configuration.md) -- Pool tuning, monitoring events

---

## MySQL Error Type Guard

```typescript
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;
}

export { isMysqlError };
export type { MysqlError };
```

**Why good:** Type guard narrows `unknown` to structured MySQL error, allows safe access to `code`, `errno`, `sqlState`

---

## Handling ER_DUP_ENTRY (Duplicate Key)

```typescript
import type { Pool, ResultSetHeader } from "mysql2/promise";
import { isMysqlError } from "./error-utils";

const MYSQL_ER_DUP_ENTRY = "ER_DUP_ENTRY";

type CreateResult =
  | { success: true; id: number }
  | { success: false; reason: "duplicate" };

async function createUserSafe(
  pool: Pool,
  email: string,
  name: string,
): Promise<CreateResult> {
  try {
    const [result] = await pool.execute<ResultSetHeader>(
      "INSERT INTO users (email, name) VALUES (?, ?)",
      [email, name],
    );
    return { success: true, id: result.insertId };
  } catch (error) {
    if (isMysqlError(error) && error.code === MYSQL_ER_DUP_ENTRY) {
      return { success: false, reason: "duplicate" };
    }
    throw error; // Re-throw unexpected errors
  }
}

export { createUserSafe };
```

**Why good:** Discriminated union return type, named constant for error code, expected errors (duplicate) return a result instead of throwing, unexpected errors propagate

```typescript
// ❌ Bad Example - Catching all errors silently
try {
  await pool.execute("INSERT INTO users (email, name) VALUES (?, ?)", [
    email,
    name,
  ]);
} catch {
  return null; // Swallows ALL errors -- connection failures, syntax errors, everything
}
```

**Why bad:** Silent catch swallows connection errors, syntax errors, and other critical failures -- only catch specific error codes

---

## Handling ER_LOCK_DEADLOCK

Deadlock handling belongs at the transaction level. See [transactions.md](transactions.md) for the full `withDeadlockRetry` pattern.

```typescript
import { isMysqlError } from "./error-utils";

const MYSQL_ER_LOCK_DEADLOCK = "ER_LOCK_DEADLOCK";

function isDeadlockError(error: unknown): boolean {
  return isMysqlError(error) && error.code === MYSQL_ER_LOCK_DEADLOCK;
}

export { isDeadlockError };
```

**Gotcha:** When MySQL detects a deadlock, it automatically rolls back the **entire transaction** for the selected victim. You cannot retry just the last query -- you must retry the entire transaction from `beginTransaction()`.

---

## Handling ER_LOCK_WAIT_TIMEOUT

```typescript
import type { Pool } from "mysql2/promise";
import { isMysqlError } from "./error-utils";

const MYSQL_ER_LOCK_WAIT_TIMEOUT = "ER_LOCK_WAIT_TIMEOUT";

async function executeWithLockTimeout<T>(
  pool: Pool,
  operation: () => Promise<T>,
): Promise<T> {
  try {
    return await operation();
  } catch (error) {
    if (isMysqlError(error) && error.code === MYSQL_ER_LOCK_WAIT_TIMEOUT) {
      throw new Error(
        "Operation timed out waiting for a database lock. Another transaction may be holding the lock.",
      );
    }
    throw error;
  }
}

export { executeWithLockTimeout };
```

**Gotcha:** Unlike `ER_LOCK_DEADLOCK`, a lock wait timeout does NOT automatically roll back the transaction. The transaction is still active -- you must explicitly `ROLLBACK` if you want to abort it.

---

## Connection Error Handling

```typescript
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

function createPoolWithErrorHandling(): Pool {
  const pool = mysql.createPool({
    uri: process.env.DATABASE_URL,
    waitForConnections: true,
  });

  // Pool-level error handler -- prevents unhandled error crashes
  pool.on("error", (err) => {
    console.error("Pool error:", err.message);
    // Do NOT exit the process -- the pool will attempt to reconnect
  });

  return pool;
}

export { createPoolWithErrorHandling };
```

**Why good:** Pool `error` event prevents process crash, pool handles reconnection automatically

**Gotcha:** The pool's `error` event fires for connection-level errors (e.g., the server closes an idle connection). Individual query errors are thrown from `execute()`/`query()` calls and are NOT emitted on the pool `error` event.

---

## Health Check Query

```typescript
import type { Pool, RowDataPacket } from "mysql2/promise";

const HEALTH_CHECK_TIMEOUT_MS = 3000;

interface HealthCheckRow extends RowDataPacket {
  result: number;
}

async function checkDatabaseHealth(pool: Pool): Promise<boolean> {
  try {
    const connection = await pool.getConnection();
    try {
      const [rows] = await connection.execute<HealthCheckRow[]>({
        sql: "SELECT 1 AS result",
        timeout: HEALTH_CHECK_TIMEOUT_MS,
      });
      return rows[0]?.result === 1;
    } finally {
      connection.release();
    }
  } catch {
    return false;
  }
}

export { checkDatabaseHealth };
```

**Why good:** Dedicated connection for health check, query-level timeout prevents hanging, returns boolean instead of throwing

---

## Error Classification Helper

```typescript
import { isMysqlError, type MysqlError } from "./error-utils";

type ErrorCategory =
  | "duplicate"
  | "deadlock"
  | "lock_timeout"
  | "connection"
  | "validation"
  | "unknown";

const ERROR_CATEGORY_MAP: Record<string, ErrorCategory> = {
  ER_DUP_ENTRY: "duplicate",
  ER_LOCK_DEADLOCK: "deadlock",
  ER_LOCK_WAIT_TIMEOUT: "lock_timeout",
  ER_DATA_TOO_LONG: "validation",
  ER_BAD_NULL_ERROR: "validation",
  ER_TRUNCATED_WRONG_VALUE: "validation",
  ER_NO_SUCH_TABLE: "validation",
  ER_BAD_FIELD_ERROR: "validation",
  ER_ACCESS_DENIED_ERROR: "connection",
  ER_BAD_DB_ERROR: "connection",
  ER_CON_COUNT_ERROR: "connection",
};

function classifyMysqlError(error: unknown): ErrorCategory {
  if (!isMysqlError(error)) return "unknown";
  return ERROR_CATEGORY_MAP[error.code] ?? "unknown";
}

function isRetryableError(error: unknown): boolean {
  const category = classifyMysqlError(error);
  return category === "deadlock" || category === "lock_timeout";
}

export { classifyMysqlError, isRetryableError };
```

**Why good:** Centralizes error classification, separates retryable from non-retryable errors, extensible map for new error codes

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_

# MySQL (mysql2) -- Streaming & Batch Examples

> Streaming large result sets, batch inserts, and multiple statements. See [core.md](core.md) for pool setup and basic queries.

**Related examples:**

- [core.md](core.md) -- Pool setup, typed queries, connection lifecycle
- [transactions.md](transactions.md) -- Transactions for batch operations
- [error-handling.md](error-handling.md) -- Error handling for long-running operations

---

## Streaming Large Result Sets

Streaming uses the **callback-based** API (not `mysql2/promise`) because `.stream()` is only available on the callback `query()` return value.

```typescript
import mysql from "mysql2";
import type { RowDataPacket } from "mysql2";
import { Transform } from "node:stream";
import { pipeline } from "node:stream/promises";

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

const STREAM_HIGH_WATER_MARK = 100;

function createCallbackPool(): ReturnType<typeof mysql.createPool> {
  const url = process.env.DATABASE_URL;
  if (!url) {
    throw new Error("DATABASE_URL environment variable is required");
  }

  return mysql.createPool({ uri: url });
}

async function streamUsersToFile(
  pool: ReturnType<typeof mysql.createPool>,
  outputPath: string,
): Promise<number> {
  let count = 0;

  const queryStream = pool
    .query("SELECT id, email, name FROM users WHERE active = 1")
    .stream({ highWaterMark: STREAM_HIGH_WATER_MARK });

  const transform = new Transform({
    objectMode: true,
    transform(row: UserRow, _encoding, callback) {
      count++;
      const line = `${row.id},${row.email},${row.name}\n`;
      callback(null, line);
    },
  });

  const { createWriteStream } = await import("node:fs");
  const fileStream = createWriteStream(outputPath);

  await pipeline(queryStream, transform, fileStream);
  return count;
}

export { createCallbackPool, streamUsersToFile };
```

**Why good:** `highWaterMark` limits buffer size, `pipeline()` handles backpressure and error propagation automatically, constant memory usage regardless of result set size

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

**Gotcha:** `.stream()` is NOT available on the promise API's `execute()` or `query()`. You must use the callback-based `mysql2` import (not `mysql2/promise`) for streaming. You can still use both APIs in the same application -- create separate pool instances.

---

## Streaming with Connection (Not Pool)

When you need streaming within a transaction or on a specific connection:

```typescript
import mysql from "mysql2";
import type { RowDataPacket } from "mysql2";

interface OrderRow extends RowDataPacket {
  id: number;
  total: number;
  created_at: Date;
}

const STREAM_BATCH_SIZE = 50;

async function processLargeOrders(
  pool: ReturnType<typeof mysql.createPool>,
): Promise<number> {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
        return;
      }

      let processed = 0;

      const stream = connection
        .query("SELECT id, total, created_at FROM orders WHERE total > 1000")
        .stream({ highWaterMark: STREAM_BATCH_SIZE });

      stream.on("data", (row: OrderRow) => {
        processed++;
        // Process each row
      });

      stream.on("end", () => {
        connection.release();
        resolve(processed);
      });

      stream.on("error", (error) => {
        connection.release();
        reject(error);
      });
    });
  });
}

export { processLargeOrders };
```

**Why good:** Manual connection management for streaming, proper cleanup on both success and error paths

---

## Batch INSERT (Single Statement)

Insert many rows in a single query for maximum throughput.

```typescript
import type { Pool, ResultSetHeader } from "mysql2/promise";

const MAX_BATCH_SIZE = 1000;

interface NewUser {
  email: string;
  name: string;
}

async function batchInsertUsers(pool: Pool, users: NewUser[]): Promise<number> {
  if (users.length === 0) return 0;

  let totalInserted = 0;

  // Process in chunks to avoid MySQL's max_allowed_packet limit
  for (let i = 0; i < users.length; i += MAX_BATCH_SIZE) {
    const chunk = users.slice(i, i + MAX_BATCH_SIZE);

    const placeholders = chunk.map(() => "(?, ?)").join(", ");
    const values = chunk.flatMap((u) => [u.email, u.name]);

    const [result] = await pool.query<ResultSetHeader>(
      `INSERT INTO users (email, name) VALUES ${placeholders}`,
      values,
    );
    totalInserted += result.affectedRows;
  }

  return totalInserted;
}

export { batchInsertUsers };
```

**Why good:** Chunking prevents exceeding `max_allowed_packet`, single INSERT per chunk is far faster than individual INSERTs, `query()` is used here because the SQL text changes per chunk (dynamic placeholder count)

**Gotcha:** `execute()` (prepared statements) cannot be used for dynamic batch inserts because the number of `?` placeholders changes per chunk. Use `query()` with parameterized values -- the parameters are still escaped, so this is safe from SQL injection.

```typescript
// ❌ Bad Example - Individual inserts in a loop
for (const user of users) {
  await pool.execute("INSERT INTO users (email, name) VALUES (?, ?)", [
    user.email,
    user.name,
  ]);
}
// 1000 users = 1000 round-trips to MySQL
```

**Why bad:** Each INSERT is a separate network round-trip, orders of magnitude slower than a single batch INSERT

---

## INSERT ... ON DUPLICATE KEY UPDATE (Upsert)

```typescript
import type { Pool, ResultSetHeader } from "mysql2/promise";

async function upsertUser(
  pool: Pool,
  email: string,
  name: string,
): Promise<{ inserted: boolean }> {
  const [result] = await pool.execute<ResultSetHeader>(
    `INSERT INTO users (email, name) VALUES (?, ?) AS new_row
     ON DUPLICATE KEY UPDATE name = new_row.name`,
    [email, name],
  );

  // affectedRows: 1 = inserted, 2 = updated existing row
  return { inserted: result.affectedRows === 1 };
}

export { upsertUser };
```

**Why good:** Atomic upsert without separate SELECT + INSERT/UPDATE, `affectedRows` distinguishes insert from update

**Gotcha:** `affectedRows` is `1` for a new insert, `2` for an update (MySQL counts the delete + insert internally), and `0` if the row exists but no values changed. `insertId` is `0` when an existing row was updated. The `VALUES()` function in `ON DUPLICATE KEY UPDATE` is deprecated since MySQL 8.0.20 -- use row alias syntax (`AS new_row`) instead.

---

## Multiple Statements

Multiple statements in a single call require `multipleStatements: true` on the pool/connection config. This is disabled by default for security.

```typescript
import mysql from "mysql2/promise";
import type { RowDataPacket } from "mysql2/promise";

// Enable ONLY when needed -- security risk with user input
const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
  multipleStatements: true,
});

interface CountRow extends RowDataPacket {
  count: number;
}

async function getTableStats(
  pool: mysql.Pool,
): Promise<{ users: number; orders: number }> {
  const [results] = await pool.query<CountRow[][]>(
    "SELECT COUNT(*) AS count FROM users; SELECT COUNT(*) AS count FROM orders",
  );

  // results is an array of result sets -- one per statement
  return {
    users: results[0][0].count,
    orders: results[1][0].count,
  };
}

export { getTableStats };
```

**Why good:** Single round-trip for multiple independent queries, typed as `RowDataPacket[][]` (array of arrays)

**When to use:** Dashboard queries, statistics gathering, schema introspection -- situations where all SQL is developer-authored.

**When NOT to use:** Any query involving user input. `multipleStatements: true` allows `;`-separated injection attacks.

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_

# MySQL (mysql2) -- Transaction Examples

> Manual transactions, savepoints, deadlock retry, and nested operations. See [core.md](core.md) for pool setup and basic queries.

**Related examples:**

- [core.md](core.md) -- Pool setup, typed queries, connection lifecycle
- [error-handling.md](error-handling.md) -- MySQL error codes, retry strategies

---

## Basic Transaction Pattern

```typescript
import type { Pool, PoolConnection, ResultSetHeader } from "mysql2/promise";

async function transferFunds(
  pool: Pool,
  fromAccountId: number,
  toAccountId: number,
  amount: number,
): Promise<void> {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    // Debit source account (balance check in SQL prevents overdraft)
    const [debitResult] = await connection.execute<ResultSetHeader>(
      "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
      [amount, fromAccountId, amount],
    );

    if (debitResult.affectedRows === 0) {
      throw new Error("Insufficient balance or account not found");
    }

    // Credit destination account
    await connection.execute<ResultSetHeader>(
      "UPDATE accounts SET balance = balance + ? WHERE id = ?",
      [amount, toAccountId],
    );

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

export { transferFunds };
```

**Why good:** `getConnection()` pins one connection for the entire transaction, `finally` guarantees release, `rollback()` in catch prevents partial commits, balance check in SQL is atomic with the UPDATE

```typescript
// ❌ Bad Example - Transaction on pool convenience methods
await pool.execute("BEGIN");
await pool.execute(
  "UPDATE accounts SET balance = balance - ? WHERE id = ?",
  [100, 1],
);
await pool.execute(
  "UPDATE accounts SET balance = balance + ? WHERE id = ?",
  [100, 2],
);
await pool.execute("COMMIT");
// Each pool.execute() may use a DIFFERENT connection -- transaction is split across connections
```

**Why bad:** Pool convenience methods do not guarantee the same connection -- BEGIN, queries, and COMMIT may run on different connections, completely breaking transaction isolation

---

## Transaction Helper (Reusable)

```typescript
import type { Pool, PoolConnection } from "mysql2/promise";

async function withTransaction<T>(
  pool: Pool,
  operation: (connection: PoolConnection) => Promise<T>,
): Promise<T> {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const result = await operation(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

export { withTransaction };
```

**Usage:**

```typescript
const orderId = await withTransaction(pool, async (conn) => {
  const [orderResult] = await conn.execute<ResultSetHeader>(
    "INSERT INTO orders (user_id, total) VALUES (?, ?)",
    [userId, total],
  );

  for (const item of items) {
    await conn.execute(
      "INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)",
      [orderResult.insertId, item.productId, item.quantity],
    );
  }

  return orderResult.insertId;
});
```

**Why good:** Encapsulates the acquire/begin/commit/rollback/release boilerplate, generic return type, caller only handles the business logic

---

## Savepoints (Nested Transaction Boundaries)

MySQL does not support nested transactions, but savepoints provide similar functionality within a transaction.

```typescript
import type { PoolConnection, ResultSetHeader } from "mysql2/promise";

async function createOrderWithOptionalNotification(
  connection: PoolConnection,
  userId: number,
  items: Array<{ productId: number; quantity: number }>,
): Promise<number> {
  // Main transaction already started by caller

  const [orderResult] = await connection.execute<ResultSetHeader>(
    "INSERT INTO orders (user_id, status) VALUES (?, 'pending')",
    [userId],
  );
  const orderId = orderResult.insertId;

  for (const item of items) {
    await connection.execute(
      "INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)",
      [orderId, item.productId, item.quantity],
    );
  }

  // Savepoint for optional notification -- failure should not roll back the order
  await connection.execute("SAVEPOINT notification_attempt");
  try {
    await connection.execute(
      "INSERT INTO notifications (user_id, type, reference_id) VALUES (?, 'order_created', ?)",
      [userId, orderId],
    );
  } catch {
    // Notification failed -- roll back to savepoint but keep the order
    await connection.execute("ROLLBACK TO SAVEPOINT notification_attempt");
  }
  await connection.execute("RELEASE SAVEPOINT notification_attempt");

  return orderId;
}

export { createOrderWithOptionalNotification };
```

**Why good:** Savepoint isolates optional work -- notification failure does not roll back the order, RELEASE SAVEPOINT frees server resources

**Gotcha:** Savepoints are NOT nested transactions. A top-level `ROLLBACK` rolls back everything, including all savepoints. A `ROLLBACK TO SAVEPOINT` only rolls back to that savepoint.

---

## Deadlock Retry

When MySQL detects a deadlock, it automatically rolls back one of the competing transactions and returns `ER_LOCK_DEADLOCK`. The application should retry the entire transaction.

```typescript
import type { Pool } from "mysql2/promise";

const MYSQL_ER_LOCK_DEADLOCK = "ER_LOCK_DEADLOCK";
const MAX_DEADLOCK_RETRIES = 3;
const RETRY_BASE_DELAY_MS = 100;

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

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

async function withDeadlockRetry<T>(
  pool: Pool,
  operation: () => Promise<T>,
  maxRetries: number = MAX_DEADLOCK_RETRIES,
): Promise<T> {
  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error) {
      const isDeadlock =
        isMysqlError(error) && error.code === MYSQL_ER_LOCK_DEADLOCK;
      const hasRetriesLeft = attempt < maxRetries;

      if (isDeadlock && hasRetriesLeft) {
        // Exponential backoff with jitter
        const delay =
          RETRY_BASE_DELAY_MS * Math.pow(2, attempt) * (0.5 + Math.random());
        await new Promise((resolve) => setTimeout(resolve, delay));
        continue;
      }
      throw error;
    }
  }

  // Unreachable, but TypeScript needs it
  throw new Error("Exhausted deadlock retries");
}

export { withDeadlockRetry };
```

**Usage:**

```typescript
await withDeadlockRetry(pool, async () => {
  await withTransaction(pool, async (conn) => {
    await conn.execute(
      "UPDATE inventory SET stock = stock - 1 WHERE product_id = ?",
      [productId],
    );
    await conn.execute(
      "INSERT INTO reservations (product_id, user_id) VALUES (?, ?)",
      [productId, userId],
    );
  });
});
```

**Why good:** Wraps entire transaction (not individual queries), exponential backoff with jitter prevents retry storms, configurable retry count, only retries deadlock errors -- other errors propagate immediately

**Gotcha:** The deadlocked transaction is already rolled back by MySQL when you receive `ER_LOCK_DEADLOCK`. You must retry the entire transaction from scratch, not just the last query.

---

## SELECT ... FOR UPDATE (Row Locking)

```typescript
import type { Pool, RowDataPacket, ResultSetHeader } from "mysql2/promise";

interface InventoryRow extends RowDataPacket {
  product_id: number;
  stock: number;
}

async function reserveStock(
  pool: Pool,
  productId: number,
  quantity: number,
): Promise<boolean> {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    // FOR UPDATE locks the row until commit/rollback
    const [rows] = await connection.execute<InventoryRow[]>(
      "SELECT stock FROM inventory WHERE product_id = ? FOR UPDATE",
      [productId],
    );

    if (rows.length === 0 || rows[0].stock < quantity) {
      await connection.rollback();
      return false;
    }

    await connection.execute<ResultSetHeader>(
      "UPDATE inventory SET stock = stock - ? WHERE product_id = ?",
      [quantity, productId],
    );

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

export { reserveStock };
```

**Why good:** `FOR UPDATE` prevents other transactions from reading or modifying the row until this transaction completes, eliminates race conditions in read-then-write patterns

**When to use:** Inventory checks, seat reservations, counter increments where the new value depends on the current value

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_