Universal-SQL-MCP-Server

Wunrry/Universal-SQL-MCP-Server

3.3

If you are the rightful owner of Universal-SQL-MCP-Server and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to henry@mcphub.com.

The Universal SQL MCP Server is a Model Context Protocol server that provides secure access to multiple SQL database engines through a standardized interface.

Tools
4
Resources
0
Prompts
0

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:

# 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:
git clone <repository-url>
cd gen-http-sql-mcp
  1. Install dependencies:
# Using pip
pip install fastmcp mysql-connector-python psycopg2-binary pyodbc sqlalchemy python-dotenv

# Or using uv
uv sync
  1. Optional: Install database-specific drivers only if needed:
# 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:
cp .env.example .env
  1. Edit the .env file with your database credentials:

MySQL Configuration

DB_TYPE=mysql
DB_HOST=localhost
DB_PORT=3306
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database

PostgreSQL Configuration

DB_TYPE=postgresql
DB_HOST=localhost
DB_PORT=5432
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database

SQLite Configuration

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

SQL Server Configuration

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

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

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:
{
  "method": "tools/call",
  "params": {
    "name": "get_database_schema"
  }
}
  1. Execute SQL Query (works with all database types):
{
  "method": "tools/call",
  "params": {
    "name": "execute_sql_query",
    "arguments": {
      "sql_query": "SELECT * FROM users LIMIT 10"
    }
  }
}
  1. Execute Write Operation (works with all database types):
{
  "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:

INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane@example.com') RETURNING id;

SQLite with autoincrement:

INSERT INTO users (name, email) VALUES ('Bob Smith', 'bob@example.com');

SQL Server with OUTPUT clause:

INSERT INTO users (name, email) OUTPUT INSERTED.id VALUES ('Alice Johnson', 'alice@example.com');
  1. Test Connection:
{
  "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)
# Enabled by default, shows basic request information
ENABLE_REQUEST_LOGGING=true
Detailed Request Logging (Debug Mode)
# 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:

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