An Instant REST API for Your Postgres Database (PostgREST)

An instant REST API for your Postgres database

Writing a CRUD API by hand is the most repetitive code in any project. WattleDB skips it: enable the REST API and PostgREST generates a full HTTP interface over your schema — filtering, sorting, pagination, relations and writes — that stays in sync with your tables and respects your access rules.

Guide · 8 min read · Updated July 2026

PostgREST reads your PostgreSQL schema and serves a REST endpoint for every table and view — no API server to write, no code to keep in sync. Change a column and the API changes with it. And because it authenticates with a JWT and honours Row-Level Security, the generated API is as safe as your policies. Here's how to use it on WattleDB.

What you get

A CRUD endpoint per table and view, straight from your schema.
Rich reads: filter, order, paginate, select columns, embed relations.
Writes: insert, update, delete, upsert — with the same URL grammar.
Every request runs under JWT + Row-Level Security.
Works with Supabase's own client libraries, since it's the same PostgREST protocol.

Your schema is your API

Enable the REST API on a database and each table becomes a resource. A table called projects is reachable at /projects, and its columns become the query grammar. There's nothing to deploy — the API is your schema, exposed over HTTP.

Reading: filter, order, paginate

Filtering uses column=operator.value; ordering and pagination are query parameters. To fetch the ten most recent active projects, selecting just three columns:

GET /projects?status=eq.active&select=id,name,created_at&order=created_at.desc&limit=10

Authorization: Bearer <jwt>

Operators cover the usual set — eq, neq, gt, lt, gte, lte, like, ilike, in, is — and you can combine them for range and full-text queries. Pagination is limit/offset (or Range headers) and responses can include an exact count.

Embedding related tables

Where you have foreign keys, PostgREST can embed related rows in one request — no N+1 round trips. To pull each project with its tasks:

GET /projects?select=id,name,tasks(id,title,done)

The response nests tasks inside each project. You can filter and order the embedded resource too, so a single call returns exactly the shape your UI needs.

Writing

Inserts, updates and deletes use the same resource URLs with the matching HTTP verb:

POST /projects
{ "name": "Roadmap", "status": "active" }

PATCH /projects?id=eq.42
{ "status": "archived" }

DELETE /projects?id=eq.42

Add a Prefer: return=representation header and the API returns the affected rows, so you don't need a follow-up read.

It respects Row-Level Security

Every request runs as the anon or authenticated role and is subject to your Row-Level Security policies. That means the API can't return anything a policy forbids — a filter you forget on the client can never leak another tenant's data, because the database enforces isolation underneath. The instant API is safe precisely because the rules live in Postgres, not in the API layer.

When you want more than REST

PostgREST covers the vast majority of app queries, but you're never boxed in: you also get direct SQL access over DIRECT_URL for complex analytics, and you can expose stored procedures as callable endpoints when you need custom logic. And since it speaks the PostgREST protocol, Supabase's client libraries work against it unchanged — handy if you're migrating.

REST API questions

What is PostgREST?

PostgREST is a server that turns a PostgreSQL database into a RESTful API automatically. It reads your schema and serves an endpoint for every table and view, with a rich query grammar for filtering, ordering, pagination and embedding related tables. WattleDB runs it for you, so enabling the REST API gives you a full CRUD interface without writing backend code.

How do I filter and paginate with the WattleDB REST API?

Filtering uses column=operator.value in the query string (e.g. status=eq.active), ordering uses order=column.desc, and pagination uses limit and offset (or Range headers). You can also select specific columns with select= and request an exact row count. It's all standard PostgREST, so the query grammar is well-documented and stable.

Can the REST API return related tables in one request?

Yes. Where foreign keys exist, PostgREST can embed related rows using select=id,name,related_table(...), returning nested data in a single request instead of multiple round trips. You can filter and order the embedded resource too, so one call returns exactly the shape your UI needs.

Is the auto-generated API secure?

Yes — every request authenticates with a JWT and runs under PostgreSQL Row-Level Security as the anon or authenticated role. The API can't return or change anything your policies forbid, so a missing filter on the client can't leak data. Because access rules live in the database rather than the API layer, the instant API is as safe as your RLS policies.

Does it work with Supabase client libraries?

Yes. WattleDB's REST API uses the same PostgREST protocol Supabase is built on, so Supabase's own client libraries work against it unchanged. That makes migrating from Supabase largely a matter of changing a few environment variables rather than rewriting your data-access code.