# Common Database MCP Server

A Model Context Protocol (MCP) server that provides database connectivity to Claude Code, Claude Desktop, and Windsurf IDE. Supports PostgreSQL, MySQL, SQLite, and DB2 iSeries databases through native Python drivers.

## Features

- ✅ **Multiple Database Support**: PostgreSQL, MySQL, SQLite, DB2 iSeries
- ✅ **Read-Only by Default**: Safe database exploration without risk of data modification
- ✅ **Connection Pooling**: Efficient connection management for network databases
- ✅ **SQL Injection Prevention**: Parameterized queries and query validation
- ✅ **Cross-Platform**: Works on macOS, Linux, and Windows
- ✅ **MCP Tools**: Execute queries, inspect schemas, explore tables
- ✅ **MCP Resources**: Database schemas as resources
- ✅ **MCP Prompts**: Guided workflows for database exploration

## Installation

### Prerequisites

- Python 3.9 or higher
- pip

### Install from Source

```bash
cd /Users/dave/claude-projects/jdbc-mcp-server
pip install -e .
```

### Install Optional Dependencies

For development and testing:

```bash
pip install -e ".[dev]"
```

### Database Driver Installation

The server automatically installs drivers for:
- PostgreSQL (`psycopg2-binary`)
- MySQL (`mysql-connector-python`)
- SQLite (`sqlite3` - built into Python)

For **DB2 iSeries on macOS**:

```bash
# DB2 driver installation
pip install --no-cache-dir ibm_db
```

Note: `ibm_db` works on macOS (both Intel and Apple Silicon M1/M2/M3).

## Configuration

### Claude Code Configuration

Add the server to your `~/.claude/mcp.json` or Claude Desktop configuration:

```json
{
  "mcpServers": {
    "database": {
      "command": "python",
      "args": ["-m", "jdbc_mcp_server"],
      "env": {
        "DB_POSTGRES_TYPE": "postgresql",
        "DB_POSTGRES_HOST": "localhost",
        "DB_POSTGRES_PORT": "5432",
        "DB_POSTGRES_DATABASE": "myapp",
        "DB_POSTGRES_USERNAME": "readonly_user",
        "DB_POSTGRES_PASSWORD": "secure_password",
        "DB_POSTGRES_READ_ONLY": "true",
        "DB_POSTGRES_POOL_SIZE": "10"
      }
    }
  }
}
```

### Environment Variables

Configure databases using environment variables with the format:

```bash
DB_<NAME>_TYPE=postgresql|mysql|sqlite|db2
DB_<NAME>_HOST=hostname
DB_<NAME>_PORT=port
DB_<NAME>_DATABASE=database_name
DB_<NAME>_USERNAME=username
DB_<NAME>_PASSWORD=password
DB_<NAME>_READ_ONLY=true|false
DB_<NAME>_POOL_SIZE=5
```

Or use connection strings:

```bash
DB_<NAME>_TYPE=postgresql
DB_<NAME>_CONNECTION_STRING=postgresql://user:pass@localhost:5432/database
```

### Multiple Database Example

Configure multiple databases:

```json
{
  "mcpServers": {
    "database": {
      "command": "python",
      "args": ["-m", "jdbc_mcp_server"],
      "env": {
        "DB_PROD_TYPE": "postgresql",
        "DB_PROD_CONNECTION_STRING": "postgresql://readonly@prod-server:5432/production",
        "DB_PROD_READ_ONLY": "true",

        "DB_LOCAL_TYPE": "sqlite",
        "DB_LOCAL_PATH": "/Users/dave/data/local.db",
        "DB_LOCAL_READ_ONLY": "false",

        "DB_ANALYTICS_TYPE": "mysql",
        "DB_ANALYTICS_HOST": "analytics.example.com",
        "DB_ANALYTICS_PORT": "3306",
        "DB_ANALYTICS_DATABASE": "analytics",
        "DB_ANALYTICS_USERNAME": "analyst",
        "DB_ANALYTICS_PASSWORD": "password"
      }
    }
  }
}
```

## Usage

### Available MCP Tools

#### `list_databases()`
List all configured database connections.

```python
list_databases()
# Returns: {"success": True, "databases": [{"name": "prod", "type": "postgresql", "read_only": True}, ...]}
```

#### `test_connection(database)`
Test database connectivity.

```python
test_connection(database="prod")
# Returns: {"success": True, "connected": True, "database_type": "PostgreSQL", "version": "15.2", ...}
```

#### `list_schemas(database)`
List all schemas/databases (PostgreSQL/MySQL only).

```python
list_schemas(database="prod")
# Returns: {"success": True, "schemas": ["public", "app", ...]}
```

#### `list_tables(database, schema=None)`
List all tables in a database.

```python
list_tables(database="prod", schema="public")
# Returns: {"success": True, "tables": ["users", "orders", ...]}
```

#### `describe_table(database, table, schema=None)`
Get detailed table schema.

```python
describe_table(database="prod", table="users", schema="public")
# Returns: {"success": True, "columns": [{"name": "id", "type": "integer", "nullable": False, "primary_key": True}, ...]}
```

#### `execute_query(database, query, parameters=None, limit=100)`
Execute a SELECT query with parameterized inputs.

```python
execute_query(
    database="prod",
    query="SELECT * FROM users WHERE status = %s AND created_at > %s",
    parameters=["active", "2024-01-01"],
    limit=50
)
# Returns: {"success": True, "columns": [...], "rows": [...], "row_count": 50}
```

#### `get_sample_data(database, table, schema=None, limit=10)`
Get sample rows from a table.

```python
get_sample_data(database="prod", table="users", limit=5)
# Returns: {"success": True, "columns": [...], "rows": [...]}
```

### Available MCP Resources

#### `db://{database}/schema`
Get complete database schema as markdown.

#### `db://{database}/tables/{table}/schema`
Get specific table schema as markdown.

### Available MCP Prompts

#### `explore_database`
Guided workflow for database exploration.

#### `query_with_safety`
Instructions for generating safe parameterized queries.

#### `analyze_table_structure`
Analyze table structure and identify relationships.

## Security Best Practices

### 1. Use Read-Only Mode

Always use read-only mode (default) when exploring production databases:

```bash
DB_PROD_READ_ONLY=true
```

### 2. Create Dedicated Database Users

Create database users with SELECT-only permissions:

**PostgreSQL:**
```sql
CREATE USER readonly_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
```

**MySQL:**
```sql
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp.* TO 'readonly_user'@'%';
FLUSH PRIVILEGES;
```

### 3. Use Environment Variables

Store credentials in environment variables, never in code:

```bash
export DB_PROD_PASSWORD="$(cat ~/.secrets/db_password)"
```

### 4. Network Security

- Use SSL/TLS for database connections
- Restrict database access by IP address
- Use SSH tunnels for remote databases

## Troubleshooting

### Connection Refused

**PostgreSQL/MySQL:**
```
Error: Cannot connect to server. Check if the server is running.
```

Solutions:
- Verify the database server is running
- Check hostname and port are correct
- Ensure firewall allows connections
- Test with `psql` or `mysql` command-line tools

### Authentication Failed

```
Error: Invalid username or password
```

Solutions:
- Verify credentials are correct
- Check user has necessary permissions
- For PostgreSQL, check `pg_hba.conf` allows connections

### SQLite Database Locked

```
Error: SQLite database is locked by another process.
```

Solutions:
- Close other applications using the database
- Wait a moment and try again
- Check file permissions

### ibm_db Installation Issues (macOS)

If `ibm_db` fails to install:

```bash
# Try with no cache
pip install --no-cache-dir ibm_db

# For Apple Silicon, ensure using Python 3.9+
python3 --version
```

## Development

### Running Tests

```bash
pytest tests/
```

### Running with Debug Logging

```bash
export LOG_LEVEL=DEBUG
python -m jdbc_mcp_server
```

### Project Structure

```
jdbc-mcp-server/
├── src/jdbc_mcp_server/
│   ├── __init__.py           # Package initialization
│   ├── __main__.py           # Entry point
│   ├── server.py             # FastMCP server and tools
│   ├── config.py             # Configuration management
│   ├── errors.py             # Exception hierarchy
│   ├── utils.py              # Utility functions
│   └── database/
│       ├── base.py           # Abstract database adapter
│       ├── postgresql.py     # PostgreSQL adapter
│       ├── mysql.py          # MySQL adapter
│       ├── sqlite.py         # SQLite adapter
│       └── db2.py            # DB2 adapter
└── tests/                    # Test suite
```

## MVP Status

### Currently Supported (v0.1.0)
- ✅ PostgreSQL
- ✅ MySQL
- ✅ SQLite
- ✅ DB2 iSeries
- ✅ Read-only queries
- ✅ Connection pooling
- ✅ Schema inspection
- ✅ Parameterized queries
- ✅ MCP tools, resources, and prompts

### Coming Soon
- 🔜 Write operations (opt-in)
- 🔜 Transaction support
- 🔜 Query caching
- 🔜 Stored procedure execution

## Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Add tests for new functionality
4. Ensure all tests pass
5. Submit a pull request

## License

MIT License - see LICENSE file for details.

## Support

For issues, questions, or contributions:
- GitHub Issues: [Create an issue](https://github.com/yourusername/jdbc-mcp-server/issues)
- Documentation: This README

## Acknowledgments

- Built with [FastMCP](https://github.com/jlowin/fastmcp)
- Implements the [Model Context Protocol](https://modelcontextprotocol.io/)
- Database drivers: psycopg2, mysql-connector-python, ibm_db