Semantic Postgres MCP

🚀 Semantic PostgreSQL MCP Server

A Model Context Protocol (MCP) server that enables AI assistants like Claude to perform semantic searches on PostgreSQL databases.

🚀 Quick Start

The Semantic PostgreSQL MCP Server is a powerful tool that connects AI assistants such as Claude to PostgreSQL databases, allowing for semantic searches. This README will guide you through the installation, usage, and understanding of how this server works.

✨ Features

What is MCP?

The Model Context Protocol (MCP) is an open - source standard developed by Anthropic. It connects AI assistants to external systems where data resides, including databases, content repositories, and business tools. It provides a standardized way for AI models to interact with external systems through a client - server architecture.

Functions of this MCP Server

This MCP server connects Claude (or other compatible AI assistants) to a PostgreSQL database with semantic search capabilities. It allows AI assistants to:

• Semantically understand the database structure: The server uses vector embeddings to understand the meaning and purpose of tables and columns, not just their names.
• Execute natural language queries: Users can ask questions in simple English and get relevant SQL query results.
• Perform hybrid searches: Combine keyword matching with semantic understanding for more accurate results.
• Provide context - aware responses: AI has full access to the database schema metadata.

Core Features

• Vector - based semantic search: Use OpenAI's embeddings to understand semantic meanings.
• Metadata - driven: Store rich descriptions of tables and columns in vector format.
• No need to modify the existing architecture: Your database tables remain unchanged; metadata is stored separately.
• Simple integration with Claude Desktop: Easy to configure.

📦 Installation

Prerequisites

• PostgreSQL 13+ with the pgvector extension
• Node.js 18+
• OpenAI API key (for generating embeddings)
• Claude Desktop (or other compatible clients)

1. Database Setup

First, install PostgreSQL and the pgvector extension, then run the schema.sql script to create the necessary tables and sample data:

psql -U postgres -f schema.sql

2. MCP Server Setup

# Clone this repository
git clone https://github.com/cpenniman12/semantic-postgres-mcp.git
cd semantic-postgres-mcp

# Install dependencies
npm install

# Configure environment variables
cp .env.example .env
# Edit the .env file with your PostgreSQL and OpenAI credentials

# Start the server
npm start

3. Configure Claude Desktop

Add the following to your Claude Desktop configuration file (usually located at ~/Library/Application Support/Claude/claude_desktop_config.json):

{
  "mcpServers": {
    "semantic-postgres": {
      "command": "node",
      "args": ["/path/to/your/semantic-postgres-mcp/index.js"],
      "env": {
        "OPENAI_API_KEY": "your-openai-api-key"
      }
    }
  }
}

Restart Claude Desktop to connect to the MCP server.

💻 Usage Examples

After setting up, you can ask Claude questions like:

• "Show all customer information."
• "Which products have low current inventory?"
• "Find orders with a total price over $1000."
• "Show the relationship between customers and orders."

Claude will use the semantic search feature to understand your request, find relevant tables and columns, and execute appropriate SQL queries.

🔧 Technical Details

How it Works

1. Metadata storage: The system stores detailed descriptions of the database schema (tables, columns, relationships) and their vector embeddings.
2. Query understanding: When you ask a question, the system converts it into an embedding and finds semantically similar columns/tables.
3. SQL generation: Generate appropriate SQL queries based on the matched metadata.
4. Result presentation: Display query results in a clear and understandable way.

📄 License

No license information provided in the original document.

Contributors

• [Your Name]