# MySQL MCP Server

A sophisticated Model Context Protocol (MCP) server that provides secure, multi-database MySQL access with configurable security levels, lazy loading, and hot reload capabilities.

## 🚀 Features

- **🗄️ Multi-Database Support**: Configure and access multiple MySQL databases simultaneously
- **🔒 Configurable Security**: Four security levels from read-only to full access
- **⚡ Lazy Loading**: Connection pools created only when needed for optimal resource usage
- **🔄 Hot Reload**: Database configuration updates without server restart
- **📊 Complete Schema Information**: Full table definitions with indexes, foreign keys, and constraints
- **🛡️ Explicit Database Selection**: Required database parameter prevents accidental operations
- **🎯 MCP Protocol Integration**: Native VS Code integration with resource browsing

## 🔧 Installation

1. **Clone and Install**
   ```bash
   git clone <your-repository-url>
   cd mysql-mcp-server
   npm install
   ```

2. **Configure Multi-Database Setup**

   Copy the example configuration file and customize it:
   ```bash
   cp .env.example .env
   ```

   Edit `.env` with your database configurations:
   ```env
   # Multi-Database Configuration (JSON Array)
   MYSQL_DATABASES='[
     {
       "name": "primary_db",
       "host": "localhost",
       "user": "your_username",
       "password": "your_password",
       "database": "your_database"
     },
     {
       "name": "analytics_db", 
       "host": "analytics.example.com",
       "user": "analytics_user",
       "password": "analytics_password",
       "database": "analytics"
     }
   ]'

   # Security Configuration
   # Options: (default), extended, all, or custom comma-separated list
   # MYSQL_ALLOWED_COMMANDS=extended

   # Connection Pool Settings
   MYSQL_CONNECTION_LIMIT=4
   MYSQL_WAIT_FOR_CONNECTIONS=true

   # Server Metadata
   MCP_SERVER_NAME=mysql-mcp-server
   MCP_SERVER_VERSION=1.0.0
   ```

3. **Add to VS Code MCP Configuration**

   Add to your VS Code `settings.json` under MCP servers:
   ```json
   {
     "mcp": {
       "mcpServers": {
         "mysql-mcp-server": {
           "command": "node",
           "args": ["C:/path/to/mysql-mcp-server/mcp-mysql-lite.js"],
           "env": {}
         }
       }
     }
   }
   ```

## 🔒 Security Levels

Configure `MYSQL_ALLOWED_COMMANDS` in your `.env` file:

### Default (Recommended)
```env
# MYSQL_ALLOWED_COMMANDS not set or commented out
```
**Allowed Commands**: `SELECT`, `SHOW`, `DESCRIBE`, `DESC`, `EXPLAIN`, `ANALYZE`  
**Use Case**: Safe read-only access for data analysis and exploration

### Extended
```env
MYSQL_ALLOWED_COMMANDS=extended
```
**Allowed Commands**: All default commands plus `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, etc.  
**Use Case**: Development environments where schema and data modifications are needed

### All (⚠️ Use with Extreme Caution)
```env
MYSQL_ALLOWED_COMMANDS=all
```
**Allowed Commands**: No restrictions - all SQL commands permitted  
**Use Case**: Trusted environments requiring full database administration capabilities

### Custom
```env
MYSQL_ALLOWED_COMMANDS="SELECT,INSERT,UPDATE,CREATE TABLE"
```
**Allowed Commands**: User-defined comma-separated list  
**Use Case**: Specific operational requirements with tailored permissions

## 🛠️ Available Tools

All tools require explicit database selection for security.

### `query_database`

Execute SQL queries with security validation and explicit database selection.

- **Parameters**:
  - `sql` (string, **required**) - The SQL query to execute
  - `database` (string, **required**) - Database name to target (security requirement)
- **Security**: Commands validated against current security level
- **Returns**: Query results with execution metadata

### `describe_table`

Get complete table structure using `SHOW CREATE TABLE` for comprehensive schema information.

- **Parameters**:
  - `table_name` (string, **required**) - Name of the table to describe
  - `database` (string, **required**) - Database containing the table (security requirement)
- **Returns**: Full table definition including columns, indexes, foreign keys, and engine details

### `list_databases`

Display all configured databases and their connection status.

- **Parameters**: None required
- **Returns**: Database configurations with connection pool status and default database indicator

## 📚 Available Resources

Tables from the default database are exposed as MCP resources with URIs like:
```text
mysql://database_name/table/table_name
```

Browse table schemas directly through VS Code's resource explorer.

## 💡 Usage Examples

### Basic Database Query
```sql
-- Query with explicit database selection (security requirement)
SELECT COUNT(*) FROM users WHERE active = 1
```
**Database**: `primary_db`

### Multi-Database Operations
```sql
-- Analytics query on different database
SELECT DATE(created_at), COUNT(*) 
FROM events 
WHERE event_type = 'purchase' 
GROUP BY DATE(created_at) 
ORDER BY DATE(created_at) DESC 
LIMIT 7
```
**Database**: `analytics_db`

### Table Schema Exploration

Use `describe_table` tool:

- **Table**: `users`
- **Database**: `primary_db`

Returns complete table definition with foreign keys, indexes, and constraints.

### Database Discovery
Use `list_databases` tool to see all configured databases and their connection status:
```json
[
  {
    "name": "primary_db",
    "host": "localhost", 
    "database": "app_production",
    "user": "app_user",
    "isDefault": true,
    "poolCreated": true
  },
  {
    "name": "analytics_db",
    "host": "analytics.company.com",
    "database": "analytics",
    "user": "analytics_user", 
    "isDefault": false,
    "poolCreated": false
  }
]
```

## 🔄 Hot Reload Feature

Update database configurations in `.env` file - they'll be automatically reloaded:

- ✅ **Credential updates**: New passwords/users applied immediately
- ✅ **New databases**: Added to available list (restart VS Code to update tool schemas)
- ✅ **Configuration changes**: Host/port updates applied on next connection
- 🔄 **Existing connections**: Remain active until naturally recycled

## ⚡ Lazy Loading

Connection pools are created only when needed:

- 📊 **Resource efficient**: No unnecessary database connections
- 🚀 **Fast startup**: Server starts immediately regardless of database availability  
- 🔍 **Status visibility**: `list_databases` shows which pools are active
- 🛡️ **Failure isolation**: One database issue doesn't affect others

## 🏗️ Architecture

```text
VS Code MCP Client
       ↓ (stdio)
MySQL MCP Server
       ↓ (lazy loading)
Connection Pools
       ↓ (MySQL protocol)  
Multiple Databases
```

Key design principles:

- **Multi-Database**: Each database has independent configuration and connection pool
- **Security**: Command validation before execution, explicit database selection required
- **Performance**: Lazy loading + connection pooling for optimal resource usage
- **Reliability**: Hot reload + error isolation for production stability

## 🚀 Development

### Running the Server
```bash
npm start
```

### Testing Configuration
```bash
# Test connection to all configured databases
node mcp-mysql-lite.js
```

### Environment Variables

- `MYSQL_DATABASES`: JSON array of database configurations
- `MYSQL_ALLOWED_COMMANDS`: Security level (default/extended/all/custom)
- `MYSQL_CONNECTION_LIMIT`: Max connections per pool (default: 4)
- `MYSQL_WAIT_FOR_CONNECTIONS`: Wait for available connections (default: true)

## 📄 License

MIT License - see LICENSE file for details.

---

Built with ❤️ for the Model Context Protocol ecosystem