Operations too complex for the client library

The query builder in supabase-js maps to single HTTP requests against one table or view. Anything that needs several statements to succeed or fail together belongs in a Postgres function, which you call with supabase.rpc().

What you see

You need one of these and can't find a client method for it:

• a transaction: create an invoice and its line items, or none of them;
• check then write: book a slot only if it's still free, without a race between two users;
• aggregates and grouping: totals per customer per month;
• writes across several tables based on a condition;
• row locking (for update), returning values from several statements, loops.

Chaining several client calls instead leaves half-finished data when one of them fails.

Why it happens

Each supabase.from(...) call is a separate HTTP request and a separate database transaction. The client can't hold a transaction open across requests. A function, on the other hand, runs entirely inside one transaction: if any statement fails, everything it did is rolled back.

How to fix it

1. Write the operation as a function in public. This one books a time slot in a bookings app and refuses double bookings:

   create or replace function public.book_slot(p_slot_id bigint, p_note text default null)
   returns public.bookings
   language plpgsql
   security invoker
   set search_path = ''
   as $$
   declare
     v_booking public.bookings;
   begin
     -- lock the slot row so two callers can't book it at once
     perform 1 from public.slots where id = p_slot_id and not is_booked for update;
     if not found then
       raise exception 'Slot % is no longer available', p_slot_id
         using errcode = 'P0001';
     end if;
   
     update public.slots set is_booked = true where id = p_slot_id;
   
     insert into public.bookings (slot_id, guest_id, note)
     values (p_slot_id, (select auth.uid()), p_note)
     returning * into v_booking;
   
     return v_booking;
   end;
   $$;
   

2. Call it from the client. Argument names must match the parameter names:

   const { data, error } = await supabase.rpc('book_slot', { p_slot_id: 812, p_note: 'Window seat' })
   if (error) alert(error.message) // "Slot 812 is no longer available"
   

   A raise exception with P0001 comes back as HTTP 400 with your message, which is easy to show to users.

3. Decide who may run it. With security invoker (the default) the caller's grants and RLS policies apply. If you don't want anonymous users to call it at all:

   revoke execute on function public.book_slot(bigint, text) from public, anon;
   grant execute on function public.book_slot(bigint, text) to authenticated;
   

   Use security definer only when the function must do something the caller can't, and keep set search_path = ''. See security definer functions.

4. For read-only reports, a view is often enough, and you can filter it with the normal query builder:

   create view public.monthly_revenue with (security_invoker = true) as
   select customer_id, date_trunc('month', issued_on) as month, sum(total_cents) as total_cents
   from public.invoices
   group by 1, 2;
   

   const { data } = await supabase.from('monthly_revenue').select('*').eq('customer_id', 17)
   

5. If the API says it can't find the function, reload the schema cache with notify pgrst, 'reload schema';.

On SupaHost

Functions and views are plain SQL, so create them in Studio's SQL editor or as a migration (the MCP apply_migration tool records it in supabase_migrations.schema_migrations). Data API responses are capped at 1000 rows, so do heavy aggregation in the database instead of fetching rows to add them up in the client.

Related

• Calling database functions (RPC)
• Database functions
• How to revoke execute on a Postgres function
• column does not exist when using or() filters