pg-mcp-server

# Postgres MCP Server A Model Context Protocol server for PostgreSQL databases. Enables LLMs to query and analyze PostgreSQL databases through a controlled interface. https://github.com/user-attachments/assets/6571c20c-91c9-4a6e-8332-13b587b79fbf ## Installation [](https://cursor.com/en/install-mcp?name=postgres&config=eyJjb21tYW5kIjoibnB4IC0teWVzIHBnLW1jcC1zZXJ2ZXIgLS10cmFuc3BvcnQgc3RkaW8iLCJlbnYiOnsiREFUQUJBU0VfVVJMIjoicG9zdGdyZXNxbDovL3Bvc3RncmVzOnBvc3RncmVzQGxvY2FsaG9zdDo1NDMyL3Bvc3RncmVzIn19) Add to your MCP client settings: ```json { "mcpServers": { "postgres": { "command": "npx", "args": ["--yes", "pg-mcp-server", "--transport", "stdio"], "env": { "DATABASE_URL": "postgresql://postgres:postgres@localhost:5432/postgres" } } } } ``` ## Configuration - `DATABASE_URL` - PostgreSQL connection string (required) - `DANGEROUSLY_ALLOW_WRITE_OPS` - Enable writes (default: `false`) - `DEBUG` - Enable debug logging (default: `false`) - `PG_SSL_ROOT_CERT` - Optional path to a TLS CA bundle (e.g., AWS RDS global bundle) ## Usage ### Transports - Default transport is **stdio**. Switch to **HTTP** with the `--transport` flag. - HTTP mode serves the MCP Streamable HTTP endpoint at `/mcp` on `PORT` (default `3000`). - Clients that support Streamable HTTP should connect to `http://localhost:3000/mcp`. Start commands: ```bash # stdio transport (default, via installed CLI) pg-mcp-server --transport=stdio # http transport pg-mcp-server --transport=http ``` ### Tools - **`query`** - Execute SQL queries ```json { "sql": "SELECT * FROM users WHERE active = true LIMIT 10" } ``` ### Resources - **`postgres://tables`** - List all tables - **`postgres://table/{schema}/{table}`** - Get table schema and sample data ### Example Prompts Here's an example prompt to test if your MCP server is working: ``` Show me the first 5 users from the database ``` ## Data Analysis & Notebooks For data analysis and exploration, I've added a Cursor rule for using the MCP server together with notebooks in [`.cursor/rules/notebooks.mdc`](.cursor/rules/notebooks.mdc): ## Quick Start with Docker ```bash # Start PostgreSQL with sample data bun run db:start # Test with MCP Inspector bun run inspector # Stop PostgreSQL bun run db:stop ``` Sample tables included: `users`, `products`, `orders`, `order_items` ## Development ```bash # Clone and install git clone https://github.com/ericzakariasson/pg-mcp-server.git cd pg-mcp-server bun install # Run (stdio transport) bun run index.ts -- --transport=stdio DEBUG=true bun run index.ts -- --transport=stdio # Run (http transport) bun run index.ts -- --transport=http DEBUG=true bun run index.ts -- --transport=http bun test # Run tests ``` Use local build in MCP client settings: ```bash bun run build:js ``` ```json { "mcpServers": { "postgres": { "command": "node", "args": ["/absolute/path/to/pg-mcp-server/lib/index.js", "--transport", "stdio"], "env": { "DATABASE_URL": "postgresql://postgres:postgres@localhost:5432/postgres" } } } } ``` ## Releases This repo auto-creates a GitHub Release when you push a tag that matches the version in `package.json`: 1. Update `CHANGELOG.md` and bump the version in `package.json`. 2. Commit the changes on `main` (or your release branch). 3. Create and push a matching tag: ```bash VERSION=$(jq -r .version package.json) git tag v"$VERSION" git push origin v"$VERSION" ``` The GitHub Actions workflow will validate that the tag (e.g. `v0.1.0`) matches `package.json`, then use `gh` to create a release with generated notes, and publish to npm. ## License MIT - see [LICENSE](LICENSE)