MCP HubMCP Hub
vignesh-codes

ai-agents-mcp-pg

by: vignesh-codes

This repo is an extension of PostgreSQL MCP Server providing functionalities to create tables, insert entries, update entries, delete entries, and drop tables.

15created 12/01/2025
Visit
PostgreSQL
Database

📌Overview

Purpose: To provide a Model Context Protocol server that enables interaction with PostgreSQL databases, allowing for efficient inspection and manipulation of data through large language models (LLMs).

Overview: The PostgreSQL MCP Server offers a robust interface for managing PostgreSQL databases using LLMs. It extends standard functionalities by facilitating the creation, updating, and deletion of database tables and entries, thereby empowering users to utilize SQL operations seamlessly with LLM capabilities.

Key Features:

  • Create Tables: Dynamically create new tables by specifying table names and column definitions, enabling flexible database structure management.

  • Insert Entries: Insert new data entries into designated tables, simplifying the process of data entry and management.

  • Update Entries: Modify existing entries within tables based on specified conditions, supporting the dynamic nature of data storage.

  • Delete Entries: Remove specific entries from tables using defined conditions, ensuring easy data cleanup.

  • Drop Tables: Eliminate entire tables from the database, facilitating comprehensive database management.

  • Query Execution: Execute read-only SQL queries to retrieve information from the database, allowing for effective data analysis and reporting.

  • Schema Information: Automatically retrieve JSON schema details for each table, helping users understand the database structure and data types.

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 the 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 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 .
  5. Open Claude Desktop and connect to the MCP server by updating the mcpServers field in claude_desktop_config.json.

Usage with Claude Desktop

Add the following configuration to the "mcpServers" section of your claude_desktop_config.json:

Docker Notes

  • 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.

Example configuration:

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

Make sure to restart the Claude Desktop app after updating the config file.

Features

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

    • Create new tables dynamically by providing a table name and column definitions.
    • Input format example: 
      {
  "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 example: 
      {
  "tableName": "example_table",
  "values": {
    "name": "John Doe",
    "age": 30
  }
}

  3. Update Entries

    • Update existing entries in a table based on conditions.
    • Input example: 
      {
  "tableName": "example_table",
  "values": {
    "age": 35
  },
  "conditions": "name = 'John Doe'"
}

  4. Delete Entries

    • Delete specific entries in a table based on conditions.
    • Input example: 
      {
  "tableName": "example_table",
  "conditions": "name = 'John Doe'"
}

  5. Drop Tables

    • Drop existing tables from the database.
    • Input example: 
      {
  "tableName": "example_table"
}

Resources

The server provides schema information for each table in the database:

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

Usage Examples

Create Tables and Add Sample Data

Input:

Lets create three tables - users, payments and orders tables. Each table must have a unique id and other required columns. Payments table must have user id as foreign key. Orders table must have payment id as foreign key. Add some sample data to these tables

Output:

  1. Create users table with columns: id, name, email, created_at.
  2. Create payments table with foreign key to users (user_id).
  3. Create orders table with foreign key to payments (payment_id).
  4. Insert sample data into all three tables.
  5. Query each table to verify data.

Summary:

  • Users table holds user information.
  • Payments table linked to users by user_id.
  • Orders table linked to payments by payment_id.

Add More Users and Payments with Different Statuses

Input:

Add few more users with IN_PROGRESS and UNSUCCESSFUL payment status

Output:

  • Added new users.
  • Added payments with statuses: IN_PROGRESS and UNSUCCESSFUL.
  • Added orders for IN_PROGRESS payments.
  • Verified payment statuses.

Query Users with IN_PROGRESS Payment Status

Input:

Fetch the users info who have IN_PROGRESS payment status

Output:

SQL query fetching users and payment details for IN_PROGRESS payments, listing users Mike Johnson and Tom Brown.

Update Payment Status

Input:

Lets update the payment status to completed for Mike Johnson

Output:

  • Updated payment status from IN_PROGRESS to completed for Mike Johnson.
  • Verified the update via SQL query result summary.

Database Entries

Example SQL queries and their output showing tables users, payments, and orders with the current data.

License

This MCP server is licensed under the MIT License. You are free to use, modify, and distribute the software under the terms of this license. See the LICENSE file in the repository for details.