Skip to main content
Glama
Sign In
enesjakozh

MCP PostgreSQL Server

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

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

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. Features
    1. Architecture
      1. Security Features
        1. Installation
          1. Configuration
            1. API Endpoints
              1. Schema Endpoints
              2. Query Endpoints
              3. Analysis Prompt Endpoints
            2. Example Queries
              1. Basic Table Query
              2. Using Analysis Prompts
            3. Development
              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 -
                  1
                  16,948
                  49,637
                  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 -
                  3
                  6
                  12
                  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 -
                  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 -
                  JavaScript

                View all related MCP servers

                Appeared in Searches

                New MCP Servers

                MCP directory API

                We provide all the information about MCP servers via our MCP API.

                curl -X GET 'https://glama.ai/api/mcp/v1/servers/Maxim2324/mcp-server-test'

                If you have feedback or need assistance with the MCP directory API, please join our Discord server