npm package: @node2flow/postgrest-mcp
Description: MCP server for PostgREST — 10 tools for CRUD operations, filtering, resource embedding, and RPC on PostgreSQL databases via REST API
Author: Node2Flow
Latest version: 1.0.0

# @node2flow/postgrest-mcp

MCP server for **PostgREST** — 10 tools for CRUD operations, filtering, resource embedding, and RPC on PostgreSQL databases via REST API.

[PostgREST](https://postgrest.org) is a standalone web server that turns your PostgreSQL database directly into a RESTful API. This MCP server lets AI assistants interact with any PostgREST instance.

## Features

- **10 MCP Tools** — Schema discovery, CRUD, filtering, resource embedding, RPC
- **PostgREST Filter Syntax** — Full support for eq, gt, like, in, is, fts, or, and, not
- **Resource Embedding** — JOIN related tables via select syntax
- **Multiple Transports** — stdio (Claude Desktop/Cursor), Streamable HTTP, Cloudflare Worker
- **JWT Authentication** — Optional Bearer token for protected databases

## Tools

| # | Tool | Description | Read-Only |
|---|------|-------------|-----------|
| 1 | `pg_get_schema` | Get OpenAPI schema — lists tables, views, functions | Yes |
| 2 | `pg_describe_table` | Get column details for a table/view | Yes |
| 3 | `pg_list_records` | Query records with filters, select, order, pagination | Yes |
| 4 | `pg_count_records` | Count records (exact, planned, or estimated) | Yes |
| 5 | `pg_call_function` | Call PostgreSQL functions via RPC | No |
| 6 | `pg_insert_records` | Insert one or more records | No |
| 7 | `pg_update_records` | Update records matching a filter | No |
| 8 | `pg_upsert_records` | Upsert (insert or update on conflict) | No |
| 9 | `pg_delete_records` | Delete records matching a filter | No |
| 10 | `pg_replace_record` | Full replace (PUT) a single record | No |

## Quick Start

### Claude Desktop / Cursor (stdio)

```json
{
  "mcpServers": {
    "postgrest": {
      "command": "npx",
      "args": ["-y", "@node2flow/postgrest-mcp"],
      "env": {
        "POSTGREST_URL": "http://localhost:3000",
        "POSTGREST_TOKEN": "your-jwt-token"
      }
    }
  }
}
```

### HTTP Mode

```bash
POSTGREST_URL=http://localhost:3000 POSTGREST_TOKEN=your-jwt npx @node2flow/postgrest-mcp --http
```

### Cloudflare Worker

```
https://postgrest-mcp-community.node2flow.net/mcp?POSTGREST_URL=http://your-server:3000&POSTGREST_TOKEN=your-jwt
```

## Configuration

| Parameter | Required | Description |
|-----------|----------|-------------|
| `POSTGREST_URL` | Yes | PostgREST server URL (e.g. `http://localhost:3000`) |
| `POSTGREST_TOKEN` | No | JWT token for authenticated requests |

> **Note**: If your PostgREST server has an anonymous role configured (`db-anon-role`), schema and read operations work without a token.

## 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:

```
# Embed related table
select=*,orders(*)

# Specific columns from related table
select=id,name,orders(id,total,status)

# Renamed embed
select=id,author:user_id(name,email)

# Filter on embedded resource
filter: orders.status=eq.completed
select: id,name,orders(*)
```

## Examples

### Explore database structure
```
Tool: pg_get_schema
→ Returns all tables, views, and functions
```

### Query with filters
```
Tool: pg_list_records
  table: "users"
  select: "id,name,email,orders(id,total)"
  filter: "status=eq.active&created_at=gt.2024-01-01"
  order: "created_at.desc"
  limit: 20
```

### Insert records
```
Tool: pg_insert_records
  table: "users"
  records: {"name": "Alice", "email": "alice@example.com"}
  return: "representation"
```

### Update with filter
```
Tool: pg_update_records
  table: "users"
  filter: "id=eq.1"
  data: {"name": "Alice Updated"}
  return: "representation"
```

### Call a function
```
Tool: pg_call_function
  function_name: "get_user_stats"
  params: {"user_id": 1}
  method: "GET"
```

## What is PostgREST?

[PostgREST](https://postgrest.org) is a standalone web server that:
- Automatically turns PostgreSQL **tables** into CRUD endpoints
- Turns **views** into read-only endpoints
- Turns **functions** into RPC endpoints
- Uses PostgreSQL's **Row Level Security** (RLS) for authorization
- Generates an **OpenAPI** specification automatically

## License

MIT