mcp-server-mysql

# 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

### Remote MCP Mode

When `IS_REMOTE_MCP=true` and `REMOTE_SECRET_KEY` is set:

- Starts Express HTTP server on `PORT` (default 3000)
- Accepts POST requests to `/mcp` endpoint
- Requires `Authorization: Bearer <REMOTE_SECRET_KEY>` header
- Uses StreamableHTTPServerTransport instead of stdio

## Project Structure

```markdown
.
├── index.ts                    # Main entry point
├── src/
│   ├── config/index.ts        # Configuration and env loading
│   ├── db/
│   │   ├── index.ts           # Database connection and query execution
│   │   ├── permissions.ts     # Schema permission checks
│   │   └── utils.ts           # SQL parsing utilities
│   ├── types/index.ts         # TypeScript type definitions
│   └── utils/index.ts         # General utilities (logging, etc.)
├── scripts/
│   └── setup-test-db.ts       # Test database setup script
├── tests/
│   ├── unit/                  # Unit tests
│   ├── integration/           # Integration tests (MySQL required)
│   └── e2e/                   # End-to-end server tests
├── evals.ts                   # MCP evaluation scripts
└── dist/                      # Compiled JavaScript output
```

## Important Development Notes

### Connection Methods

The server supports two MySQL connection methods:

1. **TCP/IP**: Set `MYSQL_HOST` and `MYSQL_PORT`
2. **Unix Socket**: Set `MYSQL_SOCKET_PATH` (takes precedence over TCP/IP)

### Testing Strategy

- Tests require a real MySQL instance
- `setup:test:db` script must run before tests to create schema and seed data
- Use `.env.test` for test-specific configuration
- Integration tests cover multi-DB mode, schema permissions, and socket connections

### ES Module Configuration

- Uses `"type": "module"` in package.json
- All imports must include `.js` extension (TypeScript quirk for ES modules)
- `tsconfig.json` uses `"module": "NodeNext"` and `"moduleResolution": "NodeNext"`

### Error Handling

- `safeExit()` function prevents process.exit during tests
- All database operations use try/catch with proper connection release
- Query errors include execution context and schema information

### Performance Considerations

- Connection pooling with configurable limit (default 10)
- Query execution timing tracked via `performance.now()`
- Lazy pool initialization on first query

## Common Development Tasks

### Adding New Query Types

1. Update SQL parser logic in `src/db/utils.ts` (`getQueryTypes`)
2. Add permission checking in `src/db/index.ts` (`executeReadOnlyQuery`)
3. Handle result formatting in `executeWriteQuery` if needed
4. Add integration tests in `tests/integration/`

### Adding Environment Variables

1. Add to `.env` example in README
2. Parse in `src/config/index.ts`
3. Export for use in other modules
4. Document in README's Environment Variables section

### Modifying Permission Logic

1. Update permission functions in `src/db/permissions.ts`
2. Modify schema extraction if needed in `src/db/utils.ts`
3. Test with schema-specific permission scenarios
4. Update permission checking in `executeReadOnlyQuery`

More agent context in benborla/mcp-server-mysql

One other file this repository gives its agents.