# 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**
```bash
git clone <repository-url>
cd database-mcp
```
2. **Install dependencies**
```bash
pip install -r requirements.txt
```
3. **Configure database connection**
Create a `.env` file in the project root:
```env
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
```bash
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
```python
# 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
```python
# 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
```python
# 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
```python
# 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:
```json
{
"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:
```bash
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.
