@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

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

2. Set up your database

Create a MySQL database and run the schema:

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:

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

3. Configure environment

Create a .env file in the project root:

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

4. Start the MCP server

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:

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

Or if installed globally via npm link:

{
  "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:

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":

{
  "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:

{
  "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