Skip to main content
Glama
sql-explorer

sql-explorer

sql-explorer

An MCP server that lets an AI assistant answer questions about a SQLite database in plain language — without being able to damage it or read the parts you have marked off limits.

Ask "which city spends the most?" and the model discovers the tables, reads the schema, writes its own SQL, and answers. It never gets a chance to write, delete, or read a blocked column.

You:    Which city has spent the most in total?
Claude: Lyon, with 14 orders totalling 2,840.03.

You:    Give me the email and phone of every customer.
Claude: I can't — the server refuses access to customers.email.

Why it exists

Handing a language model a database connection is a genuinely risky idea. Three things can go wrong:

Risk

How it is handled

It issues DELETE, UPDATE or DROP

Only statements beginning with SELECT are accepted

It reads personal data

A SQLite authorizer denies configured columns inside the engine

It returns millions of rows

Results are capped at 500 rows and queries are aborted after 5 seconds

The second one is the interesting one. The blocked columns are not filtered out of the SQL text — SQLite asks permission before reading any column and the server answers. That means a query which never mentions email, but filters on it to leak addresses one guess at a time, is refused too:

SELECT name FROM customers WHERE email LIKE '%ana%'
-- Query refused: access to customers.email is prohibited

There is no phrasing that gets around it, because the check does not look at the phrasing.

Related MCP server: safe-sql-mcp

Quick start

Requires Python 3.12+ and uv.

git clone <your-repo-url>
cd mcp_server
uv sync
uv run python scripts/make_sample_db.py   # builds the practice database
uv run pytest                             # 28 tests

To poke at the tools by hand in a browser (needs Node.js):

uv run mcp dev src/mcp_server/__init__.py

Using it with Claude Desktop

Settings → Developer → Edit config, then add:

{
  "mcpServers": {
    "sql-explorer": {
      "command": "uv",
      "args": ["run", "--directory", "/absolute/path/to/mcp_server", "mcp-server"],
      "env": {
        "SQL_EXPLORER_DB": "/absolute/path/to/your.db",
        "SQL_EXPLORER_BLOCKED_COLUMNS": "users.password_hash, users.ssn"
      }
    }
  }
}

Restart the app afterwards. Editing the file while it is running does not work — the app overwrites it on exit.

Configuration

Variable

Default

Meaning

SQL_EXPLORER_DB

sample.db in this repo

Which SQLite file to serve

SQL_EXPLORER_BLOCKED_COLUMNS

customers.email, customers.phone

Columns to deny, as table.column, comma separated

SQL_EXPLORER_TRANSPORT

stdio

stdio or streamable-http

SQL_EXPLORER_PORT

8000

Port to listen on, HTTP transport only

SQL_EXPLORER_TOKEN

none

Bearer token required by the HTTP transport. No default, and no server without it

A value that is not shaped like table.column makes the server refuse to start. A typo in a security setting should be loud, not silently ignored.

Tools

Tool

Purpose

list_tables()

Names of every table

describe_table(table)

Columns of one table: name, type, whether required

run_query(sql)

Runs a SELECT and returns {rows, row_count, truncated}

ping()

Liveness check

run_query reports truncated: true when the result hit the row cap, so a partial answer is never mistaken for a complete one.

Resources

URI

Content

schema://tables

Every table with its columns, one line each

schema://{table}

One table in detail: column name, type, whether required

Columns the server refuses to read are marked [blocked]:

customers(id, name, email [blocked], phone [blocked], city, signup_date)

That is deliberate. The protection does not depend on secrecy — the authorizer refuses regardless of what the caller knows — so naming the blocked columns costs nothing and saves a wasted SELECT * that would only be rejected.

schema://{table} is a template: one definition serves one address per table, whatever tables the database turns out to have.

Prompts

Prompt

What it does

analyze_table(table)

Walks one table: size, distributions, gaps, outliers

data_quality_report()

Audits for duplicates, orphans, impossible values, suspicious uniformity

Prompts return instructions, not data. They describe how to drive this server well — read the schema first, aggregate rather than list rows, do not reach for blocked columns — so a user who does not know the database can still ask a good question.

Running it over HTTP

By default the server runs on stdio: a client launches it as a child process and they talk over pipes. Nothing needs authenticating, because the operating system already decided who may start the process.

Set SQL_EXPLORER_TRANSPORT=streamable-http and it becomes a web service instead — and then anyone who can reach the port can talk to it. So a token is mandatory:

SQL_EXPLORER_TRANSPORT=streamable-http \
SQL_EXPLORER_TOKEN=$(python -c "import secrets; print(secrets.token_urlsafe(32))") \
uv run mcp-server

Every request must carry it:

curl -X POST http://127.0.0.1:8000/mcp \
  -H "Authorization: Bearer $SQL_EXPLORER_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2025-06-18","capabilities":{},"clientInfo":{"name":"curl","version":"1.0"}}}'

Anything else gets 401 and never reaches a tool, a resource, or the database.

With no SQL_EXPLORER_TOKEN set, the server refuses to start. It does not fall back to running open with a warning printed somewhere. A missed warning leaves the database published while everything looks healthy, which is the worst kind of failure: silent, and indistinguishable from success.

The listener binds 127.0.0.1. Read the security note below before changing that.

Before exposing this to a network

  • TLS is not optional. A bearer token over plain HTTP travels in clear text; anyone between the client and the server can read it and reuse it. Put this behind a reverse proxy that terminates HTTPS.

  • A shared token is not OAuth. The MCP specification calls for OAuth 2.1 for remote servers, which gives per-user identity, scopes and revocation. One shared secret gives none of those: every caller is the same caller, and rotating it locks everyone out at once. That is a reasonable trade for a single-user or small-team service, and the wrong one for a public deployment.

  • Rate limiting is absent. Nothing here slows down a caller hammering expensive queries.

Design notes

Why the schema is both a tool and a resource. describe_table returns structured rows for a model to compute with; schema://customers returns a readable page a person can attach to a conversation. The same information in two shapes, because tools and resources are consumed differently. The tool is also the reliable path, since resource support still varies between clients.

Why SELECT * is refused. The expansion includes the blocked columns, so the authorizer denies it. The model has to name the columns it wants. Slightly more work for it; no accidental leaks.

Why describe_table interpolates its argument. PRAGMA table_info cannot take a bound parameter, so the table name goes into the statement directly — after being checked against the real table list. An allowlist, not an escape.

Limitations

  • SQLite only. Postgres or MySQL would need a different authorization approach, since the authorizer callback is a SQLite feature.

  • Blocking is per column, not per row. There is no way to say "only this user's rows".

  • The 5 second timeout is wall clock, not CPU time.

Running the tests

uv run pytest -v

Twenty-eight tests in three files.

tests/test_guards.py covers every safety guard: refused statements, refused columns including the filter-only leak, truncation, unknown table names, and the query timeout.

tests/test_resources_and_prompts.py covers what the resources render and what the prompts say, including that blocked columns keep their [blocked] marker and that the prompts still name the tools and URIs they rely on.

tests/test_http_auth.py covers the HTTP door: a correct token passes, a missing header, a wrong token, a bare token without the Bearer prefix and a truncated token are all refused, and the server refuses to start in HTTP mode with no token set. Each refusal asserts the request never reached the endpoint, not merely that the status was 401.

tests/conftest.py builds the sample database if it is missing, so the suite runs on a fresh clone.

The guard and resource tests call the server's functions directly rather than through an MCP session, so they would not catch a decorator being removed.

Available Tools

4 tools
describe_tableA

Describe the columns of one table: name, type, and whether it is required.

Inputs: table (required). Returns: result

list_tablesA

List the names of every table in the database.

Inputs: no named parameters. Returns: result

pingA

Check that the server is alive.

Inputs: no named parameters. Returns: result

run_queryA

Run a read-only SELECT query and return the rows.

At most 500 rows come back; if the query matched more, "truncated" is true and you should add a LIMIT or aggregate instead. Queries running longer than 5 seconds are aborted.

Inputs: sql (required). Returns: object

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A4/5.0

    Scored across 4 tools

    Disambiguation5/5

    Each tool serves a unique purpose: health check, table enumeration, schema inspection, and query execution. No two tools overlap in functionality, making misselection nearly impossible.

    Naming Consistency5/5

    All tool names follow a consistent verb_noun pattern (ping, list_tables, describe_table, run_query), using lowercase with underscores throughout. The naming clearly indicates the action and target.

    Tool Count5/5

    With only 4 tools, the server is tightly scoped for its read-only SQL exploration purpose. Each tool is essential and covers the core workflow without unnecessary bloat.

    Completeness4/5

    The tool set covers the full exploration lifecycle: check health, list tables, describe schema, and run queries. Minor gaps like database-level metadata or caching are not needed for the stated purpose, so it is nearly complete.

    Related MCP Connectors

    Related MCP Servers

    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables read-only SQL database access for AI assistants, allowing schema exploration and safe query execution without risk of data modification.
      -
    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables AI assistants to query SQL databases safely with read-only access, allowing schema discovery and SELECT queries while blocking writes and DDL operations.
      -
    • A
      license
      Not graded
      quality
      D
      maintenance
      Enables AI assistants to explore and query SQLite databases through read-only tools, with defense-in-depth sandboxing preventing any data modifications.
      MIT