PostgreSQL MCP server

The PostgreSQL MCP Server provides an interface for LLMs to interact with PostgreSQL databases, enabling schema inspection, query execution, and CRUD operations. This server extends the base PostgreSQL MCP Server with additional functionality for creating tables, inserting entries, updating data, deleting entries, and dropping tables.

Installation

To install the PostgreSQL MCP Server:

1. Install Docker and Claude Desktop
2. Clone the repository:

   git clone https://github.com/vignesh-codes/ai-agents-mcp-pg.git
   

3. Run the PostgreSQL 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 .
   

Configuration with Claude Desktop

To connect the MCP server with Claude Desktop, add the following to the "mcpServers" section of your claude_desktop_config.json:

Docker Configuration

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

Note:

• On macOS, use host.docker.internal if the server is running on the host network
• Add username/password directly to the PostgreSQL URL: postgresql://user:password@host:port/db-name
• Restart Claude Desktop after updating the configuration

Features

Query Execution

Execute read-only SQL queries against the database:

{
  "sql": "SELECT * FROM users;"
}

Create Tables

Create new tables by specifying column definitions:

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

Insert Entries

Add new data to existing tables:

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

Update Entries

Modify existing data based on conditions:

{
  "tableName": "example_table",
  "values": {
    "age": 35
  },
  "conditions": "name = 'John Doe'"
}

Delete Entries

Remove data from tables:

{
  "tableName": "example_table",
  "conditions": "name = 'John Doe'"
}

Drop Tables

Remove tables from the database:

{
  "tableName": "example_table"
}

Schema Information

The server automatically provides schema information for each table in the database through the resource postgres://<host>/<table>/schema, which includes:

• Column names
• Data types
• Table relationships
• Other schema metadata

Usage Example

Here's an example workflow creating related tables and working with data:

1. Create a users table:

{
  "columns": [
    {"name": "id", "type": "INTEGER PRIMARY KEY"},
    {"name": "name", "type": "TEXT"},
    {"name": "email", "type": "TEXT"},
    {"name": "created_at", "type": "TEXT"}
  ],
  "tableName": "users"
}

2. Create a payments table with foreign key:

{
  "columns": [
    {"name": "id", "type": "INTEGER PRIMARY KEY"},
    {"name": "user_id", "type": "INTEGER"},
    {"name": "amount", "type": "REAL"},
    {"name": "status", "type": "TEXT"},
    {"name": "payment_date", "type": "TEXT"}
  ],
  "tableName": "payments"
}

3. Insert data:

{
  "values": {
    "id": "1",
    "name": "John Doe",
    "email": "[email protected]",
    "created_at": "2024-01-01"
  },
  "tableName": "users"
}

4. Query data:

{
  "sql": "SELECT u.name, p.amount, p.status FROM users u JOIN payments p ON u.id = p.user_id;"
}

5. Update data:

{
  "values": {
    "status": "completed"
  },
  "tableName": "payments",
  "conditions": {
    "user_id": "3"
  }
}

How to add this MCP server to Cursor

There are two ways to add an MCP server to Cursor. The most common way is to add the server globally in the ~/.cursor/mcp.json file so that it is available in all of your projects.

If you only need the server in a single project, you can add it to the project instead by creating or adding it to the .cursor/mcp.json file.

Adding an MCP server to Cursor globally

To add a global MCP server go to Cursor Settings > MCP and click "Add new global MCP server".

When you click that button the ~/.cursor/mcp.json file will be opened and you can add your server like this:

{
    "mcpServers": {
        "cursor-rules-mcp": {
            "command": "npx",
            "args": [
                "-y",
                "cursor-rules-mcp"
            ]
        }
    }
}

Adding an MCP server to a project

To add an MCP server to a project you can create a new .cursor/mcp.json file or add it to the existing one. This will look exactly the same as the global MCP server example above.

How to use the MCP server

Once the server is installed, you might need to head back to Settings > MCP and click the refresh button.

The Cursor agent will then be able to see the available tools the added MCP server has available and will call them when it needs to.

You can also explictly ask the agent to use the tool by mentioning the tool name and describing what the function does.