A
securityA
licenseA
qualityFacilitates management and optimization of PostgreSQL databases, offering analysis, setup guidance, and debugging, while ensuring secure and efficient database operations.
Last updated -
3
016
AGPL 3.0
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
Manages configuration through environment variables using .env files for storing database credentials, server settings, and security parameters.
Supports repository cloning for installation and source code management during development.
Provides example code for API interaction, allowing clients to execute queries and interact with analysis templates.
Handles dependency management and provides scripts for installation, development, testing, and linting.
Connects to a PostgreSQL database, providing tools for schema exploration, read-only SQL query execution, relationship visualization, and data analysis through predefined templates and sample data retrieval.
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
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-serverInstall dependencies:
npm installCreate a
.envfile based on the.env.template:cp .env.template .envUpdate the
.envfile 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 schemasGET /api/schemas/:schema/tables- List tables in a schemaGET /api/schemas/:schema/tables/:table- Get table schema detailsGET /api/schemas/:schema/relationships- Get table relationshipsGET /api/structure- Get complete database structureGET /api/search?q=term- Search tables and columns
Query Endpoints
POST /api/query- Execute a SQL queryPOST /api/query/explain- Get query execution planGET /api/schemas/:schema/tables/:table/sample- Get sample dataGET /api/schemas/:schema/tables/:table/stats- Get table statistics
Analysis Prompt Endpoints
GET /api/prompts- List analysis prompt templatesGET /api/prompts/:templateId- Get prompt template detailsPOST /api/prompts/:templateId/generate- Generate SQL from templateGET /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 devRun tests:
npm testLint code:
npm run lint
License
MIT
A server that connects to PostgreSQL databases and provides tools for safely exploring schemas, running read-only SQL queries, and performing data analysis with pre-built templates.
- Features
- Architecture
- Security Features
- Installation
- Configuration
- API Endpoints
- Example Queries
- Development
- License
Related Resources
Related MCP Servers
- -securityFlicense-qualityProvides read-only access to PostgreSQL databases, enabling users to inspect database schemas and execute read-only queries through a Model Context Protocol server.Last updated -4
- -securityFlicense-qualityA server that allows AI models to interact with PostgreSQL databases through a standardized protocol, providing database schema information and SQL query execution capabilities.Last updated -1
- -securityFlicense-qualityProvides read-only access to PostgreSQL databases, enabling LLMs to inspect database schemas and execute read-only SQL queries within a secure transaction context.Last updated -21,5521
Appeared in Searches
- A database for searching medical and scientific research articles
- PostgreSQL MCP (Multi-Version Concurrency Control or specific usage)
- Information about PostgreSQL MCP (Managed Cloud Provider or other related concepts)
- Search for information about 'rag'
- Overview and Resources for PostgreSQL Database System