PostgREST and Supabase

PostgREST turns a Postgres table into a REST resource, and Supabase’s data API is PostgREST. The generic http_bulk output writes to it: an upsert is a POST of a JSON array, a delete is a filter on the primary key. Tested against PostgREST 16.4. A hosted Supabase project has not been tried.

Use this when only the REST API is reachable. With a database connection, the Postgres output writes the same rows directly and faster.

output:
  http_bulk:
    url: http://localhost:3000
    headers:
      Prefer: resolution=merge-duplicates
    operation: "${metadata:postgres.operation}"
    upsert:
      path: /books
      format: json_array
    delete:
      method: DELETE
      path: /books?id=in.({ids})

For Supabase, the documented form is the project URL with /rest/v1 before the table, and the key in two headers:

output:
  http_bulk:
    url: https://your-project.supabase.co
    headers:
      apikey: <key>
      Authorization: Bearer <key>
      Prefer: resolution=merge-duplicates
    upsert:
      path: /rest/v1/books
      format: json_array
    delete:
      method: DELETE
      path: /rest/v1/books?id=in.({ids})

What to know

• The table needs a primary key. resolution=merge-duplicates updates the row with the same key; without the header a second write of a row fails.
• A request is one transaction. One row the table refuses, for example a text in an integer column, fails every row of that request with the database’s message. A smaller batch_size narrows it down.
• Every row of a request needs the same columns, which is what a change stream of one table gives.
• Row level security applies to the role behind the key.

All fields and the behaviour on errors are on the http_bulk page.