MCP -and-PostgreSQL-Sample-Retail

This MCP server provides a secure bridge for AI assistants to query and analyze retail sales data stored in PostgreSQL. It exposes a structured, schema-aware interface with support for row-level security (RLS) so different store managers can access only their own data. The server also enables semantic search capabilities, allowing natural language queries to be translated into SQL or data retrieval operations, and can integrate with Azure AI services for enhanced analytics and embedding-based product discovery. To use it, configure the connection to your PostgreSQL database and start the MCP server; then leverage your MCP client tools to issue natural language or structured queries against the retail data and retrieve results with appropriate access controls.

Prerequisites:

• Node.js (LTS) installed on your machine
• PostgreSQL database set up with the sample retail schema
• Basic familiarity with environment variables for DB and MCP configuration

1. Clone the repository: git clone https://github.com/microsoft/MCP-Server-and-PostgreSQL-Sample-Retail.git cd MCP-Server-and-PostgreSQL-Sample-Retail

2. Install dependencies: npm install

3. Configure environment variables (examples):

   • Create a .env file or export variables in your shell
   • DB_HOST=localhost
   • DB_PORT=5432
   • DB_NAME=retail_db
   • DB_USER=retail_user
   • DB_PASSWORD=secure-password
   • MCP_PORT=5000
   • AZURE_AI_ENDPOINT=https://your-azure-endpoint

4. Start the MCP server: npm start // or node server.js if you run directly

5. Verify the server is running by curling the MCP endpoint or by following any provided logs: curl http://localhost:5000/health

6. (Optional) Configure client tooling to interact with the MCP server using the available APIs for natural language queries, semantic search, and data retrieval.

Notes and tips:

• Ensure your PostgreSQL user has the necessary privileges and that Row-Level Security (RLS) policies are correctly configured to enforce multi-tenant access.
• If using Azure AI services, make sure your credentials and endpoints are securely stored (e.g., in a protected environment or secret store).
• Monitor the MCP server logs for authentication and authorization events to troubleshoot access issues.
• If you modify the database schema, update the MCP server data models and RLS policies accordingly.
• For production deployment, consider containerizing the server (Docker) and exposing TLS, as well as setting proper health checks and autoscaling policies.