# PostgreSQL MCP Server

An enterprise-ready **Model Context Protocol (MCP)** server built with Node.js & TypeScript for PostgreSQL databases. Compatible with **Antigravity**, **Claude Desktop**, **Cursor**, **Continue**, and any standard MCP client.

---

## 🌟 Features & Tools Exposed

| Tool Name | Mode | Description |
|---|---|---|
| `list_tables` | Read-only | Lists all schemas, tables, views, estimated row counts, and table disk sizes. |
| `describe_table` | Read-only | Details table schema: column types, nullability, defaults, primary keys, foreign keys, and indexes. |
| `read_query` | Read-only | Executes SELECT queries safely inside DB-level `READ ONLY` transactions with auto-limiting. |
| `explain_query` | Read-only | Generates PostgreSQL query execution plans (`EXPLAIN` / `EXPLAIN ANALYZE`) in JSON format. |
| `get_db_stats` | Read-only | Returns DB health stats: database size, top 10 largest tables, active connections, and cache hit ratios. |
| `execute_query` | Write (Optional) | Executes `INSERT`, `UPDATE`, `DELETE`, or DDL statements (disabled unless `ALLOW_WRITE_QUERIES=true`). |

---

## 🛠️ Quick Start

### 1. Install Dependencies & Build
```bash
npm install
npm run build
```

### 2. Configure Environment Variables
Copy `.env.example` to `.env` and fill in your PostgreSQL credentials:

```env
# Database Connection String
DATABASE_URL=postgresql://postgres:password@localhost:5432/mydb

# OR individual settings:
PGHOST=localhost
PGPORT=5432
PGUSER=postgres
PGPASSWORD=your_password
PGDATABASE=mydb

# Enable write capabilities if needed (Default: false)
ALLOW_WRITE_QUERIES=false
```

---

## 🚀 Registering with MCP Clients

This server uses standard `stdio` communication and can be added to any MCP-compliant application.

### 1. Antigravity Configuration
Add to your Antigravity MCP settings:
- **Global Config**: `%USERPROFILE%\.gemini\antigravity-cli\mcp_config.json` (Windows) or `~/.gemini/antigravity-cli/mcp_config.json` (Linux/macOS)
- **Workspace Config**: `.agents/mcp.json`

```json
{
  "mcpServers": {
    "postgres-db": {
      "command": "node",
      "args": [
        "D:/Projects/AI/MCP/postgres-connector/dist/index.js"
      ],
      "env": {
        "DATABASE_URL": "postgresql://your_user:your_password@localhost:5432/your_project_db",
        "ALLOW_WRITE_QUERIES": "false"
      }
    }
  }
}
```

### 2. Claude Desktop Configuration
Add to your Claude Desktop configuration file:
- **Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
- **macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`

```json
{
  "mcpServers": {
    "postgres-db": {
      "command": "node",
      "args": [
        "D:/Projects/AI/MCP/postgres-connector/dist/index.js"
      ],
      "env": {
        "DATABASE_URL": "postgresql://your_user:your_password@localhost:5432/your_project_db",
        "ALLOW_WRITE_QUERIES": "false"
      }
    }
  }
}
```

### 3. Cursor / Continue / Standard MCP Clients
For Cursor, Continue, or any other MCP host supporting stdio servers:
```json
{
  "mcpServers": {
    "postgres-db": {
      "command": "node",
      "args": [
        "D:/Projects/AI/MCP/postgres-connector/dist/index.js"
      ],
      "env": {
        "DATABASE_URL": "postgresql://your_user:your_password@localhost:5432/your_project_db",
        "ALLOW_WRITE_QUERIES": "false"
      }
    }
  }
}
```

> **Tip (Development Mode):** You can also run the server directly with `npx tsx` without a build step:
> ```json
> "command": "npx",
> "args": [
>   "-y",
>   "tsx",
>   "D:/Projects/AI/MCP/postgres-connector/src/index.ts"
> ]
> ```

---

## 🧪 Testing & Local Debugging

### Option 1: Interactive GUI Testing with MCP Inspector (Recommended)
The official **MCP Inspector** allows you to visually trigger tool calls (`list_tables`, `read_query`, `describe_table`, etc.) and view response payloads in your browser:

- **Quick development testing (no compilation step required):**
  ```bash
  npx @modelcontextprotocol/inspector npx tsx src/index.ts
  ```

- **Production build testing:**
  ```bash
  npm run build
  npx @modelcontextprotocol/inspector node dist/index.js
  ```

The command will automatically open the inspector in your default browser (e.g. `http://localhost:<port>/?MCP_INSPECTOR_API_TOKEN=...`), where you can inspect available tools, fill in parameters, and view responses in real time.

### Option 2: Live Integration via MCP Hosts
Register the server in your AI host config (such as Antigravity or Claude Desktop) as detailed in [Registering with MCP Clients](#-registering-with-mcp-clients), then restart your host application to test tool invocation through natural language prompts.

### Option 3: Code Compilation & Type Check
Verify TypeScript types and `esbuild` bundle output:
```bash
npm run build
```

---

## 🔒 Security Best Practices
- By default, all queries executed via `read_query` run inside `BEGIN READ ONLY; ... COMMIT;` transactions to prevent unexpected data mutations.
- Write queries via `execute_query` are disabled by default unless `ALLOW_WRITE_QUERIES=true`.

---

## 📄 License & Attribution

This project is licensed under the **[BSD 3-Clause License](file:///d:/Projects/AI/MCP/postgres-connector/LICENSE)**.

### Mandatory Terms:
- **Attribution Required**: You are free to use, modify, and distribute this software, provided that all copies retain the original copyright notice (`Copyright (c) 2026, Rajath Kumar D`) and license terms.
- **No Unauthorized Endorsement**: You may not use the author's name (**Rajath Kumar D**) or contributors to endorse or promote derived products without prior written permission.