REST API

Generated from the exposed schema (NELCOTA_DB_SCHEMA, default public). Every table, view and function in the schema becomes an endpoint, and Postgres decides access: GRANTs say which roles may use each object and RLS says which rows each user sees.

Prerequisites#

You need an initialized server, tables/functions in its exposed schema and GRANTs for the caller. Query examples use illustrative orders/products schemas; create your own matching SQL first. This reference describes current source; use release-tag documentation for older binaries.

New tables have no GRANT for anon/authenticated: until you grant it, the API answers 401/403. This is intentional.

GRANT SELECT, INSERT, UPDATE, DELETE ON public.my_table TO authenticated;
ALTER TABLE public.my_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY ... ;

Copy

Reads: GET /rest/v1/{table}#

Operators#

Values reach Postgres as parameters and Postgres itself converts them to the column type ($1::text::<type>). A value invalid for the type gives 400.

Embedding related rows#

select follows foreign keys in either direction, so one request returns an order with its customer and items:

GET /rest/v1/orders?select=id,total,customers(name),items(product,qty)

[{ "id": 10, "total": 30.00,
   "customers": { "name": "Ana" },
   "items": [{ "product": "Pen", "qty": 2 }, { "product": "Ruler", "qty": 1 }] }]

• When this table has the foreign key (orders.customer_id → customers), the embed is an object, or null when nothing matches. When the other table points here (items.order_id → orders), it is an array, [] when empty.
• items(*) takes every column. purchases:orders(*) renames the key.
• Two foreign keys to the same table (buyer_id, seller_id → users) are ambiguous: pick one with the column or the constraint name, buyer:users!buyer_id(email),seller:users!seller_id(email). The error lists the options.
• Embeds nest, up to 4 levels: customers?select=name,orders(id,items(product)). A table cannot be embedded into itself (self-reference). A key that repeats a selected column is a 400: rename it with an alias.
• RLS applies to the embedded table too: each embed runs with the request's role, so it shows exactly what a direct read of that table would. A related row the user cannot see becomes null or is left out of the array.
• Works in the representation of writes (return=representation).
• Each embed is a correlated subquery: index the foreign key columns on the "many" side (Postgres does not create those indexes on its own).

Filtering, ordering and paging embedded rows#

A parameter prefixed with the embed's key (its alias, when it has one) applies to the embedded rows; a deeper embed adds its key to the path:

GET /rest/v1/customers?select=name,orders(id,total,items(product))
    &orders.total=gte.5
    &orders.order=total.desc
    &orders.limit=2
    &orders.items.or=(qty.eq.1,qty.gt.5)

• Every filter operator and or/and work, plus order, limit and offset. Paging only applies to arrays: on a single-row embed it is a 400.
• These narrow the embedded rows only: the parent rows stay, with [] or null where nothing matched. Filters on the parent table (?id=eq.1) still pick the parent rows.
• The order of the parameters in the URL does not matter.

Combining filters: or and and#

Same syntax as PostgREST. Inside the parentheses each filter is written column.operator.value:

?or=(status.eq.paid,status.eq.shipped)
?or=(stock.eq.0,and(price.gt.100,featured.is.true))
?not.or=(status.eq.cancelled,deleted_at.not.is.null)
?or=(price.gt.100,price.lt.3)&stock=gt.0      # the group is ANDed with the rest

• and(...) and or(...) nest; not. before a group negates it, and column.not.operator.value negates one filter.
• A value with commas or parentheses goes in double quotes, with \" and \\ as escapes: or=(name.eq."Ruler, 30cm",name.eq."f(x)"). in lists keep their own quoting.
• At most 8 nested levels and 100 filters per group; beyond that, 400.
• A column literally named or or and cannot be used as a direct filter, only inside a group (and=(or.eq.1)), as in PostgREST.
• RLS still applies: a group can only narrow what the policies allow, never widen it, on reads and on PATCH/DELETE.

Responses#

• Body: a JSON array built by Postgres (json_agg).
• Content-Range: 0-19/*, or 0-19/137 with Prefer: count=exact.
• NELCOTA_MAX_ROWS defaults to 1000 and must be positive. It caps the root read, each embedded collection, and set-returning RPCs, including explicit limits larger than the cap. Embedded collections in write representations have a fixed cap of 1000. Paginate collections to reach subsequent rows.
• JSON has an 8 MiB serialization budget, checked in PostgreSQL before each row enters an aggregate, also on write representations and scalar RPCs. Intermediate embedded values count towards the same budget, so deeply nested results can reach it with less than 8 MiB of final output. Oversized results return 413 response_too_large; a write requesting such a representation rolls back. A single database value must still be serialized before its size can be checked. Administrative streaming exports remain available for large data.
• select accepts at most 128 items across the relation tree. Repeated columns are rejected; * already includes explicitly selected columns.

Writes#

An insert accepts at most 1000 rows and 128 distinct column sets per request. Split larger batches into requests. A request body (writes and RPC arguments) can have at most NELCOTA_MAX_BODY_BYTES bytes, 2 MiB by default; a larger one gets 413 payload_too_large. The body is buffered and parsed whole, so raise the limit only as far as the server's memory allows. Each accepted request keeps its transactional behavior and missing columns still receive their defaults.

• select= also picks the columns of the representation.
• On POST, missing columns get their DEFAULT, even in a batch whose objects have different keys.
• An empty body on POST/PATCH is refused (400 invalid_body); to insert a row of defaults, send {}. An /rpc call without a body has no arguments.
• PATCH/DELETE without a filter are refused (400), to avoid changing or deleting the whole table by mistake. To do that on purpose, use an explicit filter (?id=not.is.null).
• order, limit and offset are refused on PATCH/DELETE (400 invalid_query): they change every row the filters match, so narrow the filters instead.
• With Prefer: count=exact, PATCH/DELETE answer Content-Range: */<n> with the number of rows changed (and Preference-Applied: count=exact), with or without return=representation.
• return=representation runs RETURNING, which requires SELECT permission (GRANT + policy) on the written rows.

Upsert: create or update in one call#

POST /rest/v1/products
Prefer: resolution=merge-duplicates
[{ "id": 1, "price": 3.00 }, { "id": 99, "name": "Eraser", "price": 1.00 }]

• Prefer: resolution=merge-duplicates updates a row whose key already exists, with the columns the object sent (the others keep their values; the key itself is never changed). resolution=ignore-duplicates keeps the existing row; with return=representation, only the rows actually written come back.
• The key is the primary key, or ?on_conflict=col1,col2, which must match a unique constraint (otherwise 400). on_conflict without resolution is a
  400. The response confirms with Preference-Applied.
• A batch that repeats a key gets a 400 from Postgres (a row cannot be updated twice in one statement).
• A GENERATED ALWAYS AS IDENTITY column cannot receive values, so it cannot be the upsert key: use BY DEFAULT, or another unique column with on_conflict.
• RLS still decides: on a conflict, the existing row must pass the UPDATE policy. A user cannot take over someone else's row through an upsert (403).

Functions: POST /rest/v1/rpc/{function}#

Body: an object with the named arguments ({"a": 1, "b": 2}); arguments with a DEFAULT are optional. With overloads, the signature whose names match the keys wins.

RAISE EXCEPTION becomes 400 with the message, also with a custom SQLSTATE of class P0 (USING ERRCODE = 'P0002') and for a failed ASSERT; its DETAIL and HINT come back as details and hint (see Errors). The function runs with the JWT's role (unless it is SECURITY DEFINER): auth.uid() works inside it.

Warning: By default, Postgres grants EXECUTE on new functions to PUBLIC (including anon). For sensitive functions: REVOKE EXECUTE ON FUNCTION f() FROM PUBLIC; GRANT EXECUTE ON FUNCTION f() TO authenticated;

OpenAPI: GET /rest/v1/#

An OpenAPI 3.0 document generated from the catalog and filtered by the request's role: anon only sees what anon may use.

Schema models: nelcota types -o database.ts (TypeScript, default), or nelcota types --lang rust -o database.rs for the Rust SDK. The Rust models distinguish omitted write fields from explicit nulls and keep Postgres numeric precision.

Views run with their owner's underlying privileges by default and can bypass the caller's row policies even when the source table enables RLS. For views that should apply the caller's policies, use security_invoker = true and grant the caller the required access to the underlying tables. The panel warns about exposed owner views; changing that option or revoking their GRANT clears the warning. Materialized views and foreign tables have no local row policies and also need explicit access controls.

Catalog reload#

The API keeps the introspection in memory. An event trigger (migration V3) notifies the server after DDL. Reloads are coalesced/serialized and failed refreshes retry with bounded backoff; there is no fixed completion guarantee. Without a superuser (some managed Postgres services), reload by hand:

NOTIFY nelcota, 'reload schema';

Errors#

Every error from the server (REST, auth, storage, unknown routes, wrong methods, body limits and timeouts) is JSON with the same shape:

{"code": "db_error", "message": "duplicate key value violates unique constraint \"products_pkey\" (23505)",
 "sqlstate": "23505", "details": "Key (id)=(1) already exists.", "constraint": "products_pkey"}

• code and message are always present. code is stable and meant for programs; message is English text for people.
• Errors that come from Postgres add sqlstate (the 5-character SQLSTATE) and, when Postgres provides them, details, hint and constraint (DETAIL, HINT and the violated constraint's name). Absent fields are omitted, never null. For db_error, message stays "<text> (<sqlstate>)".
• Every response carries x-request-id (see Request IDs): quote it when reporting an error.

Auth and storage add their own codes; see The auth schema and Storage.

Request IDs#

Each response carries an x-request-id header: the one the request came with (for example from a proxy in front), or a new UUID. The server logs one line per response at INFO with the method, path (never the query string), status, latency and that id, so an error a user reports can be found in the log. Browsers can read the header (it is in Access-Control-Expose-Headers).

Out of the MVP#

Filtering parent rows by their embeds (PostgREST's !inner), self-referencing embeds, GET on /rpc, Accept: application/vnd.pgrst.object+json.

Related guides#

See auth, JWT/roles, storage and troubleshooting.