PostgreSQL MCP Server Demo

Overview

This project implements a Model Context Protocol (MCP) server that provides a standardized interface for AI assistants to interact with PostgreSQL databases. MCP enables secure, structured communication between AI models and external data sources through well-defined tools, resources, and prompts.

Theoretical Foundation

Core Objectives

1. Database Accessibility: Enable AI assistants to safely query PostgreSQL databases without direct database access

2. Structured Interaction: Provide standardized tools for database operations through MCP protocol

3. Security: Implement read-only operations with strict query validation to prevent data manipulation

4. Context Awareness: Supply database schema information and context for intelligent query generation

Architecture Components

1. MCP Server Framework

• FastMCP: Lightweight MCP server implementation providing transport layers (stdio/SSE)

• Transport Layer: Dual transport support for local development (stdio) and network deployment (SSE via HTTP)

• Registration System: Decorators for automatic registration of tools, resources, and prompts

2. Configuration Management

• YAML-based Configuration: Centralized settings for database connections and server parameters

• Environment Variables: Runtime configuration override capabilities

• Validation Layer: Pydantic models ensuring data integrity and type safety

3. Database Abstraction

• Connection Pooling: Async PostgreSQL connections with automatic lifecycle management

• Query Execution Engine: Isolated read-only operations with comprehensive error handling

• Result Serialization: Consistent data format conversion for MCP protocol compatibility

4. Component Organization

• Tools: Executable database operations (schema listing, table inspection, query execution)

• Resources: Static/contextual data endpoints providing database metadata

• Prompts: Dynamic instruction templates guiding AI query generation

Implementation Description

Entry Point Architecture

The main application serves as a transport-aware launcher that initializes the MCP server with appropriate communication protocols. It supports both local development through standard I/O streams and production deployment via HTTP streaming.

Configuration System

Externalized settings management loads database credentials and server parameters from structured configuration files. The system provides fallback mechanisms and environment variable overrides for flexible deployment across different environments.

Database Connection Layer

Asynchronous connection management establishes secure PostgreSQL connections using connection pooling. The abstraction layer handles connection lifecycle, error recovery, and resource cleanup while maintaining connection isolation for concurrent operations.

MCP Tools Implementation

Six specialized tools provide comprehensive database interaction capabilities:

• Health Monitoring: Basic connectivity verification returning server status

• Schema Discovery: Enumerates available database schemas for navigation

• Table Enumeration: Lists tables within specified schemas with metadata

• Schema Inspection: Retrieves detailed column information and constraints

• Query Execution: Safe SQL execution with forbidden operation filtering

• Performance Analysis: Query optimization insights through EXPLAIN plan generation

Resource Management

Contextual data endpoints serve static database information and schema-specific guidance. These provide AI assistants with domain knowledge about table relationships, data types, and common query patterns without requiring direct database inspection.

Prompt Engineering

Dynamic instruction templates guide AI assistants in generating appropriate database queries. The system provides structured workflows for safe query construction, emphasizing read-only operations and performance considerations.

Testing Framework

Connection validation utilities enable developers to verify database connectivity and explore schema structures. The testing module provides diagnostic capabilities for troubleshooting deployment issues and validating configuration correctness.

Setup

1. Install dependencies:

2. Configure database connection in config.yaml

3. Install Cloudflare Tunnel (for exposing server):

Running the Server

Local Development (stdio)

Expose via Cloudflare Tunnel (SSE)

1. Start the server (defaults to SSE transport):

The server will start on http://127.0.0.1:8000 by default.

2. In another terminal, start Cloudflare tunnel:

Cloudflare will provide a public URL (e.g., https://xxxxx.trycloudflare.com) that you can use to access your MCP server.

Environment Variables

• MCP_TRANSPORT: Transport type - sse (default) or stdio

• MCP_HOST: Host address (default: 127.0.0.1)

• MCP_PORT: Port number (default: 8000)

Example:

Tools Available

• ping: Health check

• list_schemas: List all database schemas

• list_tables: List tables in a schema

• get_table_info: Get table schema information

• run_sql_query: Execute read-only SQL queries

• run_explain_query: Get query performance metrics

Resources

• db://context: Database context information

• db://schema/{schema_name}: Schema-specific context

Prompts

• get_table_data_prompt: Prompt to generate queries for table data

Notes

• The server uses main.py as the entry point

• SSE transport is used for HTTP/network access (Cloudflare tunnel)

• stdio transport is used for local development