Skip to main content
Glama
Sign In
enesjakozh

MCP PostgreSQL Server

by Maxim2324
npmGitHub
JavaScript
MIT License
1,519
1
OverviewInspectNewSchemaRelated ServersReviewsScore
Need Help?View Source CodeReport Issue

Integrations

  • 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.

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

  1. Clone the repository:
    git clone <repository-url> cd mcp-postgres-server
  2. Install dependencies:
    npm install
  3. Create a .env file based on the .env.template:
    cp .env.template .env
  4. Update the .env file with your PostgreSQL database credentials.
  5. 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

You must be authenticated.

A
security – no known vulnerabilities
A
license - permissive license
A
quality - confirmed to work

remote-capable server

The server can be hosted and run remotely because it primarily relies on remote services or has no dependency on the local environment.

Tools

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.

  1. Current Implementations
    1. 1. MCP PostgreSQL Server (mcp-psql/)
    2. 2. MCP Figma Server (mcp-figma/)
  2. Purpose
    1. Getting Started
      1. Contributing
        1. License

          Related Resources

          Related MCP Servers

          • PostgreSQL

            modelcontextprotocol
            -
            security
            A
            license
            -
            quality
            A Model Context Protocol server that provides read-only access to PostgreSQL databases. This server enables LLMs to inspect database schemas and execute read-only queries.
            Last updated a day ago
            1
            19,917
            42,199
            JavaScript
            MIT License
            • Apple
            • Linux

          • PostgreSQL MCP Server

            nahmanmate
            A
            security
            A
            license
            A
            quality
            Facilitates management and optimization of PostgreSQL databases, offering analysis, setup guidance, and debugging, while ensuring secure and efficient database operations.
            Last updated 20 days ago
            3
            4
            TypeScript
            AGPL 3.0
            • Linux
            • Apple

          • PostgreSQL MCP Server

            vinsidious
            -
            security
            F
            license
            -
            quality
            Provides 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 2 months ago
            3
            JavaScript

          • Model Context Protocol PostgreSQL Server

            endaoment
            -
            security
            F
            license
            -
            quality
            A 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 2 months ago
            JavaScript

          View all related MCP servers

          Appeared in Searches

          ID: vaztx3bu73