chenz4027/postgres-mcp

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides PostgreSQL database access to Claude Desktop. This server enables Claude to interact with your local PostgreSQL database through a secure, well-defined interface.

Features

• Query Execution: Execute custom SQL queries with parameterization for security
• Schema Inspection: List tables, describe table structures, and get schema overviews
• CRUD Operations: Create, read, update, and delete records with structured interfaces
• Security Features: Input validation, parameterized queries, and connection pooling
• Type Safety: Full TypeScript implementation with Zod validation

Available Tools

1. postgres_query - Execute custom SQL queries
2. postgres_list_tables - List all tables in the database
3. postgres_describe_table - Get detailed table information
4. postgres_schema_overview - Get complete database schema
5. postgres_select - Select data with filtering and pagination
6. postgres_insert - Insert new records
7. postgres_update - Update existing records
8. postgres_delete - Delete records

Installation

1. Clone this repository:

   git clone <repository-url>
   cd postgres-mcp
   

2. Install dependencies:

   npm install
   

3. Build the project:

   npm run build
   

Configuration

PostgreSQL Setup

Ensure your PostgreSQL server is running on localhost:5432 with a database named postgres. The default configuration assumes you can connect without username/password (trust authentication).

Claude Desktop Configuration

Add the following to your Claude Desktop configuration file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\\Claude\\claude_desktop_config.json

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"]
    }
  }
}

Replace /path/to/postgres-mcp with the actual path to your project directory.

Usage

1. Start Claude Desktop
2. The PostgreSQL MCP server will automatically connect to your database
3. Use the available tools through Claude's interface

Example Queries

• "Show me all tables in the database"
• "Describe the users table"
• "Select all users where age > 25"
• "Insert a new user with name 'John' and email 'john@example.com'"

Security Considerations

• All queries use parameterized statements to prevent SQL injection
• Input validation using Zod schemas
• Connection pooling with timeouts
• No hardcoded credentials (uses environment/default connection)

Development

# Development mode (watch for changes)
npm run dev

# Build
npm run build

# Lint
npm run lint

# Test
npm test

Troubleshooting

1. Connection Issues: Ensure PostgreSQL is running and accessible
2. Permission Errors: Check database user permissions
3. Tool Not Found: Verify Claude Desktop configuration path

License

MIT