What is Ia Mcp Pg

ia-mcp-pg is a Model Control Protocol (MCP) server designed for PostgreSQL, implementing the MCP protocol specification for Cursor integration.

Use cases

Use cases for ia-mcp-pg include building web applications that need to interact with PostgreSQL databases, data analysis tools that require querying capabilities, and backend services that manage database resources.

How to use

To use ia-mcp-pg, install the necessary dependencies with ‘npm install’, configure your environment variables by copying ‘.env.example’ to ‘.env’ and updating the database connection details, then start the server with ‘npm start’.

Key features

Key features include health check endpoints, resource management for PostgreSQL tables, and the ability to execute SQL queries on specified tables.

Where to use

ia-mcp-pg can be used in applications that require database interaction through a standardized protocol, particularly in environments utilizing PostgreSQL for data management.

PostgreSQL MCP Server

This is a Model Control Protocol (MCP) server for PostgreSQL that implements the MCP protocol specification for Cursor integration.

Setup

1. Install dependencies:

npm install

2. Configure your environment variables:

   • Copy .env.example to .env
   • Update the database connection details in .env

3. Start the server:

npm start

MCP Protocol Endpoints

Health Check

• GET /mcp/health
• Returns server status and protocol information

{
  "status": "ok",
  "protocol": "mcp",
  "version": "1.0",
  "resources": [
    "tables"
  ]
}

Resources

• GET /mcp/resources
• Returns the available MCP resources and their operations

{
  "success": true,
  "resources": {
    "tables": {
      "name": "tables",
      "description": "PostgreSQL tables in the database",
      "operations": {
        "list": {
          "method": "GET",
          "description": "List all tables in the database",
          "parameters": {},
          "returns": {
            "type": "array",
            "items": {
              "type": "object",
              "properties": {
                "name": {
                  "type": "string"
                },
                "columns": {
                  "type": "array",
                  "items": {
                    "type": "string"
                  }
                }
              }
            }
          }
        },
        "query": {
          "method": "POST",
          "description": "Execute a query on a specific table",
          "parameters": {
            "query": {
              "type": "string",
              "description": "SQL query to execute"
            },
            "params": {
              "type": "array",
              "items": {
                "type": "any"
              },
              "optional": true
            }
          },
          "returns": {
            "type": "object",
            "properties": {
              "success": {
                "type": "boolean"
              },
              "data": {
                "type": "array",
                "items": {
                  "type": "object"
                }
              },
              "rowCount": {
                "type": "number"
              },
              "fields": {
                "type": "array",
                "items": {
                  "type": "object",
                  "properties": {
                    "name": {
                      "type": "string"
                    },
                    "dataTypeID": {
                      "type": "number"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Schema

• GET /mcp/schema
• Returns the database schema in MCP format

{
  "success": true,
  "schema": {
    "table_name": {
      "columns": {
        "column_name": {
          "type": "data_type",
          "nullable": true,
          "default": "default_value"
        }
      }
    }
  }
}

Tables Resource

List Tables

• GET /mcp/tables
• Lists all tables in the database

{
  "success": true,
  "data": [
    {
      "table_name": "users",
      "columns": [
        "id",
        "name",
        "email"
      ]
    }
  ]
}

Query Tables

• POST /mcp/tables/query
• Executes SQL queries on tables
• Body:

{
  "query": "SELECT * FROM users WHERE id = $1",
  "params": [
    1
  ]
}

• Response:

Adding to Cursor

Add this server to your Cursor MCP servers configuration:

{
  "name": "PostgreSQL MCP",
  "type": "mcp",
  "url": "http://localhost:3000",
  "headers": {
    "Content-Type": "application/json"
  }
}