Postgresql API

PostgreSQL MCP + PostgREST API

Two ways to interact with PostgreSQL directly in the Composable Stack:

• PostgreSQL MCP (postgresql-mcp) — 27 database administration and query tools via MCP
• PostgREST API — REST CRUD endpoints auto-generated from PostgreSQL schema, used from n8n/Trigger.dev/curl

PostgreSQL MCP Tools

MCP server ID: postgresql-mcp (Supabase Toolbox v0.31.0) Tool prefix: mcp__postgresql-mcp__

Query & SQL

Execute SQL

Run any single SQL statement — SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP.

Tool: mcp__postgresql-mcp__execute_sql
Input: { "sql": "SELECT * FROM orders WHERE status = 'pending' LIMIT 10" }

Tool: mcp__postgresql-mcp__execute_sql
Input: { "sql": "CREATE TABLE orders (id SERIAL PRIMARY KEY, title TEXT NOT NULL, status TEXT DEFAULT 'pending', created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now())" }

Get Query Plan

Generate EXPLAIN plan in JSON without executing the query. Safe for production use.

Tool: mcp__postgresql-mcp__get_query_plan
Input: { "query": "SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending'" }

Schema Inspection

List Tables

Detailed schema info: columns, constraints, indexes, triggers, owner, comments.

Tool: mcp__postgresql-mcp__list_tables

Filter specific tables:

Tool: mcp__postgresql-mcp__list_tables
Input: { "table_names": "orders,customers", "output_format": "detailed" }

Names only:

Tool: mcp__postgresql-mcp__list_tables
Input: { "output_format": "simple" }

List Views

Tool: mcp__postgresql-mcp__list_views
Input: { "view_name": "active_orders" }

List Indexes

Tool: mcp__postgresql-mcp__list_indexes
Input: { "table_name": "orders" }

Find unused indexes:

Tool: mcp__postgresql-mcp__list_indexes
Input: { "only_unused": true }

List Schemas

Returns schema name, owner, grants, function/table/view counts.

Tool: mcp__postgresql-mcp__list_schemas

Other Schema Tools

Performance & Monitoring

Database Overview

Server version, uptime, connection counts, replica status.

Tool: mcp__postgresql-mcp__database_overview

Table Statistics

Row counts, sizes, scan ratios, dead tuples, vacuum times.

Tool: mcp__postgresql-mcp__list_table_stats
Input: { "table_name": "orders" }

Query Statistics

Requires pg_stat_statements extension. Execution counts, timing, buffer stats.

Tool: mcp__postgresql-mcp__list_query_stats
Input: { "limit": 20 }

Active Queries

Currently running queries ordered by duration.

Tool: mcp__postgresql-mcp__list_active_queries
Input: { "min_duration": "1 second" }

Other Monitoring Tools

Server Configuration

Extensions

Replication & Infrastructure

PostgREST API (v14.8)

REST API auto-generated from PostgreSQL schema. Endpoints are created dynamically for each table and function in the public schema.

Base URL: ${POSTGRESQL_API_URL} Auth: Authorization: Bearer ${POSTGRESQL_API_TOKEN}

CRUD Operations

Read Records (GET)

# All records
curl "${POSTGRESQL_API_URL}/orders" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}"

# Select specific columns
curl "${POSTGRESQL_API_URL}/orders?select=id,title,status" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}"

# Filter records
curl "${POSTGRESQL_API_URL}/orders?status=eq.pending&amount=gt.100" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}"

# Order and paginate
curl "${POSTGRESQL_API_URL}/orders?order=created_at.desc&limit=20&offset=0" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}"

Create Records (POST)

# Single record
curl -X POST "${POSTGRESQL_API_URL}/orders" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"title": "New Order", "status": "pending"}'

# Bulk insert
curl -X POST "${POSTGRESQL_API_URL}/orders" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '[{"title": "Order 1"}, {"title": "Order 2"}]'

Update Records (PATCH)

# Update by filter
curl -X PATCH "${POSTGRESQL_API_URL}/orders?id=eq.123" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '{"status": "completed"}'

Delete Records (DELETE)

curl -X DELETE "${POSTGRESQL_API_URL}/orders?id=eq.123" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}"

Upsert (POST with conflict resolution)

curl -X POST "${POSTGRESQL_API_URL}/orders" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates,return=representation" \
  -H "on_conflict: id" \
  -d '{"id": 123, "title": "Updated Order", "status": "active"}'

Call Stored Functions (RPC)

curl -X POST "${POSTGRESQL_API_URL}/rpc/calculate_total" \
  -H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"order_id": 123}'

Filtering Operators

Prefer Header Options

PostgREST in n8n Workflows

Use HTTP Request node:

Method: GET / POST / PATCH / DELETE
URL: {{ $env.POSTGRESQL_API_URL }}/orders
Headers:
  Authorization: Bearer {{ $env.POSTGRESQL_API_TOKEN }}
  Content-Type: application/json
  Prefer: return=representation

PostgREST in Trigger.dev Tasks

const response = await fetch(
  `${process.env.POSTGRESQL_API_URL}/orders?status=eq.pending&limit=50`,
  {
    headers: {
      'Authorization': `Bearer ${process.env.POSTGRESQL_API_TOKEN}`,
      'Content-Type': 'application/json',
    },
  }
);
const orders = await response.json();

When to Use What

Best Practices

• Use NocoDB MCP for everyday record operations — it provides the simplest abstraction
• Use PostgreSQL MCP execute_sql for complex queries that would require multiple NocoDB MCP calls
• Use PostgREST API from n8n HTTP Request nodes when you need direct REST access without MCP
• Always use ${POSTGRESQL_API_URL} and ${POSTGRESQL_API_TOKEN} env vars — never hardcode URLs or tokens
• Use Prefer: return=representation on POST/PATCH to get the created/updated record back
• Use PostgREST filtering operators for simple lookups; use execute_sql for complex WHERE clauses
• Run mcp__postgresql-mcp__list_table_stats periodically to check for table bloat and missing indexes