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

> Source: <https://dev.to/skycandykey1/how-i-built-a-read-only-sqlite-mcp-server-in-python-and-why-read-only-matters-bef>
> Published: 2026-06-14 15:44:35+00:00

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:

(MIT).[github.com/skycandykey1/mcp-sqlite-server]

The ** Model Context Protocol** (MCP) is an open standard for connecting AI apps to tools and data. An MCP

`query`

, `list_tables`

, ...)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.

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.

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

``` php
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:**

``` php
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:

``` python
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,
    }
```

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

``` python
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.

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.

The most important test isn't that `SELECT`

works — it's that a write *fails even if the statement guard is bypassed*:

``` python
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.

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

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