Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Supabase PGRST116: Cannot coerce the result to a single JSON object

Zero rows and two rows share one code. The details field says which, and the fix is different: policy, session, or a unique filter.

~4 min readSQL
#Supabase#PostgREST#PostgreSQL#Row Level Security
A single object slot receiving zero rows and two row blocks with an amber 406 plate between them

Introduction & Quick Summary

Symptom. A query that obviously returns one row fails, and the error object is easy to misread:

{ "code": "PGRST116", "details": "The result contains 0 rows", "hint": null, "message": "Cannot coerce the result to a single JSON object" }

Older PostgREST versions phrase the same thing as JSON object requested, multiple (or no) rows returned, which is why searching the second string lands on answers for a different version. Either way the HTTP status is 406 and the client code that expected data gets error.

Cause. .single() asks PostgREST for a singular resource by sending Accept: application/vnd.pgrst.object+json. If the result is not exactly one row, PostgREST answers 406 with PGRST116. Zero rows and two or more rows produce the same code with different details, and the causes are unrelated: a missing row or a row hidden by Row Level Security on one side, a non-unique filter on the other.

Fix. Read details, then choose the right method:

ts
// a missing row is a normal outcome const { data, error } = await supabase .from("profiles") .select("display_name, avatar_url") .eq("id", userId) .maybeSingle(); if (error) throw error; if (!data) return null; // not created yet, or not visible to this user
ts
// several rows are legal, take one deterministically const { data, error } = await supabase .from("sessions") .select("id, created_at") .eq("user_id", userId) .order("created_at", { ascending: false }) .limit(1) .maybeSingle();

Core Mechanics, Signatures & Reproducible Setup

supabase-js has three shapes for reading rows, and the difference is both the Accept header and the client-side handling:

MethodHeader sentOn 0 rowsOn 2+ rows
.select()nonedata: []data: [...]
.single()vnd.pgrst.object+jsonerror PGRST116error PGRST116
.maybeSingle()vnd.pgrst.object+json (or a list)data: null, no errorerror PGRST116

.maybeSingle() is implemented in the client: it fetches with a limit and decides locally whether zero, one or many rows came back. That is why it tolerates zero rows without an error and still reports the multiple-row case with the same code.

Reproduce both branches against a local Supabase project with a table that has no policies, so RLS is part of the demonstration:

create table public.profiles ( id uuid primary key references auth.users (id) on delete cascade, display_name text ); alter table public.profiles enable row level security; insert into public.profiles (id, display_name) values ('00000000-0000-0000-0000-000000000001', 'first'), ('00000000-0000-0000-0000-000000000002', 'second');
ts
// 0 rows visible (RLS on, no policy) still returns PGRST116 for .single() const r1 = await supabase.from("profiles").select("display_name").eq("id", someId).single(); console.log(r1.error?.code, r1.error?.details); // PGRST116 The result contains 0 rows // 2 rows for a non-unique filter const r2 = await supabase.from("profiles").select("display_name").neq("display_name", "").single(); console.log(r2.error?.code, r2.error?.details); // PGRST116 The result contains 2 rows

The same request over HTTP shows the header and the status, which is useful when a framework or a proxy hides the client's error object:

curl -s -i "https://PROJECT.supabase.co/rest/v1/profiles?select=display_name&id=eq.$ID" \ -H "apikey: $PUBLISHABLE_KEY" \ -H "Authorization: Bearer $ACCESS_TOKEN" \ -H "Accept: application/vnd.pgrst.object+json" | head -20

Key Comparison Table

details valueWhat happenedUsual cause
The result contains 0 rowsno row matchedrow absent, filter wrong, or RLS hid it
The result contains 2 rowsmore than one row matchedfilter on a non-unique column
Results contain 0 rows, application/vnd.pgrst.object+json requires 1 rowolder PostgREST wordingsame as the first row
permission denied for tablepolicy denies the operationmissing grant, not a row count
ScenarioCorrect methodWhy
profile that may not exist yet.maybeSingle()zero rows is a valid state
lookup by primary key known to exist.single()a missing row is a real error
newest row of many.order().limit(1).maybeSingle()deterministic pick
existence checkselect("id", { count: "exact", head: true })no row body needed
list view.select()zero rows is a list, not an error

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. RLS that hides the row you can see in the dashboard

The SQL editor runs as postgres, which has bypassrls, so a row exists there and not through the API. This is the most common "0 rows" surprise:

select policyname, cmd, roles, qual from pg_policies where tablename = 'profiles';
create policy "read own profile" on public.profiles for select to authenticated using ((select auth.uid()) = id);

A table with RLS enabled and no policy returns zero rows to every anonymous request, which then becomes PGRST116 under .single().

2. Fetching before the session is restored

On page load, a client that reads a profile before Supabase has restored the session from storage sends an anonymous request. The row exists, the policy is correct, and the result is still zero:

ts
const { data: { session } } = await supabase.auth.getSession(); if (!session) return null; // do not query protected rows yet const { data } = await supabase.from("profiles").select("*") .eq("id", session.user.id).maybeSingle();

Server components have the same trap in a different form: cookies must be attached to the client before the query, or the request is anonymous.

3. Filters that are not unique

An email column without a unique constraint, or a soft-delete flag that leaves two live rows, turns .single() into a 406 the moment the second row appears. Two honest fixes:

ts
// 3a. make the filter unique in the database // alter table public.profiles add constraint profiles_email_key unique (email);
ts
// 3b. or accept the many-row case and pick deterministically const { data } = await supabase.from("profiles").select("id, updated_at") .eq("email", email).order("updated_at", { ascending: false }).limit(1).maybeSingle();

Adding .limit(1) without order is not a fix: which row you get is up to the planner.

4. Upsert-then-read without a policy for select

An insert policy with select missing allows the write and forbids the read-back. The .single() after the upsert returns PGRST116 with 0 rows even though the insert succeeded, which makes it look like a transaction problem:

create policy "insert own profile" on public.profiles for insert to authenticated with check ((select auth.uid()) = id); create policy "read own profile" on public.profiles for select to authenticated using ((select auth.uid()) = id);
ts
const { error } = await supabase.from("profiles") .upsert({ id, display_name }, { onConflict: "id" }) .select("id") // requires a select policy .single();

5. Version differences in the message

PostgREST changed the message text in late 2024 to say what actually happened, and the code stayed PGRST116. Documentation and blog posts still quote JSON object requested, multiple (or no) rows returned, and some of them label it PGRST505. Match on the code, not the string:

ts
if (error?.code === "PGRST116") { const rows = error.details?.match(/(\d+) row/)?.[1]; // "0" means missing or hidden, "2" means the filter is not unique }

Logging code plus details is what makes this diagnosable in production; a generic "query failed" message throws that away.

6. Server-side equivalents

On the server you often want the same semantics in SQL rather than in the client, and the maybeSingle idea maps to limit 1:

select display_name from public.profiles where id = auth.uid() limit 1;

If the caller must distinguish "no row" from "many rows", run a count(*) first or fetch all matching rows and decide in code. Letting PostgREST coerce to an object hides that distinction behind one error code.

Common Compilation/Runtime Mistakes & Version Gotchas

  • Using .single() for a row that may not exist. .maybeSingle() exists for exactly this.
  • Treating PGRST116 as a permissions error. It is a row-count error; 42501 is the permission one.
  • Adding .limit(1) without an order. The result is legal but arbitrary.
  • Reading a protected row before the session is restored. Anonymous requests see nothing under RLS.
  • Forgetting a select policy next to an insert policy. The write works and the read-back fails.
  • Matching on the message text. The wording changed across PostgREST versions.
  • Logging only error.message. details is what tells 0 rows from 2 rows.
  • Assuming the SQL editor's result matches the API's. postgres bypasses RLS.

Draft and check the policy conditions with the SQL formatter, and inspect a row payload or an error object with the JSON formatter before deciding which side is wrong.

Reference: singular and plural responses, PostgREST errors, and the supabase-js single reference.

Official references

primary documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.