npm package: @node-anjan/ecommerce-mcp-server
Description: An MCP server that lets AI assistants manage an e-commerce product catalog — add, update, search, delete products — all through natural language.
Author: Anjan CH
Latest version: 1.2.2

# @node-anjan/ecommerce-mcp-server

An MCP server that lets AI assistants manage an e-commerce product catalog — add, update, search, delete products — all through natural language.

> **Think of it this way:** MCP (Model Context Protocol) is like a USB port for AI. Just as USB lets your computer talk to a printer without knowing how the printer works, MCP lets AI talk to your database without knowing SQL. This server is that USB cable — connecting AI to your product database.

---

## What Does This Server Do?

You connect this server to an AI client (like VS Code Copilot, Claude Desktop, or MCP Inspector), and the AI can:

- **Add products** to your MySQL database
- **Search & list** products with pagination
- **Update** any product field (price, name, stock, etc.)
- **Delete** products
- **Auto-generate descriptions** using AI when you don't provide one
- **Monitor low stock** items automatically

No REST API. No frontend. The AI **is** the interface.

---

## Architecture (The Simple Version)

```
┌─────────────────┐     stdio      ┌─────────────────┐        ┌─────────┐
│   AI Client     │ ◄────────────► │   MCP Server    │ ◄────► │  MySQL  │
│ (Copilot/Claude)│   JSON-RPC     │  (this project) │  SQL   │   DB    │
└─────────────────┘                └─────────────────┘        └─────────┘
```

The AI client sends tool calls over stdin/stdout. This server translates them into SQL queries. Results flow back the same way.

---

## Quick Start

### 1. Install dependencies

```bash
npm i @node-anjan/ecommerce-mcp-server
```

### 2. Set up your database

Create a MySQL database and run the schema:

```sql
CREATE DATABASE mcp_curd_db;
USE mcp_curd_db;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  quantity INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

Or just run the included file:

```bash
mysql -u root -p < sql/schema.sql
```

### 3. Configure environment

Create a `.env` file in the project root:

```env
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=your_password
MYSQL_DATABASE=mcp_curd_db
```

### 4. Start the MCP server

```bash
npm run dev:mcp
```

That's it. The server is now waiting for an AI client to connect.

---

## Connect to VS Code (Copilot)

Add this to `.vscode/mcp.json` in any workspace:

```json
{
  "servers": {
    "ecommerce-mcp": {
      "type": "stdio",
      "command": "npx",
      "args": ["tsx", "/path/to/this/project/src/mcp/server.ts"]
    }
  }
}
```

Or if installed globally via `npm link`:

```json
{
  "servers": {
    "ecommerce-mcp": {
      "type": "stdio",
      "command": "ecommerce-products-mcp"
    }
  }
}
```

Then open the MCP panel in VS Code — you'll see your tools listed.

---

## Use via npm (Public Package)

Don't want to clone the repo? Just use the published npm package directly. **No installation needed** — `npx` downloads and runs it on the fly.

### Prerequisites

1. **Node.js 18+** installed
2. **MySQL** running with the products table created:

```sql
CREATE DATABASE mcp_curd_db;
USE mcp_curd_db;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  quantity INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### Claude Desktop

Open your Claude Desktop config file:

- **macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`

Add this inside `"mcpServers"`:

```json
{
  "mcpServers": {
    "ecommerce-products": {
      "command": "npx",
      "args": ["@node-anjan/ecommerce-mcp-server@latest"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "mcp_curd_db"
      }
    }
  }
}
```

Restart Claude Desktop. You'll see the tools appear — ask Claude to "list all products" or "add a product" and it just works.

### VS Code (Copilot)

Add this to `.vscode/mcp.json` in your workspace:

```json
{
  "servers": {
    "ecommerce-products": {
      "type": "stdio",
      "command": "npx",
      "args": ["@node-anjan/ecommerce-mcp-server@latest"],
      "env": {
        "MYSQL_HOST": "localhost",
        "MYSQL_PORT": "3306",
        "MYSQL_USER": "root",
        "MYSQL_PASSWORD": "your_password",
        "MYSQL_DATABASE": "mcp_curd_db"
      }
    }
  }
}
```

Click **Start** next to the server name in `mcp.json`. The tools will be available in Copilot Chat.

### Environment Variables

| Variable | Default | Description |
|----------|---------|-------------|
| `MYSQL_HOST` | `localhost` | MySQL server hostname |
| `MYSQL_PORT` | `3306` | MySQL server port |
| `MYSQL_USER` | `root` | Database username |
| `MYSQL_PASSWORD` | — | Database password |
| `MYSQL_DATABASE` | — | Database name |

> **Tip:** Replace `@latest` with a specific version (e.g., `@1.2.0`) if you want to pin a known working version.

---

## What's Inside

### 10 Tools (things the AI can *do*)

| Tool | What it does |
|------|-------------|
| `add_product` | Create a new product (sku, name, price, quantity) |
| `add_product_smart` | Same as above, but AI writes the description if you skip it |
| `get_product` | Fetch a product by ID |
| `list_products` | List products with pagination (`limit`, `offset`) |
| `search_products` | Search by name (fuzzy match) |
| `update_product` | Update any fields on an existing product |
| `delete_product` | Remove a product by ID |
| `health_check` | Confirm the server is alive |
| `list_tools` | Show all available tools |
| `get_tool` | Get details about a specific tool |

### 2 Resources (data the AI can *read*)

| Resource | URI | What it returns |
|----------|-----|----------------|
| All Products | `products://all` | Full product catalog (up to 1000) |
| Low Stock | `products://low-stock` | Products with quantity < 10 |

### 1 Prompt (template the AI can *use*)

| Prompt | What it generates |
|--------|------------------|
| `product_description_generator` | SEO-friendly product description with headline, features, and use cases |

---

## Project Structure

```
src/
├── mcp/
│   └── server.ts          ← MCP server — registers all tools, resources, prompts
├── services/
│   └── ProductService.ts   ← Business logic + Zod validation
├── repo/
│   └── ProductRepository.ts ← Raw SQL queries (MySQL)
├── models/
│   └── Product.ts           ← TypeScript interface for a product
├── db.ts                    ← MySQL connection pool
└── index.ts                 ← Demo script (non-MCP, for testing)
```

**Data flows top-down:** MCP Server → Service → Repository → MySQL

---

## NPM Scripts

| Command | What it does |
|---------|-------------|
| `npm run dev:mcp` | Start MCP server (stdio transport) |
| `npm run dev:demo` | Run a quick CRUD demo without MCP |
| `npm run build` | Compile TypeScript to `dist/` |
| `npm run check` | Type-check without emitting files |

---

## How `add_product_smart` Works

This is the coolest part. When you call `add_product_smart` without a description, the server uses **MCP Sampling** — it asks the AI client to generate a description, then saves the product with that AI-written text.

```
You: "Add a product called Wireless Mouse, SKU WM-100, price $29.99, qty 50"
 ↓
Server: description is missing... let me ask the AI to write one
 ↓
AI generates: "Ergonomic wireless mouse with precision tracking..."
 ↓
Server: saves product with the AI-generated description to MySQL
```

The server doesn't contain an AI model — it asks your AI client to do the thinking. That's what MCP Sampling is.

---

## Tech Stack

- **TypeScript** — type-safe from top to bottom
- **@modelcontextprotocol/sdk** — official MCP SDK
- **Zod** — runtime input validation
- **mysql2** — async MySQL driver with connection pooling
- **dotenv** — environment config

---

## License

MIT — use it, learn from it, build on it.

**Built by [Anjan CH](https://github.com/node-anjan)**