Provides read-only analytical access to an online store's SQLite database, including tools for customer and country statistics, top spenders, top-selling products, top revenue categories, and period revenue.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type @ followed by the MCP server name and your instructions, e.g., "@Shop Analytics MCP Server how much revenue did we generate in 2025?"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
A read-only MCP server, over stdio, that lets an AI
agent answer analytical questions about an online store's SQLite database
(customers, products, orders, order_items) — without ever being able to
modify it.
See SPEC.md for the full design rationale (decisions log, schema, security model, testing strategy).
Node.js >= 24.10.0 (needed for node:sqlite's setAuthorizer, used by the
read-only guarantee below). Check with node --version.
No other runtime dependencies beyond what npm ci installs.
Related MCP server: Read-Only SQLite Shop Database MCP Server
npm ci
npm run build
SHOP_DB_PATH=./shop.db npm startshop.db ships in this repository, ready to use. If you ever need to
regenerate it deterministically from the schema, run npm run seed (see
Database below).
SHOP_DB_PATH is optional; it defaults to shop.db in the current working
directory. No absolute path is hard-coded anywhere in the source.
The server speaks MCP over stdio only — there is no HTTP server and nothing else to run.
Config examples for two clients are in config/:
config/claude-code.mcp.json — copy into a
project's .mcp.json, or run claude mcp add-json with its shop-analytics
entry. Fill in absolute paths for args/env first.
config/codex.mcp.toml — copy the
[mcp_servers.shop-analytics] table into ~/.codex/config.toml (or a
project-scoped .codex/config.toml), or use the codex mcp add command in
the file's header comment.
To poke at the server manually without any specific agent, use the tool-agnostic MCP Inspector:
SHOP_DB_PATH=$(pwd)/shop.db npx @modelcontextprotocol/inspector node dist/src/index.jsThe server exposes exactly 8 specialized, read-only tools — no tool accepts or
executes arbitrary SQL. Every successful response is { "data": [...], "meta": {...} };
every error is a plain, safe, human-readable message (no SQL, file paths, or
stack traces), flagged with isError: true.
Tool | Answers | Key parameters |
| "Show me all tables and what they contain." | (none) |
| "How many customers are from Germany?" |
|
| "Which country has the most customers?" |
|
| "Who spent the most money?" |
|
| "What are the top 5 best-selling products?" |
|
| "What are the top 3 categories by revenue?" |
|
| "How much revenue did we generate in 2025?" |
|
| "Which customer placed the most orders?" |
|
from/to are YYYY-MM-DD and define a half-open UTC interval [from, to);
from must be strictly earlier than to. All financial and count metrics
exclude orders with status cancelled. Full per-tool contracts (exact response
shapes, tie-break rules) are in SPEC.md §4.
Three independent, defense-in-depth layers guarantee the database is never modified, even by an adversarial prompt like "Delete all cancelled orders":
The SQLite connection is opened with readOnly: true.
PRAGMA query_only = ON is set immediately after opening.
A SQLite authorizer explicitly denies every write/DDL action
(INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, ATTACH, DETACH,
transactions, ...).
On top of that, no tool accepts raw SQL, table names, or column names — every
query is a fixed prepared statement, and every input is validated with zod
and passed as a bound parameter, never string-interpolated.
shop.db is generated from database/schema.sql by a
deterministic seed script — re-running it produces byte-identical data
every time (fixed PRNG seed, no wall-clock dependency):
npm run seed # builds, then (re)writes ./shop.db from schema.sql + the seed scriptThe seed script also asserts, at generation time, that the dataset has no ambiguous leaderboards (e.g. a unique top country, a unique top spender) and non-zero 2025 revenue — see SPEC.md §3.
npm run build # tsc + copy database/schema.sql into dist/
npm run test:unit # business logic, in isolation, against fixture databases
npm run test:integration # spawns the built server over stdio via the MCP SDK client
npm test # bothThis project was built with TDD: for every module, a failing test was written first, then the implementation, tool by tool. The integration suite covers all 8 acceptance scenarios end-to-end, SQL-injection-shaped inputs, invalid parameter combinations, and asserts the database file's SHA-256 hash is unchanged after every run.
database/ schema.sql + the deterministic seed generator
src/
db.ts read-only SQLite connection (see Safety above)
errors.ts error taxonomy, safe error formatting
validation.ts zod schemas shared across tools (dates, limits, periods)
period.ts half-open period SQL clause builder
tools/ one module per tool: pure query function + types
server.ts registers all 8 tools on the MCP server
index.ts stdio entrypoint
test/
unit/ one file per module/tool, fixture-based
integration/ spawns dist/src/index.js over stdio via the MCP SDK client
config/ example client configuration (Claude Code, Codex CLI)Returns how many customers are registered in a given country (case-insensitive match, e.g. 'germany' matches 'Germany'). An unknown country is a successful result with count 0, not an error. Use this to answer questions like 'how many customers are from Germany?'.
Inputs: country (required).
Lists every business table (customers, products, orders, order_items), their columns (name, SQLite type, nullability, primary key) and foreign key relationships. Use this first to understand what data is available before calling the other tools. Takes no parameters.
Inputs: no named parameters.
Sums revenue (quantity * unit_price) across all orders in the requested period, or the whole dataset if no bounds are given. Orders with status 'cancelled' are always excluded from this metric. A period with no matching orders is a successful zero-revenue result, not an error. Optional half-open UTC interval [from, to). Both are 'YYYY-MM-DD'. Omitting a bound leaves that side open (e.g. only 'to' means "everything before to"). 'from' must be strictly earlier than 'to' or the call is rejected. Use this to answer 'how much revenue did we generate in 2025?' (pass from='2025-01-01', to='2026-01-01').
Inputs: to, from.
Ranks product categories by revenue (sum of quantity * unit_price for their products), descending (ties broken by category name ascending). Orders with status 'cancelled' are always excluded from this metric. Defaults to the top 3 categories. Optional half-open UTC interval [from, to). Both are 'YYYY-MM-DD'. Omitting a bound leaves that side open (e.g. only 'to' means "everything before to"). 'from' must be strictly earlier than 'to' or the call is rejected. Use this to answer 'what are the top 3 product categories by revenue?'.
Inputs: to, from, limit.
Ranks countries by number of registered customers, descending (ties broken by country name ascending). Defaults to the single top country; pass a higher 'limit' for a top-N leaderboard. Use this to answer 'which country has the most customers?'.
Inputs: limit.
Ranks customers by how many orders they placed, descending (ties broken by customer id ascending). Orders with status 'cancelled' are always excluded from this metric. Defaults to the single top customer; pass a higher 'limit' for a top-N leaderboard. Optional half-open UTC interval [from, to). Both are 'YYYY-MM-DD'. Omitting a bound leaves that side open (e.g. only 'to' means "everything before to"). 'from' must be strictly earlier than 'to' or the call is rejected. Use this to answer 'which customer placed the most orders?'.
Inputs: to, from, limit.
Ranks customers by total money spent (sum of quantity * unit_price across their orders), descending. Orders with status 'cancelled' are always excluded from this metric. Ties broken by customer id ascending. Defaults to the single top spender; pass a higher 'limit' for a top-N leaderboard. Optional half-open UTC interval [from, to). Both are 'YYYY-MM-DD'. Omitting a bound leaves that side open (e.g. only 'to' means "everything before to"). 'from' must be strictly earlier than 'to' or the call is rejected. Use this to answer 'who is the customer who spent the most money?'.
Inputs: to, from, limit.
Ranks products by units sold, descending (ties broken by revenue descending, then product id ascending). Orders with status 'cancelled' are always excluded from this metric. Defaults to the top 5 products. Optional half-open UTC interval [from, to). Both are 'YYYY-MM-DD'. Omitting a bound leaves that side open (e.g. only 'to' means "everything before to"). 'from' must be strictly earlier than 'to' or the call is rejected. Use this to answer 'what are the top 5 best-selling products?'.
Inputs: to, from, limit.
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
Scored across 8 tools
Each tool targets a distinct analytics query—schema, country customer counts, leaderboards for customers, products, categories, and revenue periods. There is no overlap; an agent can unambiguously pick the right tool for a given question.
All tool names follow the same pattern: 'get_' followed by a descriptive phrase in snake_case (e.g., get_top_selling_products, get_revenue_for_period). The naming is perfectly consistent and immediately conveys the operation and focus.
With 8 tools, the server is well-scoped for a shop analytics domain. Each tool covers a distinct business question without redundancy, and the count is in the ideal range for usability and clarity.
The set covers core analytics needs: customer demographics, top customers by spend and orders, top products and categories, revenue totals, and schema discovery. Minor gaps exist (e.g., no per-product revenue breakdown or filtering by specific customers), but the surface is largely complete for typical analytics queries.
Read-only analytics for all your apps: users, AI traffic, revenue, with the SQL behind every number.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
