The MCP PostgreSQL Server provides structured, AI-enabled interaction with PostgreSQL databases through a safe and validated API. You can:

Connect to Databases: Establish connections using connection details (default connections available)
Execute Queries: Run parameterized SELECT queries for safe data retrieval
Modify Data: Perform INSERT, UPDATE, and DELETE operations
Explore Schema: List all tables and describe specific table structures
Validate Operations: Ensure query safety through read-only validation
Use Templates: Access pre-built analysis templates for streamlined data analysis
Support AI Systems: Enable structured database access for AI-powered analysis

MCP PostgreSQL Server

A Model-Controller-Provider (MCP) server that:

Connects to a PostgreSQL database
Exposes table schemas as resources
Provides tools for running read-only SQL queries
Includes prompts for common data analysis tasks

Features

Schema Exploration: Browse database schemas, tables, and columns
Read-only Query Execution: Safely run SQL queries against your database
Data Analysis Prompts: Pre-built SQL templates for common analysis tasks
Data Visualization: Generate data for visualization
Relationship Exploration: Visualize table relationships and foreign keys
API Documentation: Auto-generated OpenAPI specification

Related MCP server: PostgreSQL MCP Server

Architecture

This application follows the Model-Controller-Provider (MCP) pattern:

Model Layer: Direct interaction with the database
Provider Layer: Business logic and data processing
Controller Layer: API endpoints and request handling

Security Features

Read-only query validation
SQL injection protection
Rate limiting
Parameterized queries
Authentication support
CORS configuration

Installation

Clone the repository:
git clone <repository-url> cd mcp-postgres-server
Install dependencies:
npm install
Create a .env file based on the .env.template:
cp .env.template .env
Update the .env file with your PostgreSQL database credentials.
Start the server:
npm start

Configuration

All configuration is managed through environment variables:

Server: Port, environment, CORS settings
Database: Connection details, pool settings
Security: JWT settings, rate limiting
Query: Execution limits, result size limits

API Endpoints

Schema Endpoints

GET /api/schemas - List all schemas
GET /api/schemas/:schema/tables - List tables in a schema
GET /api/schemas/:schema/tables/:table - Get table schema details
GET /api/schemas/:schema/relationships - Get table relationships
GET /api/structure - Get complete database structure
GET /api/search?q=term - Search tables and columns

Query Endpoints

POST /api/query - Execute a SQL query
POST /api/query/explain - Get query execution plan
GET /api/schemas/:schema/tables/:table/sample - Get sample data
GET /api/schemas/:schema/tables/:table/stats - Get table statistics

Analysis Prompt Endpoints

GET /api/prompts - List analysis prompt templates
GET /api/prompts/:templateId - Get prompt template details
POST /api/prompts/:templateId/generate - Generate SQL from template
GET /api/schemas/:schema/tables/:table/analysis/suggest - Get analysis suggestions

Example Queries

Basic Table Query

// API request fetch('/api/query', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ sql: 'SELECT * FROM users LIMIT 10' }) }) .then(response => response.json()) .then(data => console.log(data));

Using Analysis Prompts

// Get suggested analysis for a table fetch('/api/schemas/public/tables/orders/analysis/suggest') .then(response => response.json()) .then(suggestions => { // Use a suggestion const suggestionId = suggestions.data[0].templateId; const params = suggestions.data[0].params; // Generate SQL from the template return fetch(`/api/prompts/${suggestionId}/generate`, { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ params }) }); }) .then(response => response.json()) .then(data => { // Execute the generated SQL return fetch('/api/query', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ sql: data.data.sql }) }); }) .then(response => response.json()) .then(results => console.log(results));

Development

Run in development mode: npm run dev
Run tests: npm test
Lint code: npm run lint

License

MIT

MCP PostgreSQL Server

MCP PostgreSQL Server

Features

Architecture

Security Features

Installation

Configuration

API Endpoints

Schema Endpoints

Query Endpoints

Analysis Prompt Endpoints

Example Queries

Basic Table Query

Using Analysis Prompts

Development

License

Resources

Tools

Appeared in Searches

New MCP Servers

Latest Blog Posts

MCP directory API