Secure SQLite access for AI agents with Python MCP server

AI agents need safe ways to interact with databases, but granting direct write access can lead to costly mistakes. When integrating an AI assistant like Claude Desktop with a SQLite database, the solution isn't to restrict cautiously — it's to eliminate risk entirely. A new open-source MCP server delivers read-only database access through a lightweight Python implementation that combines two independent protection layers.

Why read-only access matters for AI database interaction

Connecting large language models to databases introduces unique risks. A single misformatted query can accidentally alter or delete critical data. While demonstrations often overlook these dangers, production environments demand rigorous safeguards. The simplest approach is to remove write capabilities entirely, allowing agents to perform analysis without exposing the system to unintended changes.

This strategy isn't just theoretical. The MCP server architecture provides a structured way to expose only necessary functionality while maintaining strict control over what operations are permitted.

Building the MCP server with layered security

The implementation follows a clear separation of concerns: core safety logic remains independent from the MCP protocol itself. This design choice enables thorough testing without requiring a full AI client setup.

Core connection handling with SQLite safeguards

The database connection enforces read-only behavior at the engine level:

import sqlite3

def connect(path: str) -> sqlite3.Connection:
    """Open a SQLite database in READ-ONLY mode with dual protection."""
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA query_only = ON")
    return conn

The connection string parameter mode=ro tells SQLite to open the file exclusively for reading, while PRAGMA query_only = ON provides an additional safety net. Any attempt to execute write operations results in immediate OperationalError exceptions.

Query validation before execution

Before any query reaches the database, it undergoes validation to ensure it's a single read-only operation:

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 validation blocks multi-statement attacks and provides clear error messages when improper queries are attempted. Even if future code changes weaken this guard, the underlying SQLite connection would still block write operations.

Result management and resource control

The query execution includes safeguards against overwhelming the system:

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 function caps returned rows at 100 by default (configurable to 1,000 maximum), preventing excessive data transfers that could strain the AI client's context window.

Implementing the MCP server interface

The server itself serves as a minimal wrapper around these core components:

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 server exposes two key tools: list_tables for database discovery and query for executing read-only SQL. Additional functionality includes schema resources and prompt templates that enable agents to self-discover the database structure.

Deploying with Claude Desktop

To integrate with Claude Desktop, add the configuration to your settings file:

{
  "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"
      }
    }
  }
}

After restarting the application, agents can safely query the database without write capabilities. For example, asking "Show me the top 5 orders by amount" would trigger the agent to call list_tables, examine the schema resource, construct an appropriate SELECT statement, and return results — all while remaining physically incapable of making modifications.

Verifying the safety guarantees

The most critical test validates that write operations fail even when the statement guard is bypassed:

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

This test demonstrates defense-in-depth: if any layer fails, the other maintains the system's integrity.

Key insights for building AI-accessible tools

• Principle of least privilege: Grant only the minimum necessary permissions. For database agents, read-only access satisfies most use cases without introducing risk.
• Isolation of critical components: Safety logic should exist independently from framework-specific code to enable proper testing.
• Defense in depth: Multiple independent safeguards create resilience against single points of failure.
• User-friendly validation: Clear error messages guide users toward correct behavior rather than cryptic engine exceptions.

As AI agents increasingly interact with operational data, secure integration patterns will become essential. This read-only MCP server demonstrates how thoughtful architecture can enable powerful functionality while maintaining robust safety guarantees.