PostgREST API
PostgREST turns a PostgreSQL schema into a REST API: every table and view in an exposed schema gets endpoints, every function becomes /rpc/<name>. Use it when the caller is an HTTP client — an n8n HTTP Request node, a background task, curl — and an MCP connection is not available or not wanted. For SQL, schema inspection and administration over MCP, see postgres-mcp-tools. Tables you design with this plugin's create-tables and relations skills are served as they are.
Version. This reference was run against PostgREST 16.3 (16.4 was current on 2026-10-02) and applies to 14–16. Do not hardcode a version: the server names itself in the Server response header (Server: postgrest/16.3), and GET / returns the OpenAPI description of what the current role can reach. PostgREST 16 needs PostgreSQL 14 or later, and changes two things you may meet in an old config — see Upgrading to 16.
Base URL: ${POSTGRESQL_API_URL} (no trailing slash)
Auth: Authorization: Bearer ${POSTGRESQL_API_TOKEN}
How Access Works
A request runs as the PostgreSQL role named in the JWT role claim; with no token it runs as the configured anonymous role. Table and function GRANTs are the permission model — a missing grant answers 401 for the anonymous role and 403 for an authenticated one, with PostgreSQL code 42501 (permission denied for table …); it is not a PostgREST setting. After you change the schema (new table, column, grant), PostgREST must reload its schema cache (NOTIFY pgrst, 'reload schema' or a restart) before the API shows it.
CRUD Operations
Read Records (GET)
# All records (the server may cap the page: db-max-rows)
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}"
# One object instead of an array (406 unless exactly one row matches)
curl "${POSTGRESQL_API_URL}/orders?id=eq.123" \
-H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
-H "Accept: application/vnd.pgrst.object+json"
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 — every object needs the same keys (see "Bulk insert with missing keys")
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 — always send a 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"}'
A PATCH or DELETE without a filter changes every row you may touch. Add the guard from Safety Net for Bulk Writes to every call that is built from variable input.
Delete Records (DELETE)
curl -X DELETE "${POSTGRESQL_API_URL}/orders?id=eq.123" \
-H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}"
Upsert (POST with conflict resolution)
on_conflict is a query parameter, not a header. It names the unique column (or comma-separated columns) that decides "same row"; without it the primary key is used. The Prefer header only chooses what happens on a conflict.
# Merge on a unique business key, return the stored row
curl -X POST "${POSTGRESQL_API_URL}/orders?on_conflict=external_ref" \
-H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
-H "Content-Type: application/json" \
-H "Prefer: resolution=merge-duplicates,return=representation" \
-d '{"external_ref": "ext-1", "title": "Updated Order", "status": "active"}'
# Conflict on the primary key (on_conflict may be omitted)
curl -X POST "${POSTGRESQL_API_URL}/orders?on_conflict=id" \
-H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
-H "Content-Type: application/json" \
-H "Prefer: resolution=merge-duplicates,return=representation" \
-d '{"id": 123, "title": "Updated Order", "status": "active"}'
An on_conflict: external_ref header is ignored: on a conflict with another unique column the call fails with 409 and PostgreSQL code 23505 (duplicate key value violates unique constraint). With merge-duplicates every column in the body is overwritten, the primary key included, so send the key you want to keep. resolution=ignore-duplicates skips conflicting rows instead of updating them.
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}'
A STABLE or IMMUTABLE function can also be called with GET /rpc/calculate_total?order_id=123. The role needs EXECUTE on the function.
Filtering Operators
| Operator | Meaning | Example |
|---|---|---|
eq |
Equals | ?status=eq.active |
neq |
Not equals | ?status=neq.deleted |
gt |
Greater than | ?amount=gt.100 |
gte |
Greater or equal | ?amount=gte.100 |
lt |
Less than | ?amount=lt.50 |
lte |
Less or equal | ?amount=lte.50 |
like |
LIKE pattern (* is the wildcard) |
?name=like.*john* |
ilike |
Case-insensitive LIKE | ?name=ilike.*john* |
in |
In list | ?status=in.(active,pending) |
is |
IS (null, true, false) | ?deleted_at=is.null |
not |
Negate | ?status=not.eq.deleted |
cs |
Contains (arrays/JSON) | ?tags=cs.{urgent} |
cd |
Contained by | ?tags=cd.{urgent,important} |
or |
Any of several conditions | ?or=(status.eq.active,status.eq.pending) |
Prefer Header Options
Combine options in one header, comma separated: Prefer: return=representation,missing=default. The server echoes what it applied in Preference-Applied — check it when an option seems to do nothing.
| Value | Effect |
|---|---|
return=representation |
Return the created/updated rows in the body |
return=minimal |
No body, 204 (the default for writes) |
return=headers-only |
201 with a Location header, no body (inserts) |
resolution=merge-duplicates |
Upsert — update the existing row, insert new ones |
resolution=ignore-duplicates |
Skip conflicting rows on insert |
count=exact |
Exact row count in Content-Range (0-0/4); also planned and estimated (cheaper, approximate) |
missing=default |
A key missing from a bulk-insert object takes the column DEFAULT instead of NULL — use it with ?columns= |
handling=strict |
Reject an unknown or unusable preference with 400 instead of ignoring it; required for max-affected |
max-affected=N |
Fail with 400 / PGRST124 when a write would touch more than N rows — needs handling=strict |
tx=rollback |
Run the request and roll it back (a dry run) — needs db-tx-end = commit-allow-override; with the default commit the preference is silently ignored. (db-tx-end takes commit, commit-allow-override, rollback, rollback-allow-override; under rollback-allow-override every request rolls back unless the client sends tx=commit.) |
return=none is not a PostgREST value; it is ignored and the default minimal applies.
Safety Net for Bulk Writes
For any PATCH or DELETE whose filter comes from variable input (a workflow, an agent), say how many rows you expect:
curl -X PATCH "${POSTGRESQL_API_URL}/orders?status=eq.pending" \
-H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
-H "Content-Type: application/json" \
-H "Prefer: handling=strict,max-affected=50,return=representation" \
-d '{"status": "queued"}'
The same guard on a DELETE:
curl -X DELETE "${POSTGRESQL_API_URL}/orders?status=eq.cancelled" \
-H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
-H "Prefer: handling=strict,max-affected=50"
If more than 50 rows match, nothing is changed and the answer is 400 / PGRST124 with "The query affects N rows". Without handling=strict, max-affected is ignored and a filterless DELETE removes everything.
Bulk Insert With Missing Keys
PostgREST rejects an array whose objects have different keys (PGRST102, All object keys must match). Name the columns once and say what a missing key means:
curl -X POST "${POSTGRESQL_API_URL}/orders?columns=id,title,status" \
-H "Authorization: Bearer ${POSTGRESQL_API_TOKEN}" \
-H "Content-Type: application/json" \
-H "Prefer: missing=default,return=representation" \
-d '[{"id": 101, "title": "A", "status": "paid"}, {"id": 102, "title": "B"}]'
The second row gets the column default for status; without missing=default it would get NULL.
PostgREST in n8n Workflows
Use an HTTP Request node. Keep the token in a credential, not in an expression:
Method: GET / POST / PATCH / DELETE
URL: <your PostgREST base URL>/orders (fixed in the node)
Authentication: Generic Credential Type → Header Auth
(Name: Authorization, Value: Bearer <POSTGRESQL_API_TOKEN>)
Headers: Content-Type: application/json
Prefer: return=representation
Do not build the URL or the token with {{ $env.POSTGRESQL_API_URL }}: n8n 2.x blocks $env in expressions and Code nodes by default (N8N_BLOCK_ENV_ACCESS_IN_NODE; the 2.0 breaking-changes list sets it to true, while the environment-variable reference page still shows false — verify on your instance). Use a credential plus a fixed URL. If you must read the environment, set N8N_BLOCK_ENV_ACCESS_IN_NODE=false on purpose and accept that every workflow author can then read the container's environment. In a Code node, do the data shaping and leave the call to the HTTP Request node.
PostgREST in Trigger.dev Tasks
Read the URL and token from the task's environment and check the status — fetch does not throw on a 4xx:
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",
},
}
);
if (!response.ok) {
throw new Error(`PostgREST ${response.status}: ${await response.text()}`);
}
const orders = await response.json();
A deployed task only sees environment variables you sync to the Trigger.dev environment — see trigger-dev:config-and-build (syncEnvVars).
Upgrading to 16
- If you set
jwt-role-claim-key, it is now a JSON Path and must start with$:.roles.readbecomes$.roles.read. If you never set it, nothing changes - Filtering or ordering on an embedded table by its table name when the embed has an alias (
?select=alias:table(*)&table.id=eq.1) is deprecated — use the alias (alias.id=eq.1); the new behaviour needsurl-use-legacy-target-namesset tofalse - PostgreSQL 13 and older are not supported
Best Practices
- Always use
${POSTGRESQL_API_URL}and${POSTGRESQL_API_TOKEN}— never hardcode a URL or token - Send
Prefer: return=representationon POST/PATCH when you need the stored row back - Pass
on_conflictin the query string for upserts on a business key - Guard every variable-input
PATCH/DELETEwithhandling=strict,max-affected=N - Give the JWT role the narrowest grants the job needs; the API can do exactly what that role can do
- Use filtering operators for simple lookups; use
execute_sql(seepostgres-mcp-tools) for JOINs, CTEs and anything that needs several round trips