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", "john@example.com", "password123")

# Find user by email
user_get(email="john@example.com")

# 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 checki