Enhanced PostgreSQL MCP server

The PostgreSQL MCP Server provides a Model Context Protocol implementation that allows Language Models to interact with PostgreSQL databases. This enhanced version enables both reading and writing capabilities, including data querying, data modification, and schema management operations.

Installation

You can use the PostgreSQL MCP Server with Claude Desktop by adding it to your configuration in one of two ways:

Using Docker

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run", 
        "-i", 
        "--rm", 
        "mcp/postgres", 
        "postgresql://host.docker.internal:5432/mydb"]
    }
  }
}

Notes:

• When running Docker on macOS, use host.docker.internal if the server is running on the host network
• Username/password can be added to the PostgreSQL URL: postgresql://user:password@host:port/db-name
• Add ?sslmode=no-verify to bypass SSL certificate verification if needed

Using NPX

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://localhost/mydb"
      ]
    }
  }
}

Remember to replace mydb with your actual database name.

Using the MCP Server

The PostgreSQL MCP Server provides several tools that let you interact with your database.

Querying Data

To execute read-only SQL queries:

/query SELECT * FROM users LIMIT 5

All queries are executed within a READ ONLY transaction.

Modifying Data

Execute SQL Statements

To run SQL statements that modify data:

/execute INSERT INTO users (name, email) VALUES ('Jane Doe', '[email protected]')

Insert Records

To insert data using a simplified command:

/insert table="users", data={"name": "John Doe", "email": "[email protected]"}

Update Records

To update existing records:

/update table="users", data={"status": "inactive"}, where="id='123'"

Delete Records

To delete records:

/delete table="users", where="email='[email protected]'"

Managing Database Schema

Creating Tables

/createTable tableName="tasks", columns=[
  {"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"}, 
  {"name": "title", "type": "VARCHAR(100)", "constraints": "NOT NULL"},
  {"name": "created_at", "type": "TIMESTAMP", "constraints": "DEFAULT CURRENT_TIMESTAMP"}
]

Creating Functions

/createFunction name="update_timestamp", parameters="", returnType="TRIGGER", language="plpgsql", body="BEGIN NEW.updated_at = NOW(); RETURN NEW; END;"

Creating Triggers

/createTrigger name="set_timestamp", tableName="tasks", functionName="update_timestamp", when="BEFORE", events=["UPDATE"], forEach="ROW"

Creating Indexes

/createIndex tableName="users", indexName="idx_user_email", columns=["email"], unique=true

Altering Tables

/alterTable tableName="users", operation="ADD COLUMN", details="login_count INTEGER DEFAULT 0"

Resources

The server automatically exposes schema information for all tables in the connected database:

• Table schemas are available at postgres://<host>/<table>/schema
• These schemas include column names and data types
• This information is automatically discovered from database metadata

Security Features

The PostgreSQL MCP Server includes several security features:

• All data modification operations use transactions with proper COMMIT/ROLLBACK handling
• Each operation returns the SQL that was executed for transparency
• Parameterized queries are used for insert/update operations to prevent SQL injection

How to install this MCP server

For Claude Code

To add this MCP server to Claude Code, run this command in your terminal:

claude mcp add-json "postgres" '{"command":"npx","args":["-y","@modelcontextprotocol/server-postgres","postgresql://localhost/mydb"]}'

See the official Claude Code MCP documentation for more details.

For 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 > Tools & Integrations and click "New MCP Server".

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

{
    "mcpServers": {
        "postgres": {
            "command": "npx",
            "args": [
                "-y",
                "@modelcontextprotocol/server-postgres",
                "postgresql://localhost/mydb"
            ]
        }
    }
}

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 explicitly ask the agent to use the tool by mentioning the tool name and describing what the function does.

For Claude Desktop

To add this MCP server to Claude Desktop:

1. Find your configuration file:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json
• Linux: ~/.config/Claude/claude_desktop_config.json

2. Add this to your configuration file:

{
    "mcpServers": {
        "postgres": {
            "command": "npx",
            "args": [
                "-y",
                "@modelcontextprotocol/server-postgres",
                "postgresql://localhost/mydb"
            ]
        }
    }
}

3. Restart Claude Desktop for the changes to take effect