# General-Purpose MCP Database Server (Python)

A Model Context Protocol (MCP) server that provides LLMs with generic database access capabilities for PostgreSQL databases. Built with Python and FastMCP.

## Features

- **Query Tools**: Execute SELECT queries, write operations (INSERT/UPDATE/DELETE), and export data
- **Schema Management**: Create, alter, and drop tables, list tables, describe table schemas
- **Insights**: Store and retrieve business insights in a dedicated memo table
- **Connection Pooling**: Efficient database connection management
- **Docker Support**: Easy containerized deployment
- **Flexible Configuration**: Support for DATABASE_URL or individual connection parameters
- **Multiple Transport Modes**: stdio (local) or SSE (HTTP for remote deployment)

## Installation

### Local Development

1. **Clone or navigate to the repository:**
```bash
cd general-database-mcp-python
```

2. **Create a virtual environment:**
```bash
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
```

3. **Install dependencies:**
```bash
pip install -r requirements.txt
```

4. **Configure environment variables:**
```bash
cp .env.example .env
# Edit .env with your database credentials
```

5. **Run the server:**
```bash
python main.py
```

### Docker Deployment

1. **Build the Docker image:**
```bash
docker build -t general-database-mcp .
```

2. **Run the container:**
```bash
docker run -p 8008:8008 \
  -e DATABASE_URL="postgresql://user:password@host:5432/database" \
  -e MCP_TRANSPORT=http \
  general-database-mcp
```

## Configuration

### Environment Variables

**Database Configuration** (choose one option):

**Option 1: DATABASE_URL** (recommended)
```env
DATABASE_URL=postgresql://user:password@host:5432/database
```

**Option 2: Individual parameters**
```env
DB_HOST=localhost
DB_PORT=5432
DB_NAME=your_database
DB_USER=your_username
DB_PASSWORD=your_password
```

**Server Configuration:**
```env
PORT=8008
MCP_TRANSPORT=stdio  # Options: stdio (local) or http (remote)
API_KEY=your_secret_key  # Optional: for authentication
```

## Available Tools

### Query Tools

#### `read_query`
Execute SELECT queries to read data from the database.
- **Parameters**: `query` (string) - SQL SELECT statement
- **Returns**: Query results as JSON

#### `write_query`
Execute INSERT, UPDATE, or DELETE queries to modify data.
- **Parameters**: `query` (string) - SQL modification statement
- **Returns**: Number of affected rows

#### `export_query`
Execute a SELECT query and export results in CSV or JSON format.
- **Parameters**: 
  - `query` (string) - SQL SELECT statement
  - `format` (string) - "csv" or "json" (default: "json")
- **Returns**: Formatted query results

### Schema Management Tools

#### `create_table`
Create new tables in the database.
- **Parameters**: `query` (string) - CREATE TABLE statement
- **Returns**: Success status

#### `alter_table`
Modify existing table schema (add columns, rename, etc.).
- **Parameters**: `query` (string) - ALTER TABLE statement
- **Returns**: Success status

#### `drop_table`
Remove a table from the database with safety confirmation.
- **Parameters**: 
  - `table_name` (string) - Name of table to drop
  - `confirm` (boolean) - Must be True to proceed
- **Returns**: Success status

#### `list_tables`
Get a list of all tables in the database.
- **Parameters**: None
- **Returns**: Array of table names

#### `describe_table`
View schema information for a specific table.
- **Parameters**: `table_name` (string) - Name of table
- **Returns**: Column definitions with types, constraints, etc.

### Insights Tools

#### `append_insight`
Add a business insight to the memo table.
- **Parameters**: `insight` (string) - Text of the insight
- **Returns**: Success status

#### `list_insights`
List all business insights stored in the memo table.
- **Parameters**: None
- **Returns**: Array of insights with timestamps

### Monitoring

#### `health_check`
Health check endpoint for monitoring and deployment verification.
- **Parameters**: None
- **Returns**: Server status and database connection info

## Usage Examples

### Using with Claude Desktop

Add to your Claude Desktop configuration file:

**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
**Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
**Linux**: `~/.config/Claude/claude_desktop_config.json`

```json
{
  "mcpServers": {
    "database": {
      "command": "python",
      "args": [
        "/absolute/path/to/general-database-mcp-python/main.py"
      ],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/mydb"
      }
    }
  }
}
```

### Example Prompts for Claude

**Query data:**
```
Show me all tables in the database.
Describe the structure of the users table.
Select all records from the products table where price > 100.
```

**Modify data:**
```
Insert a new user with name 'John Doe' and email 'john@example.com'.
Update all products in the 'Electronics' category to increase price by 10%.
Delete all orders older than 2 years.
```

**Schema operations:**
```
Create a new table called 'logs' with columns for id, timestamp, and message.
Add a 'created_at' column to the users table.
Drop the temporary_data table (confirm=True).
```

**Export data:**
```
Export all customer data as CSV.
Export sales summary as JSON.
```

**Business insights:**
```
Add an insight: "Sales increased 25% in Q4 due to holiday promotions"
Show me all stored insights.
```

## Architecture

```
general-database-mcp-python/
├── main.py                    # FastMCP server entry point
├── db/
│   ├── __init__.py
│   └── postgres_adapter.py   # PostgreSQL adapter with connection pooling
├── tools/
│   ├── __init__.py
│   ├── query_tools.py        # Query execution tools
│   ├── schema_tools.py       # DDL operation tools
│   └── insights_tools.py     # Business insights tools
└── utils/
    ├── __init__.py
    └── format_utils.py       # Response formatting and CSV conversion
```

## Development

### Running Tests
```bash
# TODO: Add test suite
python -m pytest tests/
```

## Security Considerations

1. **Never expose database credentials** in your conversations with Claude
2. **Use environment variables** for sensitive configuration
3. **Enable API_KEY** for production deployments
4. **Review SQL queries** generated by the LLM before execution in production
5. **Use read-only database users** when only SELECT access is needed

## License

MIT License - See LICENSE file for details

## Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

## Support

For issues, questions, or contributions, please open an issue on the repository.

## Version

Current Version: 1.0.0

## Acknowledgments

- Built with [FastMCP](https://github.com/jlowin/fastmcp) framework
- Inspired by [general-database-mcp](https://github.com/executeautomation/mcp-database-server)
- Part of the Model Context Protocol ecosystem