npm package: @igorvinicius/postgres-mcp-server
Description: MCP server for PostgreSQL database operations
Author: Igor Vinicius Avanci
Latest version: 0.0.2

# PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides tools for connecting to and querying PostgreSQL databases.

## Features

- **Connect to PostgreSQL**: Connect to any PostgreSQL database with credentials
- **Schema Discovery**: List all schemas in the database
- **Table Discovery**: List all tables in a schema
- **Table Description**: Get detailed information about table structure, including columns, types, constraints, and relationships
- **Query Execution**: Run SELECT queries safely with parameter support

## Installation

```bash
npm install
npm run build
```

## Usage

### As an MCP Server

Add to your MCP client configuration (e.g., Claude Desktop, Cline):

#### Option 1: Auto-connect with Environment Variables (Recommended)

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp-server/build/index.js"],
      "env": {
        "PG_HOST": "your-host",
        "PG_PORT": "5432",
        "PG_USER": "your-user",
        "PG_PASSWORD": "your-password",
        "PG_DATABASE": "your-database"
      }
    }
  }
}
```

When environment variables are configured, the server will automatically connect on startup.

#### Option 2: Manual Connection

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp-server/build/index.js"]
    }
  }
}
```

Without environment variables, use the `connect_db` tool to connect manually.

#### Quick Setup for Cursor

The file `cursor-mcp-config.json` contains a ready-to-use configuration. Add this to your `~/.cursor/mcp.json` file under the `mcpServers` section.

### Available Tools

#### 1. `connect_db`

Connect to a PostgreSQL database.

**Parameters:**

- `host` (required): Database host
- `port` (optional): Database port (default: 5432)
- `database` (required): Database name
- `user` (required): Database user
- `password` (required): Database password

**Example:**

```json
{
  "host": "localhost",
  "port": 5432,
  "database": "mydb",
  "user": "postgres",
  "password": "mypassword"
}
```

#### 2. `list_schemas`

List all schemas in the connected database (excludes system schemas).

**Parameters:** None

#### 3. `list_tables`

List all tables in a specific schema.

**Parameters:**

- `schema` (optional): Schema name (default: "public")

#### 4. `describe_table`

Get detailed information about a table's structure.

**Parameters:**

- `schema` (optional): Schema name (default: "public")
- `table` (required): Table name

Returns column names, data types, nullability, defaults, primary keys, and foreign key relationships.

#### 5. `query`

Execute a SELECT query on the database.

**Parameters:**

- `sql` (required): SQL SELECT query (use $1, $2, etc. for parameters)
- `params` (optional): Array of query parameters

**Example:**

```json
{
  "sql": "SELECT * FROM users WHERE age > $1 AND city = $2",
  "params": [18, "New York"]
}
```

**Note:** Only SELECT queries are allowed for safety. The tool validates that queries start with SELECT.

## Development

### Build

```bash
npm run build
```

### Project Structure

```
postgres-mcp-server/
├── src/
│   └── index.ts          # Main server implementation
├── build/                # Compiled JavaScript output
├── package.json
├── tsconfig.json
└── README.md
```

## Security Considerations

- This server only allows SELECT queries through the `query` tool to prevent accidental data modification
- Database credentials are required for each connection
- Use environment variables or secure credential management for production deployments
- Always use parameterized queries to prevent SQL injection

## Requirements

- Node.js 18 or higher
- PostgreSQL database (any version supported by the `pg` library)

## License

MIT