API: PostgREST vs custom API and when to use which

Introduction

Supabase provides PostgREST: a REST API generated from your Postgres schema (CRUD by table, filter, sort, pagination). Sometimes you need complex logic, multiple tables, webhooks or external integration → then you write a custom API (NestJS, Express, FastAPI) that calls Supabase (DB or PostgREST). This post compares PostgREST and custom API, when to use which, and how to keep a clear REST contract.

Goal: Choose the right channel (PostgREST vs your own backend) and design a consistent API (contract, versioning if needed).

Features

PostgREST

• Automatic: Each table (e.g. conversations) → resource GET/POST/PATCH/DELETE /rest/v1/conversations. Filter via query (?id=eq.xxx), select columns, order, limit/offset. Header Authorization: Bearer <jwt>; RLS applies.
• Pros: No need to write endpoints; schema change (new column) → API gets it. Good for simple CRUD and frontend calling directly.
• Limits: Complex logic (multi-step, transaction, external services) does not live in PostgREST; you need a separate backend or Edge Function.

Custom API

• Your backend: NestJS, Express, FastAPI, etc. on server/Vercel/Lambda. Can use Supabase client (service role) to read/write DB or call PostgREST; or Prisma/raw SQL.
• Use when: Multi-step workflows, transactions, webhooks, sending email, calling third-party APIs; or when you want to hide schema (API contract different from table structure).

Workflow / Process

1. Shape the use case: Simple CRUD only with a frontend holding a JWT → use PostgREST. Complex logic or a need to hide the DB → design a custom endpoint.
2. Clear contract: Whether PostgREST or custom, define "what the client calls and what it gets back" (method, path, body, response). A custom API should have OpenAPI/Swagger or static docs.
3. Assign roles: PostgREST = direct access to tables (already under RLS); custom API = orchestration, calling Supabase internally. Avoid duplicating endpoints for the same business operation (pick one channel).
4. Auth: Both accept a JWT; the custom API verifies the JWT (post 5) then calls Supabase with the JWT (pass-through) or the service role (when you need controlled RLS bypass).

Convention: Clearly separate the "public API" (called by the frontend) and the "data layer" (Supabase); the custom API is a layer on top of the data layer, not exposing schema details unless needed.

Sample code

Call PostgREST from frontend

const { data } = await supabase
  .from('conversations')
  .select('id, title, created_at')
  .eq('user_id', userId)
  .order('updated_at', { ascending: false })
  .limit(20);

Custom API (Express): aggregate endpoint

// GET /api/me/conversations-summary
app.get('/api/me/conversations-summary', async (req, res) => {
  const userId = getUserIdFromRequest(req);
  if (!userId) return res.status(401).json({ error: 'Unauthorized' });
  const { data, error } = await supabaseAdmin
    .from('conversations')
    .select('id, title, updated_at')
    .eq('user_id', userId)
    .order('updated_at', { ascending: false })
    .limit(20);
  if (error) return res.status(500).json({ error: error.message });
  res.json(data);
});

supabaseAdmin uses the service role; or use the user's JWT (pass-through) so that RLS still applies.

Apply in your architecture

• API contract: PostgREST contract = schema + RLS; custom API contract = endpoint + request/response. Keep consistent (names, JSON format, error codes) so clients and team can integrate easily.
• Separate consumer and data: The frontend can call PostgREST directly (simple) or only call the custom API (hiding the schema). The custom backend plays the "orchestrator" role when needed, without duplicating the entire CRUD already available in PostgREST.
• Single source of truth: The data still lives in Supabase; PostgREST and the custom API read/write the same DB, avoiding syncing two sources.

Conclusion

PostgREST is enough for CRUD and simple queries; use a custom API when you have complex logic or need to hide the schema. The next post will cover Realtime and Edge Functions.

Next post: 09 — Realtime and Edge Functions

Further reading: Supabase — PostgREST API