MCP Server And PostgreSQL Sample Retail

MCP Server and PostgreSQL Sample - Retail Sales Analysis

Learn MCP with Database Integration through Hands-on Examples

[](https://GitHub.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail/graphs/contributors) [](https://GitHub.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail/issues) [](https://GitHub.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail/pulls) [](http://makeapullrequest.com)

[](https://discord.com/invite/ByRwuEEgH4)

Follow these steps to get started using these resources:

1. Fork the Repository: Click here to fork
2. Clone the Repository: git clone https://github.com/YOUR-USERNAME/MCP-Server-and-PostgreSQL-Sample-Retail.git
3. Join The Azure AI Foundry Discord: Meet experts and fellow developers

🌐 Multi-Language Support

Supported via GitHub Action (Automated & Always Up-to-Date)

[French](./translations/fr/README.md) | [Spanish](./translations/es/README.md) | [German](./translations/de/README.md) | [Russian](./translations/ru/README.md) | [Arabic](./translations/ar/README.md) | [Persian (Farsi)](./translations/fa/README.md) | [Urdu](./translations/ur/README.md) | [Chinese (Simplified)](./translations/zh/README.md) | [Chinese (Traditional, Macau)](./translations/mo/README.md) | [Chinese (Traditional, Hong Kong)](./translations/hk/README.md) | [Chinese (Traditional, Taiwan)](./translations/tw/README.md) | [Japanese](./translations/ja/README.md) | [Korean](./translations/ko/README.md) | [Hindi](./translations/hi/README.md) | [Bengali](./translations/bn/README.md) | [Marathi](./translations/mr/README.md) | [Nepali](./translations/ne/README.md) | [Punjabi (Gurmukhi)](./translations/pa/README.md) | [Portuguese (Portugal)](./translations/pt/README.md) | [Portuguese (Brazil)](./translations/br/README.md) | [Italian](./translations/it/README.md) | [Polish](./translations/pl/README.md) | [Turkish](./translations/tr/README.md) | [Greek](./translations/el/README.md) | [Thai](./translations/th/README.md) | [Swedish](./translations/sv/README.md) | [Danish](./translations/da/README.md) | [Norwegian](./translations/no/README.md) | [Finnish](./translations/fi/README.md) | [Dutch](./translations/nl/README.md) | [Hebrew](./translations/he/README.md) | [Vietnamese](./translations/vi/README.md) | [Indonesian](./translations/id/README.md) | [Malay](./translations/ms/README.md) | [Tagalog (Filipino)](./translations/tl/README.md) | [Swahili](./translations/sw/README.md) | [Hungarian](./translations/hu/README.md) | [Czech](./translations/cs/README.md) | [Slovak](./translations/sk/README.md) | [Romanian](./translations/ro/README.md) | [Bulgarian](./translations/bg/README.md) | [Serbian (Cyrillic)](./translations/sr/README.md) | [Croatian](./translations/hr/README.md) | [Slovenian](./translations/sl/README.md) | [Ukrainian](./translations/uk/README.md) | [Burmese (Myanmar)](./translations/my/README.md)

If you wish to have additional translations languages supported are listed here

Introduction

This sample demonstrates how to build and deploy a comprehensive Model Context Protocol (MCP) server that provides AI assistants with secure, intelligent access to retail sales data through PostgreSQL. The project showcases enterprise-grade features including Row Level Security (RLS), semantic search capabilities, and Azure AI integration for real-world retail analytics scenarios.

Key Use Cases:

• AI-Powered Sales Analytics: Enable AI assistants to query and analyze retail sales data through natural language
• Secure Multi-Tenant Access: Demonstrate Row Level Security implementation where different store managers can only access their store's data
• Semantic Product Search: Showcase AI-enhanced product discovery using text embeddings
• Enterprise Integration: Illustrate how to integrate MCP servers with Azure services and PostgreSQL databases

Perfect for:

• Developers learning to build MCP servers with database integration
• Data engineers implementing secure multi-tenant analytics solutions
• AI application developers working with retail or e-commerce data
• Anyone interested in combining AI assistants with enterprise databases

Join the Azure AI Foundry Discord Community

Share your experiences of MCP and meet the experts and product groups

[](https://discord.com/invite/ByRwuEEgH4)

Sales Analysis MCP Server

A Model Context Protocol (MCP) server that provides comprehensive customer sales database access for Zava Retail DIY Business. This server enables AI assistants to query and analyze retail sales data through a secure, schema-aware interface.

📚 Complete Implementation Guide

For a detailed breakdown of how this solution is built and how to implement similar MCP servers, see our comprehensive [Sample Walkthrough](Sample_Walkthrough.md). This guide provides:

• Architecture Deep Dive: Component analysis and design patterns
• Step-by-Step Building: From project setup to deployment
• Code Breakdown: Detailed explanation of MCP server implementation
• Advanced Features: Row Level Security, semantic search, and monitoring
• Best Practices: Security, performance, and development guidelines
• Troubleshooting: Common issues and solutions

Perfect for developers who want to understand the implementation details and build similar solutions.

🤖 What is MCP (Model Context Protocol)?

Model Context Protocol (MCP) is an open standard that enables AI assistants to securely access external data sources and tools in real-time. Think of it as a bridge that allows AI models to connect with databases, APIs, file systems, and other resources while maintaining security and control.

Key Benefits:

• Real-time Data Access: AI assistants can query live databases and APIs
• Secure Integration: Controlled access with authentication and permissions
• Tool Extensibility: Add custom capabilities to AI assistants
• Standardized Protocol: Works across different AI platforms and tools

New to MCP?

If you're new to Model Context Protocol, we recommend starting with Microsoft's comprehensive beginner resources:

📖 MCP for Beginners Guide

This resource provides:

• Introduction to MCP concepts and architecture
• Step-by-step tutorials for building your first MCP server
• Best practices for MCP development
• Integration examples with popular AI platforms
• Community resources and support

Once you understand the basics, return here to explore this advanced retail analytics implementation!

📚 Comprehensive Learning Guide: /walkthrough

This repository includes a complete 12-module learning walkthrough that deconstructs this MCP retail server sample into digestible, step-by-step lessons. The walkthrough transforms this working example into a comprehensive educational resource perfect for developers who want to understand how to build production-ready MCP servers with database integration.

What You'll Learn

The walkthrough covers everything from basic MCP concepts to advanced production deployment, including:

• MCP Fundamentals: Understanding the Model Context Protocol and its real-world applications
• Database Integration: Implementing secure PostgreSQL connectivity with Row Level Security
• AI-Enhanced Features: Adding semantic search capabilities with Azure OpenAI embeddings
• Security Implementation: Enterprise-grade authentication, authorization, and data isolation
• Tool Development: Building sophisticated MCP tools for data analysis and business intelligence
• Testing & Debugging: Comprehensive testing strategies and debugging techniques
• VS Code Integration: Configuring AI Chat for natural language database queries
• Production Deployment: Containerization, scaling, and cloud deployment strategies
• Monitoring & Observability: Application Insights, logging, and performance monitoring

Learning Path Overview

The walkthrough follows a progressive learning structure designed for developers of all skill levels:

| Module | Focus Area | Description | Time Estimate | |--------|------------|-------------|---------------| | [00-Introduction](walkthrough/00-Introduction/README.md) | Foundation | MCP concepts, Zava Retail case study, architecture overview | 30 minutes | | [01-Architecture](walkthrough/01-Architecture/README.md) | Design Patterns | Technical architecture, layered design, system components | 45 minutes | | [02-Security](walkthrough/02-Security/README.md) | Enterprise Security | Azure authentication, Row Level Security, multi-tenant isolation | 60 minutes | | [03-Setup](walkthrough/03-Setup/README.md) | Environment | Docker setup, Azure CLI, project configuration, validation | 45 minutes | | [04-Database](walkthrough/04-Database/README.md) | Data Layer | PostgreSQL schema, pgvector, RLS policies, sample data | 60 minutes | | [05-MCP-Server](walkthrough/05-MCP-Server/README.md) | Core Implementation | FastMCP framework, database integration, connection management | 90 minutes | | [06-Tools](walkthrough/06-Tools/README.md) | Tool Development | MCP tool creation, query validation, business intelligence features | 75 minutes | | [07-Semantic-Search](walkthrough/07-Semantic-Search/README.md) | AI Integration | Azure OpenAI embeddings, vector search, hybrid search strategies | 60 minutes | | [08-Testing](walkthrough/08-Testing/README.md) | Quality Assurance | Unit testing, integration testing, performance testing, debugging | 75 minutes | | [09-VS-Code](walkthrough/09-VS-Code/README.md) | Development Experience | VS Code configuration, AI Chat integration, debugging workflows | 45 minutes | | [10-Deployment](walkthrough/10-Deployment/README.md) | Production Ready | Containerization, Azure Container Apps, CI/CD pipelines, scaling | 90 minutes | | [11-Monitoring](walkthrough/11-Monitoring/README.md) | Observability | Application Insights, structured logging, performance metrics | 60 minutes | | [12-Best-Practices](walkthrough/12-Best-Practices/README.md) | Production Excellence | Security hardening, performance optimization, enterprise patterns | 45 minutes |

Total Learning Time: ~12-15 hours of comprehensive hands-on learning

🎯 How to Use the Walkthrough

For Beginners:

1. Start with [Module 00: Introduction](walkthrough/00-Introduction/README.md) to understand MCP fundamentals
2. Follow the modules sequentially for a complete learning experience
3. Each module builds on previous concepts and includes practical exercises

For Experienced Developers:

1. Review the [Main Walkthrough Overview](walkthrough/README.md) for a complete module summary
2. Jump to specific modules that interest you (e.g., Module 07 for AI integration)
3. Use individual modules as reference material for your own projects

For Production Implementation:

1. Focus on Modules 02 (Security), 10 (Deployment), and 11 (Monitoring)
2. Review Module 12 (Best Practices) for enterprise guidelines
3. Use the code examples as production-ready templates

🚀 Quick Start Options

Option 1: Complete Learning Path (Recommended for newcomers)

# Clone and start with the introduction
git clone https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail.git
cd MCP-Server-and-PostgreSQL-Sample-Retail/walkthrough
# Follow along starting with 00-Introduction/README.md

Option 2: Hands-On Implementation (Jump right into building)

# Start with setup and build as you learn
cd walkthrough/03-Setup
# Follow the setup guide and continue through implementation modules

Option 3: Production Focus (Enterprise deployment)

# Focus on production-ready aspects
# Review modules: 02-Security, 10-Deployment, 11-Monitoring, 12-Best-Practices

📋 Learning Prerequisites

Recommended Background:

• Basic Python programming experience
• Familiarity with REST APIs and databases
• General understanding of AI/ML concepts
• Basic command-line and Docker knowledge

Not Required (but helpful):

• Prior MCP experience (we cover this from scratch)
• Azure cloud experience (we provide step-by-step guidance)
• Advanced PostgreSQL knowledge (we explain concepts as needed)

💡 Learning Tips

1. Hands-On Approach: Each module includes working code examples you can run and modify
2. Progressive Complexity: Concepts build gradually from simple to advanced
3. Real-World Context: All examples use realistic retail business scenarios
4. Production Ready: Code examples are designed for actual production use
5. Community Support: Join our Discord community for help and discussions

🔗 Related Resources

• MCP for Beginners: Essential background reading
• [Sample Walkthrough](Sample_Walkthrough.md): High-level technical overview
• Azure AI Foundry: Cloud platform used in examples
• FastMCP Framework: Python MCP implementation framework

Ready to start learning? Begin with [Module 00: Introduction](walkthrough/00-Introduction/README.md) or explore the [complete walkthrough overview](walkthrough/README.md).

Prerequisites

1. Docker Desktop installed
2. Git installed
3. Azure CLI: Install and authenticate with Azure CLI
4. Access to OpenAI text-embedding-3-small model and optionally gpt-4o-mini model.

Getting Started

Open a terminal window and running the following commands:

1. Authenticate with Azure CLI

``bash az login ``

1. Clone the repository

``bash git clone https://github.com/gloveboxes/Zava-MCP-Server-and-PostgreSQL-Sample ``

1. Navigate to the project directory

``bash cd Zava-MCP-Server-and-PostgreSQL-Sample ``

Deploy Azure Resources

Run the following scripts to automate the deployment of Azure resources needed for the MCP server.

The deployment scripts will automatically deploy the text-embedding-3-small model. During deployment, you'll have the option to also include the gpt-4o-mini model. Note that gpt-4o-mini is not required for this project and is only included for potential future enhancements.

Choose the script for your platform:

Windows (PowerShell)

# Run from the project root directory
cd infra && ./deploy.ps1

macOS/Linux (Bash)

# Run from the project root directory
cd infra && ./deploy.sh

Running the MCP Server

The easiest way to run the complete stack (PostgreSQL + MCP Server) is using Docker Compose:

Start the Stack

# Start PostgreSQL and MCP Server
docker compose up -d

# View logs
docker compose logs -f

# View MCP Server Logs
docker compose logs -f mcp_server

# View the PostgreSQL Logs
docker compose logs -f pg17

# Stop the stack
docker compose down -v

Usage

The following assumes you'll be using the built-in VS Code MCP server support.

1. Open the project in VS Code. From the terminal, run:

``bash code . ``

1. Start one or more MCP servers using the configurations in .vscode/mcp.json. The file contains four different server configurations, each representing a different store manager role:

• Each configuration uses a unique RLS (Row Level Security) user ID
• These user IDs simulate different store manager identities accessing the database
• The RLS system restricts data access based on the manager's assigned store
• This mimics real-world scenarios where store managers sign in with different Entra ID accounts

```json { "servers": { "zava-sales-analysis-headoffice"

…

Source & license

This open-source MCP server is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: microsoft
• Source: microsoft/MCP-Server-and-PostgreSQL-Sample-Retail
• License: MIT

Install and usage instructions live in the source repository linked above.