SQL MCP Server

A Model Context Protocol server that exposes a Postgres database to LLM agents (Claude Desktop, Claude Code, or any MCP client) through six read-only tools. Point an agent at it and ask questions like "which customers placed more than five orders last month?" — the agent explores the schema and queries the data itself, through the tools below.

Tools

Related MCP server: mcp-data-gateway

Why this isn't "just a wrapper around psycopg2"

Text-to-SQL demos are common; the part that's actually hard — and where this project puts its effort — is making execute_select safe to hand to an LLM that will generate arbitrary SQL:

1. Read-only Postgres role. The server connects as mcp_readonly, a role with SELECT-only grants (see scripts/init_schema.sql). Even a bug in the application-level checks below can't cause a write.

2. Session-level read-only enforcement. Every connection runs SET TRANSACTION READ ONLY (db.py).

3. Statement validation (security.py): only a single SELECT/WITH statement is allowed — no stacked statements (; DROP TABLE ...), no SQL comments (blocks comment-based statement smuggling), and a keyword blocklist covers INSERT/UPDATE/DELETE/DDL/GRANT/etc., including SELECT ... INTO (which silently creates a table).

4. Identifier validation. describe_table, sample_rows, and count_rows take a table name as a parameter. Since SQL identifiers can't be parameterized with placeholders, table names are checked against a strict regex and a live allow-list fetched from information_schema — not just string-escaped.

5. Resource limits. A Postgres statement_timeout prevents runaway queries, and a server-side row cap is enforced on every query result, even if the LLM's query didn't specify a LIMIT.

Quickstart

git clone <this-repo>
cd sql-mcp-server
pip install -r requirements.txt

# 1. Start Postgres with the sample schema
docker compose up -d

# 2. Generate sample e-commerce data (uses the postgres superuser, not mcp_readonly)
PGUSER=postgres PGPASSWORD=postgres python scripts/generate_sample_data.py

# 3. Configure the server to use the read-only role
cp .env.example .env
# edit .env if you changed the default mcp_readonly password

# 4. Run the tests
pytest

# 5. Run the server (stdio transport, for use with an MCP client)
python -m sql_mcp_server.server

Connecting to Claude Desktop

Add to your Claude Desktop MCP config (claude_desktop_config.json):

{
  "mcpServers": {
    "sql-explorer": {
      "command": "python",
      "args": ["-m", "sql_mcp_server.server"],
      "cwd": "/absolute/path/to/sql-mcp-server",
      "env": {
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGDATABASE": "sales",
        "PGUSER": "mcp_readonly",
        "PGPASSWORD": "change_me"
      }
    }
  }
}

Restart Claude Desktop, then ask something like "What tables are available, and which product category has the highest total revenue?"

Sample schema

orders → order_items → products → categories, plus customers. Revenue for an order = sum(order_items.quantity * order_items.unit_price). The generator seeds ~600 customers, ~3,500 orders, and a handful of intentional data quirks (missing emails, a few bulk-order outliers) so queries look like they're hitting real data.

Testing

tests/test_security.py and tests/test_tools.py run without a database — they test the validation layer directly and the tool functions with the DB layer mocked. This is what CI runs. db.py itself (the psycopg2 layer) is exercised in practice by running the server against the Docker Postgres instance; see Quickstart above.

Project structure

sql_mcp_server/
  config.py    Environment-based settings
  security.py  SQL/identifier validation (the core safety logic)
  db.py        psycopg2 access layer
  server.py    MCP tool definitions
scripts/
  init_schema.sql            Schema + read-only role setup
  generate_sample_data.py    Faker-based sample data
tests/
  test_security.py  Validation logic (18+ cases: injection, stacked
                     statements, comment smuggling, DDL/DML blocking, etc.)
  test_tools.py     Tool functions with mocked DB