Metadata-Version: 2.4
Name: nuuly-postgres-mcp-server
Version: 1.0.2
Summary: MCP server for accessing Nuuly PostgreSQL database resources
Author-email: Nuuly Engineering <engineering@nuuly.com>
License: Proprietary
Project-URL: Homepage, https://github.com/urbn/r15-mcp
Project-URL: Repository, https://github.com/urbn/r15-mcp
Requires-Python: >=3.8
Description-Content-Type: text/markdown
Requires-Dist: fastmcp>=2.2.1
Requires-Dist: httpx>=0.24.0
Requires-Dist: psycopg2-binary>=2.9.5

# Nuuly Postgres MCP Server

This MCP server provides AI code editors with access to PostgreSQL databases through the Model Context Protocol (MCP). It allows AI assistants to understand database schemas, tables, and to run queries against non-prod PostgreSQL databases. This implementation acts as a client that forwards requests to a remote PostgreSQL MCP server running on Google Cloud Run.

## Features

- List available PostgreSQL databases and their aliases
- Get detailed schema information for all tables in a database
- Run SQL queries against PostgreSQL databases

## Prerequisites

- Python 3.8+

## Installation

### Step 1: Install Python (Mac Users)

**Option A: Using Homebrew (Recommended)**

1. Open Terminal
2. Check if Homebrew is installed by typing: `brew --version`
3. If Homebrew is not installed, install it by copying and pasting this command:
   ```bash
   /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
   ```
4. Once Homebrew is installed, install Python:
   ```bash
   brew install python
   ```

**Option B: Download Python directly**

1. Go to [python.org](https://www.python.org/downloads/)
2. Download Python 3.8 or newer
3. Run the installer and follow the prompts

### Step 2: Install the Postgres MCP Server

1. Open Terminal
2. Install the server by running:
   ```bash
   pip install nuuly-postgres-mcp-server
   ```
3. Wait for the installation to complete

### Step 3: Find the Installation Path

You'll need to find where the server was installed to configure Claude:

1. In Terminal, run:
   ```bash
   which nuuly-postgres-mcp
   ```
2. This will show something like:   
   `/Users/yourname/Library/Python/3.12/bin/nuuly-postgres-mcp`
3. **Copy this path** - you'll need it for the next step

### Step 4: Configure Claude Desktop

1. Open Claude Desktop application
2. Find the "MCP Servers" section
3. Click "Edit Config" or open the configuration file
4. Add the following configuration, replacing the path with your actual path from Step 3:

```json
{
  "nuuly-postgres-mcp": {
    "command": "/Users/yourname/Library/Python/3.12/bin/nuuly-postgres-mcp",
    "env": {
      "PYTHONUNBUFFERED": "1",
      "POSTGRES_MCP_SERVER_URL": "https://postgres-mcp-toolbox-186512416539.us-east4.run.app",
      "DB_PROXY_API_KEY": "YOUR_API_KEY_HERE"
    }
  }
}
```

**Environment URLs:**
- **Dev** (rental-dev): `https://postgres-mcp-toolbox-186512416539.us-east4.run.app`
- **Staging** (rental-staging): `https://postgres-mcp-toolbox-526194183507.us-east4.run.app`

Use `POSTGRES_MCP_SERVER_URL` to switch between dev and staging.

### Step 5: Restart Claude

1. Restart Claude Desktop
2. The PostgreSQL tools should now be available

## Available Tools

The server provides the following tools, which are forwarded to the remote PostgreSQL MCP server:

### 1. list_databases

Lists all databases and their aliases accessible via the Cloud Run DB Proxy.

```python
list_databases()
```

Example response:
```json
{
  "databases": [
    {
      "name": "orders",
      "aliases": ["orders_db", "order_system"],
      "instance": "rental-dev:us-east4:orders-db"
    },
    {
      "name": "products",
      "aliases": ["product_db"],
      "instance": "rental-dev:us-east4:product-db"
    }
  ]
}
```

### 2. get_schema

Gets the schema of all tables in the specified database.

```python
get_schema(database="orders")
```

Example response:
```json
{
  "tables": [
    {
      "name": "orders",
      "columns": [
        {
          "name": "id",
          "type": "integer",
          "nullable": false,
          "primary_key": true
        },
        {
          "name": "customer_id",
          "type": "integer",
          "nullable": false,
          "foreign_key": {
            "table": "customers",
            "column": "id"
          }
        },
        {
          "name": "created_at",
          "type": "timestamp",
          "nullable": false
        }
      ]
    }
  ],
  "success": true
}
```

### 3. run_query

Runs a SQL query against the database and returns results.

```python
run_query(database="orders", sql="SELECT * FROM orders LIMIT 10")
```

Example response:
```json
{
  "columns": ["id", "customer_id", "created_at"],
  "rows": [
    [1001, 5001, "2023-01-15T10:30:00Z"],
    [1002, 5002, "2023-01-15T11:45:00Z"]
  ],
  "count": 2,
  "success": true
}
```

## Backend Deployment

The Postgres MCP client forwards requests to a Cloud Run DB Proxy. The backend is deployed via Terraform in `infra_tools/postgres_mcp_server/terraform/`:

- **Dev**: `rental-dev`, `config/dev.yaml`, Cloud SQL private IPs
- **Staging**: `rental-staging`, `config/staging.yaml`, Cloud SQL + AlloyDB private IPs
- Region: `us-east4`
- VPC Connector: `rental-vpc-connector`

See `infra_tools/postgres_mcp_server/README.md` for build and deploy instructions.

## Troubleshooting

### Connection Issues with Claude Desktop

If you experience connection issues with Claude Desktop, ensure that:

1. The server is running with the correct URL for the Cloud Run DB Proxy
2. The API key is correctly set in your configuration
3. The server is properly handling heartbeat and shutdown messages
4. The JSON-RPC initialization response includes `keepAlive: true` and `supportsHeartbeats: true`

Example curl commands to test the API:
```bash
# Dev
curl -X GET "https://postgres-mcp-toolbox-186512416539.us-east4.run.app/databases" -H 'x-api-key: YOUR_API_KEY_HERE'

# Staging
curl -X GET "https://postgres-mcp-toolbox-526194183507.us-east4.run.app/databases" -H 'x-api-key: YOUR_API_KEY_HERE'
```

### Common Issues

- **Missing API Key**: Ensure the `DB_PROXY_API_KEY` environment variable is set
- **Permission Errors**: Verify that your API key has the necessary permissions
- **Timeout Errors**: Check network latency or increase the timeout settings in the code
- **Heartbeat Issues**: Ensure the MCP server properly implements heartbeat handlers
