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