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