Database MCP Server

Database MCP Server

A Model Context Protocol (MCP) server that provides database operations through a standardized interface. This server enables AI assistants and other MCP clients to interact with your MySQL database using natural language commands.

Features

• Database Introspection: List tables, describe schemas, and get table statistics
• CRUD Operations: Create, read, update, and delete operations for Users, Products, and Orders
• Safe SQL Execution: Execute SELECT queries with built-in safety checks
• Health Monitoring: Database connectivity testing
• Pagination Support: Handle large datasets efficiently
• Search Capabilities: Fuzzy search across user data

Quick Start

Prerequisites

• Python 3.8+
• MySQL database server
• MCP-compatible client (Claude Desktop, etc.)

Installation

1. Clone the repository

   git clone <repository-url>
   cd database-mcp
   

2. Install dependencies

   pip install -r requirements.txt
   

3. Configure database connection

   Create a .env file in the project root:

   DB_USER=your_username
   DB_PASSWORD=your_password
   DB_HOST=127.0.0.1
   DB_PORT=3306
   DB_NAME=your_database_name
   

4. Initialize database tables

   The server automatically creates tables on startup using SQLAlchemy models.

Running the Server

python main.py

The server will start and listen for MCP connections.

Database Schema

The server manages the following tables:

Users (user)

• id (Primary Key)
• name (String, 50 chars)
• email (String, 50 chars, unique)
• password (String, 100 chars)

Products (product)

• id (Primary Key)
• name (String, 100 chars)
• price (Float)
• stock (Integer)

Orders (order_list)

• id (Primary Key)
• user_id (Foreign Key → user.id)
• product_id (Foreign Key → product.id)
• quantity (Integer)

Additional Tables

• chat_history - Conversation logging
• chat_summary - Session summaries
• chat_user - Chat session users
• reminders - User reminders
• recommendations - System recommendations

Available Tools

Database Introspection

• health_check() - Test database connectivity
• list_tables() - Get all table names
• describe_table(table) - Get table schema details
• table_count(table) - Count rows in a table
• sample_rows(table, limit=5) - Get sample data

SQL Execution

• run_sql_select(sql, max_rows=1000) - Execute SELECT queries safely

User Management

• user_create(name, email, password) - Create new user
• user_get(id=None, email=None) - Get user by ID or email
• user_list(limit=100, offset=0, q=None) - List users with search
• user_exists(email) - Check if user exists
• user_update(id, updates) - Update user fields
• user_delete(id) - Delete user

Product Management

• product_create(name, price, stock) - Create new product
• product_get(id) - Get product by ID
• product_list(limit=100, offset=0, q=None) - List products with search
• product_update(id, updates) - Update product fields
• product_delete(id) - Delete product

Order Management

• order_create(user_id, product_id, quantity) - Create new order
• order_get(id) - Get order by ID
• order_list(limit=100, offset=0, user_id=None) - List orders
• order_update(id, updates) - Update order
• order_delete(id) - Delete order

Usage Examples

Basic Database Operations

# Check database health
health_check()
# Returns: {"ok": true}

# List all tables
list_tables()
# Returns: ["user", "product", "order_list", ...]

# Get table schema
describe_table("user")
# Returns detailed column information

User Operations

# Create a user
user_create("John Doe", "[email protected]", "password123")

# Find user by email
user_get(email="[email protected]")

# Search users
user_list(q="john", limit=10)

# Update user
user_update(1, {"name": "John Smith"})

Product Operations

# Create product
product_create("Laptop", 999.99, 50)

# List products with search
product_list(q="laptop", limit=20)

# Update stock
product_update(1, {"stock": 45})

Order Operations

# Create order
order_create(user_id=1, product_id=1, quantity=2)

# Get user's orders
order_list(user_id=1)

MCP Client Configuration

Claude Desktop

Add to your Claude Desktop configuration:

{
  "mcpServers": {
    "database": {
      "command": "python",
      "args": ["path/to/database-mcp/main.py"],
      "env": {
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_HOST": "127.0.0.1",
        "DB_PORT": "3306",
        "DB_NAME": "your_database"
      }
    }
  }
}

Security Features

• SQL Injection Protection: Uses parameterized queries
• Read-Only SQL: run_sql_select only allows SELECT statements
• Row Limits: Automatic pagination and row count limits
• Input Validation: Type checking and bounds validation

Error Handling

The server includes comprehensive error handling:

• Database connection failures
• Invalid SQL queries
• Missing records
• Constraint violations

All errors are logged and return user-friendly messages.

Development

Project Structure

database-mcp/
├── main.py              # MCP server implementation
├── database.py          # Database connection setup
├── models.py            # SQLAlchemy models
├── requirements.txt     # Python dependencies
├── .env                 # Environment configuration
└── schemas/             # Pydantic schemas (optional)
    ├── user_schema.py
    ├── product_schema.py
    └── order_schema.py

Adding New Models

1. Define model in models.py
2. Add CRUD tools in main.py
3. Update database schema as needed

Testing

Test database connectivity:

python -c "from main import health_check; print(health_check())"

Troubleshooting

Common Issues

Connection Refused

• Check MySQL server is running
• Verify connection credentials in .env
• Ensure database exists

Import Errors

• Install all requirements: pip install -r requirements.txt
• Check Python version compatibility

Permission Denied

• Verify database user has necessary privileges
• Check firewall settings

Logging

The server logs errors to help with debugging. Check console output for detailed error messages.

Contributing

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

License

This project is licensed under the MIT License - see the LICENSE file for details.

Support

For issues and questions:

• Check the troubleshooting section
• Review server logs for error details
• Open an issue on GitHub

Note: This MCP server is designed for development and testing. For production use, implement additional security measures, authentication, and monitoring.