# @node2flow/postgrest-mcp

[![smithery badge](https://smithery.ai/badge/node2flow/postgrest)](https://smithery.ai/server/node2flow/postgrest)
[![npm version](https://img.shields.io/npm/v/@node2flow/postgrest-mcp.svg)](https://www.npmjs.com/package/@node2flow/postgrest-mcp)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

MCP server for **PostgREST** — a standalone web server that turns PostgreSQL databases into RESTful APIs. 10 tools for schema discovery, CRUD operations, filtering, resource embedding, and RPC via the Model Context Protocol.

## Quick Start

### Claude Desktop / Cursor

Add to your MCP config:

```json
{
  "mcpServers": {
    "postgrest": {
      "command": "npx",
      "args": ["-y", "@node2flow/postgrest-mcp"],
      "env": {
        "POSTGREST_URL": "http://localhost:3000",
        "POSTGREST_TOKEN": "your-jwt-token"
      }
    }
  }
}
```

> **Note:** JWT token is optional. If PostgREST has an anonymous role configured (`db-anon-role`), schema and read operations work without authentication.

### HTTP Mode

```bash
POSTGREST_URL=http://localhost:3000 POSTGREST_TOKEN=your-jwt npx @node2flow/postgrest-mcp --http
```

MCP endpoint: `http://localhost:3000/mcp`

### Cloudflare Worker

Available at: `https://postgrest-mcp-community.node2flow.net/mcp`

```
POST https://postgrest-mcp-community.node2flow.net/mcp?POSTGREST_URL=http://your-server:3000&POSTGREST_TOKEN=your-jwt
```

---

## Tools (10)

### Schema (2) — No Auth Required (with anonymous role)

| Tool | Description |
|------|-------------|
| `pg_get_schema` | Get OpenAPI schema — lists all tables, views, functions |
| `pg_describe_table` | Get column details, types, constraints for a table/view |

### Read (3) — Read-Only

| Tool | Description |
|------|-------------|
| `pg_list_records` | Query records with filters, select, order, pagination |
| `pg_count_records` | Count records (exact, planned, or estimated) |
| `pg_call_function` | Call PostgreSQL functions/procedures via RPC |

### Write (5) — Requires Auth

| Tool | Description | Risk |
|------|-------------|------|
| `pg_insert_records` | Insert one or more records | Safe |
| `pg_update_records` | Update records matching a filter | Safe (filter required) |
| `pg_upsert_records` | Insert or update on conflict | Safe |
| `pg_delete_records` | Delete records matching a filter | **Destructive** |
| `pg_replace_record` | Full replace (PUT) a single record | Safe |

---

## Filter Syntax

PostgREST uses a powerful filter syntax on query parameters:

```
# Comparison
age=gt.18                    # greater than
status=eq.active             # equals
price=lte.100               # less than or equal

# Pattern matching
name=ilike.*john*            # case-insensitive LIKE
email=like.*@gmail.com       # case-sensitive LIKE

# Lists and NULL
id=in.(1,2,3)               # IN list
deleted_at=is.null           # NULL check

# Logic
or=(age.lt.18,age.gt.65)    # OR conditions
not.status=eq.inactive       # NOT

# Full-text search
content=fts.postgresql       # full-text search

# Array/JSONB
tags=cs.{tech,api}           # array contains
```

---

## Resource Embedding (JOINs)

Query related tables using the `select` parameter:

```
*,orders(*)                         — embed all columns from related table
id,name,orders(id,total,status)     — specific columns from embed
id,author:user_id(name,email)       — renamed embed
```

Filter on embedded resources:

```
select: id,name,orders(*)
filter: orders.status=eq.completed
```

---

## Configuration

| Parameter | Required | Description |
|-----------|----------|-------------|
| `POSTGREST_URL` | Yes | PostgREST server URL (e.g. `http://localhost:3000`) |
| `POSTGREST_TOKEN` | No | JWT token for authenticated requests |

---

## JWT Authentication

PostgREST uses JSON Web Tokens (JWT) for stateless authentication.

1. Configure `jwt-secret` in your PostgREST config
2. Generate a JWT with the appropriate `role` claim
3. Pass the token via `POSTGREST_TOKEN` environment variable

**JWT Claims:**
- `role` — Database role name (required for RLS)
- `exp` — Token expiration (Unix timestamp)
- Custom claims accessible in SQL via `current_setting()`

> **Tip:** Use `db-anon-role` in PostgREST config for public read-only access without tokens.

---

## Safety

- **Update and Delete require a filter** — prevents accidental full-table operations
- **Use `pg_count_records`** to verify filter matches before delete
- **Use `return="representation"`** to see what was changed
- **Use `pg_describe_table`** to check required columns before insert

---

## What is PostgREST?

[PostgREST](https://postgrest.org) is a standalone web server that:
- Turns PostgreSQL **tables** into CRUD endpoints (GET, POST, PATCH, PUT, DELETE)
- Turns **views** into read-only endpoints
- Turns **functions** into RPC endpoints (`/rpc/function_name`)
- Uses PostgreSQL **Row Level Security** (RLS) for authorization
- Auto-generates an **OpenAPI** specification

---

## License

MIT License - see [LICENSE](LICENSE)

Copyright (c) 2026 [Node2Flow](https://node2flow.net)

## Links

- [npm Package](https://www.npmjs.com/package/@node2flow/postgrest-mcp)
- [PostgREST Documentation](https://postgrest.org/)
- [MCP Protocol](https://modelcontextprotocol.io/)
- [Node2Flow](https://node2flow.net)