# Operations too complex for the client library

> When a query builder chain can't express what you need (transactions, multi-table writes, aggregates, locking), put the logic in a Postgres function and call it with supabase.rpc().

Source: https://supahost.app/kb/complex-operations-rpc · Updated 2026-11-11

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:

   ```sql
   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:

   ```js
   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:

   ```sql
   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](/kb/security-definer-functions).

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

   ```sql
   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;
   ```

   ```js
   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)](/docs/api/rpc)
- [Database functions](/docs/database/functions)
- [How to revoke execute on a Postgres function](/kb/revoke-function-execute)
- [column does not exist when using or() filters](/kb/or-filter-column-error)