How I Built a Read-Only SQLite MCP Server in Python (and Why Read-Only Matters)

Giving an LLM a database connection is one of those ideas that sounds great in a demo and terrifying in production. The agent writes a slightly-wrong query, and now you're explaining to your team why orders is empty.

So when I wanted an AI agent (Claude Desktop, in my case) to answer questions about a SQLite database, I didn't want to hand it a read-write connection and hope for the best. I built a small MCP server that gives the agent read-only SQL access — and I made "read-only" mean it, with two independent layers of protection.

Here's how it works, and the design decisions that matter.

Full source: github.com/skycandykey1/mcp-sqlite-server (MIT).

A 30-second primer on MCP

The Model Context Protocol (MCP) is an open standard for connecting AI apps to tools and data. An MCP client (Claude Desktop, Claude Code, Cursor, ...) connects to MCP servers that expose three kinds of capability:

Tools — functions the model can call (query, list_tables, ...)
Resources — read-only data the model can pull in (a schema, a file)
Prompts — reusable prompt templates

The Python SDK ships a high-level helper, FastMCP, that turns this into a few decorators. The interesting part isn't the protocol — it's the safety design behind the tools.

Design: keep the dangerous part away from the protocol

The first decision: the read-only safety logic has zero MCP dependency. It lives in a plain module (db.py) that knows nothing about MCP, so I can unit-test it with nothing but the standard library. The server (server.py) is a thin wrapper.

That separation matters: the part that must never be wrong (write protection) is testable in isolation, without spinning up an MCP client.

Two layers of write protection

A single guard is a single point of failure. So write protection happens twice, independently.

Layer 1 — open the database read-only at the engine level:

import sqlite3

def connect(path: str) -> sqlite3.Connection:
    """Open a SQLite database in READ-ONLY mode. Any write raises OperationalError."""
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA query_only = ON")  # defense in depth
    return conn

?mode=ro tells SQLite to open the file read-only; PRAGMA query_only is a second belt on the same trousers. Any INSERT/UPDATE/DELETE raises OperationalError from the engine itself.

Layer 2 — reject anything that isn't a single SELECT before it runs:

def _ensure_read_only(sql: str) -> str:
    stmt = sql.strip().rstrip(";").strip()
    if not stmt:
        raise ValueError("empty query")
    if ";" in stmt:
        raise ValueError("only a single statement is allowed")
    head = stmt.lower()
    if not (head.startswith("select") or head.startswith("with")):
        raise ValueError("only read-only SELECT / WITH queries are allowed")
    return stmt

This gives the model a clean, early error message ("only read-only SELECT / WITH queries are allowed") instead of a raw engine exception, and it blocks multi-statement tricks. Even if a future refactor weakens this guard, Layer 1 still holds — and vice versa.

The query runner ties it together and caps the row count so a SELECT * on a huge table can't blow up the context window:

def run_query(conn, sql, max_rows=100):
    stmt = _ensure_read_only(sql)
    max_rows = max(1, min(int(max_rows), 1000))
    cur = conn.execute(stmt)
    cols = [d[0] for d in cur.description] if cur.description else []
    rows = cur.fetchmany(max_rows)
    return {
        "columns": cols,
        "rows": [dict(zip(cols, r)) for r in rows],
        "row_count": len(rows),
        "truncated": len(rows) == max_rows,
    }

The MCP server is a thin wrapper

With the core done, the server is almost boring — which is the point:

import os
from mcp.server.fastmcp import FastMCP
from mcp_sqlite.db import session, list_tables as _list, run_query as _run

mcp = FastMCP("sqlite-readonly")

def _db_path() -> str:
    path = os.environ.get("SQLITE_DB_PATH")
    if not path:
        raise RuntimeError("Set SQLITE_DB_PATH to your .db file.")
    return path

@mcp.tool()
def list_tables() -> list[str]:
    """List all tables in the database."""
    with session(_db_path()) as conn:
        return _list(conn)

@mcp.tool()
def query(sql: str, max_rows: int = 100) -> dict:
    """Run a READ-ONLY SQL query (a single SELECT or WITH) and return the rows."""
    with session(_db_path()) as conn:
        return _run(conn, sql, max_rows)

if __name__ == "__main__":
    mcp.run()  # stdio transport

The full version also exposes a schema://tables resource (the whole schema as text) and an explore_database prompt — all three MCP primitives, so the agent can discover the database on its own.

Wiring it into Claude Desktop

Add this to your Claude Desktop config (Settings → Developer → Edit Config), using absolute paths:

{
  "mcpServers": {
    "sqlite-readonly": {
      "command": "python",
      "args": ["-m", "mcp_sqlite.server"],
      "cwd": "/absolute/path/to/mcp-sqlite-server",
      "env": { "SQLITE_DB_PATH": "/absolute/path/to/your.db" }
    }
  }
}

Restart, then ask: "What tables are in my database? Show me the top 5 orders by amount." The agent calls list_tables, reads the schema resource, writes a SELECT, and answers — and physically cannot write.

Test the dangerous path

The most important test isn't that SELECT works — it's that a write fails even if the statement guard is bypassed:

def test_readonly_connection_blocks_writes_even_if_guard_bypassed():
    with db.session(_make_db()) as c:  # _make_db() builds a temp sample DB
        try:
            c.execute("INSERT INTO customers (name) VALUES ('x')")
            assert False, "read-only connection should refuse writes"
        except sqlite3.OperationalError:
            pass

That's the whole value proposition in one test: defense in depth, proven.

Takeaways

When you give an AI agent power, give it the least power that does the job. Read-only access answers 90% of "ask my database" use cases with none of the risk.
Put safety-critical logic where you can test it without the framework.
Two cheap, independent guards beat one clever one.

The full project — tests, a sample database, and the Claude Desktop config — is on GitHub: mcp-sqlite-server. If you're building agents, you might also like my minimal, framework-free ai-agent-starter.

I build AI agents, MCP servers, and LLM automation, and I take on contract work. If you're putting an agent in front of real systems and want it done safely, get in touch.

推荐订阅源

DEV Community