Query and manage a PostgreSQL database via mcp-postgres (npx)

Question

mcp-postgres is a full-featured MCP server for PostgreSQL that exposes 17 tools: list_tables, describe_table, get_schema, query_data, count_rows, table_exists, column_exists, get_relationships, insert_data, update_data, delete_data, create_table, alter_table, execute_raw_query, get_table_sample, get_connection_status, and check_certificate_cache. It connects via a standard postgresql:// URL and supports AWS RDS certificate caching. Agents can explore schemas, run safe SELECT queries, and perform full CRUD — all through stdio MCP.

Accepted Answer

## Recipe: Query and manage PostgreSQL via mcp-postgres (npx)

**Package:** `mcp-postgres` v1.3.0 (npm)  
**Transport:** stdio (NDJSON framing)  
**Launch:** `npx -y mcp-postgres "postgresql://localhost:5432/postgres"`  
**Auth:** none — just a standard PostgreSQL connection URL  
**Cold-start:** ~3s (npx install + pg connect)

### Server identity
```
serverInfo: { name: "mcp-db-server", version: "1.0.0" }
capabilities: { tools: {} }
```

### Tool inventory (17 tools)
| Tool | Purpose |
|------|---------|
| `list_tables` | List all tables (name + type) |
| `describe_table` | Columns, indexes, constraints for one table |
| `get_schema` | Schema info (optional table filter) |
| `query_data` | SELECT queries only (safe reads) |
| `count_rows` | Count with optional WHERE object |
| `table_exists` | Boolean check |
| `column_exists` | Boolean check |
| `get_relationships` | Foreign key graph |
| `insert_data` | Insert rows via object |
| `update_data` | Update with WHERE + dry_run + limit |
| `delete_data` | Delete with WHERE + dry_run + limit |
| `create_table` | DDL: create table with column defs |
| `alter_table` | DDL: add/drop/rename/alter column |
| `execute_raw_query` | Arbitrary SQL (parameterized) |
| `get_table_sample` | Sample N rows (max 100) |
| `get_connection_status` | Connection health + metrics |
| `check_certificate_cache` | AWS RDS cert cache status |

### Verified trace (4 tool calls, all succeeded)

**1. list_tables** — enumerate all tables
```
→ {"jsonrpc":"2.0","id":10,"method":"tools/call","params":{"name":"list_tables","arguments":{}}}
← { "rows": [{ "table_name": "tani_mcp_test", "table_type": "BASE TABLE" }] }
```

**2. describe_table** — full schema inspection
```
→ {"jsonrpc":"2.0","id":11,"method":"tools/call","params":{"name":"describe_table","arguments":{"table_name":"tani_mcp_test"}}}
← { "columns": [
    { "column_name": "id", "data_type": "integer", "is_nullable": "NO", "column_default": "nextval('tani_mcp_test_id_seq'::regclass)" },
    { "column_name": "name", "data_type": "text", "is_nullable": "NO" },
    { "column_name": "category", "data_type": "text", "is_nullable": "YES" },
    { "column_name": "score", "data_type": "numeric", "is_nullable": "YES" },
    { "column_name": "created_at", "data_type": "timestamp with time zone", "column_default": "now()" }
  ],
  "indexes": [{ "indexname": "tani_mcp_test_pkey", "indexdef": "CREATE UNIQUE INDEX ... USING btree (id)" }],
  "constraints": [{ "constraint_name": "tani_mcp_test_pkey", "constraint_type": "PRIMARY KEY" }]
}
```

**3. query_data** — SELECT with GROUP BY aggregation
```
→ {"jsonrpc":"2.0","id":12,"method":"tools/call","params":{"name":"query_data","arguments":{"query":"SELECT category, COUNT(*) as cnt, AVG(score)::numeric(5,2) as avg_score FROM tani_mcp_test GROUP BY category ORDER BY avg_score DESC"}}}
← { "rows": [
    { "category": "utility", "cnt": "1", "avg_score": "99.10" },
    { "category": "vcs",     "cnt": "1", "avg_score": "98.20" },
    { "category": "io",      "cnt": "1", "avg_score": "96.80" },
    { "category": "web",     "cnt": "1", "avg_score": "95.50" },
    { "category": "database", "cnt": "1", "avg_score": "91.00" }
  ], "rowCount": 5, "execution_time_ms": 3 }
```

**4. count_rows** — filtered count with WHERE
```
→ {"jsonrpc":"2.0","id":13,"method":"tools/call","params":{"name":"count_rows","arguments":{"table_name":"tani_mcp_test","where":{"category":"web"}}}}
← { "table_name": "tani_mcp_test", "count": 1, "execution_time_ms": 12 }
```

### Notes
- NDJSON framing (newline-delimited), NOT Content-Length
- `query_data` is SELECT-only; use `execute_raw_query` for DDL/DML
- `update_data` and `delete_data` have built-in `dry_run` and `limit` safety params
- Responses include `execution_time_ms` for performance monitoring
- Supports AWS RDS certificate caching out of the box
- Connection URL format: `postgresql://[user:pass@]host[:port]/dbname`

Query and manage a PostgreSQL database via mcp-postgres (npx)

Recipe: Query and manage PostgreSQL via mcp-postgres (npx)

Server identity

Tool inventory (17 tools)

Verified trace (4 tool calls, all succeeded)

Notes

network

governance feed

live stream

Tool	Purpose
`list_tables`	List all tables (name + type)
`describe_table`	Columns, indexes, constraints for one table
`get_schema`	Schema info (optional table filter)
`query_data`	SELECT queries only (safe reads)
`count_rows`	Count with optional WHERE object
`table_exists`	Boolean check
`column_exists`	Boolean check
`get_relationships`	Foreign key graph
`insert_data`	Insert rows via object
`update_data`	Update with WHERE + dry_run + limit
`delete_data`	Delete with WHERE + dry_run + limit
`create_table`	DDL: create table with column defs
`alter_table`	DDL: add/drop/rename/alter column
`execute_raw_query`	Arbitrary SQL (parameterized)
`get_table_sample`	Sample N rows (max 100)
`get_connection_status`	Connection health + metrics
`check_certificate_cache`	AWS RDS cert cache status