# MCP MySQL Server

A Model Context Protocol (MCP) server for MySQL database operations with built-in safety features and operation resistance. Perfect for use with Claude Code, Codex CLI, and other MCP-compatible AI assistants.

## Features

- **Safe MySQL Operations**: Execute SELECT queries freely while controlling destructive operations
- **Operation Resistance**: Configurable INSERT, UPDATE, and DELETE restrictions
- **Schema Protection**: DROP, TRUNCATE, and ALTER operations are always blocked
- **SQL Injection Prevention**: Uses prepared statements with parameter binding
- **Multiple Tools**: Query execution, table listing, schema inspection, and database info
- **Connection Pooling**: Efficient connection management for better performance
- **Detailed Error Reporting**: Clear error messages with SQL codes

## Quick Start

### Option 1: Automated Setup (Easiest)

```bash
# One command - does everything!
./setup.sh
```

The script will:
- Auto-detect Bun or npm
- Install dependencies
- Create and optionally configure .env
- Build the project
- Test database connection

### Option 2: Manual Setup

**Using Bun (Faster):**
```bash
bun install
cp .env.example .env && nano .env
bun run build
```

**Using npm:**
```bash
npm install
cp .env.example .env && nano .env
npm run build
```

## Integration Guides

- **[Claude Code Setup](examples/claude-code-setup.md)** - Complete guide for Claude Code integration
- **[Codex CLI Setup](examples/codex-cli-setup.md)** - Command-line usage and automation
- **[Usage Examples](examples/usage-examples.md)** - Comprehensive query examples and patterns

## Configuration

### Environment Variables

Create a `.env` file (use `.env.example` as a template):

```env
# MySQL Connection
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=your_username
MYSQL_PASS=your_password
MYSQL_DB=your_database

# Operation Permissions (true/false)
ALLOW_INSERT_OPERATION=false
ALLOW_UPDATE_OPERATION=false
ALLOW_DELETE_OPERATION=false
```

### MCP Client Configuration

For Claude Code, add to your `~/.config/claude-code/.mcp.json`:

```json
{
  "mcpServers": {
    "mysql": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-mysql-server/dist/index.js"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "your_username",
        "MYSQL_PASS": "your_password",
        "MYSQL_DB": "your_database",
        "ALLOW_INSERT_OPERATION": "false",
        "ALLOW_UPDATE_OPERATION": "false",
        "ALLOW_DELETE_OPERATION": "false"
      }
    }
  }
}
```

**Need more examples?** See [docs/mcp-config-examples.md](docs/mcp-config-examples.md) for:
- Multiple databases
- Remote/Docker configurations
- Development vs production setups
- Platform-specific paths

See the [integration guides](#integration-guides) for detailed setup instructions.

## Safety Features

### Always Blocked Operations
These operations are **always blocked** regardless of configuration:
- `DROP` - Dropping tables/databases
- `TRUNCATE` - Truncating tables
- `ALTER` - Altering table structure

### Configurable Operations
Control these operations via environment variables:
- `INSERT` - Controlled by `ALLOW_INSERT_OPERATION`
- `UPDATE` - Controlled by `ALLOW_UPDATE_OPERATION`
- `DELETE` - Controlled by `ALLOW_DELETE_OPERATION`

### Always Allowed Operations
- `SELECT` - Reading data is always permitted

## Available Tools

### 1. mysql_query
Execute SQL queries with safety restrictions.

**Parameters:**
- `query` (string, required): The SQL query to execute
- `params` (array, optional): Parameters for prepared statement

**Example:**
```json
{
  "query": "SELECT * FROM users WHERE id = ?",
  "params": [123]
}
```

**Response:**
```json
{
  "success": true,
  "rows": [...],
  "rowCount": 5,
  "fields": [...]
}
```

### 2. mysql_list_tables
List all tables in the current database.

**Example Response:**
```json
{
  "success": true,
  "tables": [
    { "Tables_in_db": "users" },
    { "Tables_in_db": "products" }
  ]
}
```

### 3. mysql_describe_table
Get the structure/schema of a specific table.

**Parameters:**
- `table` (string, required): Table name

**Example Response:**
```json
{
  "success": true,
  "table": "users",
  "columns": [
    {
      "Field": "id",
      "Type": "int",
      "Null": "NO",
      "Key": "PRI",
      "Default": null,
      "Extra": "auto_increment"
    }
  ]
}
```

### 4. mysql_get_database_info
Get information about the database connection and permissions.

**Example Response:**
```json
{
  "success": true,
  "connection": {
    "host": "localhost",
    "port": 3306,
    "user": "tommygun",
    "database": "db"
  },
  "permissions": {
    "INSERT": false,
    "UPDATE": false,
    "DELETE": false,
    "SELECT": true,
    "DROP": false,
    "TRUNCATE": false,
    "ALTER": false
  }
}
```


## Usage Examples

For comprehensive examples, see [examples/usage-examples.md](examples/usage-examples.md).

### Quick Examples

**Safe SELECT Query:**
```
Get the first 10 users from the database
```

**Parameterized Query (Prevents SQL Injection):**
```
Find products where category is 'electronics' and price is less than 1000
```

**Data Analysis:**
```
Show me monthly revenue for the last 6 months
```

**Schema Exploration:**
```
What tables are in my database?
Show me the structure of the users table
```

### Blocked Operations

When operations are disabled, you'll receive clear error messages:
```json
{
  "error": "INSERT operations are disabled. Set ALLOW_INSERT_OPERATION=true to enable.",
  "blocked": true
}
```

## Enabling Write Operations

To enable write operations, update your `.env`:

```env
# Enable INSERT operations
ALLOW_INSERT_OPERATION=true

# Enable UPDATE operations
ALLOW_UPDATE_OPERATION=true

# Enable DELETE operations (use with caution!)
ALLOW_DELETE_OPERATION=true
```

After changing permissions, restart the MCP server.

## Security Best Practices

1. **Default to Read-Only**: Keep write operations disabled unless specifically needed
2. **Use Prepared Statements**: Always use the `params` array for user input
3. **Validate Table Names**: The server validates table names in describe operations
4. **Schema Protection**: DROP/TRUNCATE/ALTER are permanently blocked
5. **Connection Pooling**: Uses connection pooling for better performance and resource management

## Error Handling

The server provides detailed error responses:

```json
{
  "error": "Table 'db.nonexistent' doesn't exist",
  "code": "ER_NO_SUCH_TABLE",
  "sqlState": "42S02"
}
```

## Testing and Verification

### Quick Tests

Once connected to your MCP client, try these commands:

1. **Check Connection:**
   ```
   Show me the MySQL database information
   ```

2. **List Tables:**
   ```
   What tables are in my database?
   ```

3. **Test Safety Features:**
   ```
   Try to insert a test record into any table
   ```
   (Should be blocked with a clear error message)

4. **Query Data:**
   ```
   Show me sample data from the users table
   ```

### Verification Checklist

- [ ] Server shows as connected in MCP client
- [ ] Database connection info displays correctly
- [ ] Table listing works
- [ ] SELECT queries execute successfully
- [ ] INSERT/UPDATE/DELETE are blocked (default)
- [ ] Clear error messages for blocked operations

For detailed testing examples, see [examples/usage-examples.md](examples/usage-examples.md).

## Development

```bash
# Build the project
npm run build

# Run the server directly (for testing)
npm start

# Build and run
npm run dev

# Watch mode (if needed)
npm run build -- --watch
```

### Project Structure

```
mcp-mysql-server/
├── src/
│   └── index.ts          # Main server implementation
├── dist/                 # Compiled JavaScript output
├── examples/             # Integration guides and examples
│   ├── claude-code-setup.md
│   ├── codex-cli-setup.md
│   └── usage-examples.md
├── .env.example          # Environment template
├── package.json
├── tsconfig.json
└── README.md
```

### Contributing

See [CONTRIBUTING.md](CONTRIBUTING.md) for development guidelines.

## Troubleshooting

### Connection Issues
- Verify MySQL is running: `mysql -u username -p`
- Check host/port configuration
- Verify user credentials
- Ensure database exists

### Permission Errors
- Check MySQL user has required permissions
- Verify `ALLOW_*_OPERATION` settings match your needs

### Tool Not Found
- Ensure the server is properly configured in `.mcp.json`
- Verify the build succeeded: `ls -la dist/index.js`
- Check MCP client can find the server

## Resources

- **[Quick Start Guide](docs/QUICKSTART.md)** - Get started in 5 minutes
- **[Setup Checklist](docs/SETUP_CHECKLIST.md)** - Step-by-step setup guide
- **[Documentation Index](docs/)** - All documentation organized
- [MCP Protocol Documentation](https://modelcontextprotocol.io/)
- [Integration Guides](examples/) - Claude Code & Codex CLI
- [Usage Examples](examples/usage-examples.md)
- [Contributing Guidelines](CONTRIBUTING.md)
- [Security Policy](.github/SECURITY.md)
- [Project Improvements](docs/PROJECT_IMPROVEMENTS.md) - What's new

## Support

- Issues: Report bugs or request features via GitHub issues
- Questions: Check the [examples](examples/) directory for common use cases
- Security: For security concerns, please email the maintainers

## Changelog

### Version 1.0.0
- Initial release
- Basic MySQL operations (SELECT, INSERT, UPDATE, DELETE)
- Safety features and operation resistance
- Prepared statements for SQL injection prevention
- Connection pooling
- Comprehensive documentation

## License

MIT - see [LICENSE](LICENSE) file for details