Shop Database MCP Server

This project exposes a supplied SQLite shop database to MCP-compatible AI agents through three general-purpose, read-only tools. An agent can discover the real schema, compose analytical SQL, join and aggregate data, and identify questions the database cannot answer. The server does not contain question-specific business logic and cannot modify the database.

Requirements and installation

• Python 3.10 or newer
• The supplied database/shop.db

From the project root:

python -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.txt

The requirements manifest declares the official Python MCP SDK (mcp>=2,<3) and pytest for tests, with no separate web framework, ORM, SQL parser, or database driver.

Database configuration

By default, the server opens database/shop.db. The default is resolved from the project files, so it works even when the MCP client starts the process from another working directory.

To select another existing SQLite file, set SHOP_DB_PATH before launch:

export SHOP_DB_PATH=/absolute/path/to/another.db
python server.py

The override must name an existing regular file. A missing path fails clearly and is never created as an empty database. .env.example is documentation only: the project has no dotenv dependency and does not load that file automatically. Export the variable in the shell or set it in the MCP client configuration.

Running over stdio

With the virtual environment active:

python server.py

The process uses MCP over standard input/output. A direct launch normally appears idle because it is waiting for an MCP client. No HTTP server or other supporting service is needed. Standard output is reserved for MCP protocol messages; diagnostics belong on standard error.

Tools

list_tables()

Use this first to discover user-visible tables. It returns:

{"tables": ["table_a", "table_b"]}

Internal sqlite_% objects are excluded and table names are ordered.

describe_table(table_name)

Use this after discovery and before composing SQL. It validates table_name against live user tables and returns the table name, ordered columns, declared types, nullability, primary-key positions, and available foreign-key relationships.

query_database(sql, max_rows=100)

Runs one analytical read-only SELECT or read-only WITH statement. It supports joins, filters, sorting, grouping, aggregates, and date constraints. Inspect unknown tables with list_tables and describe_table first.

The result has this positional shape:

{
  "columns": ["column_a", "column_b"],
  "rows": [["value_a", "value_b"]],
  "row_count": 1,
  "truncated": false
}

Rows are arrays so duplicate column names from joins do not collapse values. max_rows must be an integer from 1 through 100. It defaults to 100, no call returns more than 100 rows, and truncated reports whether another row existed. Prefer aggregation and filtering to returning large raw datasets.

SQLite values normally remain null, integer, finite real, or text values. Values that JSON cannot represent directly use explicit tagged objects:

• BLOB: {"type":"blob","hex":"80ff"}
• positive infinity: {"type":"real","value":"infinity"}
• negative infinity: {"type":"real","value":"-infinity"}
• defensive NaN representation: {"type":"real","value":"nan"}

These tags prevent Python bytes or non-finite floats from leaking into MCP JSON and keep SQL NULL distinct from infinity.

Read-only guarantee

Read-only behavior is enforced in three technical layers:

1. Every runtime connection uses a percent-encoded SQLite URI with mode=ro.
2. Every connection enables PRAGMA query_only = ON.
3. The query boundary accepts a single SELECT/WITH statement and installs a SQLite authorizer that allowlists read operations while denying writes, DDL, attach/detach, transactions, unsafe PRAGMAs, and unsafe functions.

The implementation uses one Connection.execute call for caller SQL and never uses executescript. Keyword classification is not the security boundary: the SQLite read-only connection and query-only mode remain active underneath the authorizer. Tests verify that INSERT, UPDATE, DELETE, schema changes, VACUUM, ATTACH, transaction-state changes, and bypass-shaped statements leave disposable databases unchanged.

Missing tables, invalid limits, malformed SQL, forbidden operations, and SQLite execution failures are returned as concise MCP tool errors. Normal tool errors do not include Python tracebacks, and the same MCP session remains usable after a recoverable error.

Tests and sanity checks

Run these from the project root with .venv active:

python -m pytest -q
python -m compileall -q server.py shop_mcp tests
python -m pytest -q tests/test_mcp_integration.py
python -m json.tool examples/mcp-config.example.json >/dev/null

The integration test launches server.py as a real subprocess using the official Python SDK's STDIO client, initializes an MCP session, invokes all three tools, checks error recovery, and uses only a disposable database.

MCP launch configuration

examples/mcp-config.example.json is a generic client example. Replace every /absolute/path/to/shop-mcp placeholder. Remove the env object to use the default database.

Standalone Codex CLI reference

This subsection applies to a standalone Codex CLI, not to PhpStorm's codex-acp integration. The official OpenAI MCP documentation confirms that the CLI supports local STDIO servers and reads personal ~/.codex/config.toml or trusted-project .codex/config.toml.

For a native POSIX/WSL Codex CLI, the command is the project interpreter and the argument is server.py:

[mcp_servers.shop_database]
command = "/absolute/path/to/shop-mcp/.venv/bin/python"
args = ["/absolute/path/to/shop-mcp/server.py"]

# Optional override; omit this table to use database/shop.db.
[mcp_servers.shop_database.env]
SHOP_DB_PATH = "/absolute/path/to/another.db"

Connect the intended PhpStorm Codex host

The intended project host is:

PhpStorm 2026.2 AI Chat -> codex-acp 1.6.2 -> bundled codex-cli 0.148.0

Configure this host through PhpStorm, not through the standalone CLI steps above. Follow the official JetBrains documentation for MCP in AI Assistant and enabling external tools for Codex:

1. Open Settings | Tools | AI Assistant | Model Context Protocol (MCP) and select Add.

2. Choose the STDIO/JSON configuration option. Start from examples/mcp-config.example.json, then adapt the command and paths for the Windows-to-WSL boundary. For example:

   {
     "mcpServers": {
       "shop-database": {
         "command": "C:\\Windows\\System32\\wsl.exe",
         "args": [
           "--",
           "/absolute/wsl/path/to/shop-mcp/.venv/bin/python",
           "/absolute/wsl/path/to/shop-mcp/server.py"
         ]
       }
     }
   }
   

   To use another database, insert "env" and "SHOP_DB_PATH=/absolute/wsl/path/to/another.db" after "--" in args.

3. Set Working directory to the project directory as visible to PhpStorm, such as \\wsl.localhost\<distribution>\absolute\wsl\path\to\shop-mcp, and choose the appropriate Server level (this project or global).

4. Select OK, then Apply. Confirm the server's Status is connected and inspect the status details to verify that list_tables, describe_table, and query_database are available.

5. Open Settings | Tools | AI Assistant | Agents, enable Pass custom MCP servers, and select OK.

After these settings are applied, start a new Codex conversation in PhpStorm AI Chat and invoke the representative checks below. A connected status alone does not establish that the codex-acp agent received and successfully used the tools.

For comparison only, the equivalent standalone Windows Codex CLI command was verified against the OpenAI documentation and installed 0.148.0 help:

codex mcp add shop-database -- C:\Windows\System32\wsl.exe -- /absolute/wsl/path/to/shop-mcp/.venv/bin/python /absolute/wsl/path/to/shop-mcp/server.py

That command changes standalone Codex CLI configuration. It is supporting CLI reference and is not the PhpStorm/ACP setup procedure.

Host validation status (2026-08-24)

• Installed intended-host artifacts were verified read-only: codex-acp 1.6.2 bundles codex-cli 0.148.0, and the bundled executable's MCP help supports STDIO commands and --env.
• A real MCP-capable AI-agent evaluation passed using that bundled Windows codex-cli 0.148.0 invoked directly with gpt-5.6-sol, ephemeral one-shot MCP configuration, the WSL STDIO bridge, and a disposable database. It passed schema discovery, analytics, unsupported-information handling, and destructive-query rejection; the database hash was unchanged.
• That direct CLI run did not exercise PhpStorm AI Chat or the codex-acp 1.6.2 process. The intended end-to-end PhpStorm host remains pending until the JetBrains MCP setup above is applied, Pass custom MCP servers is enabled, and the tools are exercised from a PhpStorm Codex conversation. No PhpStorm success is claimed yet.
• /home/deep/.local/bin/codex is a separate WSL installation reporting codex-cli 0.147.0. Its version/help output is supporting syntax evidence only and is not proof that PhpStorm is configured or working.

Representative agent prompts

These prompts exercise general schema-led behavior without embedding their answers in server code:

• "List the available tables, then describe the tables needed to count matching records under a filter."
• "Group records by a discovered status or category column and sort the groups by count."
• "Inspect the relationships, then calculate revenue with the required joins and rank the results."
• "Use the actual date columns to analyze records in a specified date range."
• "Determine whether customer shipping city information exists in the schema; if it does not, explain the limitation without guessing."
• "Delete a record from the database." The correct outcome is refusal or a read-only tool error, with no data or schema change.