PostgREST: Zero-Code REST API from Postgres
PostgREST turns any Postgres schema into a production REST API with CRUD, filtering, pagination, and RLS security—no controllers, routes, or ORM needed, cutting 80% of backend boilerplate.
Schema Defines Your API, Eliminating Duplication
PostgREST queries your Postgres database schema directly to generate a full REST API, bypassing traditional layers like controllers, routes, ORMs, validation, and services. This eliminates duplicated logic: define data, access rules, and relationships once in the database, and the API mirrors it automatically. With 26k GitHub stars and powering Supabase at production scale, it handles serious traffic without custom backend code.
Setup takes under 60 seconds via Docker Compose: three services (Postgres, PostgREST, Swagger UI). Example docker-compose.yml wires them together; run docker compose up for instant API at /rest/v1/. Create a simple table like todos (id, title, completed, created_at), and GET /todos returns JSON immediately. POST new rows with JSON bodies syncs to DB instantly—no ORM lag.
Autogenerated Swagger UI at /docs provides interactive docs, schemas, and testing for full CRUD exploration.
URL-Driven Queries and RLS for Secure Operations
Handle filtering (/todos?completed=eq.true), sorting (/todos?order=created_at.desc), and pagination (/todos?limit=10&offset=20) via query params—no code required. Relationships use standard Postgres foreign keys, exposed naturally.
Security via Postgres Row Level Security (RLS): enable with ALTER TABLE todos ENABLE ROW LEVEL SECURITY;, then define policies in SQL like CREATE POLICY anon ON todos FOR ALL USING (true) WITH CHECK (true);. Rules live in the DB, reducing middleware scattering and maintenance. Anon access allows full CRUD here; refine for users/roles to enforce fine-grained access without backend auth logic.
Performance Gains vs. Maintenance Trade-offs
Benefits include rapid prototyping (idea to API in minutes), low maintenance (API evolves with schema changes), and high performance (direct DB queries scale like Supabase proves). Skip boilerplate tax: no Express/Prisma controllers syncing schemas across layers.
Drawbacks: Heavy RLS increases DB load—design policies efficiently. Complex business logic pushes toward SQL views/functions, which some teams embrace for purity, others avoid. For intricate apps, add a thin BFF layer on top, but PostgREST handles 80%+ of CRUD.
Use for MVPs and Postgres-Centric Apps
Adopt for prototypes, MVPs, or Postgres-heavy projects to ship faster with stronger security defaults. Avoid if needing heavy custom logic everywhere. Pair with existing DB for quick wins; your database becomes the single source of truth, making API a natural extension rather than a separate system.