FastMCP Orders Server

A Model Context Protocol (MCP) server built with FastMCP that exposes a tool for querying customer orders from a PostgreSQL database.

---

Project Structure

├── mcp_server.py        # FastMCP server — stdio transport (default), HTTP optional
├── mcp_client.py        # Demo client — connects via stdio, calls the tool
├── .env.example         # Template for DB credentials
├── requirements.txt     # Python dependencies
├── schema.sql           # DDL to create customers + orders tables
├── seed.sql             # Sample data for local testing
└── README.md            # This file

---

Prerequisites

• Python ≥ 3.10
• PostgreSQL instance with a database containing the schema described below

Database Schema

The server expects two tables:

Table	Columns
customers	id (PK), name (text), email (text, unique)
orders	id (PK), customer_id (FK→customers), order_date, total_amount (numeric, €), status

Run the provided SQL files to create them:

psql -d orders_db -f schema.sql
psql -d orders_db -f seed.sql

---

Setup

1. Install dependencies

pip install -r requirements.txt

2. Configure database credentials

Copy the env template and fill in your connection details:

cp .env.example .env
nano .env   # edit values to match your PostgreSQL instance

---

Running the Server

Stdio transport (default)

python mcp_server.py

This is the standard mode for local MCP servers and works with Claude Desktop, the MCP Inspector, and any stdio-compatible client.

HTTP transport (optional — remote clients)

python mcp_server.py --transport http --port 9000

Useful when clients need a remote HTTP endpoint instead of a local process.

---

Running the Demo Client

The bundled client connects to the server via stdio and runs three test scenarios:

python mcp_client.py

Expected output (with seed data loaded):

[TEST 1] Orders for customer_id=1 with min_amount=100.0
  Order ID   Date          Amount (€)     Status
  --------------------------------------------------------
  5          2025-06-15    180.75 €       completed
  3          2025-03-01    520.00 €       shipped
  1          2025-01-10    250.00 €       completed

  → 3 order(s) found

[TEST 2] Orders for 'carlos@example.com' with min_amount=300.0
  Order ID   Date          Amount (€)     Status
  --------------------------------------------------------
  8          2025-05-10    890.00 €       shipped
  6          2025-02-01    340.00 €       completed

  → 2 order(s) found

[TEST 3] Lookup nonexistent email (expecting error)
  ⚠ Customer not found (nobody@nowhere.com)

---

Testing with the MCP Inspector

The MCP Inspector is an interactive tool for exploring MCP servers.

npx @modelcontextprotocol/inspector node mcp_server.js

For Python servers:

npx @modelcontextprotocol/inspector python mcp_server.py

1. The inspector opens a browser window on http://localhost:6287.
2. In the UI, locate get_customer_orders_above_amount under Tools.
3. Fill in parameters (e.g., {"customer_id": 1, "min_amount": 100}) and click Call.
4. Inspect the structured JSON response.

---

Tool Specification

get_customer_orders_above_amount

Retrieve a customer's orders whose total exceeds a minimum amount in euros.

Parameter	Type	Required	Description
customer_id	int | None	No	Primary key of the customer (takes precedence if both given)
customer_email	str | None	No	Email address for alternative lookup
min_amount	float	No	Minimum order value in € (inclusive, default 0.0)

At least one of customer_id or customer_email must be provided.

Returns: list[dict] — each dict has keys order_id, order_date, total_amount, status. Results are ordered by order_date descending.

Error responses are returned as a list containing a single dict with an "error" key (no raw stack traces leak to the client).

---

Security Notes

• No hardcoded credentials — all DB parameters come from environment variables loaded via python-dotenv.
• Parameterized queries only — SQL injection is prevented by using $1, $2 placeholders throughout.
• Connection pooling — an asyncpg pool (2–10 connections) is created once at server startup and closed at shutdown.

Claude code configurations

If you want disable the autocompact to hav emore context window space use this parameter in your claude settings.json

"DISABLE_AUTO_COMPACT": "true"