vignesh-codes/ai-agents-mcp-pg

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 POSTGRESUSER=admin -e POSTGRESPASSWORD=adminpassword -e POSTGRESDB=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 claudedesktopconfig.json:
## Usage with Claude Desktop
To use this server with the Claude Desktop app, add the following configuration to the "mcpServers" section of your claudedesktopconfig.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.
json
"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 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:
json
"tableName": "exampletable",
"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:
json
"tableName": "exampletable",
"values": {
"name": "John Doe",
"age": 30
3. Update Entries
Update existing entries in a table based on conditions.
Input from Claude Desktop:
json
"tableName": "exampletable",
"values": {
"age": 35
},
"conditions": "name = 'John Doe'"
4. Delete Entries
Delete specific entries in a table based on conditions.
Input from Claude Desktop:
json
"tableName": "exampletable",
"conditions": "name = 'John Doe'"
5. Drop Tables
Drop existing tables from the database.
Input from Claude Desktop:
json
"tableName": "exampletable"
### Resources
The server provides schema information for each table in the database:
Table Schemas (postgres:// / /schema)
JSON schema information for each table.
Includes column names and data types.
Automatically discovered from database metadata.
## Sample
### Input to Claude Desktop
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