Skip to content

Latest commit

 

History

History
249 lines (176 loc) · 7.7 KB

CLAUDE.md

File metadata and controls

249 lines (176 loc) · 7.7 KB

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

# Build TypeScript to dist/
pnpm build

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

Running

# Run built version
pnpm start

# Development mode with ts-node
pnpm dev

# Run directly with environment variables
pnpm exec

Testing

# 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

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

.
├── 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