REST API

Every table, view and function in the public schema is an HTTP endpoint at /rest/v1. Nothing is generated or deployed — the API is read from the catalog on demand, so a table created a second ago is queryable now. The dialect is a PostgREST-compatible subset, so client patterns transfer directly.

The endpoints

Route	Does
GET `/rest/v1/:table`	Read rows, with filters, ordering, paging and embeds
POST `/rest/v1/:table`	Insert one row or many, optionally as an upsert
PATCH `/rest/v1/:table`	Update the rows the filters match — a filter is required
DELETE `/rest/v1/:table`	Delete the rows the filters match — a filter is required
POST `/rest/v1/rpc/:function`	Call a Postgres function with named arguments

Authenticating a request

apikey: <anon key>                          # which Postgres role the request runs as
authorization: Bearer <user access token>   # who the user is

Either header alone works, and the bearer token wins when both are present. With no credentials at all the request runs as anon. What happens next is decided by row level security, not by this API.

Reading

curl -s "$URL/rest/v1/articles?select=id,title,published_at&published_at=not.is.null&order=published_at.desc&limit=3" \
  -H "apikey: $ANON_KEY" -i

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
content-range: 0-2/*

[{"id":9,"title":"Indexes for policies","published_at":"2026-08-20T08:00:00.000Z"},
 {"id":7,"title":"Reading a query plan","published_at":"2026-08-14T08:00:00.000Z"},
 {"id":4,"title":"Two databases","published_at":"2026-08-02T08:00:00.000Z"}]

The whole filter grammar — every operator, the value rules, logic trees, ordering and paging — has its own page: Filtering and paging. What follows here is the shape of the API around it.

Choosing columns

Form	Meaning
`select=*`	Every column. The default.
`select=id,title`	Those columns
`select=name:title`	Alias — the JSON key is `name`
`select=id::text`	Cast to a single-word type name
`select=*,author:users(id,name)`	One level of embedded resource
`select=*,users!articles_author_fkey(name)`	Disambiguate when two foreign keys point at the same table

Embedded resources

Embedding follows a foreign key in either direction. A many-to-one embed produces an object; a one-to-many embed produces an array. Each embed compiles to one LEFT JOIN LATERAL producing a single JSON column, so nested objects arrive in the same round trip and no join fan-out has to be undone in JavaScript.

curl -s "$URL/rest/v1/articles?select=id,title,author:users(id,name),comments(id,body)&id=eq.9" \
  -H "apikey: $ANON_KEY"

[{"id":9,
  "title":"Indexes for policies",
  "author":{"id":"6c1f5c62-8e4c-4a6b-9a54-1c9d0a0d1a11","name":"Ada"},
  "comments":[{"id":31,"body":"Useful, thanks"},{"id":33,"body":"Also index the FK"}]}]

Only one level is supported. a(b(c)) is a deliberate 400, because the second level is where the query plan stops being predictable.

{"error":{"code":"bad_request",
  "message":"only one level of embedded resources is supported (\"comments(author(name))\")",
  "details":null}}

Two foreign keys to the same table are ambiguous, and the error names your way out:

{"error":{"code":"bad_request",
  "message":"the relationship between articles and users is ambiguous; disambiguate with users!<constraint> — candidates: articles_author_fkey, articles_editor_fkey",
  "details":null}}

An embedded resource is a table like any other, so its own policies apply. An embed of a table your role cannot read yields null or [], not an error.

One row instead of an array

curl -s "$URL/rest/v1/articles?id=eq.9" \
  -H "apikey: $ANON_KEY" \
  -H 'accept: application/vnd.pgrst.object+json'

Returns the object itself. A result that did not contain exactly one row is a 406:

{"error":{"code":"not_acceptable",
  "message":"JSON object requested, but 0 rows were returned","details":null}}

In the client that is .single(), or .maybeSingle() to allow zero.

Writing

Insert

curl -s -X POST "$URL/rest/v1/articles" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"title":"Hello","slug":"hello"}' -i

HTTP/1.1 201 Created
location: /rest/v1/articles?id=eq.12
content-type: application/json; charset=utf-8

[{"id":12,"author":"6c1f5c62-…","slug":"hello","title":"Hello","body":"","published_at":null,
  "created_at":"2026-08-24T10:02:41.882Z"}]

The body is an object or an array of objects. Without Prefer: return=representation the response is 201 with an empty body and just the Location header — which is what you want for a bulk insert, so the rows do not cross the wire twice.

A bulk insert takes the union of the keys across all rows; a row missing one of them gets that column's DEFAULT.

-d '[{"title":"One","slug":"one"},{"title":"Two","slug":"two","body":"…"}]'

Upsert

Header or parameter	Meaning
`Prefer: resolution=merge-duplicates`	`ON CONFLICT … DO UPDATE`
`Prefer: resolution=ignore-duplicates`	`ON CONFLICT … DO NOTHING`
`x-upsert: true`	The same as `merge-duplicates`
`?on_conflict=slug`	The conflict target. Defaults to the primary key.

curl -s -X POST "$URL/rest/v1/articles?on_conflict=slug" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -H 'Prefer: resolution=merge-duplicates,return=representation' \
  -d '{"slug":"hello","title":"Hello again"}'

A table with no primary key and no on_conflict is a 400 that says exactly that: upsert needs a conflict target: give the table a primary key or pass the on_conflict parameter.

Update

curl -s -X PATCH "$URL/rest/v1/articles?id=eq.12" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"title":"Edited"}'

Without Prefer: return=representation the response is 204 with no body.

Delete

curl -s -X DELETE "$URL/rest/v1/articles?id=eq.12" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN"

A filter is required on writes

curl -s -X DELETE "$URL/rest/v1/articles" -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN"

{"error":{"code":"bad_request",
  "message":"a filter is required for update/delete",
  "details":{"hint":"add a filter such as ?id=eq.1, or send \"Prefer: unsafe-mutation\" to delete every row"}}}

Calling functions

curl -s -X POST "$URL/rest/v1/rpc/search_articles" \
  -H "apikey: $ANON_KEY" -H 'content-type: application/json' \
  -d '{"term":"postgres","max_results":5}'

create or replace function public.search_articles(term text, max_results int default 20)
returns setof public.articles
language sql stable
as $$
  select * from public.articles
   where to_tsvector('english', title || ' ' || body) @@ websearch_to_tsquery('english', term)
   limit max_results;
$$;

The function returns	The response is
A set of composite rows	A JSON array of objects
A set of scalars	A JSON array of values
A single composite	A JSON object
A single scalar	A bare JSON value — `42`, `"ok"`, `null`
`void`	`204` with no body

Overloads are resolved by the argument names you supply. When none matches, the error lists the signatures that exist:

{"error":{"code":"bad_request",
  "message":"no overload of public.search_articles accepts (q) — signatures: search_articles(term, max_results)",
  "details":null}}

Request and response headers

Header	Direction	Meaning
`apikey`	request	The project key. Names the Postgres role.
`authorization: Bearer …`	request	The user's access token. Wins over `apikey`.
`accept: application/vnd.pgrst.object+json`	request	Return one object, or `406`
`prefer: return=representation`	request	Send the affected rows back
`prefer: count=exact`	request	Fill in the total in `content-range`
`prefer: resolution=merge-duplicates`	request	Upsert
`prefer: unsafe-mutation`	request	Allow an unfiltered write
`range: items=0-19`	request	Paging, when `limit`/`offset` are absent
`x-upsert: true`	request	The same as `resolution=merge-duplicates`
`content-range`	response	`0-19/*`, or `0-19/347` when a count was asked for
`location`	response	On `201`, the filter URL of the new row

CORS exposes content-range and x-total-count, and allows authorization, apikey, content-type, prefer, range, x-client-info and x-upsert. A custom header your app invents will be blocked by the browser until you add it to that list in src/index.ts.

Errors

Every error has the same shape, from every prefix of the API:

{"error":{"code":"unique_violation",
  "message":"duplicate key value violates unique constraint \"articles_slug_key\"",
  "details":{"detail":"Key (slug)=(hello) already exists.","hint":null,"sqlstate":"23505"}}}

HTTP	`code`	Cause
400	`bad_request`	Malformed filter, unknown column, unknown operator, missing filter on a write
400	`not_null_violation`	A required column was absent — SQLSTATE `23502`
400	`check_violation`	A `CHECK` constraint refused the row — `23514`
400	`invalid_text_representation`	A value could not be cast to the column's type — `22P02`
400	`string_too_long`	`22001`
400	`undefined_column`	`42703`
400	`undefined_function`	`42883`
400	`raise_exception`	A `raise exception` in a trigger or function — `P0001`
401	`unauthorized`	Malformed, expired or wrongly signed token
403	`forbidden` / `insufficient_privilege`	The role lacks table grants — `42501`. Not an RLS denial.
404	`not_found` / `undefined_table`	No such table or view in `public` — `42P01`
406	`not_acceptable`	A singular response was requested and the row count was not 1
408	`statement_timeout`	Over `DATABASE_STATEMENT_TIMEOUT_MS` — `57014`
409	`unique_violation` / `foreign_key_violation`	`23505` / `23503`
413	`payload_too_large`	Over the body limit
429	`rate_limited`	300 requests per minute per IP across the whole API
500	`internal_error`	Anything unmapped

How a request is compiled

Worth knowing, because it explains why some things are a 400 rather than a surprising query plan.

A value from the request never reaches the SQL text. Exactly three kinds of token are interpolated: operators looked up in a fixed table, identifiers copied out of the catalog (not out of the request), and cast type names that matched a strict regex. Everything else is a bound parameter.
An unknown column is resolved against the catalog and refused with a 400 before anything reaches the planner.
limit defaults to 1000 and is clamped to 10000, so an unbounded select cannot exhaust memory.
Filter nesting is capped at 20 levels, so a hostile or=(or=(or=(…))) cannot exhaust the stack.
Prefer: count=exact runs a second aggregate in the same transaction, so the total is the number of rows your policies allow, not the table's raw row count.

In the client

const { data, error, count } = await bl
  .from('articles')
  .select('id, title, author:users(name)', { count: 'exact' })
  .eq('published', true)
  .order('created_at', { ascending: false })
  .range(0, 19);

await bl.from('articles').insert({ title: 'Hello' }).select().single();
await bl.from('articles').upsert(rows, { onConflict: 'slug' });
await bl.from('articles').update({ title: 'x' }).eq('id', 12);
await bl.from('articles').delete().eq('id', 12);
await bl.rpc('search_articles', { term: 'postgres' });

Full reference: the JavaScript client.

Performance notes

A filter is a WHERE clause and a policy is another one. Index both.
Prefer: count=exact costs a second aggregate over the same predicate. Leave it off for infinite scroll; use it for page numbers.
An embedded resource is a LATERAL subquery per row of the outer result. Index the foreign key column.
The catalog snapshot is cached for 60 seconds and invalidated immediately by DDL through /admin/v1.

Edit this page Report a problem