Shop Database MCP Server

A local, read-only Model Context Protocol server for exploring and analyzing the included educational SQLite shop fixture. It exposes exactly three tools: list_tables, describe_table, and query_database.

The committed shop.db contains synthetic educational country values. They are deterministic fixture data, not inferred personal attributes.

1. Install

Prerequisites: Node.js 20 or newer, npm, and a platform supported by better-sqlite3 (or a local C/C++ build toolchain if npm cannot obtain a prebuilt native binary).

npm install

2. Configure

No configuration is required when using the included shop.db. To select a different compatible database, set an absolute or relative path:

export SHOP_DB_PATH=/path/to/shop.db

The server resolves the default database relative to its own module, not the caller's working directory. It will never create a missing database. See .env.example; environment files are not loaded automatically.

3. Prepare or verify the fixture

npm run prepare-db

This explicit setup command atomically adds or validates the deterministic synthetic customers.country fixture and its index. It preserves existing customer fields and IDs and is idempotent. It is never invoked by start, dev, or server runtime. The repository already contains the prepared database, so this normally acts as verification.

4. Build

npm run typecheck
npm run build

5. Run

npm run start

The process speaks MCP over stdin/stdout and waits for a client. It does not print a startup banner; stdout is reserved exclusively for MCP messages. Development mode is available as npm run dev.

6. Connect

A generic stdio MCP configuration is provided in configuration.example.json:

{
  "mcpServers": {
    "shop_database": {
      "command": "node",
      "args": ["/absolute/path/to/project/dist/server.js"],
      "env": { "SHOP_DB_PATH": "/absolute/path/to/project/shop.db" }
    }
  }
}

For Codex CLI, either add the server at the command line:

codex mcp add shop_database --env SHOP_DB_PATH=/absolute/path/to/project/shop.db -- node /absolute/path/to/project/dist/server.js
codex mcp list

Or copy the template from config/codex-config.example.toml into your Codex configuration and replace the placeholders:

[mcp_servers.shop_database]
command = "node"
args = ["/absolute/path/to/project/dist/server.js"]
tool_timeout_sec = 15
required = true
enabled_tools = ["list_tables", "describe_table", "query_database"]

[mcp_servers.shop_database.env]
SHOP_DB_PATH = "/absolute/path/to/project/shop.db"

Run codex mcp list, start Codex, and use /mcp to verify that shop_database and all three tools are available.

7. Test

npm test

The suite covers input boundaries, SQL tokenization and refusal, serialization, schema discovery, all acceptance analytics, pagination, named bindings, stdio protocol behavior, and database immutability across the destructive-query matrix.

Example prompts

1. Show me all available tables and explain what information each table contains.

2. How many customers are from Germany?

3. Which country has the most customers?

4. Who is the customer who spent the most money?

5. What are the top 5 best-selling products?

6. What are the top 3 product categories by revenue?

7. How much revenue did we generate in 2025?

8. Which customer placed the most orders?

Query rules and read-only guarantee

The database is opened with readonly: true and fileMustExist: true, then placed in SQLite query-only mode. The SQL policy independently permits only one SELECT or non-recursive WITH ... SELECT; mutation, DDL, PRAGMA, attachment, maintenance, extension loading, recursive CTEs, and extra statements are refused. Prepared statements must also identify as readers. User values are passed only through named bindings.

Each result page contains at most 500 rows. Use a deterministic ORDER BY and continue with next_offset while has_more is true. The synchronous SQLite driver cannot interrupt a CPU-heavy query in-process; v1 relies on query restrictions, bounded output, and the recommended 15-second MCP client timeout rather than a hard server-side execution deadline.

Revenue, spending, units sold, and product/category sales exclude cancelled orders. Placed-order counts include every status. Whole-order revenue and customer spending use orders.total_amount; historical product/category revenue uses order_items.quantity * order_items.unit_price. Dates are timezone-naive, and calendar-year filters use half-open intervals. The database declares no currency, so amounts are monetary units.

Troubleshooting

• DATABASE_UNAVAILABLE: verify SHOP_DB_PATH, file existence, and read permission. The server does not create databases.

• DATABASE_SCHEMA_MISMATCH: use the committed fixture or run npm run prepare-db; a custom database must have every required table, column, foreign key, and fixture prerequisite.

• Native dependency installation fails: use a supported Node.js release and install your platform's compiler/build tools, then rerun npm install.

• MCP framing or JSON errors: do not add console.log or other stdout logging to runtime code. Send diagnostics to stderr only.

• A query returns SQL_ERROR: inspect tables first, use unique aliases for duplicate result-column names, and verify placeholder names and SQL syntax.