# mcp-server-postgres

[![npm version](https://img.shields.io/npm/v/mcp-pg-server.svg)](https://www.npmjs.com/package/mcp-pg-server)
[![npm downloads](https://img.shields.io/npm/dm/mcp-pg-server.svg)](https://www.npmjs.com/package/mcp-pg-server)
[![CI](https://github.com/ofershap/mcp-server-postgres/actions/workflows/ci.yml/badge.svg)](https://github.com/ofershap/mcp-server-postgres/actions/workflows/ci.yml)
[![TypeScript](https://img.shields.io/badge/TypeScript-strict-blue.svg)](https://www.typescriptlang.org/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

Query PostgreSQL, inspect schemas, and explain queries from your AI assistant. A daily-driver MCP for local and dev Postgres — same belt as docker and sqlite servers, not an enterprise DBA suite.

```bash
DATABASE_URL=postgres://user:pass@localhost:5432/mydb npx mcp-pg-server
```

> Works with Claude Desktop, Cursor, VS Code Copilot, and any MCP client. Connects via `DATABASE_URL` or per-call `connectionString`.

## Why

Postgres is the default for most new backends, local dev stacks, and agent workflows that need a real SQL database. You already run it in Docker or on localhost — this server lets your assistant query it, read schemas, and explain plans without leaving the IDE. Read-only by default so exploration stays safe; opt into writes when you need them.

## Tools

| Tool           | What it does                                                                  |
| -------------- | ----------------------------------------------------------------------------- |
| `query`        | Execute SQL (SELECT, WITH, EXPLAIN, SHOW, ANALYZE). Returns a text table.     |
| `schema`       | Tables in a schema (default `public`) with columns and approximate row counts |
| `table_info`   | One table: columns, types, null/default, PK, exact row count                  |
| `explain`      | EXPLAIN (FORMAT TEXT), optional ANALYZE                                       |
| `list_schemas` | List non-system schemas                                                       |

## Quick Start

### Cursor

Add to `.cursor/mcp.json`:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["mcp-pg-server"],
      "env": {
        "DATABASE_URL": "postgres://user:pass@localhost:5432/mydb"
      }
    }
  }
}
```

### Claude Desktop

Add to `claude_desktop_config.json` (macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["mcp-pg-server"],
      "env": {
        "DATABASE_URL": "postgres://user:pass@localhost:5432/mydb"
      }
    }
  }
}
```

### VS Code

Configure the MCP server in your VS Code settings to run `npx mcp-pg-server` with `DATABASE_URL` set.

## Example prompts

- "Show me the schema of the public tables"
- "Query users: SELECT * FROM users LIMIT 10"
- "Explain this join query"
- "What schemas exist in this database?"
- "How many rows are in the orders table?"

## Safety

Read-only by default. The `query` tool accepts only SELECT, WITH, EXPLAIN, SHOW, and ANALYZE in readonly mode. Set `readonly=false` to enable INSERT, UPDATE, DELETE, and DDL.

Pass `connectionString` on any tool to override `DATABASE_URL` for a single call.

## Development

```bash
npm install
npm run typecheck
npm run build
npm test
npm run format
npm run lint
```

## See also

More MCP servers and developer tools on my [portfolio](https://gitshow.dev/ofershap).

## Author

[![Made by ofershap](https://gitshow.dev/api/card/ofershap)](https://gitshow.dev/ofershap)

[![LinkedIn](https://img.shields.io/badge/LinkedIn-Connect-0A66C2?style=flat&logo=linkedin&logoColor=white)](https://linkedin.com/in/ofershap)
[![GitHub](https://img.shields.io/badge/GitHub-Follow-181717?style=flat&logo=github&logoColor=white)](https://github.com/ofershap)

## License

MIT © 2026 Ofer Shapira