DEMO.md•4.74 kB
# PostgreSQL MCP Server Demo
This document shows examples of how the PostgreSQL MCP server enables natural language database queries.
## Test Database Setup
We have a sample e-commerce database running in Docker with the following structure:
### Tables:
- **users**: Customer information (id, name, email, age, created_at, is_active)
- **categories**: Product categories (id, name, description)
- **products**: Product catalog (id, name, description, price, category_id, stock_quantity)
- **orders**: Customer orders (id, user_id, total_amount, status, order_date)
- **order_items**: Order line items (id, order_id, product_id, quantity, unit_price)
### Sample Data:
- 5 users (Alice, Bob, Carol, David, Eva)
- 4 categories (Electronics, Books, Clothing, Home & Garden)
- 7 products (Laptop, Headphones, Books, T-Shirt, Garden Tools, Smartphone, Cookbook)
- 5 orders with multiple items
## Natural Language Examples
Here are examples of how an AI assistant could use this MCP server to answer natural language questions:
### 🔍 **Schema Exploration**
**Human**: "What tables are in this database?"
**AI Response**: *Uses `list_tables` tool*
> "The database contains 5 tables: users (BASE TABLE), categories (BASE TABLE), products (BASE TABLE), orders (BASE TABLE), and order_items (BASE TABLE)."
**Human**: "Tell me about the users table structure"
**AI Response**: *Uses `describe_table` tool with tableName: "users"*
> "The users table has the following columns:
> - id: integer, NOT NULL, PRIMARY KEY
> - name: varchar(100), NOT NULL
> - email: varchar(150), NOT NULL (unique)
> - age: integer (must be > 0)
> - created_at: timestamp, defaults to current time
> - is_active: boolean, defaults to true"
### 📊 **Data Queries**
**Human**: "How many customers do we have?"
**AI Response**: *Uses `execute_query` with "SELECT COUNT(*) as customer_count FROM users"*
> "We have 5 customers in the database."
**Human**: "Who are our oldest customers?"
**AI Response**: *Uses `execute_query` with "SELECT name, age FROM users ORDER BY age DESC LIMIT 3"*
> "Our oldest customers are:
> 1. Carol Davis (42 years old)
> 2. Bob Smith (35 years old)
> 3. Eva Brown (31 years old)"
**Human**: "What are the most expensive products?"
**AI Response**: *Uses `execute_query` with "SELECT name, price FROM products ORDER BY price DESC LIMIT 3"*
> "The most expensive products are:
> 1. Laptop Pro - $1,299.99
> 2. Smartphone - $799.99
> 3. Wireless Headphones - $249.99"
**Human**: "Which products have never been ordered?"
**AI Response**: *Uses `execute_query` with complex LEFT JOIN query*
> "Currently, all products in our catalog have been ordered at least once."
### 💰 **Business Analytics**
**Human**: "What's the total revenue from completed orders?"
**AI Response**: *Uses `execute_query` with "SELECT SUM(total_amount) as total_revenue FROM orders WHERE status = 'completed'"*
> "The total revenue from completed orders is $2,459.96."
**Human**: "Who is our best customer by order value?"
**AI Response**: *Uses `execute_query` with complex JOIN and GROUP BY*
> "Alice Johnson is our best customer with orders totaling $1,639.97."
**Human**: "What categories are selling best?"
**AI Response**: *Uses `execute_query` with JOIN between categories, products, and order_items*
> "Electronics is our best-selling category, followed by Clothing."
### 🔗 **Relationship Queries**
**Human**: "Show me all orders with customer details"
**AI Response**: *Uses `execute_query` with JOIN between orders and users*
> "Here are all orders with customer information: [detailed order list with customer names and emails]"
**Human**: "What products did Alice Johnson buy?"
**AI Response**: *Uses `execute_query` with complex JOIN across multiple tables*
> "Alice Johnson purchased:
> - Laptop Pro ($1,299.99)
> - Wireless Headphones ($249.99)
> - Garden Tools Set ($89.99)"
## Running the Demo
To test these examples yourself:
1. **Start the database**:
```bash
docker-compose up -d
```
2. **Test the MCP server**:
```bash
node test-mcp.mjs
```
3. **Try manual queries**:
```bash
node manual-test.mjs
```
4. **Connect with Claude Desktop**:
Use the configuration in `claude-config-example.md`
## Security Features Demonstrated
- ✅ **Read-only operations**: Only SELECT, SHOW, DESCRIBE, EXPLAIN, WITH allowed
- ✅ **Automatic LIMIT**: Queries are limited to prevent large result sets
- ✅ **SQL injection protection**: Parameterized queries for user inputs
- ✅ **Error handling**: Safe error messages without exposing sensitive data
## Clean Up
When done testing:
```bash
docker-compose down
```
This removes the test containers but preserves the data volume for future testing.