Semantic PostgreSQL MCP Server

Semantic PostgreSQL MCP Server

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

What is MCP?

Model Context Protocol (MCP) is an open standard developed by Anthropic that connects AI assistants to external systems where data lives, 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.

What This MCP Server Does

This MCP server connects Claude (or any MCP-compatible AI assistant) to a PostgreSQL database with semantic search capabilities. It allows the AI assistant to:

• Understand your database structure semantically: 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 plain English and get relevant SQL queries executed.
• Perform hybrid search: Combines keyword matching with semantic understanding for more accurate results.
• Provide context-aware responses: The AI has access to comprehensive metadata about your database schema.

Key Features

• Vector-based semantic search: Uses OpenAI's embeddings to understand semantic meaning.
• Metadata-driven: Stores rich descriptions of tables and columns in vector format.
• No schema modification needed: Your existing database tables remain unchanged; metadata is stored separately.
• Simple integration with Claude Desktop: Easy configuration with Claude Desktop.

Prerequisites

• PostgreSQL 13+ with pgvector extension
• Node.js 18+
• OpenAI API key (for generating embeddings)
• Claude Desktop (or any MCP-compatible client)

Installation

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 .env with your PostgreSQL and OpenAI credentials

# Start the server
npm start

3. Configure Claude Desktop

Add the following to your Claude Desktop configuration file (typically 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

Once set up, you can ask Claude questions like:

• "Show me all customer information"
• "What products are currently low in stock?"
• "Find orders with a total amount over $1000"
• "Show me the relationship between customers and orders"

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

How It Works

1. Metadata Storage: The system stores detailed descriptions of your database schema (tables, columns, relationships) with vector embeddings.
2. Query Understanding: When you ask a question, the system converts it to an embedding and finds semantically similar columns/tables.
3. SQL Generation: Based on the matched metadata, the system generates appropriate SQL queries.
4. Result Presentation: The results are formatted and presented to the user in a readable format.

Project Structure

• index.js - Main MCP server implementation
• schema.sql - Database schema with metadata tables and sample data
• .env.example - Example environment configuration
• package.json - Node.js dependencies

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT