Postgres REST API: Instant Endpoints from Your Schema

A Postgres REST API generated from your schema turns every table, view and function into an HTTP endpoint the moment you create it, so you do not write or deploy a CRUD layer at all. On StitchDB each project serves one at https://<project-ref>.p.stitchdb.com/rest/v1/, speaking the PostgREST protocol, so client libraries for that API work against it. Authorization is not in the API layer; it lives in Postgres as Row Level Security, and runs on every request.

This article explains how the generated API maps to your schema, what it does well, where it stops and how to keep it safe.

#How the schema becomes an API

The rule is short: everything in the public schema is exposed. Create a table and it is reachable at once:

create table public.todos (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users on delete cascade,
  task text not null,
  is_complete boolean not null default false,
  created_at timestamptz not null default now()
);

The API reloads its schema automatically when you create or change tables, views or functions, so there is no restart step after a migration. Studio's API page shows generated docs for each table and function, with JavaScript and cURL you can copy.

#Reading data

Every request carries your key in the apikey header. To act as a signed-in user, add their access token as a bearer token:

curl 'https://<project-ref>.p.stitchdb.com/rest/v1/todos?select=id,task&is_complete=eq.false' \
  -H "apikey: <anon-key>" \
  -H "Authorization: Bearer <user-access-token>"

The same query from JavaScript:

const { data, error } = await stitchdb
  .from('todos')
  .select('id, task')
  .eq('is_complete', false)
  .gt('id', 10)
  .or('task.ilike.%ship%,task.ilike.%launch%')

Filters are query parameters in the form column=operator.value. The common ones:

#Joins without writing joins

Name a related table inside select and the API follows the foreign key:

const { data } = await stitchdb.from('todos').select('id, task, owner:profiles(username)')
const { data: mine } = await stitchdb.from('todos').select('id, profiles!inner(username)').eq('profiles.username', 'ada')

owner: renames the embedded object; !inner keeps only rows with a match, the same as an inner join. This is where a generated API saves the most time compared with a hand-written one: a nested response shape that would take a resolver or a serializer is one string.

#Counting and paging

const { count } = await stitchdb.from('todos').select('*', { count: 'exact', head: true })

const { data } = await stitchdb
  .from('todos')
  .select('*')
  .order('created_at', { ascending: false, nullsFirst: false })
  .range(0, 9) // the first 10 rows

Over HTTP, Prefer: count=exact returns the total in the Content-Range header. count: 'planned' or 'estimated' are faster on large tables. Each response returns at most your plan's rows per request (1,000 on Free, 5,000 on Pro, 10,000 on Scale, as of November 2026). For deep pages, filter on an indexed column (.gt('id', lastId)) instead of a large offset, which has to scan every skipped row.

You can also ask for CSV (Accept: text/csv or .csv()) and for the query plan (.explain()), which helps when a filter is slower than you expect.

#Writing data

await stitchdb.from('todos').insert([{ task: 'One' }, { task: 'Two' }]) // many rows at once
await stitchdb.from('todos').update({ is_complete: true }).eq('id', 1)

// Insert or update by primary key (or another unique column)
await stitchdb.from('profiles').upsert({ id: uid, username: 'ada' }, { onConflict: 'id' })

// Get the changed rows back
const { data } = await stitchdb.from('todos').insert({ task: 'New task' }).select()

Upserts over HTTP use ?on_conflict=id with Prefer: resolution=merge-duplicates, and Prefer: return=representation returns the changed rows. Two safety rails are built in: updates and deletes without a filter are rejected, so a missing .eq() cannot change every row, and while a project is over its database size limit every write fails with error 25006 while reads keep working.

#Business logic as Postgres functions

When an operation is more than one statement, put it in a function and call it over RPC:

create function public.search_todos(term text) returns setof public.todos
language sql stable as $$
  select * from public.todos where task ilike '%' || term || '%'
$$;

// Arguments go in the JSON body; set-returning functions can be filtered like tables
const { data } = await stitchdb.rpc('search_todos', { term: 'ship' }).eq('is_complete', false).limit(10)

// Read-only functions can also be called with GET
const { data: n } = await stitchdb.rpc('count_open_todos', {}, { get: true })

A function runs in one transaction, next to the data, so "reserve stock and create the order" either happens completely or not at all. Functions run with the caller's role by default (security invoker), so Row Level Security still applies. A security definer function runs with its owner's rights and skips RLS; give it a fixed search_path and revoke execute from roles that should not call it:

create function public.admin_stats() returns json
language sql security definer set search_path = '' as $$
  select json_build_object('users', (select count(*) from auth.users))
$$;

revoke execute on function public.admin_stats() from anon, authenticated;

#Security lives in the database

A generated API is only as safe as your policies. The anon key is public by design; it ships in your web bundle and your mobile app. What it can do is decided by Row Level Security:

alter table public.todos enable row level security;

create policy "Users manage their own todos" on public.todos
  for all to authenticated
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

With that policy, GET /rest/v1/todos returns only the caller's rows, without a user_id filter in the request. The same rule protects the REST API, GraphQL, Realtime and any SDK, because they all end at the same Postgres check.

The biggest risk is forgetting. Postgres creates tables with RLS off, and a table in public without RLS is readable and writable by anyone with the anon key. StitchDB has three guards for that:

• RLS Guard turns Row Level Security on for every new table in public, however it is created. It is on by default for new projects.
• Exposure check in Studio lists tables with RLS off, policies that are always true, SECURITY DEFINER functions callable through the API and public buckets, then tests each table as an anonymous visitor and as a random signed-in user inside rolled-back transactions.
• Block exposing migrations makes the migrations API refuse a migration that leaves a new critical exposure.

The Row Level Security guide covers policy patterns for teams and roles.

#Limits and abuse control

Values from the plans API as of November 2026; see API limits and pricing. Requests above the per-second limit get 429. On top of that, you can add your own rules in API › Rate limits, such as "10 comments a minute per user" on POST comments or "5 sign-ins a minute per IP". Requests over a rule get 429 with Retry-After before they reach the database. For limits that depend on data, call ratelimit.enforce(name, max, per) from a trigger. See rate limits.

Responses over 1 KB are compressed with brotli or gzip when the client asks, which most do.

#When the generated API is not enough

A generated API covers most app traffic, but some work needs real code:

• Secrets and third parties. Charging a card or calling an AI model needs a key that cannot sit in a client. Use an Edge Function.
• Webhooks from other services. They need signature checks and a fixed URL. Edge Functions again, or the built-in Stripe and in-app purchase sync.
• Long or heavy jobs. Requests are cancelled at the query timeout. Use background jobs or queues.
• A public API for other developers with its own versioning. Expose a dedicated schema or views rather than your raw tables, so you can change the tables without breaking callers.

If your clients prefer GraphQL, the same schema is also available at /graphql/v1; see GraphQL from Postgres.

#Typed clients

Because the API is derived from the schema, the types can be too:

stitchdb gen types typescript --linked -o src/database.types.ts

Pass the Database type to createClient<Database>() and a renamed column becomes a compile error. Studio can regenerate types on every schema change and open a GitHub pull request; see typed clients.

#FAQ

#Is this the PostgREST API?

It speaks the PostgREST protocol, the same one the open-source Supabase stack uses, so PostgREST query syntax and client libraries work against it.

#Can I expose a schema other than public?

Only public is exposed by default. The Accept-Profile and Content-Profile headers (or stitchdb.schema('name')) choose another exposed schema.

#How do I stop the API from returning a table?

Move it to a schema that is not exposed, revoke privileges from anon and authenticated, or enable RLS with no policy for those roles. An RLS-enabled table without policies returns no rows.

#Do I need an API server at all?

For many apps, no. Clients call the generated API with the anon key and a user token; policies authorize; Edge Functions handle the few places that need secrets.

Get an instant REST API on your own Postgres schema: create a free project, or read the API docs.