REST data API

Every table in your project is a REST endpoint. Row-Level Security is enforced on every request.

All requests need Authorization: Bearer <key-or-token>. PATCH and DELETE refuse to run without at least one filter; there is no accidental full-table write.

Reading rows

GET /rest/v1/todos?select=id,task&done=is.false&order=id.desc&limit=20

Filter operators

Quote items in in.(…) to include commas (?tag=in.("a,b","c")). A full-text operator may name a text-search config: ?body=fts(english).hello.

Negation and logical trees

Prefix any operator with not. to negate it, and combine conditions with or=(…) / and=(…): nestable, using the dotted column.op.value form inside:

# NOT: rows where id is not 3
GET /rest/v1/todos?id=not.eq.3

# OR: done, or high priority
GET /rest/v1/todos?or=(done.is.true,priority.gte.5)

# nested: (done = false) AND (priority ≥ 5 OR pinned = true)
GET /rest/v1/todos?done=is.false&and=(priority.gte.5,or(pinned.is.true))

Counting rows

Ask for the total number of matching rows with the Prefer: count=exact header. The total comes back in the Content-Range response header (and populates the count field that client libraries read):

curl -sI "https://<ref>.kethosbase.com/rest/v1/todos?done=is.false" \
  -H "Authorization: Bearer <key>" \
  -H "Prefer: count=exact"

→ Content-Range: 0-19/42

The count applies the same filters as the read, ignoring limit and offset.

A single object instead of an array

Send Accept: application/vnd.pgrst.object+json to get one JSON object back instead of an array. It answers 406 (code PGRST116) when the result is not exactly one row; this is the contract behind a client's .single() (errors on zero rows) and .maybeSingle() (maps the 406 to null):

curl "https://<ref>.kethosbase.com/rest/v1/todos?id=eq.1" \
  -H "Authorization: Bearer <key>" \
  -H "Accept: application/vnd.pgrst.object+json"

→ { "id": 1, "task": "ship it", "done": false }

Embedding related tables

Name a related table inside select to pull its rows in the same request. The relationship is resolved through a foreign key: a to-one FK embeds a JSON object (or null), a to-many FK embeds an array.

GET /rest/v1/posts?select=id,title,author(*),comments(id,body)

→ [ { "id": 1, "title": "Hello",
      "author": { "id": 7, "name": "Alice" },
      "comments": [ { "id": 3, "body": "nice" } ] } ]

Add !inner to drop parent rows with no embedded match (author!inner(*)); !left is the default. Embeds nest, and mix freely with * and plain columns. Every embedded subquery runs under the same Row-Level Security as the base read; an embed can never surface a row a direct read couldn't, so it adds reach, never privilege.

Filtering, ordering and paging an embed

Qualify a parameter with the embedded resource's name (or its alias, when you gave it one) to aim it at that resource instead of the base table. Filters take the same operators, negation and or()/ and() trees as a top-level filter:

GET /rest/v1/posts?select=title,comments(id,body)&comments.body=ilike.*nice*
GET /rest/v1/posts?select=title,comments(id,body)&comments.order=id.desc&comments.limit=5
GET /rest/v1/posts?select=title,writer:author(name)&writer.name=eq.Alice

By default a filter only narrows the embedded array; parent rows stay, possibly with an empty embed. Combine it with !inner to filter the parents too:

GET /rest/v1/posts?select=title,comments!inner(body)&comments.body=eq.nice

→ only the posts that have a comment reading "nice"

Nested embeds are addressed by path (posts.comments.body=eq.nice), and an embed with no limit stays unbounded. You can also order the base table by a to-one embed's column with order=rel(col): for example ?select=title,author(name)&order=author(name).desc. All of it runs inside the embedded subquery, so a filter can only narrow what your policies already allow: it can never reveal a row a direct read couldn't.

When two tables share more than one foreign key

Naming the table alone is ambiguous when more than one foreign key joins the same pair, a messages table with both a sender_id and a recipient_id pointing at users, say. That returns a 400 listing the candidates rather than guessing. Add !hint to say which one you mean: either the constraint's name or the column carrying the foreign key.

GET /rest/v1/messages?select=body,sender:users!sender_id(name),recipient:users!recipient_id(name)

→ [ { "body": "hi",
      "sender":    { "name": "Ada" },
      "recipient": { "name": "Alan" } } ]

The same hint works in the to-many direction (users?select=name,messages!sender_id(body)), and a hint that matches nothing is a 400 too; a typo never passes as decoration.

Embedding also works across composite (multi-column) foreign keys, in either direction, with nothing extra to write: the correlation uses every column of the key.

Merging an embed's columns into the row

Prefix an embed with ... to spread it: the related row's columns land beside the parent's own instead of nested under a key. Useful when a to-one relationship is really an extension of the row.

GET /rest/v1/posts?select=title,...authors(name,email)

→ [ { "title": "Hello", "name": "Alice", "email": "alice@example.com" } ]

Name each column you want (* does not expand here) and alias them to rename the merged keys (...authors(author_name:name)). Values keep their types, and a parent with no match keeps its row with the merged columns null, exactly as an ordinary to-one embed does; add !inner to drop those parents instead. !hint composes too.

A filter aimed at a spread works as it does for any embed (&authors.name=eq.Alice); it decides whether the row matches, so a miss merges nulls. order, limit and offset are rejected on a spread rather than quietly ignored: they describe choosing among many rows, and a to-one has at most one.

Spread is for to-one relationships only. A to-many has no single row to merge (five titles cannot all become title) so it returns a 400 pointing you at the ordinary rel(...) embed, which is what expresses that.

Inserting rows

POST a JSON object, or an array of objects for a batch insert:

curl -X POST "https://<ref>.kethosbase.com/rest/v1/todos" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d '[{"task": "one"}, {"task": "two"}]'

With Prefer: return=representation the response carries the inserted rows (including generated ids); without it you get 201 Created and no body.

Upsert

Insert, but merge on a conflict instead of failing. POST with Prefer: resolution=merge-duplicates (update the existing row) or resolution=ignore-duplicates (leave it). The conflict target defaults to the table's primary key; name a different unique column set with ?on_conflict=:

curl -X POST "https://<ref>.kethosbase.com/rest/v1/todos?on_conflict=id" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates,return=representation" \
  -d '{"id": 1, "task": "revised"}'

A table with neither a primary key nor an explicit on_conflict answers 400 with a clear message rather than a raw database error.

Updating and deleting

curl -X PATCH "https://<ref>.kethosbase.com/rest/v1/todos?id=eq.1" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -d '{"done": true}'

curl -X DELETE "https://<ref>.kethosbase.com/rest/v1/todos?done=is.true" \
  -H "Authorization: Bearer <key>"

Both answer 204 No Content, or the affected rows with Prefer: return=representation.

Calling functions (RPC)

Invoke a PostgreSQL function in the public schema over /rest/v1/rpc/{function}. POST supplies named arguments in the JSON body; a read-only function can be called with GET and query-string arguments:

curl -X POST "https://<ref>.kethosbase.com/rest/v1/rpc/search_todos" \
  -H "Authorization: Bearer <key>" \
  -H "Content-Type: application/json" \
  -d '{"q": "ship"}'

A set-returning function yields a JSON array; a scalar or single-composite function yields the bare value; a void function returns 204. The call runs under the caller's role with RLS applied, so a function honours its own PostgreSQL EXECUTE grants, to keep a function off the publishable key, revoke execute … from public (and the anon role).

Responses and errors

Successful reads return a JSON array (or a single object with the object Accept header). On the data routes errors come back as a flat object so a client's error type populates directly:

{ "message": "…", "code": "…", "details": null, "hint": null }

An anonymous caller gets generic text with no details/ hint; only the secret key sees database detail. The auth and storage routes keep their own { "error": { "code", "message" } } envelope. See Limits & errors for every status code, the 10-second statement timeout, and the daily request quotas.