KatCoder MySQL MCP Server

A secure and feature-rich MySQL Model Context Protocol (MCP) server that enables AI agents and applications to interact with MySQL databases through a standardized interface.

Features

🔒 Security First

• SQL Injection Prevention: Comprehensive input validation and sanitization
• Identifier Validation: Strict validation of table and column names
• Query Whitelisting: Read-only operations by default, write operations require explicit permission
• Connection Pooling: Secure connection management with timeout controls
• Error Handling: Secure error messages that don't expose sensitive information

🛠️ Database Operations

• List: Browse tables and view table structures
• Read: Query data with filtering, pagination, and sorting
• Create: Insert new records with validation
• Add Column: Add new columns to existing tables with full type and constraint support
• Drop Column: Remove columns from tables with safety checks
• Modify Column: Change column definitions (type, constraints, defaults)
• Rename Column: Rename existing columns while preserving data
• Rename Table: Rename tables with safety validation
• Add Index: Create indexes (BTREE, HASH, FULLTEXT, SPATIAL) with unique constraints
• Drop Index: Remove indexes from tables
• Bulk Insert: Efficiently insert multiple records in a single operation
• Update: Modify existing records safely
• Delete: Remove records with mandatory WHERE clauses
• Execute: Run custom SQL queries with security restrictions
• DDL: Execute Data Definition Language statements
• Transaction: Execute multiple operations atomically
• Utility: Database health checks and metadata operations

🔧 Configuration Options

• Connection String: Standard MySQL connection format
• Tool Selection: Enable only the tools you need
• Connection Pooling: Configurable pool settings
• Timeout Controls: Connection and query timeouts

Installation

Note: This package is currently in development and not yet published to npm. Use the development installation method below.

Development Installation (Recommended)

git clone https://github.com/katkoder/katcoder-mysql-mcp.git
cd katcoder-mysql-mcp
npm install
npm run build

Future npm Installation (Coming Soon)

Once published to npm, you will be able to install globally:

# This will be available after publication
npm install -g katcoder-mysql-mcp

Local npm Installation (Coming Soon)

# This will be available after publication
npm install katcoder-mysql-mcp

Usage

Command Line Interface

Current Development Usage

# After building the project (npm run build)
# Basic usage with all tools enabled
node dist/cli.js "mysql://user:password@localhost:3306/database_name"

# With specific tools enabled
node dist/cli.js "mysql://user:password@localhost:3306/database_name" "list,read,utility"

# With verbose logging
node dist/cli.js "mysql://user:password@localhost:3306/database_name" "all" --verbose

Future npm Usage (After Publication)

# Basic usage with all tools enabled
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/database_name"

# With specific tools enabled
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/database_name" "list,read,utility"

# With verbose logging
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/database_name" "all" --verbose

Configuration for AI Agents

Current Development Configuration

Claude Desktop Configuration: Add this configuration to your Claude Desktop configuration file:

{
  "mcpServers": {
    "katkoder_mysql": {
      "command": "node",
      "args": [
        "/path/to/katcoder-mysql-mcp/dist/cli.js",
        "mysql://root:password@localhost:3306/production_db",
        "list,read,create,update,delete,utility"
      ],
      "cwd": "/path/to/katcoder-mysql-mcp"
    }
  }
}

Cursor IDE Configuration: For Cursor IDE, add to your settings:

{
  "mcp.servers": {
    "katkoder_mysql": {
      "command": "node",
      "args": [
        "/path/to/katcoder-mysql-mcp/dist/cli.js",
        "mysql://user:password@localhost:3306/development_db",
        "list,read,execute,utility"
      ],
      "cwd": "/path/to/katcoder-mysql-mcp"
    }
  }
}

Future npm Configuration (After Publication)

Claude Desktop Configuration:

{
  "mcpServers": {
    "katkoder_mysql": {
      "command": "npx",
      "args": [
        "-y",
        "katcoder-mysql-mcp",
        "mysql://root:password@localhost:3306/production_db",
        "list,read,create,update,delete,utility"
      ]
    }
  }
}

Cursor IDE Configuration:

{
  "mcp.servers": {
    "katkoder_mysql": {
      "command": "npx",
      "args": [
        "-y",
        "katcoder-mysql-mcp",
        "mysql://user:password@localhost:3306/development_db",
        "list,read,execute,utility"
      ]
    }
  }
}

Connection String Format

mysql://[user[:password]@]host[:port]/database

Basic Examples:

• mysql://root@localhost:3306/mydb - Local database without password
• mysql://user:password@localhost:3306/mydb - Local database with password
• mysql://user:password@192.168.1.100:3306/mydb - Remote database

Advanced Examples:

• mysql://user:password@db.example.com:3306/production?ssl=true - Remote database with SSL
• mysql://root:password@mysql-container:3306/docker_db - Docker database
• mysql://user:password@localhost:3307/alternative_port - Different port

Available Tools

1. List Tool

Browse database structure and table information.

Parameters:

• table (optional): Specific table name to get column information

Examples:

{
  "name": "list",
  "arguments": {}
}

{
  "name": "list",
  "arguments": {
    "table": "users"
  }
}

Practical Usage Scenarios:

• Database Discovery: When connecting to a new database, use the list tool without parameters to see all available tables
• Schema Exploration: Use with a table name to understand the structure before writing queries
• Data Modeling: Examine relationships between tables by checking foreign key constraints
• Migration Planning: Understand existing schema before making changes

2. Read Tool

Query data from tables with filtering and pagination.

Parameters:

• table (required): Table name to query
• columns (optional): Array of specific columns to select
• where (optional): Object with filter conditions
• limit (optional): Maximum number of rows (max: 10,000)
• offset (optional): Number of rows to skip
• orderBy (optional): Order by clause

Basic Examples:

{
  "name": "read",
  "arguments": {
    "table": "users",
    "columns": ["id", "name", "email"],
    "where": {"status": "active"},
    "limit": 10,
    "orderBy": "created_at DESC"
  }
}

{
  "name": "read",
  "arguments": {
    "table": "products",
    "where": {"category": "electronics", "price": {"$gt": 100}},
    "limit": 50
  }
}

Advanced Filtering Examples:

{
  "name": "read",
  "arguments": {
    "table": "users",
    "columns": ["id", "email", "created_at"],
    "where": {"status": "active", "created_at": {"$gte": "2024-01-01"}},
    "limit": 25,
    "offset": 50,
    "orderBy": "last_login DESC"
  }
}

3. Bulk Insert Tool

Efficiently insert multiple records into a table in a single operation.

Parameters:

• table (required): Target table name
• data (required): Array of objects with identical column-value pairs

Examples:

{
  "name": "bulk_insert",
  "arguments": {
    "table": "users",
    "data": [
      {
        "name": "John Doe",
        "email": "john@example.com",
        "age": 30,
        "status": "active"
      },
      {
        "name": "Jane Smith",
        "email": "jane@example.com",
        "age": 25,
        "status": "active"
      },
      {
        "name": "Bob Wilson",
        "email": "bob@example.com",
        "age": 35,
        "status": "inactive"
      }
    ]
  }
}

Usage in Transactions:

{
  "name": "transaction",
  "arguments": {
    "operations": [
      {
        "type": "bulk_insert",
        "table": "users",
        "data": [
          {
            "name": "Alice Brown",
            "email": "alice@example.com",
            "age": 28,
            "status": "active"
          }
        ]
      },
      {
        "type": "update",
        "table": "user_stats",
        "data": { "total_users": 1 },
        "where": { "id": 1 }
      }
    ]
  }
}

Response Format:

{
  "success": true,
  "table": "users",
  "recordCount": 3,
  "affectedRows": 3,
  "insertedId": 1,
  "message": "Successfully inserted 3 records into users"
}

4. Create Tool

Insert new records into tables.

Parameters:

• table (required): Target table name
• data (required): Object with column-value pairs

Examples:

{
  "name": "create",
  "arguments": {
    "table": "users",
    "data": {
      "name": "John Doe",
      "email": "john@example.com",
      "status": "active"
    }
  }
}

4. Update Tool

Modify existing records safely.

Parameters:

• table (required): Target table name
• data (required): Object with column-value pairs to update
• where (required): Object with filter conditions

Examples:

{
  "name": "update",
  "arguments": {
    "table": "users",
    "data": {
      "status": "inactive",
      "updated_at": "2024-01-01 12:00:00"
    },
    "where": {"id": 123}
  }
}

5. Delete Tool

Remove records with mandatory WHERE clauses.

Parameters:

• table (required): Target table name
• where (required): Object with filter conditions

Examples:

{
  "name": "delete",
  "arguments": {
    "table": "sessions",
    "where": {"expired": true}
  }
}

6. Execute Tool

Run custom SQL queries with security restrictions.

Parameters:

• query (required): SQL query string
• params (optional): Array of query parameters
• allowWrite (optional): Boolean to allow write operations

Basic Examples:

{
  "name": "execute",
  "arguments": {
    "query": "SELECT COUNT(*) as total FROM users WHERE created_at > ?",
    "params": ["2024-01-01"]
  }
}

{
  "name": "execute",
  "arguments": {
    "query": "UPDATE users SET last_login = NOW() WHERE id = ?",
    "params": [123],
    "allowWrite": true
  }
}

Complex Query Examples:

{
  "name": "execute",
  "arguments": {
    "query": "SELECT u.email, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING order_count > 5"
  }
}

{
  "name": "execute",
  "arguments": {
    "query": "SELECT DATE(created_at) as date, COUNT(*) as daily_signups FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY date",
    "params": []
  }
}

7. DDL Tool

Execute Data Definition Language statements.

Parameters:

• statement (required): DDL statement

Examples:

{
  "name": "ddl",
  "arguments": {
    "statement": "CREATE INDEX idx_email ON users(email)"
  }
}

### 8. Add Column Tool
Add new columns to existing tables with comprehensive type and constraint support.

**Parameters:**
- `table` (required): Target table name
- `column` (required): Object with column definition
  - `name` (required): New column name
  - `type` (required): Column data type (e.g., VARCHAR(255), INT, DATETIME)
  - `nullable` (optional): Whether column can contain NULL values
  - `default` (optional): Default value for the column
  - `autoIncrement` (optional): Whether column should auto-increment
  - `comment` (optional): Column comment
- `position` (optional): Object specifying column position
  - `after` (optional): Place column after this existing column
  - `first` (optional): Place column as the first column

**Examples:**
```json
{
  "name": "add_column",
  "arguments": {
    "table": "users",
    "column": {
      "name": "email",
      "type": "VARCHAR(255)",
      "nullable": false,
      "default": "no-email@example.com"
    },
    "position": {
      "after": "name"
    }
  }
}

{
  "name": "add_column",
  "arguments": {
    "table": "products",
    "column": {
      "name": "is_active",
      "type": "BOOLEAN",
      "default": true,
      "comment": "Product availability status"
    }
  }
}

9. Drop Column Tool

Remove columns from tables with safety validation.

Parameters:

• table (required): Table name to remove column from
• column (required): Column name to drop

Examples:

{
  "name": "drop_column",
  "arguments": {
    "table": "users",
    "column": "old_field"
  }
}

10. Modify Column Tool

Change existing column definitions including type, constraints, and defaults.

Parameters:

• table (required): Table name containing the column
• column (required): Column name to modify
• newDefinition (required): Object with new column definition
  • type (required): New column data type
  • nullable (optional): Whether column can contain NULL values
  • default (optional): New default value
  • comment (optional): Column comment

Examples:

{
  "name": "modify_column",
  "arguments": {
    "table": "users",
    "column": "age",
    "newDefinition": {
      "type": "INT",
      "nullable": true,
      "default": null
    }
  }
}

11. Rename Column Tool

Rename existing columns while preserving data.

Parameters:

• table (required): Table name containing the column
• oldName (required): Current column name
• newName (required): New column name
• newDefinition (optional): Column definition for the renamed column

Examples:

{
  "name": "rename_column",
  "arguments": {
    "table": "users",
    "oldName": "user_name",
    "newName": "username"
  }
}

12. Rename Table Tool

Rename tables with safety validation.

Parameters:

• oldName (required): Current table name
• newName (required): New table name

Examples:

{
  "name": "rename_table",
  "arguments": {
    "oldName": "user_profiles",
    "newName": "user_settings"
  }
}

13. Add Index Tool

Create indexes on tables for improved query performance.

Parameters:

• table (required): Table name to add index to
• name (required): Index name
• columns (required): Array of column names to include in the index
• type (optional): Index type (BTREE, HASH, FULLTEXT, SPATIAL)
• unique (optional): Whether the index should be unique

Examples:

{
  "name": "add_index",
  "arguments": {
    "table": "users",
    "name": "idx_email",
    "columns": ["email"],
    "unique": true
  }
}

{
  "name": "add_index",
  "arguments": {
    "table": "products",
    "name": "idx_category_price",
    "columns": ["category_id", "price"],
    "type": "BTREE"
  }
}

14. Drop Index Tool

Remove indexes from tables.

Parameters:

• table (required): Table name containing the index
• name (required): Index name to drop

Examples:

{
  "name": "drop_index",
  "arguments": {
    "table": "users",
    "name": "idx_temp"
  }
}

15. Transaction Tool

Execute multiple operations atomically.

Parameters:

• operations (required): Array of operations to execute in transaction

Basic Examples:

{
  "name": "transaction",
  "arguments": [
    {
      "type": "create",
      "table": "orders",
      "data": {"user_id": 123, "total": 99.99}
    },
    {
      "type": "update",
      "table": "users",
      "data": {"last_order_date": "2024-01-01"},
      "where": {"id": 123}
    }
  ]
}

Advanced Transaction Examples with Schema Changes:

{
  "name": "transaction",
  "arguments": {
    "operations": [
      {
        "type": "add_column",
        "table": "users",
        "column": {
          "name": "phone",
          "type": "VARCHAR(20)",
          "nullable": true
        }
      },
      {
        "type": "add_index",
        "table": "users",
        "name": "idx_phone",
        "columns": ["phone"],
        "unique": true
      },
      {
        "type": "update",
        "table": "users",
        "data": {"phone": "+1234567890"},
        "where": {"id": 1}
      }
    ]
  }
}

Response Format:

{
  "success": true,
  "operations": 3,
  "results": [
    {
      "description": "Add column 'phone' to table 'users'",
### v1.1.0 (Latest)
- **New Feature**: Added Comprehensive Schema Modification Tools
  - Implemented `add_column` tool for adding new columns with full type and constraint support
  - Implemented `drop_column` tool for safely removing columns from tables
  - Implemented `modify_column` tool for changing column definitions
  - Implemented `rename_column` tool for renaming existing columns
  - Implemented `rename_table` tool for renaming tables
  - Implemented `add_index` tool for creating various types of indexes
  - Implemented `drop_index` tool for removing indexes from tables
  - Added comprehensive schema validation and security measures
  - Enhanced transaction support for schema operations with rollback mechanisms
  - Added detailed documentation with examples and usage scenarios

### v1.0.1
- **New Feature**: Added Bulk Insert Tool for efficient multi-record insertion
  - Implemented `bulk_insert` tool for batch data imports
  - Supports inserting multiple records in a single database operation
  - Includes comprehensive validation and error handling
  - Can be used within transactions for atomic operations
  - Added detailed documentation with examples and usage scenarios

### v1.0.0
- Initial release
- All database operations implemented
- Comprehensive security features
- Full documentation
      "affectedRows": 0
    },
    {
      "description": "Create unique index 'idx_phone' on table 'users'",
      "affectedRows": 0
    },
    {
      "description": "Update user record with phone number",
      "affectedRows": 1
    }
  ]
}

{ "name": "ddl", "arguments": { "statement": "ALTER TABLE users ADD COLUMN phone VARCHAR(20)" } }

### 8. Transaction Tool
Execute multiple operations atomically.

**Parameters:**
- `operations` (required): Array of operations to execute

**Basic Examples:**
```json
{
  "name": "transaction",
  "arguments": {
    "operations": [
      {
        "type": "create",
        "table": "orders",
        "data": {"user_id": 123, "total": 99.99}
      },
      {
        "type": "update",
        "table": "users",
        "data": {"last_order_date": "2024-01-01"},
        "where": {"id": 123}
      }
    ]
  }
}

Advanced Transaction Examples:

{
  "name": "transaction",
  "arguments": {
    "operations": [
      {
        "type": "create",
        "table": "orders",
        "data": {"user_id": 123, "total": 99.99, "status": "pending"}
      },
      {
        "type": "update",
        "table": "users",
        "data": {"last_order_date": "2024-01-01"},
        "where": {"id": 123}
      },
      {
        "type": "create",
        "table": "order_items",
        "data": {"order_id": "LAST_INSERT_ID()", "product_id": 456, "quantity": 2}
      }
    ]
  }
}

9. Utility Tool

Database health checks and metadata operations.

Parameters:

• action (required): Utility action (ping, version, stats, describe_table)
• table (optional): Table name (required for describe_table)

Examples:

{
  "name": "utility",
  "arguments": {
    "action": "ping"
  }
}

{
  "name": "utility",
  "arguments": {
    "action": "stats"
  }
}

{
  "name": "utility",
  "arguments": {
    "action": "describe_table",
    "table": "users"
  }
}

Security Features

SQL Injection Prevention

• Input Sanitization: All table and column names are sanitized
• Parameter Binding: All queries use parameterized statements
• Query Validation: Dangerous SQL patterns are blocked
• Write Operation Protection: Write operations require explicit permission

Identifier Validation

• Table Names: Only alphanumeric characters and underscores allowed
• Column Names: Validated against SQL injection patterns
• Where Conditions: Values are checked for dangerous content

Connection Security

• Connection Pooling: Secure connection management
• Timeout Controls: Prevents hanging connections
• Error Handling: Secure error messages without sensitive data

Security Best Practices

1. Use Dedicated Database User

Create a specific MySQL user with limited permissions:

CREATE USER 'mcp_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'mcp_user'@'localhost';
FLUSH PRIVILEGES;

2. Enable Only Required Tools

# Read-only access
npx katcoder-mysql-mcp "mysql://readonly:password@localhost:3306/mydb" "list,read,utility"

# Write access without DDL
npx katcoder-mysql-mcp "mysql://writer:password@localhost:3306/mydb" "list,read,create,update,delete,utility"

3. Use Environment Variables

export MYSQL_URL="mysql://user:password@localhost:3306/mydb"
npx katcoder-mysql-mcp "$MYSQL_URL" "list,read,utility"

Error Handling

The server provides detailed error messages while maintaining security:

{
  "error": true,
  "message": "Table 'nonexistent_table' does not exist",
  "details": "Check the table name and try again"
}

Development

Building the Project

npm run build

Running in Development Mode

npm run dev

Testing

npm test

Environment Variables

• LOG_LEVEL: Set logging level (debug, info, warn, error)
• NODE_ENV: Set environment (development, production)

Troubleshooting

Connection Issues

• Verify MySQL server is running
• Check connection string format
• Ensure database exists
• Verify user permissions

Test Connection

# Test with utility tool
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "utility"

# Then use: {"name": "utility", "arguments": {"action": "ping"}}

Check Database Version

npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "utility"

# Then use: {"name": "utility", "arguments": {"action": "version"}}

Permission Errors

• Check MySQL user privileges
• Ensure database access is granted
• Verify table-level permissions

Performance Issues

• Monitor connection pool usage
• Check query execution times
• Optimize database indexes

Monitor Performance

npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "utility"

# Then use: {"name": "utility", "arguments": {"action": "stats"}}

Advanced Configuration

Custom Connection Pool Settings

# Environment variables for connection tuning
export MYSQL_CONNECTION_LIMIT=20
export MYSQL_ACQUIRE_TIMEOUT=30000
export MYSQL_TIMEOUT=45000

npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb"

Logging Configuration

# Enable debug logging
export LOG_LEVEL=debug

# Enable verbose output
npx katcoder-mysql-mcp "mysql://user:password@localhost:3306/mydb" "all" --verbose

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests
5. Submit a pull request

License

MIT License - see LICENSE file for details.

Support

For issues and questions:

• GitHub Issues: https://github.com/katkoder/katcoder-mysql-mcp/issues
• Documentation: https://github.com/katkoder/katcoder-mysql-mcp/wiki

Changelog

v1.0.1 (Latest)

• New Feature: Added Bulk Insert Tool for efficient multi-record insertion
  • Implemented bulk_insert tool for batch data imports
  • Supports inserting multiple records in a single database operation
  • Includes comprehensive validation and error handling
  • Can be used within transactions for atomic operations
  • Added detailed documentation with examples and usage scenarios

v1.0.0

• Initial release
• All database operations implemented
• Comprehensive security features
• Full documentation