PostgreSQL MCP Server 🐘

English | Tiếng Việt

A robust Model Context Protocol (MCP) server for PostgreSQL databases. This server allows AI assistants (like Claude, Antigravity, or VS Code extensions) to safely interact with your PostgreSQL data, perform queries, and inspect schemas.

🌟 Features

• Direct Database Access: Connect to any PostgreSQL database using a connection string.
• Schema Inspection: Easily view tables, columns, constraints, and indexes.
• Full Text Search: Search for content across all text columns in a table.
• Performance Analysis: Run EXPLAIN to see query execution plans.
• Safety First: Separate tools for read-only (SELECT) and modification queries.

🛠️ Available Tools

Tool	Description
list_tables	List all tables in the public schema.
describe_table	Get detailed schema information (columns, types, defaults) for a table.
list_indexes	View all indexes on a specific table.
list_constraints	View Primary Keys, Foreign Keys, and other constraints.
search_in_table	Search for a text string in any text column of a table.
run_read_only_query	Execute safe SELECT queries. Blocks modification commands.
run_modification_query	Execute INSERT, UPDATE, DELETE, CREATE commands.
explain_query	Get the JSON execution plan for performance tuning.

🚀 Installation & Setup

1. Prerequisites

• Node.js (v18 or higher)
• A running PostgreSQL database

2. Installation

Clone this repository and install dependencies:

git clone <your-repo-url>
cd mcp-postgre-server
npm install

3. Configuration (.env)

Create a .env file in the root directory to store your database credentials. Important: Never commit your real .env file to version control.

cp .env.example .env

Edit .env and set your DATABASE_URL:

# Format: postgresql://USER:PASSWORD@HOST:PORT/DATABASE
DATABASE_URL=postgresql://myuser:mypassword@localhost:5432/mydatabase

4. Build

Compile the TypeScript code:

npm run build

🔌 Connect to Clients

VS Code (Claude / MCP Extension) & Antigravity

To use this server with MCP-compliant clients, add the following configuration to your MCP settings file (e.g., mcp_config.json or VS Code extension settings).

Config Path (Example):

• Windows: %APPDATA%\Code\User\globalStorage\mcp-server\mcp_settings.json (varies by extension)
• Antigravity: c:\Users\<User>\.gemini\antigravity\mcp_config.json

JSON Configuration:

{
  "mcpServers": {
    "postgre-server": {
      "command": "node",
      "args": ["D:\\Path\\To\\mcp-postgre-server\\build\\index.js"],
      "env": {
        "DATABASE_URL": "postgresql://myuser:mypassword@localhost:5432/mydatabase"
      }
    }
  }
}

Note: You can either set DATABASE_URL in the .env file (if you run the server from the correct working directory) or pass it directly in the env section of the JSON config as shown above. Using the JSON config is often more reliable for absolute paths.

🔒 Security

• Read-Only Enforcement: The run_read_only_query tool implements a basic check to prevent INSERT/UPDATE/DELETE keywords.
• Input Sanitization: Table names are validated in search tools to prevent SQL injection.

📜 License