wehub-resource-sync
15 KiB
Introduction to MCP Database Integration
🎯 What This Lab Covers
This introduction lab provides a comprehensive overview of building Model Context Protocol (MCP) servers with database integration. You'll understand the business case, technical architecture, and real-world applications through the Zava Retail analytics use case at https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail.
Overview
Model Context Protocol (MCP) enables AI assistants to securely access and interact with external data sources in real-time. When combined with database integration, MCP unlocks powerful capabilities for data-driven AI applications.
This learning path teaches you to build production-ready MCP servers that connect AI assistants to retail sales data through PostgreSQL, implementing enterprise patterns like Row Level Security, semantic search, and multi-tenant data access.
Learning Objectives
By the end of this lab, you will be able to:
- Define Model Context Protocol and its core benefits for database integration
- Identify key components of an MCP server architecture with databases
- Understand the Zava Retail use case and its business requirements
- Recognize enterprise patterns for secure, scalable database access
- List the tools and technologies used throughout this learning path
🧭 The Challenge: AI Meets Real-World Data
Traditional AI Limitations
Modern AI assistants are incredibly powerful but face significant limitations when working with real-world business data:
| Challenge | Description | Business Impact |
|---|---|---|
| Static Knowledge | AI models trained on fixed datasets can't access current business data | Outdated insights, missed opportunities |
| Data Silos | Information locked in databases, APIs, and systems AI can't reach | Incomplete analysis, fragmented workflows |
| Security Constraints | Direct database access raises security and compliance concerns | Limited deployment, manual data preparation |
| Complex Queries | Business users need technical knowledge to extract data insights | Reduced adoption, inefficient processes |
The MCP Solution
Model Context Protocol addresses these challenges by providing:
- Real-time Data Access: AI assistants query live databases and APIs
- Secure Integration: Controlled access with authentication and permissions
- Natural Language Interface: Business users ask questions in plain English
- Standardized Protocol: Works across different AI platforms and tools
🏪 Meet Zava Retail: Our Learning Case Study https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail
Throughout this learning path, we'll build an MCP server for Zava Retail, a fictional DIY retail chain with multiple store locations. This realistic scenario demonstrates enterprise-grade MCP implementation.
Business Context
Zava Retail operates:
- 8 physical stores across Washington state (Seattle, Bellevue, Tacoma, Spokane, Everett, Redmond, Kirkland)
- 1 online store for e-commerce sales
- Diverse product catalog including tools, hardware, garden supplies, and building materials
- Multi-level management with store managers, regional managers, and executives
Business Requirements
Store managers and executives need AI-powered analytics to:
- Analyze sales performance across stores and time periods
- Track inventory levels and identify restocking needs
- Understand customer behavior and purchasing patterns
- Discover product insights through semantic search
- Generate reports with natural language queries
- Maintain data security with role-based access control
Technical Requirements
The MCP server must provide:
- Multi-tenant data access where store managers see only their store's data
- Flexible querying supporting complex SQL operations
- Semantic search for product discovery and recommendations
- Real-time data reflecting current business state
- Secure authentication with row-level security
- Scalable architecture supporting multiple concurrent users
🏗️ MCP Server Architecture Overview
Our MCP server implements a layered architecture optimized for database integration:
┌─────────────────────────────────────────────────────────────┐
│ VS Code AI Client │
│ (Natural Language Queries) │
└─────────────────────┬───────────────────────────────────────┘
│ HTTP/SSE
▼
┌─────────────────────────────────────────────────────────────┐
│ MCP Server │
│ ┌─────────────────┐ ┌─────────────────┐ ┌───────────────┐ │
│ │ Tool Layer │ │ Security Layer │ │ Config Layer │ │
│ │ │ │ │ │ │ │
│ │ • Query Tools │ │ • RLS Context │ │ • Environment │ │
│ │ • Schema Tools │ │ • User Identity │ │ • Connections │ │
│ │ • Search Tools │ │ • Access Control│ │ • Validation │ │
│ └─────────────────┘ └─────────────────┘ └───────────────┘ │
└─────────────────────┬───────────────────────────────────────┘
│ asyncpg
▼
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL Database │
│ ┌─────────────────┐ ┌─────────────────┐ ┌───────────────┐ │
│ │ Retail Schema │ │ RLS Policies │ │ pgvector │ │
│ │ │ │ │ │ │ │
│ │ • Stores │ │ • Store-based │ │ • Embeddings │ │
│ │ • Customers │ │ Isolation │ │ • Similarity │ │
│ │ • Products │ │ • Role Control │ │ Search │ │
│ │ • Orders │ │ • Audit Logs │ │ │ │
│ └─────────────────┘ └─────────────────┘ └───────────────┘ │
└─────────────────────┬───────────────────────────────────────┘
│ REST API
▼
┌─────────────────────────────────────────────────────────────┐
│ Azure OpenAI │
│ (Text Embeddings) │
└─────────────────────────────────────────────────────────────┘
Key Components
1. MCP Server Layer
- FastMCP Framework: Modern Python MCP server implementation
- Tool Registration: Declarative tool definitions with type safety
- Request Context: User identity and session management
- Error Handling: Robust error management and logging
2. Database Integration Layer
- Connection Pooling: Efficient asyncpg connection management
- Schema Provider: Dynamic table schema discovery
- Query Executor: Secure SQL execution with RLS context
- Transaction Management: ACID compliance and rollback handling
3. Security Layer
- Row Level Security: PostgreSQL RLS for multi-tenant data isolation
- User Identity: Store manager authentication and authorization
- Access Control: Fine-grained permissions and audit trails
- Input Validation: SQL injection prevention and query validation
4. AI Enhancement Layer
- Semantic Search: Vector embeddings for product discovery
- Azure OpenAI Integration: Text embedding generation
- Similarity Algorithms: pgvector cosine similarity search
- Search Optimization: Indexing and performance tuning
🔧 Technology Stack
Core Technologies
| Component | Technology | Purpose |
|---|---|---|
| MCP Framework | FastMCP (Python) | Modern MCP server implementation |
| Database | PostgreSQL 17 + pgvector | Relational data with vector search |
| AI Services | Azure OpenAI | Text embeddings and language models |
| Containerization | Docker + Docker Compose | Development environment |
| Cloud Platform | Microsoft Azure | Production deployment |
| IDE Integration | VS Code | AI Chat and development workflow |
Development Tools
| Tool | Purpose |
|---|---|
| asyncpg | High-performance PostgreSQL driver |
| Pydantic | Data validation and serialization |
| Azure SDK | Cloud service integration |
| pytest | Testing framework |
| Docker | Containerization and deployment |
Production Stack
| Service | Azure Resource | Purpose |
|---|---|---|
| Database | Azure Database for PostgreSQL | Managed database service |
| Container | Azure Container Apps | Serverless container hosting |
| AI Services | Microsoft Foundry | OpenAI models and endpoints |
| Monitoring | Application Insights | Observability and diagnostics |
| Security | Azure Key Vault | Secrets and configuration management |
🎬 Real-World Usage Scenarios
Let's explore how different users interact with our MCP server:
Scenario 1: Store Manager Performance Review
User: Sarah, Seattle Store Manager
Goal: Analyze last quarter's sales performance
Natural Language Query:
"Show me the top 10 products by revenue for my store in Q4 2024"
What Happens:
- VS Code AI Chat sends query to MCP server
- MCP server identifies Sarah's store context (Seattle)
- RLS policies filter data to Seattle store only
- SQL query generated and executed
- Results formatted and returned to AI Chat
- AI provides analysis and insights
Scenario 2: Product Discovery with Semantic Search
User: Mike, Inventory Manager
Goal: Find products similar to a customer request
Natural Language Query:
"What products do we sell that are similar to 'waterproof electrical connectors for outdoor use'?"
What Happens:
- Query processed by semantic search tool
- Azure OpenAI generates embedding vector
- pgvector performs similarity search
- Related products ranked by relevance
- Results include product details and availability
- AI suggests alternatives and bundling opportunities
Scenario 3: Cross-Store Analytics
User: Jennifer, Regional Manager
Goal: Compare performance across all stores
Natural Language Query:
"Compare sales by category for all stores in the last 6 months"
What Happens:
- RLS context set for regional manager access
- Complex multi-store query generated
- Data aggregated across store locations
- Results include trends and comparisons
- AI identifies insights and recommendations
🔒 Security and Multi-Tenancy Deep Dive
Our implementation prioritizes enterprise-grade security:
Row Level Security (RLS)
PostgreSQL RLS ensures data isolation:
-- Store managers see only their store's data
CREATE POLICY store_manager_policy ON retail.orders
FOR ALL TO store_managers
USING (store_id = get_current_user_store());
-- Regional managers see multiple stores
CREATE POLICY regional_manager_policy ON retail.orders
FOR ALL TO regional_managers
USING (store_id = ANY(get_user_store_list()));
User Identity Management
Each MCP connection includes:
- Store Manager ID: Unique identifier for RLS context
- Role Assignment: Permissions and access levels
- Session Management: Secure authentication tokens
- Audit Logging: Complete access history
Data Protection
Multiple layers of security:
- Connection Encryption: TLS for all database connections
- SQL Injection Prevention: Parameterized queries only
- Input Validation: Comprehensive request validation
- Error Handling: No sensitive data in error messages
🎯 Key Takeaways
After completing this introduction, you should understand:
✅ MCP Value Proposition: How MCP bridges AI assistants and real-world data
✅ Business Context: Zava Retail's requirements and challenges
✅ Architecture Overview: Key components and their interactions
✅ Technology Stack: Tools and frameworks used throughout
✅ Security Model: Multi-tenant data access and protection
✅ Usage Patterns: Real-world query scenarios and workflows
🚀 What's Next
Ready to dive deeper? Continue with:
Lab 01: Core Architecture Concepts
Learn about MCP server architecture patterns, database design principles, and the detailed technical implementation that powers our retail analytics solution.
📚 Additional Resources
MCP Documentation
- MCP Specification - Official protocol documentation
- MCP for Beginners - Comprehensive MCP learning guide
- FastMCP Documentation - Python SDK documentation
Database Integration
- PostgreSQL Documentation - Complete PostgreSQL reference
- pgvector Guide - Vector extension documentation
- Row Level Security - PostgreSQL RLS guide
Azure Services
- Azure OpenAI Documentation - AI service integration
- Azure Database for PostgreSQL - Managed database service
- Azure Container Apps - Serverless containers
Disclaimer: This is a learning exercise using fictional retail data. Always follow your organization's data governance and security policies when implementing similar solutions in production environments.