# MySQL MCP Server

A Model Context Protocol (MCP) server that provides structured MySQL database operations for Claude Desktop. Converts natural language database requests into safe, parameterized queries.

## Features

- **7 tools**: `list_tables`, `describe_table`, `fetch`, `update`, `delete`, `truncate`, `modify_bulk`
- **Safety first**: No raw SQL, parameterized queries, identifier sanitization
- **Guard rails**: UPDATE/DELETE require filters, TRUNCATE requires confirmation
- **Preview mode**: Bulk operations show impact before executing
- **Stdio transport**: Designed for Claude Desktop integration

## Project Structure

```
mysql-mcp/
├── package.json
└── src/
    ├── index.js       # MCP server entry point (stdio transport)
    ├── db.js          # MySQL connection pool
    ├── tools.js       # Tool definitions (JSON Schema)
    └── handlers.js    # Tool execution logic + safety checks
```

## Setup

### 1. Install dependencies

```bash
cd mysql-mcp
npm install
```

### 2. Configure Claude Desktop

Open your Claude Desktop config file:

- **Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
- **macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`

Add this to the `mcpServers` section:

```json
{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["D:/100xDev/mysql-mcp/src/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "3306",
        "DB_USER": "root",
        "DB_PASSWORD": "your_password_here",
        "DB_NAME": "your_database_name"
      }
    }
  }
}
```

> **Important**: Use the full absolute path to `index.js` in the `args` array. Update the `env` values to match your MySQL setup.

### 3. Restart Claude Desktop

Close and reopen Claude Desktop. You should see the MySQL tools available in the tools menu.

## Available Tools

| Tool | Description |
|------|-------------|
| `list_tables` | Show all tables in the database |
| `describe_table` | Show column structure of a table |
| `fetch` | SELECT with filters, sorting, pagination |
| `update` | UPDATE with required filters |
| `delete` | DELETE with required filters |
| `truncate` | Drop all rows (requires `confirm: true`) |
| `modify_bulk` | Deduplicate, normalize, or cleanup data |

## Safety Rules

1. **No raw SQL** — All queries are built from structured input
2. **Identifier sanitization** — Table/column names are validated against `[a-zA-Z_][a-zA-Z0-9_]*`
3. **Parameterized queries** — All values use `?` placeholders (no SQL injection)
4. **UPDATE requires filters** — Cannot update all rows accidentally
5. **DELETE requires filters** — Cannot delete all rows accidentally
6. **TRUNCATE requires confirmation** — Must pass `confirm: true`
7. **Bulk operations preview first** — Shows impact before executing
8. **Row limit cap** — Fetch is capped at 1000 rows max

## Example Prompts

Once connected in Claude Desktop, you can use natural language:

- *"Show me all tables"*
- *"What columns does the users table have?"*
- *"Get all users where status is active"*
- *"Update the name to 'Zoher' where id is 5"*
- *"Delete all orders where status is cancelled"*
- *"Find duplicate entries in users by email"*
- *"Normalize status values: replace 'actve' with 'active' in users"*

## Environment Variables

| Variable | Default | Description |
|----------|---------|-------------|
| `DB_HOST` | `localhost` | MySQL host |
| `DB_PORT` | `3306` | MySQL port |
| `DB_USER` | `root` | MySQL username |
| `DB_PASSWORD` | `password` | MySQL password |
| `DB_NAME` | `test_db` | Database name |