npm package: @spec-this/mcp-postgres-sql
Description: MCP server for PostgreSQL database queries
Latest version: 1.0.0

# MCP PostgreSQL

A Model Context Protocol (MCP) server that provides PostgreSQL database query capabilities with built-in connection management.

## Features

- PostgreSQL database support using the `pg` driver
- Connection management via MCP tools (add, list, update, remove)
- Connections stored in `~/.mcp-postgres-sql/connections.json`
- No environment variables needed — manage connections through the tools
- Passwords hidden when listing connections
- SSL support
- Stdio transport for MCP integration

## Installation

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

## MCP Configuration

Add to your Claude Desktop or Claude Code MCP config:

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

Or if installed globally:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "mcp-postgres-sql"
    }
  }
}
```

## Tools

### `add_connection`

Save a new PostgreSQL connection configuration.

| Parameter  | Type    | Required | Default | Description        |
|------------|---------|----------|---------|--------------------|
| `name`     | string  | yes      |         | Name for this connection |
| `host`     | string  | yes      |         | Database host      |
| `port`     | number  | no       | 5432    | Database port      |
| `database` | string  | yes      |         | Database name      |
| `user`     | string  | yes      |         | Database user      |
| `password` | string  | yes      |         | Database password  |
| `ssl`      | boolean | no       | false   | Use SSL connection |

### `list_connections`

List all saved connections. Passwords are hidden from the output.

### `update_connection`

Update an existing connection. Only the fields you provide will be changed.

| Parameter  | Type    | Required | Description              |
|------------|---------|----------|--------------------------|
| `name`     | string  | yes      | Connection name to update |
| `host`     | string  | no       | New host                 |
| `port`     | number  | no       | New port                 |
| `database` | string  | no       | New database name        |
| `user`     | string  | no       | New user                 |
| `password` | string  | no       | New password             |
| `ssl`      | boolean | no       | New SSL setting          |

### `remove_connection`

Remove a saved connection. If this is the active connection, it will be disconnected first.

| Parameter | Type   | Required | Description              |
|-----------|--------|----------|--------------------------|
| `name`    | string | yes      | Connection name to remove |

### `connect`

Connect to a saved connection by name. Disconnects any existing connection first.

| Parameter | Type   | Required | Description                  |
|-----------|--------|----------|------------------------------|
| `name`    | string | yes      | Connection name to connect to |

### `disconnect`

Disconnect from the current database.

### `query`

Execute a SQL query against the connected database.

| Parameter | Type   | Required | Description          |
|-----------|--------|----------|----------------------|
| `sql`     | string | yes      | The SQL query to run |

Returns JSON-formatted query results.

## Usage Example

Once configured as an MCP server, use the tools in sequence:

```
1. add_connection  →  name: "local-dev", host: "localhost", database: "myapp", user: "postgres", password: "secret"
2. connect         →  name: "local-dev"
3. query           →  sql: "SELECT * FROM users LIMIT 10"
4. disconnect
```

## Connection Storage

Connections are stored at `~/.mcp-postgres-sql/connections.json`:

```json
{
  "connections": {
    "local-dev": {
      "host": "localhost",
      "port": 5432,
      "database": "myapp",
      "user": "postgres",
      "password": "secret",
      "ssl": false
    }
  }
}
```

The directory is created with `0700` permissions and the file with `0600` permissions.

## Development

```bash
# Install dependencies
npm install

# Run in development mode
npm run dev

# Build for production
npm run build

# Type check
npx tsc --noEmit
```

## Testing

Tests require Docker.

```bash
# Start PostgreSQL test container
npm run test:up

# Run tests
npm test

# Stop and clean up
npm run test:down

# Or run everything at once
npm run test:ci
```

The test container runs PostgreSQL 16 on port 5433 to avoid conflicts with any local instance.

## License

MIT