SQL MCP Server

A Model Context Protocol (MCP) server that exposes a read-only view of a Postgres database to any MCP-compatible AI assistant. Point an MCP client at it and ask questions like "which customers placed more than five orders last month?" — the assistant explores the schema and queries the data itself, through the six tools described below.

MCP is a standard protocol that lets an AI assistant call external tools in a structured way, instead of guessing at raw database credentials or APIs. This server implements the "tool provider" side of that protocol for a Postgres database.

What this actually solves

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 on its own:

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 caller's query didn't specify a LIMIT.

If every one of those checks failed at once, the database connection itself still couldn't write anything — that's the point of layering them.

Related MCP server: PostgreSQL MCP Server

Architecture

 User question
      │
      ▼
 AI assistant (any MCP client)
      │  decides which tool to call
      ▼
 MCP server (this project, sql_mcp_server/server.py)
      │  validates the request
      ▼
 security.py   — statement / identifier validation
 db.py         — psycopg2 access layer
      │  only if valid
      ▼
 PostgreSQL  (mcp_readonly role, READ ONLY transaction, statement_timeout)

The server itself never decides what to query — that's the assistant's job. It only decides whether a given request is safe to run.

Tools

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.

Tech stack

Getting started

1. Clone the repository

git clone https://github.com/Kenza-21/MCP-SQL-Server.git
cd MCP-SQL-Server

2. Set up Postgres

Option A — Docker (recommended, matches this repo's defaults):

docker compose up -d

This starts Postgres 16 and applies scripts/init_schema.sql automatically (creates the sample tables and the mcp_readonly role).

Option B — an existing/native PostgreSQL instance:

# Create the database first
psql -U postgres -c "CREATE DATABASE sales;"

# Then apply the schema + read-only role
psql -U postgres -d sales -f scripts/init_schema.sql

3. Install Python dependencies

python -m venv venv
# Windows: venv\Scripts\activate | macOS/Linux: source venv/bin/activate
pip install -r requirements.txt

4. Generate sample data

Uses an admin/superuser role (not mcp_readonly), since it needs to write:

PGUSER=postgres PGPASSWORD=postgres python scripts/generate_sample_data.py

5. Configure the server

cp .env.example .env
# edit .env if your Postgres credentials differ from the defaults

6. Run the tests

pytest

7. Run the server

python -m sql_mcp_server.server

The server speaks MCP over stdio — it's meant to be launched by an MCP client, not run standalone and typed into.

Connecting an MCP client

Any MCP-compatible client that supports stdio servers can use this configuration shape (exact file location depends on the client):

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

Once connected, ask the assistant something like "What tables are available, and which product category has the highest total revenue?" — it will call list_tables, describe_table, and execute_select on its own to answer.

You can also test the server manually, without any AI assistant, using MCP Inspector:

npx @modelcontextprotocol/inspector python -m sql_mcp_server.server

This opens a local web UI where you can call each tool by hand and inspect the raw responses — useful for verifying the server works before wiring it into a client.

Web console (optional)

A small browser UI to try the six tools by hand, independent of any MCP client. It imports the same db.py and security.py modules as the MCP server itself, so whatever it rejects (stacked statements, comments, DML/DDL keywords) is rejected by the real validation logic — not a separate re-implementation that could drift out of sync. Standard library only, no extra dependencies.

python -m web.console      # then open http://localhost:8765

Requires the same Postgres connection / .env as the MCP server.

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 a real Postgres instance; see Getting Started 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
web/
  console.py   Optional browser console over db.py + security.py
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

Security notes

• Never commit .env — it's already listed in .gitignore. Only .env.example (placeholder values) is tracked.

• The default mcp_readonly password (change_me) is a placeholder for local development. Change it before pointing this at anything that isn't a throwaway sample database.

• execute_select returns structured {"error": "..."} responses for rejected queries instead of raising exceptions, so a calling assistant gets a clear reason and can retry with a corrected query — it never silently fails.