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

CODE Kopieren

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:

CODE Kopieren

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:

CODE Kopieren

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:

CODE Kopieren

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:

CODE Kopieren

{

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

CODE Kopieren

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

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.

Vollständiger Original-Artikel

Den kompletten Beitrag mit allen Details direkt auf dev.to lesen.

↗ Original-Artikel auf dev.to lesen