MCP PostgreSQL Server

This is an MCP (Model Context Protocol) server that provides read-only access to PostgreSQL databases. The server allows LLMs to inspect database schemas and execute read-only queries.

Features

Resources

• Table Schemas (postgres://<table>/schema)
  • JSON schema information for each table
  • Includes column names and data types
  • Automatically discovered from database metadata

Tools

• query
  • Executes read-only SQL queries on the connected database
  • Input: sql (string): The SQL query to be executed
  • All queries are executed within a READ ONLY transaction

Prompts

• analyze-table: Helps analyze a specific table
• join-tables: Helps create JOIN queries between multiple tables

Installation and Usage

Prerequisites

• Node.js (version 18 or higher)
• Docker and Docker Compose
• WSL2 (if using Windows)

Using Docker (Recommended)

1. Clone the repository:

git clone [repository-url]
cd mcp-postgres

2. Install dependencies:

npm install

3. Compile TypeScript:

npm run build

4. Start containers using Docker Compose:

docker-compose up -d

This will:

• Start a PostgreSQL container with sample data
• Start the MCP server configured to connect to PostgreSQL

The database will be available at:

• Host: localhost
• Port: 5432
• Database: mcp_db
• User: mcp_user
• Password: mcp_password

⚠️ Important Notice for WSL Users: If you're using WSL on Windows, you might encounter connection issues when using "localhost". In this case, you'll need to:

1. Find your WSL IP using the command:

ip addr show eth0 | grep -oP '(?<=inet\s)\d+(\.\d+){3}'

2. Use this IP instead of "localhost" in the PostgreSQL connection string

Cursor Configuration

To use this MCP server with Cursor:

1. Open Cursor
2. Press:
   • Windows/Linux: Ctrl + Shift + P
   • macOS: Cmd + Shift + P
3. Type "Configure MCP Server" and select it
4. Add the following configuration:

If you're using Windows without WSL or Linux:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": [
        "./dist/index.js",
        "postgresql://mcp_user:mcp_password@localhost:5432/mcp_db"
      ]
    }
  }
}

If you're using WSL, you might need to specify the full Node and file paths, as shown in this example:

{
  "mcpServers": {
    "postgres": {
      "command": "wsl.exe",
      "args": [
        "-e",
        "/home/<your-user>/.nvm/versions/node/v20.18.0/bin/node",
        "/home/<your-user>/path/to/mcp-postgres/dist/index.js",
        "postgresql://mcp_user:mcp_password@localhost:5432/mcp_db"
      ]
    }
  }
}

Sample Database

The Docker Compose includes a PostgreSQL database with the following sample tables:

• customers (formerly clientes): Customer information including name, email, gender, birth date, and phone
• addresses: Customer addresses with full location details
• products (formerly produtos): Product catalog with name, description, price, and stock information
• orders (formerly pedidos): Order records with customer, address, and total amount information
• order_items (formerly itens_pedido): Items included in each order with quantity and unit price

You can use these tables to test the MCP server functionalities.

Security

• The server executes read-only queries only
• All queries are executed within READ ONLY transactions
• Automatic rollback after each query to ensure no changes are made

License

This project is licensed under the MIT License.