PostgreSQL MCP Server

# Getting Started with PostgreSQL MCP Server Examples This guide walks you through using the PostgreSQL MCP Server examples to get started quickly with real-world scenarios. ## 🚀 Quick Start ### 1. Choose Your Schema Start by setting up a database using one of our example schemas: ```bash # For e-commerce examples psql -d your_database -f examples/schemas/e-commerce.sql ``` ### 2. Configure Your Environment Use our configuration examples based on your environment: ```bash # Development setup cp examples/configurations/.env.dev .env # Production setup cp examples/configurations/.env.production .env # Then edit .env with your actual credentials ``` ### 3. Test Your Setup ```bash # Test database connection python main.py --test # Try the validation demo python demo_validation.py ``` ## 📚 Learning Path ### Step 1: Basic Queries Start with simple validation examples: ```json { "name": "list_tables", "arguments": { "schema": "public" } } ``` ### Step 2: Query Validation Learn about security and performance: ```json { "name": "validate_query", "arguments": { "sql": "SELECT * FROM products WHERE price > 100", "schema": "public" } } ``` ### Step 3: Natural Language Queries (🤖 **NEW!**) Use Ollama LLM for natural language database interaction: ```bash python examples/ollama-integration.py ``` Ask questions like "What are the top selling products?" and get intelligent responses! ### Step 4: Real-world Scenarios Follow our conversation examples: - 📊 **E-commerce Analytics** - Business intelligence queries - 🎧 **Customer Support** - Help desk workflows - 🔍 **Data Exploration** - Schema discovery - 🤖 **LLM Integration** - Natural language database queries ## 🎯 Use Case Examples ### 🛒 E-commerce Platform **Perfect for:** Online stores, marketplaces, retail analytics **Key Features:** - Customer behavior analysis - Sales performance tracking - Inventory management - Order tracking and support **Start here:** `examples/conversations/e-commerce-analysis.md` ### 🏢 Enterprise SaaS **Perfect for:** B2B platforms, multi-tenant applications **Key Features:** - Customer segmentation - Usage analytics - Billing and subscription tracking - Performance monitoring **Start here:** `examples/configurations/production-config.md` ### 📞 Customer Support **Perfect for:** Help desks, support teams, customer success **Key Features:** - Customer lookup and history - Order tracking and issues - Proactive problem detection - Case management workflows **Start here:** `examples/workflows/customer-support-workflow.md` ## 🔧 Configuration Guide ### Development Environment ```bash # Quick development setup cp examples/configurations/.env.dev .env python main.py --test ``` ### Production Environment ```bash # Production setup with security cp examples/configurations/.env.production .env # Edit credentials and security settings python examples/configurations/validate-config.py ``` ## 📖 Example Walkthroughs ### 1. Basic Database Exploration ```json // Step 1: List available tables { "name": "list_tables", "arguments": {"schema": "public"} } // Step 2: Examine a table structure { "name": "describe_table", "arguments": {"table_name": "orders"} } // Step 3: Run a simple query { "name": "query", "arguments": { "sql": "SELECT COUNT(*) FROM orders WHERE status = 'delivered'" } } ``` ### 2. Advanced Analytics ```json // Complex business intelligence query { "name": "query", "arguments": { "sql": "SELECT DATE(created_at) as order_date, COUNT(*) as orders, SUM(total_amount) as revenue FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '30 days' GROUP BY DATE(created_at) ORDER BY order_date DESC LIMIT 30" } } ``` ### 3. Query Optimization ```json // Validate and optimize your queries { "name": "validate_query", "arguments": { "sql": "SELECT * FROM users WHERE email LIKE '%@gmail.com'", "schema": "public" } } ``` ## 🛡️ Security Best Practices ### ✅ Good Practices - **Always validate queries** before execution - **Use specific column names** instead of SELECT * - **Include LIMIT clauses** to prevent large result sets - **Use parameterized queries** with $1, $2, etc. - **Restrict schema access** in production ### ❌ Avoid These Patterns - Never use dynamic SQL construction - Don't use leading wildcards in LIKE (e.g., LIKE '%text') - Avoid Cartesian products (JOINs without ON clauses) - Don't expose all schemas in production ## 📊 Example Results ### Good Query Response ```json { "name": "query", "arguments": { "sql": "SELECT id, name, price FROM products WHERE category_id = 1 ORDER BY price DESC LIMIT 10" } } ``` **Response includes:** - ✅ Query results in JSON format - 📊 Performance analysis - 💡 Optimization suggestions - 🔒 Security validation results ### Query Validation Response ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 3/10 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see execution plan 2. Consider adding index on products.category_id 3. Query looks well-optimized for current use ``` ## 🚀 Next Steps ### 1. Customize for Your Data - Adapt the schema examples to your database structure - Modify queries to match your business needs - Configure security settings for your environment ### 2. Integrate with Your LLM - Use the conversation examples as templates - Build custom workflows for your use cases - Implement proper error handling and fallbacks ### 3. Production Deployment - Follow the production configuration guide - Set up proper monitoring and logging - Implement backup and disaster recovery ### 4. Advanced Features - Explore complex analytical queries - Implement custom validation rules - Build automated reporting workflows ## 💡 Tips for Success ### 🎯 **Start Simple** Begin with basic queries and gradually build complexity ### 🔍 **Use Validation** Always validate queries before execution, especially in production ### 📝 **Document Patterns** Keep track of successful query patterns for reuse ### 🔧 **Monitor Performance** Use the optimization suggestions to improve query performance ### 🛡️ **Security First** Follow security best practices and regularly audit access ## 📞 Getting Help ### Common Issues 1. **Connection failures** - Check configuration and network access 2. **Permission errors** - Verify database user permissions 3. **Query timeouts** - Optimize queries or increase timeout limits 4. **Validation errors** - Review query syntax and security patterns ### Resources - 📖 **Query examples** in `examples/queries/` - 💬 **Conversation patterns** in `examples/conversations/` - ⚙️ **Configuration guides** in `examples/configurations/` - 🔄 **Workflow examples** in `examples/workflows/` Ready to start building with the PostgreSQL MCP Server? Choose your use case and dive into the examples! 🚀