postgres-recommender-mcp
Provides read-only SQL analytics and ALS-based collaborative filtering recommendations on a PostgreSQL e-commerce database.
This server does not currently have a deployable release on Glama. Follow the repository's setup instructions to install and configure it for your MCP client, including any required credentials and local dependencies.
Postgres MCP Server + Recommender
An MCP server that lets any MCP-compatible client or agent — Claude Desktop, Claude Code, Cursor, or a custom LLM harness — do two things over one e-commerce Postgres database: read-only SQL analytics and ML recommendations (ALS collaborative filtering). Because it speaks the open Model Context Protocol, any agentic host that supports MCP can drive it — the same data, two capabilities, one server.

Analytics, an ML recommendation, and a refused DROP — all driven through the MCP server. Scripted walkthrough built with Remotion, using real values from the live database and trained model.
Quickstart
# 1. Postgres in Docker (schema.sql seeds the table + read-only role on first boot)
docker compose up -d
# 2. Python env + deps, then install this package (needed so the server imports cleanly)
uv venv --python 3.11 .venv
uv pip install "mcp[cli]" "psycopg[binary]" sqlglot pytest implicit scipy numpy pandas openpyxl
uv pip install -e .
# 3. Load the real UCI Online Retail dataset (~398k transactions)
# Download to data/online_retail.xlsx first:
# https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx
python scripts/load_data.py
# 4. Train + offline-eval the recommender (writes models/als.pkl)
python scripts/train_recs.py
# 5. Inspect all tools locally
mcp dev src/mcp_postgres_explorer/server.pyRelated MCP server: VTION E-Commerce MCP Server
Connect a client
Any MCP-compatible host works (Cursor, Cline, Zed, or a custom agent using an MCP SDK). Claude is shown below as the tested example.
Use the Python from the .venv you installed into — clients launch the command directly and do not
activate a virtualenv. On Windows that's .venv\Scripts\python.exe; on macOS/Linux .venv/bin/python.
RECS_MODEL must be an absolute path so the recommender finds the model regardless of working directory.
Claude Code:
claude mcp add postgres-explorer \
-e DATABASE_URL=postgresql://readonly:readonly@localhost:5432/demo \
-e RECS_MODEL=/ABS/PATH/models/als.pkl \
-- /ABS/PATH/.venv/bin/python /ABS/PATH/src/mcp_postgres_explorer/server.pyClaude Desktop — add to claude_desktop_config.json (Settings → Developer → Edit Config), restart:
{
"mcpServers": {
"postgres-explorer": {
"command": "/ABS/PATH/.venv/bin/python",
"args": ["/ABS/PATH/src/mcp_postgres_explorer/server.py"],
"env": {
"DATABASE_URL": "postgresql://readonly:readonly@localhost:5432/demo",
"RECS_MODEL": "/ABS/PATH/models/als.pkl"
}
}
}
}Tools / resources / prompts
Name | Kind | Description |
| tool | List tables in the public schema. |
| tool | Columns and types for a table. |
| tool | Run a read-only SQL query (rejects any write/DDL). |
| tool | Top-k product recommendations for a customer (ALS). |
| tool | "Customers also bought" item-item neighbors. |
| resource | Full DB schema as text. |
| prompt | Template to profile a table. |
Safety model (three read-only layers)
A DROP is rejected three independent ways:
DB role — the server connects as
readonly, which only holdsSELECT(seeschema.sql).Read-only transactions —
SET default_transaction_read_only = onat the driver layer (db.py).SQL-parser allowlist —
guard.assert_readonlyparses withsqlglotand permits onlySELECT/WITH.
Unit-tested in tests/test_guard.py (run pytest -q).

Live in Claude Desktop: the agent can query freely but a DROP TABLE is refused by the guardrail.
Recommender
Algorithm: ALS matrix factorization (
implicit), 64 factors, trained on customer × product purchase confidence.Offline eval (leave-last-out, 4,247 held-out users): recall@10 = 0.1095, precision@10 = 0.0109.
Serving: the trained model is pickled to
models/als.pkland loaded lazily byrecs.py. Because it's trained on your own DB, runscripts/train_recs.pybefore serving — the model is not shipped.

Live in Claude Desktop: recommend_for_user returns ranked products with ALS scores for a customer.
License
MIT
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
This server cannot be deployed
Maintenance
Related MCP Connectors
Read-only analytics for all your apps: users, AI traffic, revenue, with the SQL behind every number.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Read-only bank & investment accounts via Plaid: balances, holdings, transactions, SQL analytics.
Read-only zobrx e-commerce data: P&L, orders, inventory, marketplace, tax & shelf insights.
Related MCP Servers
- FlicenseNot gradedqualityDmaintenanceEnables AI agents to explore and query a mock e-commerce store's data including customers, products, inventory, and orders through conversational interactions backed by PostgreSQL.-
- FlicenseNot gradedqualityCmaintenanceProvides secure, read-only access to VTION e-commerce analytics data through MCP protocol. Enables AI agents to query PostgreSQL databases, discover schemas, and analyze product, order, and customer data with automatic query validation and parallel execution.-
- AlicenseNot gradedqualityDmaintenanceRead-only access to PostgreSQL databases, enabling schema inspection and safe SQL queries.20 npmMIT
- AlicenseNot gradedqualityDmaintenanceEnables e-commerce clients to query their own analytics data in plain English with strict tenant isolation enforced by the database.MIT