Fix PGRST116: multiple (or no) rows returned

PGRST116: JSON object requested, multiple (or no) rows returned means .single() asked PostgREST for exactly one row and got zero or several. On Supabase the zero case is usually an RLS policy filtering your row out — not a missing record — because RLS hides rows instead of raising an error. Use .maybeSingle() to handle the empty case, and diagnose which one you have before touching a policy.

Here's the full response body:

{
  "message": "JSON object requested, multiple (or no) rows returned",
  "code": "PGRST116",
  "details": "Results contain 0 rows, application/vnd.pgrst.object+json requires 1 row",
  "hint": null
}

Read details first — it's the part that tells you which failure you actually hit. Results contain 0 rows and Results contain 2 rows are completely different bugs sharing one error code.

What PGRST116 actually is

When you call .single(), supabase-js sets the Accept: application/vnd.pgrst.object+json header. That header is a contract: return one object, not an array. PostgREST honours it strictly — anything other than exactly one row is a 406, not a partial result. .single() is an assertion, and PGRST116 is that assertion failing.

Which gives three real causes:

1. Zero rows because the row doesn't exist. Wrong id, wrong table, row deleted, or a filter that doesn't match.
2. Zero rows because RLS filtered it out. The row exists. Your query is correct. Your policy doesn't let this user see it.
3. Two or more rows. No unique constraint on the column you filtered by, or a join that fanned out.

Case 2 is the one that eats afternoons, and it's the subject of the best thread on this error — literally titled "How do I determine if no rows found is caused by RLS or incorrect id?".

How do I tell whether RLS or a missing row caused PGRST116?

You can't, from the client — and that's deliberate. As the Supabase discussion puts it, RLS behaves like an extra WHERE clause: if you don't satisfy the policy, the rows simply aren't there. There's no "exists but forbidden" response, because that response would itself be a leak — it would confirm to an attacker that a record exists at a given id.

So you diagnose it from a context that isn't subject to RLS. Two ways:

A. Check from the SQL editor. It runs as postgres, which bypasses RLS:

select id, user_id from public.documents where id = '<the-id>';

Row comes back here but not from your app? It's RLS. Nothing here either? It's a missing row.

B. Ask the database what the policy sees. More precise, because it tests the actual predicate against the actual token:

create or replace function public.debug_visibility(doc_id uuid)
returns jsonb language sql security definer set search_path = '' as $$
  select jsonb_build_object(
    'uid',        auth.uid(),
    'role',       auth.role(),
    'exists',     exists(select 1 from public.documents d where d.id = doc_id),
    'owner',      (select d.user_id from public.documents d where d.id = doc_id)
  );
$$;

-- SECURITY DEFINER means this bypasses RLS by design. Keep it off the anon role.
revoke execute on function public.debug_visibility(uuid) from public, anon;
grant  execute on function public.debug_visibility(uuid) to authenticated;

Call it with supabase.rpc('debug_visibility', { doc_id }) from the signed-in client. If exists is true, uid is populated, and owner doesn't equal uid, you've found it in one call. If uid is NULL, you have a different problem entirely — no user JWT is reaching Postgres, and every policy is failing, not just this one.

Be deliberate about this one: it is an existence oracle. Any caller who can execute it learns whether an arbitrary id exists and who owns it — precisely the leak that RLS's silent filtering exists to prevent. drop function public.debug_visibility(uuid); the moment you have your answer.

The fixes, by cause

Cause 1 — the row genuinely isn't there. Stop asserting one row. .maybeSingle() returns data: null for zero rows instead of throwing, which is what you almost always want on a detail page:

const { data, error } = await supabase
  .from("documents")
  .select("*")
  .eq("id", id)
  .maybeSingle();

if (error) throw error;      // a real failure
if (!data) notFound();       // absent or not visible to this user — same 404

Note that .maybeSingle() still throws on 2+ rows. That's correct: two rows for a supposedly unique id is a data bug you want surfaced.

Cause 2 — RLS is filtering the row. Fix the policy so it expresses what you actually mean. If documents belong to a user:

create policy "owners read their documents" on public.documents
  for select using (auth.uid() = user_id);

If they belong to a team, go through the membership table rather than widening the policy:

create policy "members read team documents" on public.documents
  for select using (
    exists (
      select 1 from public.team_members m
      where m.team_id = documents.team_id
        and m.user_id = auth.uid()
    )
  );

Watch the types on both sides of the comparison. auth.uid() returns uuid; if user_id is text, the comparison can quietly evaluate to false rather than erroring — a type mismatch that presents exactly like a policy bug.

Cause 3 — more than one row. Add the unique constraint you assumed existed, or make the query honest with .limit(1) plus an explicit .order(...) so "which one" isn't left to the planner.

The fix that turns a 406 into a breach

The tempting shortcut, when you've established that RLS is what's hiding the row:

CREATE POLICY "read documents" ON public.documents
  FOR SELECT USING (true);

The error disappears instantly, which is what makes it so persuasive at 1am. What it means is that every row in documents is now readable by anyone holding the anon key — and the anon key ships in your browser bundle by design, so that's anyone at all. RLS is enabled, the dashboard shows a green check, and the table is wide open. It's the most common way a Supabase app with RLS "on" leaks everything, and GuardLayer flags it in your migrations before it merges.

A policy is only doing work if its predicate references the caller — auth.uid(), auth.jwt(), or auth.role(). If yours doesn't, it isn't a security policy, it's a switch left in the on position.

Quick self-check

# Every .single() is an assertion that exactly one row exists. Audit them.
grep -rn "\.single()" --include=*.ts --include=*.tsx app/ lib/

For each hit, ask: is one row guaranteed by a unique constraint and visible under this user's policies? If either answer is no, it should be .maybeSingle() with an explicit empty-state path.

-- Any SELECT policy that never mentions the caller?
select tablename, policyname, qual
from pg_policies
where schemaname = 'public'
  and cmd = 'SELECT'
  and qual not like '%auth.%';

FAQ

What does PGRST116 mean in Supabase? Your query used .single(), which asks PostgREST for exactly one row via the application/vnd.pgrst.object+json Accept header. PostgREST returned 0 or 2+ rows, so it refused with a 406 and code PGRST116.

Why does .single() fail when the row clearly exists in my table? Because RLS filtered it out for the requesting user. RLS acts as an implicit WHERE clause — non-matching rows don't exist as far as your query is concerned, and no error is raised to tell you so.

What's the difference between .single() and .maybeSingle()? .single() errors on 0 rows and on 2+ rows. .maybeSingle() returns null data on 0 rows and errors only on 2+. Use .maybeSingle() whenever "not found" is a legitimate outcome.

Should I add USING (true) to fix it? No. That grants every row to every caller, including unauthenticated ones holding the public anon key. Scope the policy to auth.uid() instead.

Why won't Supabase just tell me it was RLS? Because distinguishing "doesn't exist" from "exists but you can't see it" leaks the existence of protected records. The ambiguity is a security property, not an oversight.