MCP Server MySQL — mysql2 driver, connection pooling, and prepared statements

Driver setup: mysql2 promise API with connection pooling

The mysql2 package is the modern replacement for the original mysql package — it offers prepared statement support, a promise-based API, and better performance. Always import from mysql2/promise (not mysql2 directly) to get the async/await interface.

import mysql from 'mysql2/promise';

// Singleton pool — initialized once at startup
const pool = mysql.createPool({
  host: process.env.MYSQL_HOST ?? 'localhost',
  port: parseInt(process.env.MYSQL_PORT ?? '3306'),
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,

  // SSL required for PlanetScale, RDS, Cloud SQL
  ssl: process.env.MYSQL_SSL === 'true'
    ? { rejectUnauthorized: true }
    : undefined,

  // Pool sizing
  connectionLimit: 10,    // max concurrent connections
  queueLimit: 50,         // max waiting requests (0 = unlimited — avoid this)
  waitForConnections: true,

  // Type coercions
  typeCast: true,         // auto-convert DATE/DATETIME to JS Date objects
  timezone: '+00:00',     // store/retrieve timestamps as UTC

  // Connection health
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000
});

export { pool };

One MySQL-specific gotcha: timezone: '+00:00' is critical for correct DATETIME handling. MySQL DATETIME columns store literal time strings without timezone info. If your Node.js process runs in a different timezone than the MySQL server, Date objects will be serialized and deserialized with the wrong offset unless both sides agree on UTC. Set +00:00 on the connection and store all timestamps in UTC.

Parameterized queries: ? placeholder syntax

MySQL uses ? as the parameter placeholder (unlike PostgreSQL's $1, $2 syntax). Pass an array of values as the second argument to pool.query(). The driver replaces each ? with the corresponding array element, properly escaped at the driver level — not by string concatenation.

import { z } from 'zod';
import { McpError, ErrorCode } from '@modelcontextprotocol/sdk/types.js';
import { pool } from './db.js';
import type { RowDataPacket, ResultSetHeader } from 'mysql2';

// ---- query_orders ----
server.tool(
  'query_orders',
  {
    customer_id: z.string().optional(),
    status: z.enum(['pending', 'processing', 'shipped', 'delivered', 'cancelled']).optional(),
    min_amount: z.number().positive().optional(),
    limit: z.number().int().min(1).max(100).default(20),
    offset: z.number().int().min(0).default(0)
  },
  async ({ customer_id, status, min_amount, limit, offset }) => {
    const conditions: string[] = [];
    const params: unknown[] = [];

    if (customer_id) {
      conditions.push('customer_id = ?');
      params.push(customer_id);
    }
    if (status) {
      conditions.push('status = ?');
      params.push(status);
    }
    if (min_amount !== undefined) {
      conditions.push('total_amount >= ?');
      params.push(min_amount);
    }

    const where = conditions.length > 0
      ? `WHERE ${conditions.join(' AND ')}`
      : '';

    // LIMIT and OFFSET via params — prevents injection even for numeric values
    const sql = `
      SELECT id, customer_id, status, total_amount, created_at
      FROM orders
      ${where}
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `;
    params.push(limit, offset);

    const [rows] = await pool.query<RowDataPacket[]>(sql, params);

    return {
      content: [{
        type: 'text',
        text: JSON.stringify({ rows, count: rows.length }, null, 2)
      }]
    };
  }
);

// ---- create_order ----
server.tool(
  'create_order',
  {
    customer_id: z.string().uuid(),
    items: z.array(z.object({
      product_id: z.string(),
      quantity: z.number().int().positive(),
      unit_price: z.number().positive()
    })).min(1),
    notes: z.string().max(500).optional()
  },
  async ({ customer_id, items, notes }) => {
    const totalAmount = items.reduce(
      (sum, item) => sum + item.quantity * item.unit_price,
      0
    );

    const [result] = await pool.query<ResultSetHeader>(
      `INSERT INTO orders (customer_id, status, total_amount, notes, created_at)
       VALUES (?, 'pending', ?, ?, NOW())`,
      [customer_id, totalAmount, notes ?? null]
    );

    const orderId = result.insertId;
    return {
      content: [{
        type: 'text',
        text: JSON.stringify({ order_id: orderId, total_amount: totalAmount })
      }]
    };
  }
);

The generic type parameter to pool.query<RowDataPacket[]> makes TypeScript understand the destructured return type. pool.query() returns [rows, fields] — destructure to [rows] and ignore fields unless you need column metadata. For INSERT/UPDATE/DELETE, use ResultSetHeader to get insertId, affectedRows, and changedRows.

Transactions with getConnection()

Multi-statement transactions require a pinned connection. Use pool.getConnection() to check out a dedicated connection, then manually manage the transaction lifecycle. The try/catch/finally pattern is critical — failing to release the connection leaks it from the pool permanently.

server.tool(
  'process_payment',
  {
    order_id: z.number().int().positive(),
    payment_method: z.enum(['card', 'bank_transfer', 'wallet']),
    amount: z.number().positive()
  },
  async ({ order_id, payment_method, amount }) => {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      // Lock the order row during processing
      const [orderRows] = await connection.query<RowDataPacket[]>(
        'SELECT id, status, total_amount FROM orders WHERE id = ? FOR UPDATE',
        [order_id]
      );

      if (orderRows.length === 0) {
        throw new McpError(ErrorCode.InvalidParams, `Order ${order_id} not found`);
      }
      const order = orderRows[0];

      if (order.status !== 'pending') {
        throw new McpError(
          ErrorCode.InvalidParams,
          `Order ${order_id} is ${order.status}, not pending`
        );
      }

      if (Math.abs(amount - order.total_amount) > 0.01) {
        throw new McpError(
          ErrorCode.InvalidParams,
          `Payment amount ${amount} does not match order total ${order.total_amount}`
        );
      }

      // Record payment
      const [paymentResult] = await connection.query<ResultSetHeader>(
        `INSERT INTO payments (order_id, payment_method, amount, status, created_at)
         VALUES (?, ?, ?, 'captured', NOW())`,
        [order_id, payment_method, amount]
      );

      // Update order status
      await connection.query(
        'UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?',
        ['processing', order_id]
      );

      await connection.commit();

      return {
        content: [{
          type: 'text',
          text: JSON.stringify({
            payment_id: paymentResult.insertId,
            order_status: 'processing'
          })
        }]
      };
    } catch (err) {
      await connection.rollback();
      throw err;
    } finally {
      connection.release();  // must always run — even on error
    }
  }
);

The FOR UPDATE row lock on the order prevents two simultaneous payment attempts for the same order from both succeeding. Without it, a duplicate payment race condition could process two payments before either commits.

Schema introspection as MCP resources

MySQL's information_schema exposes table and column definitions. Use it to let calling agents discover available tables before constructing queries.

import {
  ListResourcesRequestSchema,
  ReadResourceRequestSchema
} from '@modelcontextprotocol/sdk/types.js';

server.setRequestHandler(ListResourcesRequestSchema, async () => {
  const [tables] = await pool.query<RowDataPacket[]>(
    `SELECT
       TABLE_NAME,
       TABLE_TYPE,
       TABLE_ROWS,
       ENGINE,
       CREATE_TIME
     FROM information_schema.TABLES
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_TYPE IN ('BASE TABLE', 'VIEW')
     ORDER BY TABLE_NAME`
  );

  return {
    resources: tables.map((t) => ({
      uri: `mysql://${t.TABLE_SCHEMA}/${t.TABLE_NAME}`,
      name: t.TABLE_NAME as string,
      description: `${t.TABLE_TYPE} (${t.ENGINE}) — ~${Number(t.TABLE_ROWS ?? 0).toLocaleString()} rows`,
      mimeType: 'application/json'
    }))
  };
});

server.setRequestHandler(ReadResourceRequestSchema, async ({ params }) => {
  const match = params.uri.match(/^mysql:\/\/[^/]+\/([a-zA-Z_][a-zA-Z0-9_]*)$/);
  if (!match) {
    throw new McpError(ErrorCode.InvalidParams, `Unrecognised URI: ${params.uri}`);
  }
  const [, tableName] = match;

  const [columns] = await pool.query<RowDataPacket[]>(
    `SELECT
       COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT,
       CHARACTER_MAXIMUM_LENGTH, COLUMN_KEY, EXTRA
     FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
     ORDER BY ORDINAL_POSITION`,
    [tableName]
  );

  if (columns.length === 0) {
    throw new McpError(ErrorCode.InvalidParams, `Table ${tableName} not found`);
  }

  const [indexes] = await pool.query<RowDataPacket[]>(
    `SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
     FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
     ORDER BY INDEX_NAME, SEQ_IN_INDEX`,
    [tableName]
  );

  return {
    contents: [{
      uri: params.uri,
      mimeType: 'application/json',
      text: JSON.stringify({ table: tableName, columns, indexes }, null, 2)
    }]
  };
});

Note that TABLE_ROWS in information_schema.TABLES is an estimate for InnoDB tables — it can be off by 40–50% for large tables. InnoDB maintains this statistic via sampling, not an exact count. For exact counts on specific tables, run SELECT COUNT(*).

Health endpoint: /health/mysql

The MySQL pool exposes connection counts through the pool object itself. Wire a health endpoint that checks query latency and pool stats.

import express from 'express';

const app = express();

app.get('/health/mysql', async (_req, res) => {
  const start = Date.now();
  try {
    await pool.query('SELECT 1');
    const queryMs = Date.now() - start;

    // mysql2 pool exposes these via the internal pool object
    // Access via the underlying pool reference
    const internalPool = (pool as unknown as {
      pool: {
        _allConnections: { length: number };
        _freeConnections: { length: number };
        _connectionQueue: { length: number };
        config: { connectionLimit: number };
      }
    }).pool;

    const stats = {
      total: internalPool._allConnections.length,
      idle: internalPool._freeConnections.length,
      queued: internalPool._connectionQueue.length,
      limit: internalPool.config.connectionLimit
    };

    const healthy = stats.queued === 0;

    res.status(healthy ? 200 : 503).json({
      status: healthy ? 'ok' : 'degraded',
      query_ms: queryMs,
      pool: stats
    });
  } catch (err) {
    res.status(503).json({
      status: 'error',
      error: (err as Error).message,
      elapsed_ms: Date.now() - start
    });
  }
});

app.listen(3001);

Wire AliveMCP to this endpoint. A 503 response means the database is unreachable. A 200 response with pool.queued > 0 means all connections are occupied and requests are waiting — a precursor to timeouts if load continues.

MySQL error codes in MCP tool handlers

MySQL errors have numeric codes. Map the most common ones to McpError so callers receive actionable messages.

function mapMysqlError(err: unknown): never {
  const mysqlErr = err as { errno?: number; sqlMessage?: string; sqlState?: string };

  switch (mysqlErr.errno) {
    case 1062: // ER_DUP_ENTRY — unique constraint violation
      throw new McpError(
        ErrorCode.InvalidParams,
        `Duplicate entry: ${mysqlErr.sqlMessage}`
      );
    case 1452: // ER_NO_REFERENCED_ROW_2 — foreign key violation (insert/update)
      throw new McpError(
        ErrorCode.InvalidParams,
        `Foreign key violation: referenced row does not exist`
      );
    case 1451: // ER_ROW_IS_REFERENCED_2 — foreign key violation (delete)
      throw new McpError(
        ErrorCode.InvalidParams,
        `Cannot delete: row is referenced by another table`
      );
    case 1146: // ER_NO_SUCH_TABLE
      throw new McpError(
        ErrorCode.InvalidParams,
        `Table does not exist`
      );
    case 1054: // ER_BAD_FIELD_ERROR — unknown column
      throw new McpError(
        ErrorCode.InvalidParams,
        `Unknown column: ${mysqlErr.sqlMessage}`
      );
    case 1205: // ER_LOCK_WAIT_TIMEOUT
      throw new McpError(
        ErrorCode.InternalError,
        'Lock wait timeout — another transaction is holding a row lock'
      );
    case 1213: // ER_LOCK_DEADLOCK
      throw new McpError(
        ErrorCode.InternalError,
        'Deadlock detected — please retry the operation'
      );
    default:
      throw err;
  }
}

Errors 1205 (lock wait timeout) and 1213 (deadlock) are transient and should be retried with exponential backoff. Return them as ErrorCode.InternalError with a "please retry" message rather than InvalidParams, since the issue is contention, not a caller error.

Frequently asked questions

Further reading

• MCP Server PostgreSQL — pg driver, parameterized queries, and connection pooling
• MCP Server MongoDB — CRUD tools, aggregation pipelines, and health monitoring
• MCP Server Drizzle ORM — type-safe queries for MCP tools
• MCP Server Connection Pooling — pool sizing and exhaustion detection
• MCP Server Error Handling — mapping database errors to McpError codes