# Universal SQL MCP Server

A Model Context Protocol (MCP) server that provides secure access to multiple SQL database engines. This server enables AI assistants and other MCP clients to interact with various SQL databases through a standardized interface.

## Supported Databases

- **MySQL** - Full support with comprehensive schema information
- **PostgreSQL** - Full support with comprehensive schema information  
- **SQLite** - Full support, perfect for local development and testing
- **SQL Server** - Full support with ODBC connectivity


## Features

- **Multi-Database Support**: Works with MySQL, PostgreSQL, SQLite, and SQL Server
- **Database Schema Inspection**: Get comprehensive information about all tables, columns, indexes, and constraints
- **Safe Query Execution**: Execute SELECT queries with built-in security restrictions
- **Controlled Write Operations**: Execute INSERT and UPDATE operations with proper security controls
- **Connection Testing**: Verify database connectivity and configuration
- **Environment-based Configuration**: Secure configuration through environment variables
- **Comprehensive Logging**: Detailed logging for monitoring and debugging
- **Database-Specific Optimizations**: Tailored queries and features for each database engine


## Tools Provided

### 1. `get_database_schema`
Retrieves comprehensive information about all tables in the database including:
- Table names and comments
- Column definitions with data types, constraints, and comments
- Index information (primary keys, unique indexes, regular indexes)
- Table statistics (estimated row count, storage size)

### 2. `execute_sql_query`
Executes SQL SELECT queries safely with the following restrictions:
- Only SELECT statements are allowed
- Dangerous keywords (DROP, DELETE, UPDATE, etc.) are blocked
- Returns results as structured data with metadata

### 3. `execute_write_operation` (Optional)
Executes SQL write operations (INSERT and UPDATE) safely with the following restrictions:
- Only INSERT and UPDATE statements are allowed
- DELETE, DROP, TRUNCATE, ALTER, CREATE operations are blocked
- Returns affected row count and last insert ID (for INSERT operations)
- Provides transaction safety with automatic commit
- **Note**: This tool is only available when `ENABLE_WRITE_OPERATIONS=true` is set in the configuration

### 4. `test_database_connection`
Tests the database connection to ensure proper configuration and connectivity.

## Quick Start

### Try the Demo (SQLite)

The fastest way to see the Universal SQL MCP Server in action:

```bash
# Clone the repository
git clone <repository-url>
cd gen-http-sql-mcp

# Install dependencies
pip install fastmcp mysql-connector-python psycopg2-binary pyodbc sqlalchemy python-dotenv

# Run the demo (creates a SQLite database with sample data)
python demo.py

# Start the MCP server
python main.py
```

The demo creates a SQLite database with sample users and orders, then demonstrates all MCP tools.

## Installation

1. Clone this repository:
```bash
git clone <repository-url>
cd gen-http-sql-mcp
```

2. Install dependencies:
```bash
# Using pip
pip install fastmcp mysql-connector-python psycopg2-binary pyodbc sqlalchemy python-dotenv

# Or using uv
uv sync
```

3. **Optional**: Install database-specific drivers only if needed:
```bash
# For MySQL only
pip install fastmcp mysql-connector-python python-dotenv

# For PostgreSQL only  
pip install fastmcp psycopg2-binary python-dotenv

# For SQLite only (no additional drivers needed)
pip install fastmcp python-dotenv

# For SQL Server only
pip install fastmcp pyodbc python-dotenv
```


## Configuration

1. Copy the example environment file:
```bash
cp .env.example .env
```

2. Edit the `.env` file with your database credentials:

### MySQL Configuration
```env
DB_TYPE=mysql
DB_HOST=localhost
DB_PORT=3306
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database
```

### PostgreSQL Configuration
```env
DB_TYPE=postgresql
DB_HOST=localhost
DB_PORT=5432
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database
```

### SQLite Configuration
```env
DB_TYPE=sqlite
DB_NAME=/path/to/your/database.db
# Note: SQLite doesn't require host, port, user, or password
```

### SQL Server Configuration
```env
DB_TYPE=sqlserver
DB_HOST=localhost
DB_PORT=1433
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database
DB_DRIVER=ODBC Driver 17 for SQL Server
```

### Common Optional Settings
```env
# Optional: Connection pool settings (not applicable for SQLite)
DB_POOL_SIZE=5
DB_MAX_OVERFLOW=10

# Optional: Connection timeout settings (in seconds)
DB_CONNECT_TIMEOUT=10
DB_READ_TIMEOUT=30
DB_WRITE_TIMEOUT=30

# Optional: Enable write operations (INSERT/UPDATE) - set to true to enable
ENABLE_WRITE_OPERATIONS=false
```


### Configuration Options

- **DB_TYPE**: Specifies the database engine to use
  - `mysql`: MySQL database (requires mysql-connector-python)
  - `postgresql`: PostgreSQL database (requires psycopg2-binary)
  - `sqlite`: SQLite database (built-in Python support)
  - `sqlserver`: SQL Server database (requires pyodbc)

- **ENABLE_WRITE_OPERATIONS**: Controls whether the `execute_write_operation` tool is available
  - `false` (default): Only read-only operations are allowed (SELECT queries only)
  - `true`: Enables INSERT and UPDATE operations through the `execute_write_operation` tool
  - For security reasons, DELETE, DROP, TRUNCATE, ALTER, and CREATE operations are always blocked

- **Request Logging Configuration**:
  - **ENABLE_REQUEST_LOGGING**: Enable basic request logging (`true` by default)
  - **ENABLE_DETAILED_REQUEST_LOGGING**: Enable detailed request logging with headers and payloads (`false` by default)
  - **REQUEST_LOG_LEVEL**: Log level for request logging (`INFO` by default)
  - **MAX_PAYLOAD_LOG_LENGTH**: Maximum length of logged payloads (`2000` by default)
  - **LOG_LEVEL**: General application log level (`INFO` by default)

### Database-Specific Notes

- **SQLite**: Only requires `DB_NAME` (file path). Connection pooling settings are ignored.
- **SQL Server**: May require additional ODBC driver installation and `DB_DRIVER` specification.
- **PostgreSQL**: Uses `psycopg2-binary` for optimal performance and compatibility.
- **MySQL**: Uses the official `mysql-connector-python` driver.


## Usage

### Running the Server

Start the MCP server:
```bash
uv run python main.py
```

The server will:
1. Load configuration from environment variables
2. Test the database connection
3. Start the MCP server and listen for requests

### Using with MCP Clients

This server implements the Model Context Protocol and can be used with any MCP-compatible client. The server provides three tools that can be called by MCP clients.

#### Example Tool Calls

1. **Get Database Schema**:
```json
{
  "method": "tools/call",
  "params": {
    "name": "get_database_schema"
  }
}
```

2. **Execute SQL Query** (works with all database types):
```json
{
  "method": "tools/call",
  "params": {
    "name": "execute_sql_query",
    "arguments": {
      "sql_query": "SELECT * FROM users LIMIT 10"
    }
  }
}
```

3. **Execute Write Operation** (works with all database types):
```json
{
  "method": "tools/call",
  "params": {
    "name": "execute_write_operation",
    "arguments": {
      "sql_query": "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')"
    }
  }
}
```

### Database-Specific Query Examples

**PostgreSQL with RETURNING clause:**
```sql
INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane@example.com') RETURNING id;
```

**SQLite with autoincrement:**
```sql
INSERT INTO users (name, email) VALUES ('Bob Smith', 'bob@example.com');
```

**SQL Server with OUTPUT clause:**
```sql
INSERT INTO users (name, email) OUTPUT INSERTED.id VALUES ('Alice Johnson', 'alice@example.com');
```


4. **Test Connection**:
```json
{
  "method": "tools/call",
  "params": {
    "name": "test_database_connection"
  }
}
```

## Security Features

- **Controlled Write Access**: Only INSERT and UPDATE operations are permitted for write operations
- **Read Access**: SELECT queries are available through dedicated tool
- **Query Validation**: Dangerous SQL keywords (DELETE, DROP, TRUNCATE, etc.) are blocked
- **Operation Separation**: Read and write operations are handled by separate tools
- **Environment Variables**: Sensitive configuration is stored in environment variables
- **Connection Management**: Proper connection handling with timeouts and cleanup
- **Transaction Safety**: Write operations include automatic commit and error handling

## Project Structure

```
gen-http-sql-mcp/
├── main.py              # Main server entry point
├── database.py          # Universal database connection and management
├── tools.py             # MCP tools implementation
├── .env.example         # Environment configuration template
├── pyproject.toml       # Project dependencies and metadata
└── README.md           # This file
```

## Database Engine Support Details

### MySQL
- Full schema introspection with table comments, column details, and index information
- Supports connection pooling and timeout configurations
- Uses `mysql-connector-python` for optimal compatibility

### PostgreSQL  
- Comprehensive schema information including table and column comments
- Advanced index information and constraint details
- Uses `psycopg2-binary` for high performance

### SQLite
- Complete table and column information
- Index details and primary key information
- Perfect for development, testing, and lightweight applications
- No additional driver installation required

### SQL Server
- Full table and column schema information
- Supports both Windows and SQL Server authentication
- Uses ODBC connectivity via `pyodbc`
- Configurable ODBC driver selection


## Dependencies

- **fastmcp**: FastMCP framework for building MCP servers
- **mysql-connector-python**: Official MySQL driver for Python
- **psycopg2-binary**: PostgreSQL adapter for Python
- **pyodbc**: ODBC database connectivity for SQL Server
- **sqlalchemy**: SQL toolkit and Object-Relational Mapping library
- **python-dotenv**: Environment variable loading
- **sqlite3**: Built-in Python SQLite support (no additional installation needed)


## Error Handling

The server includes comprehensive error handling:
- Database connection errors are logged and reported
- Invalid SQL queries are rejected with clear error messages
- Configuration validation ensures required parameters are present
- Graceful shutdown on interruption

## Logging

The server provides comprehensive logging capabilities:

### Basic Logging
- Connection status and database information
- Query execution results and performance
- Error messages with context
- Server startup and shutdown events

### Request Logging
The server includes advanced request logging middleware to help debug client connection issues:

#### Simple Request Logging (Default)
```bash
# Enabled by default, shows basic request information
ENABLE_REQUEST_LOGGING=true
```

#### Detailed Request Logging (Debug Mode)
```bash
# Enable detailed logging with headers and payloads
ENABLE_DETAILED_REQUEST_LOGGING=true
REQUEST_LOG_LEVEL=DEBUG
MAX_PAYLOAD_LOG_LENGTH=5000
LOG_LEVEL=DEBUG
```

### Docker Debug Environment
For debugging client connection issues, use the debug environment:

```bash
# Start debug environment with detailed logging
make debug

# View debug logs
make logs-debug

# View only MCP server debug logs
make logs-debug-mcp
```

The debug environment enables:
- Detailed request/response logging
- HTTP headers logging
- Request payload logging
- Response payload logging
- Execution timing
- Client information tracking

## 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](LICENSE) file for details.


## Support

For issues and questions:
1. Check the logs for error messages
2. Verify your database configuration
3. Ensure your database server is accessible
4. Create an issue in the repository