# PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides access to PostgreSQL database schemas and metadata. This server allows Cursor and other MCP-compatible tools to explore your database structure, inspect table schemas, and understand relationships.

## Features

- **List Tables**: Get all tables in a schema with basic information
- **Describe Table**: Get detailed column information for any table
- **Table Relationships**: Explore foreign key relationships
- **List Schemas**: See all available schemas in your database
- **Index Information**: View indexes for specific tables

## Prerequisites

- Node.js 18+ 
- PostgreSQL database (local or remote)
- npm or yarn package manager

## Installation

1. **Clone or download this repository**

2. **Install dependencies**:
   ```bash
   npm install
   ```

3. **Configure database connection**:
   ```bash
   cp config.env.example .env
   ```
   
   Edit the `.env` file with your PostgreSQL connection details:
   ```env
   DB_HOST=localhost
   DB_PORT=5432
   DB_NAME=your_database_name
   DB_USER=your_username
   DB_PASSWORD=your_password
   ```

4. **Test the server** (optional):
   ```bash
   npm start
   ```

## Cursor Integration

To use this MCP server with Cursor, you need to add it to your Cursor configuration.

### Step 1: Add to Cursor MCP Configuration

Open Cursor and go to Settings → Features → Model Context Protocol, or edit your MCP configuration file directly.

Add the following configuration:

```json
{
  "mcpServers": {
    "postgres-schema": {
      "command": "node",
      "args": ["/absolute/path/to/pg_mcp/src/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432", 
        "DB_NAME": "your_database_name",
        "DB_USER": "your_username",
        "DB_PASSWORD": "your_password"
      }
    }
  }
}
```

**Important**: Replace `/absolute/path/to/pg_mcp/src/index.js` with the actual absolute path to your `src/index.js` file.

### Step 2: Alternative Configuration (Using .env file)

If you prefer to use the `.env` file for configuration:

```json
{
  "mcpServers": {
    "postgres-schema": {
      "command": "node",
      "args": ["/absolute/path/to/pg_mcp/src/index.js"],
      "cwd": "/absolute/path/to/pg_mcp"
    }
  }
}
```

### Step 3: Restart Cursor

After adding the configuration, restart Cursor to load the MCP server.

## Available Tools

Once configured, you'll have access to these tools in Cursor:

### `list_tables`
Lists all tables in a specified schema (defaults to 'public')
```
Parameters:
- schema (optional): Schema name (default: "public")
```

### `describe_table` 
Gets detailed schema information for a specific table
```
Parameters:
- table_name (required): Name of the table to describe
- schema (optional): Schema name (default: "public")
```

### `get_table_relationships`
Shows foreign key relationships for a table
```
Parameters:
- table_name (required): Name of the table
- schema (optional): Schema name (default: "public")
```

### `list_schemas`
Lists all available schemas in the database
```
No parameters required
```

### `get_indexes`
Shows indexes for a specific table
```
Parameters:
- table_name (required): Name of the table
- schema (optional): Schema name (default: "public")
```

## Usage Examples

Once configured in Cursor, you can ask questions like:

- "What tables are in my database?"
- "Show me the schema for the users table"
- "What are the foreign key relationships for the orders table?"
- "List all indexes on the products table"
- "What schemas are available in my database?"

## Troubleshooting

### Connection Issues

1. **Database connection fails**: 
   - Verify your database credentials in `.env`
   - Ensure PostgreSQL is running
   - Check if the database allows connections from your host

2. **MCP server not loading in Cursor**:
   - Verify the absolute path to `index.js` is correct
   - Check that Node.js is installed and accessible
   - Review Cursor's MCP logs for error messages

3. **Permission denied errors**:
   - Ensure the database user has read permissions on `information_schema`
   - Grant necessary SELECT permissions on system tables

### Common Error Messages

- `Database connection failed`: Check your connection parameters
- `Table not found`: Verify the table name and schema
- `Permission denied`: Ensure your database user has appropriate read permissions

## Security Considerations

- This server only reads database metadata (schema information)
- No actual table data is accessed or exposed
- Uses parameterized queries to prevent SQL injection
- Database credentials are stored locally in your environment

## Development

To run in development mode with auto-reload:
```bash
npm run dev
```

## License

MIT License - see LICENSE file for details.