CLAUDE.md · benborla/mcp-server-mysql

Sections

1. Project Overview
2. Build and Development Commands
3. Architecture
4. Project Structure
5. Important Development Notes
6. Common Development Tasks

First lines (shown under the repo's MIT license)

# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Project Overview

This is a Model Context Protocol (MCP) server that provides MySQL database access to LLMs like Claude, with enhanced write operation support and Claude Code optimizations.

**Key Technologies:**

- TypeScript with ES Modules (NodeNext)
- MCP SDK (@modelcontextprotocol/sdk v1.15.1)
- mysql2 for database connectivity
- Vitest for testing
- Express for remote MCP mode (optional)

## Build and Development Commands

### Building

```bash
# Build TypeScript to dist/
pnpm build

# Watch mode (auto-rebuild on changes)
pnpm watch
```

### Running

```bash
# Run built version
pnpm start

# Development mode with ts-node
pnpm dev

# Run directly with environment variables
pnpm exec
```

### Testing

```bash
# Setup test database (creates mcp_test DB and seeds data)
pnpm run setup:test:db

# Run all tests (includes pretest hook that runs setup:test:db)
pnpm test

# Run specific test suites
pnpm test:unit          # Unit tests only
pnpm test:integration   # Integration tests only
pnpm test:e2e          # End-to-end tests only
pnpm test:socket       # Socket connection tests

# Watch mode for development
pnpm test:watch

# Coverage report
pnpm test:coverage
```

**Test Environment Requirements:**

- MySQL server running locally or remotely
- Test database user: `mcp_test` with password `mcp_test_password`
- Test database: `mcp_test`
- Configuration via `.env.test` file

### Linting

```bash
pnpm lint
```

## Architecture

### Core Entry Point

- `index.ts` - Main server file that exports `createMcpServer()` function
  - Supports both stdio and HTTP transport modes
  - Handles MCP protocol setup and request routing
  - Configures shutdown handlers for graceful cleanup

### Key Modules

**Configuration (`src/config/index.ts`)**

- Loads environment variables via dotenv
- Exports connection config, permission flags, and mode detection
- Key exports:
  - `mcpConfig` - MySQL connection configuration
  - `isMultiDbMode` - Boolean indicating multi-database mode
  - `ALLOW_*_OPERATION` - Global write permission flags
  - `SCHEMA_*_PERMISSIONS` - Schema-specific permission maps
  - `MYSQL_DISABLE_READ_ONLY_TRANSACTIONS` - Control transaction mode

**Database Layer (`src/db/index.ts`)**

- Connection pooling via `getPool()` and lazy-loaded `poolPromise`
- Three query execution methods:
  - `executeQuery<T>()` - Basic query execution
  - `executeReadOnlyQuery<T>()` - Enforces read-only transaction mode (unless disabled)
  - `executeWriteQuery<T>()` - Handles INSERT/UPDATE/DELETE/DDL with transactions
- Permission checking integrated into query execution
- Schema extraction from queries for permission enforcement

**Permissions (`src/db/permissions.ts`)**

- Schema-specific permission checking functions:
  - `isInsertAllowedForSchema(schema)`
  - `isUpdateAllowedForSchema(schema)`
  - `isDeleteAllowedForSchema(schema)`
  - `isDDLAllowedForSchema(schema)`
- Falls back to global permissions if no schema-specific rule exists

**Utilities (`src/db/utils.ts`)**

- `getQueryTypes()` - Parses SQL to identify operation types (SELECT, INSERT, etc.)
- `extractSchemaFromQuery()` - Extracts database schema from qualified table names or USE statements

### MCP Protocol Implementation

**Resources** (Database introspection):

- `ListResourcesRequest` - Returns all tables across accessible schemas
- `ReadResourceRequest` - Returns column metadata for specific tables
- URIs: `mysql://tables` and `mysql://tables/{tableName}`

**Tools** (Query execution):

- `mysql_query` - Single tool that executes SQL queries
- Input: `{ sql: string }`
- Output: JSON result set with execution time
- Enforces permissions based on query type and target schema

### Multi-Database Mode

When `MYSQL_DB` environment variable is empty or unset:

- Server operates in multi-DB mode
- Queries must use fully qualified table names (`database.table`) or `USE` statements
- Schema-specific permissions apply per database
- Write operations disabled by default unless `MULTI_DB_WRITE_MODE=true`

### Permission System

**Two-Level Hierarchy:**

1. Global flags: `ALLOW_INSERT_OPERATION`, `ALLOW_UPDATE_OPERATION`, `ALLOW_DELETE_OPERATION`, `ALLOW_DDL_OPERATION`
2. Schema-specific overrides: `SCHEMA_*_PERMISSIONS` environment variables

**Format:** `SCHEMA_INSERT_PERMISSIONS=development:true,test:true,production:false`

**Transaction Safety:**

- Read operations use `SET SESSION TRANSACTION READ ONLY` by default
- Can be disabled with `MYSQL_DISABLE_READ_ONLY_TRANSACTIONS=true` for DDL support
- Write operations use explicit transactions with commit/rollback