PostgreSQL MCP Server

A Model Context Protocol server that provides access to PostgreSQL databases. This server enables LLMs to interact with databases to inspect schemas, execute queries, and perform CRUD (Create, Read, Update, Delete) operations on database entries. This repo is an extension of PostgreSQL MCP Server providing functionalities to create tables, insert entries, update entries, delete entries, and drop tables.

Installation

To install the PostgreSQL MCP Server, follow these steps:

1. Install Docker and Claude Desktop
2. Clone the repository: git clone https://github.com/vignesh-codes/ai-agents-mcp-pg.git
3. Run PG Docker container docker run --name postgres-container -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=admin_password -e POSTGRES_DB=mydatabase -p 5432:5432 -d postgres:latest
4. Build the mcp server: docker build -t mcp/postgres -f src/Dockerfile .
5. Open Claude Desktop and connect to the MCP server by updating the mcpServers field in claude_desktop_config.json:

Usage with Claude Desktop

To use this server with the Claude Desktop app, add the following configuration to the “mcpServers” section of your claude_desktop_config.json:

Docker

• When running Docker on macOS, use host.docker.internal if the server is running on the host network (e.g., localhost).
• Username/password can be added to the PostgreSQL URL with postgresql://user:password@host:port/db-name.

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "mcp/postgres",
        "postgresql://username:[email protected]:5432/mydatabase"
      ]
    }
  }
}

Make sure to restart the claude desktop app after updating the config file.

Features Added

Existing Functionality

• query
  • Execute read-only SQL queries against the connected database.
  • Input: sql (string): The SQL query to execute.
  • All queries are executed within a READ-ONLY transaction.

New Functionality

1. Create Tables

   • Ability to create new tables dynamically by providing a table name and column definitions.
   • Input from Claude Desktop:

     {
       "tableName": "example_table",
       "columns": [
         { "name": "id", "type": "SERIAL PRIMARY KEY" },
         { "name": "name", "type": "VARCHAR(255)" },
         { "name": "age", "type": "INTEGER" }
       ]
     }
     

2. Insert Entries

   • Insert new entries into a specified table.
   • Input from Claude Desktop:

     {
       "tableName": "example_table",
       "values": {
         "name": "John Doe",
         "age": 30
       }
     }
     

3. Update Entries

   • Update existing entries in a table based on conditions.
   • Input from Claude Desktop: