Postgres MCP Server

A Model Context Protocol (MCP) server that provides secure database access to PostgreSQL through Kysely ORM. This server enables Claude Desktop to interact with PostgreSQL databases using natural language.

Features

• MCP Tools: Query execution, table listing, schema inspection, and constraint information
• Type Safety: Full TypeScript support with typed inputs/outputs
• Connection Pooling: Configurable connection limits with idle timeout
• Error Handling: Graceful error messages for connection and query issues
• Security: Parameterized queries to prevent SQL injection

Installation

npx postgres-mcp-server

Configuration

Create a .env file with your database credentials:

DB_HOST=127.0.0.1
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password_here
DB_NAME=postgres
DB_SSL=true

Available Tools

Tool	Description	Required Parameters	Optional Parameters
query	Execute SQL queries with pagination support	sql (string)	pageSize (1-500), offset (number), parameters (array)
describe_table	Get table structure and column details	schema (string), table (string)	-
list_tables	List all tables in a schema	schema (string)	-
list_schemas	List all schemas in the database	-	includeSystemSchemas (boolean)
get_constraints	Get table constraints (PK, FK, etc.)	schema (string), table (string)	-
list_indexes	List indexes for a table or schema	schema (string)	table (string)
list_views	List views in a schema	schema (string)	-
list_functions	List functions and procedures	schema (string)	-
explain_query	Get query execution plan	sql (string)	analyze (boolean), format (text/json/xml/yaml)
get_table_stats	Get table size and statistics	schema (string)	table (string)

Key Features

• Pagination: Query tool supports up to 500 rows per page with automatic LIMIT/OFFSET handling
• Security: Parameterized queries prevent SQL injection, READ_ONLY mode by default
• Type Safety: Full TypeScript support with Zod schema validation

Claude Desktop Configuration

Add this server to your Claude Desktop configuration file:

Edit claude_desktop_config.json:

{
  "mcpServers": {
    "postgres-mcp-server": {
      "command": "npx",
      "args": ["postgres-mcp-server"],
      "env": {
        "DB_HOST": "127.0.0.1",
        "DB_PORT": "5432",
        "DB_USER": "postgres",
        "DB_PASSWORD": "your_password_here",
        "DB_NAME": "your_database_name",
        "DB_SSL": "true"
      }
    }
  }
}

Configuration File Locations

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

Development

# Clone and install dependencies
git clone https://github.com/abiswas97/postgres-mcp-server.git
cd postgres-mcp-server
npm install

# Run in development mode with hot reload
npm run dev

# Build for production
npm run build

# Run tests
npm run test

# Run specific test suites
npm run test:unit
npm run test:integration

Environment Variables

Variable	Default	Description
DB_HOST	127.0.0.1	PostgreSQL host
DB_PORT	5432	PostgreSQL port
DB_USER	postgres	Database user
DB_PASSWORD	required	Database password
DB_NAME	postgres	Database name
DB_SSL	true	Enable SSL connection
READ_ONLY	true	Restrict to SELECT/WITH/EXPLAIN queries
QUERY_TIMEOUT	30000	Query timeout in milliseconds
MAX_PAGE_SIZE	500	Maximum rows per page
DEFAULT_PAGE_SIZE	100	Default page size when not specified

License

ISC