Überblick: Entwicklung der Architektur

Der hier in vier Ausbaustufen vorgestellte MCP-Server erlaubt es, eine SQLite-Datenbank über einen MCP-Client wie Claude Code in natürlicher Sprache abzufragen. Der Benutzer muss dazu keine SQL-Kenntnisse besitzen: Das LLM interpretiert die Aufgabe, ermittelt die benötigten Tabellen und Spalten, erzeugt die erforderlichen SQL-Statements und bereitet das Ergebnis als verständliche Sicht auf die Daten auf.

Als durchgängiges Beispiel dient eine Datenbank zur Konjugation altgriechischer Verbklassen. Sie besteht aus acht vielspaltigen Tabellen mit identischer Grundstruktur, jeweils einer Tabelle pro Verb- bzw. Konjugationsklasse (paideuw, poiw, timw, doulw, diwkw, grafw, peithw und angellw). Die folgende Abbildung zeigt als Ausschnitt die ersten Spalten der Tabelle für παιδεύω (paideuw), so wie sie auf einer klassischen Website dargestellt werden kann.

Die vier Ausbaustufen

1. Stufe 1 · Local MCP / Local DB (generic): Ein lokaler MCP-Client (Claude Code) ruft einen lokal gestarteten MCP-Server in Python auf. Der MCP-Server stellt die Datenbankstruktur bereit; das LLM interpretiert die natürlichsprachige Anfrage, übersetzt sie in SQL und fragt die lokale SQLite-Datenbank direkt ab.

   

2. Stufe 2 · Local MCP / Remote DB (limited): Ein lokaler MCP-Client ruft weiterhin einen lokal gestarteten Python-MCP-Server auf. Dieser interpretiert die natürlichsprachige Aufgabe und ruft ein entferntes CGI-Script lediglich mit zwei Parametern auf: einer Liste der benötigten Tabellen und einer Liste der benötigten Spalten. Die SQL-Logik liegt vollständig im CGI-Script.

   

3. Stufe 3 · Local MCP / Remote DB (generic): Der lokale MCP-Client nutzt einen lokal gestarteten, nun schemafreien Python-MCP-Server. Das LLM ermittelt über diesen selbständig die Tabellenstruktur, erzeugt das SQL-Statement und übergibt es einem entfernten CGI-Script, das dieses Statement gegen die dortige SQLite-Datenbank ausführt.

   

4. Stufe 4 · Remote MCP / Remote DB (generic): Auf dem MCP-Client ist kein lokaler MCP-Server mehr installiert. Clientseitig verbleiben nur die MCP-Konfiguration und das Zugriffstoken. Der komplette MCP-Server läuft auf dem entfernten Webserver; Claude ermittelt über ihn Schema und SQL und lässt ihn die Datenbank direkt abfragen.

   

Die vier Varianten zeigen damit eine schrittweise Trennung von Client, MCP-Protokoll, Datenzugriff und Web-Infrastruktur. Gleichzeitig wird der MCP-Server immer generischer: vom explizit beschriebenen Schema bis zur dynamischen Schema-Ermittlung auf dem entfernten Server.

SELECT
    p."Präsens Indikativ",
    t."Präsens Indikativ"
FROM poiw AS p
JOIN timw AS t ON t.rowid = p.rowid;

/usr/local/bin/verbs-mcp-server

/usr/local/lib/verbs-mcp/
    server.py
    .venv/

/usr/local/share/verbs-mcp/
    verbsdynamic.db

Projektverzeichnis:
    .mcp.json

#!/usr/bin/env python3
"""Minimal read-only MCP server for verbsdynamic.db."""

from __future__ import annotations

import json
import os
import re
import sqlite3
from pathlib import Path
from typing import Any

from mcp.server import MCPServer

SERVER_NAME = "verbsdynamic"
DEFAULT_DB = Path("/usr/local/share/verbs-mcp/verbsdynamic.db")
DB_PATH = Path(os.environ.get("VERBSDYNAMIC_DB", str(DEFAULT_DB)))
MAX_ROWS = 200

mcp = MCPServer(SERVER_NAME)

ROW_SEMANTICS = {
    1: "1. Person Singular",
    2: "2. Person Singular",
    3: "3. Person Singular",
    4: "1. Person Plural",
    5: "2. Person Plural",
    6: "3. Person Plural",
}


def _connect() -> sqlite3.Connection:
    if not DB_PATH.is_file():
        raise RuntimeError(f"SQLite database not found: {DB_PATH}")

    # URI mode=ro makes the database read-only at SQLite level.
    con = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)
    con.execute("PRAGMA query_only = ON")
    con.set_authorizer(_authorizer)
    return con


def _authorizer(action: int, arg1: str | None, arg2: str | None,
                dbname: str | None, source: str | None) -> int:
    """Allow only operations needed for SELECT queries."""
    allowed = {
        sqlite3.SQLITE_SELECT,
        sqlite3.SQLITE_READ,
        sqlite3.SQLITE_FUNCTION,
    }
    if hasattr(sqlite3, "SQLITE_RECURSIVE"):
        allowed.add(sqlite3.SQLITE_RECURSIVE)
    return sqlite3.SQLITE_OK if action in allowed else sqlite3.SQLITE_DENY


def _first_sql_keyword(sql: str) -> str:
    """Return first SQL keyword after leading whitespace/comments."""
    s = sql.lstrip()
    while True:
        if s.startswith("--"):
            nl = s.find("\n")
            s = "" if nl < 0 else s[nl + 1:].lstrip()
            continue
        if s.startswith("/*"):
            end = s.find("*/", 2)
            if end < 0:
                return ""
            s = s[end + 2:].lstrip()
            continue
        break
    m = re.match(r"([A-Za-z]+)", s)
    return m.group(1).upper() if m else ""


def _schema_dict() -> dict[str, Any]:
    with _connect() as con:
        tables = [
            row[0]
            for row in con.execute(
                "SELECT name FROM sqlite_master "
                "WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
            )
        ]

        result: dict[str, Any] = {
            "database": str(DB_PATH),
            "semantics": {
                "one_table_per_verb": True,
                "rowid": ROW_SEMANTICS,
                "important": (
                    "All verb tables have the same column layout. "
                    "There is no explicit id/person column. SQLite rowid 1..6 "
                    "means 1sg, 2sg, 3sg, 1pl, 2pl, 3pl respectively. "
                    "Join different verb tables on rowid when comparing persons."
                ),
            },
            "tables": {},
        }

        for table in tables:
            quoted = table.replace('"', '""')
            cols = [
                {"name": row[1], "type": row[2] or ""}
                for row in con.execute(f'PRAGMA table_info("{quoted}")')
            ]
            count = con.execute(f'SELECT count(*) FROM "{quoted}"').fetchone()[0]
            result["tables"][table] = {"rows": count, "columns": cols}

        return result


@mcp.resource("verbs://schema", mime_type="application/json")
def database_schema_resource() -> str:
    """Schema and row semantics of the Ancient Greek verb database."""
    return json.dumps(_schema_dict(), ensure_ascii=False, indent=2)


@mcp.tool()
def get_database_schema() -> dict[str, Any]:
    """Return all verb tables, columns and row semantics before writing SQL.

    Use this when the user's question requires database access and the exact
    table/column names are not already known. Each table represents one verb.
    rowid 1..6 corresponds to 1sg, 2sg, 3sg, 1pl, 2pl, 3pl.
    """
    return _schema_dict()


@mcp.tool()
def query_sql(sql: str) -> dict[str, Any]:
    """Execute one read-only SELECT/WITH query against the verb database.

    The database contains one table per verb and equal schemas across tables.
    Use quoted identifiers for German column names, e.g.
    SELECT rowid, "Präsens Indikativ" FROM poiw ORDER BY rowid.
    To compare verbs, join tables on rowid. Only SELECT or WITH is accepted.
    At most 200 result rows are returned.
    """
    keyword = _first_sql_keyword(sql)
    if keyword not in {"SELECT", "WITH"}:
        raise ValueError("Only SELECT or WITH queries are allowed")

    with _connect() as con:
        cur = con.execute(sql)
        columns = [d[0] for d in cur.description] if cur.description else []
        rows = cur.fetchmany(MAX_ROWS + 1)

    truncated = len(rows) > MAX_ROWS
    if truncated:
        rows = rows[:MAX_ROWS]

    return {
        "columns": columns,
        "rows": [list(row) for row in rows],
        "row_count": len(rows),
        "truncated": truncated,
        "max_rows": MAX_ROWS,
    }


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

Installationsschritte der finalen Variante

1. Paket auf dem Server installieren

   unzip verbs-remote-mcp-mcpuser.zip
   cd verbs-remote-mcp-mcpuser
   sudo ./install.sh

2. Datenbankzugriff für den Benutzer mcp prüfen

   sudo -u mcp test -r \
     /var/www/html/Public/Greek/verbsdynamic.db \
     && echo readable

3. systemd-Service starten

   sudo systemctl enable --now verbs-remote-mcp
   systemctl status verbs-remote-mcp

4. Apache-Proxy aktivieren und konfigurieren

   sudo a2enmod proxy proxy_http
   sudo apachectl configtest
   sudo systemctl reload apache2

5. Client konfigurieren

   export VERBS_MCP_TOKEN='...'
   claude mcp list

Read-only- und Betriebsabsicherung

Direkter Vergleich

Overview: Evolution of the Architecture

The MCP server presented here in four evolution stages makes it possible to query an SQLite database in natural language through an MCP client such as Claude Code. The user does not need SQL knowledge: the LLM interprets the task, determines the required tables and columns, generates the necessary SQL statements, and presents the result as a useful view of the data.

The running example is a database for the conjugation of Ancient Greek verb classes. It consists of eight wide tables with the same basic structure, one table for each verb or conjugation class (paideuw, poiw, timw, doulw, diwkw, grafw, peithw, and angellw). The following image shows the first columns of the table for παιδεύω (paideuw) as they might be displayed on a conventional website.

The Four Evolution Stages

1. Stage 1 · Local MCP / Local DB (generic): A local MCP client (Claude Code) calls a locally started Python MCP server. The MCP server exposes the database structure; the LLM interprets the natural-language request, translates it into SQL, and queries the local SQLite database directly.

   

2. Stage 2 · Local MCP / Remote DB (limited): A local MCP client still calls a locally started Python MCP server. The natural-language task is interpreted and a remote CGI script is called with only two parameters: a list of required tables and a list of required columns. The SQL logic resides entirely in the CGI script.

   

3. Stage 3 · Local MCP / Remote DB (generic): The local MCP client uses a locally started Python MCP server that is now schema-independent. Through this server, the LLM discovers the table structure, creates the SQL statement, and sends it to a remote CGI script that executes it against the SQLite database there.

   

4. Stage 4 · Remote MCP / Remote DB (generic): No local MCP server is installed on the MCP client. Only the MCP configuration and access token remain on the client. The complete MCP server runs on the remote web server; Claude uses it to discover the schema and SQL and lets it query the database directly.

   

The four variants therefore show a gradual separation of client, MCP protocol, data access, and web infrastructure. At the same time, the MCP server becomes increasingly generic: from an explicitly described schema to dynamic schema discovery on the remote server.

SELECT
    p."Präsens Indikativ",
    t."Präsens Indikativ"
FROM poiw AS p
JOIN timw AS t ON t.rowid = p.rowid;

/usr/local/bin/verbs-mcp-server

/usr/local/lib/verbs-mcp/
    server.py
    .venv/

/usr/local/share/verbs-mcp/
    verbsdynamic.db

Project directory:
    .mcp.json

#!/usr/bin/env python3
"""Minimal read-only MCP server for verbsdynamic.db."""

from __future__ import annotations

import json
import os
import re
import sqlite3
from pathlib import Path
from typing import Any

from mcp.server import MCPServer

SERVER_NAME = "verbsdynamic"
DEFAULT_DB = Path("/usr/local/share/verbs-mcp/verbsdynamic.db")
DB_PATH = Path(os.environ.get("VERBSDYNAMIC_DB", str(DEFAULT_DB)))
MAX_ROWS = 200

mcp = MCPServer(SERVER_NAME)

ROW_SEMANTICS = {
    1: "1. Person Singular",
    2: "2. Person Singular",
    3: "3. Person Singular",
    4: "1. Person Plural",
    5: "2. Person Plural",
    6: "3. Person Plural",
}


def _connect() -> sqlite3.Connection:
    if not DB_PATH.is_file():
        raise RuntimeError(f"SQLite database not found: {DB_PATH}")

    # URI mode=ro makes the database read-only at SQLite level.
    con = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)
    con.execute("PRAGMA query_only = ON")
    con.set_authorizer(_authorizer)
    return con


def _authorizer(action: int, arg1: str | None, arg2: str | None,
                dbname: str | None, source: str | None) -> int:
    """Allow only operations needed for SELECT queries."""
    allowed = {
        sqlite3.SQLITE_SELECT,
        sqlite3.SQLITE_READ,
        sqlite3.SQLITE_FUNCTION,
    }
    if hasattr(sqlite3, "SQLITE_RECURSIVE"):
        allowed.add(sqlite3.SQLITE_RECURSIVE)
    return sqlite3.SQLITE_OK if action in allowed else sqlite3.SQLITE_DENY


def _first_sql_keyword(sql: str) -> str:
    """Return first SQL keyword after leading whitespace/comments."""
    s = sql.lstrip()
    while True:
        if s.startswith("--"):
            nl = s.find("\n")
            s = "" if nl < 0 else s[nl + 1:].lstrip()
            continue
        if s.startswith("/*"):
            end = s.find("*/", 2)
            if end < 0:
                return ""
            s = s[end + 2:].lstrip()
            continue
        break
    m = re.match(r"([A-Za-z]+)", s)
    return m.group(1).upper() if m else ""


def _schema_dict() -> dict[str, Any]:
    with _connect() as con:
        tables = [
            row[0]
            for row in con.execute(
                "SELECT name FROM sqlite_master "
                "WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
            )
        ]

        result: dict[str, Any] = {
            "database": str(DB_PATH),
            "semantics": {
                "one_table_per_verb": True,
                "rowid": ROW_SEMANTICS,
                "important": (
                    "All verb tables have the same column layout. "
                    "There is no explicit id/person column. SQLite rowid 1..6 "
                    "means 1sg, 2sg, 3sg, 1pl, 2pl, 3pl respectively. "
                    "Join different verb tables on rowid when comparing persons."
                ),
            },
            "tables": {},
        }

        for table in tables:
            quoted = table.replace('"', '""')
            cols = [
                {"name": row[1], "type": row[2] or ""}
                for row in con.execute(f'PRAGMA table_info("{quoted}")')
            ]
            count = con.execute(f'SELECT count(*) FROM "{quoted}"').fetchone()[0]
            result["tables"][table] = {"rows": count, "columns": cols}

        return result


@mcp.resource("verbs://schema", mime_type="application/json")
def database_schema_resource() -> str:
    """Schema and row semantics of the Ancient Greek verb database."""
    return json.dumps(_schema_dict(), ensure_ascii=False, indent=2)


@mcp.tool()
def get_database_schema() -> dict[str, Any]:
    """Return all verb tables, columns and row semantics before writing SQL.

    Use this when the user's question requires database access and the exact
    table/column names are not already known. Each table represents one verb.
    rowid 1..6 corresponds to 1sg, 2sg, 3sg, 1pl, 2pl, 3pl.
    """
    return _schema_dict()


@mcp.tool()
def query_sql(sql: str) -> dict[str, Any]:
    """Execute one read-only SELECT/WITH query against the verb database.

    The database contains one table per verb and equal schemas across tables.
    Use quoted identifiers for German column names, e.g.
    SELECT rowid, "Präsens Indikativ" FROM poiw ORDER BY rowid.
    To compare verbs, join tables on rowid. Only SELECT or WITH is accepted.
    At most 200 result rows are returned.
    """
    keyword = _first_sql_keyword(sql)
    if keyword not in {"SELECT", "WITH"}:
        raise ValueError("Only SELECT or WITH queries are allowed")

    with _connect() as con:
        cur = con.execute(sql)
        columns = [d[0] for d in cur.description] if cur.description else []
        rows = cur.fetchmany(MAX_ROWS + 1)

    truncated = len(rows) > MAX_ROWS
    if truncated:
        rows = rows[:MAX_ROWS]

    return {
        "columns": columns,
        "rows": [list(row) for row in rows],
        "row_count": len(rows),
        "truncated": truncated,
        "max_rows": MAX_ROWS,
    }


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

Installation Steps for the Final Variant

1. Install the package on the server

   unzip verbs-remote-mcp-mcpuser.zip
   cd verbs-remote-mcp-mcpuser
   sudo ./install.sh

2. Check database access for user mcp

   sudo -u mcp test -r \
     /var/www/html/Public/Greek/verbsdynamic.db \
     && echo readable

3. Start the systemd service

   sudo systemctl enable --now verbs-remote-mcp
   systemctl status verbs-remote-mcp

4. Enable and configure the Apache reverse proxy

   sudo a2enmod proxy proxy_http
   sudo apachectl configtest
   sudo systemctl reload apache2

5. Configure the client

   export VERBS_MCP_TOKEN='...'
   claude mcp list

Read-only and Operational Protection

Direct Comparison