🐝 How a Simple Supabase RPC Function Cleaned Up My Frontend Logic

Egor — Fri, 24 Oct 2025 01:32:20 +0000

A few days ago, while building a beekeeping management platform, I hit a small but annoying wall 👨‍💻.

We track honey batches uploaded by different beekeepers 🍯.
Each batch record has its sender’s email, while the sold honey records just reference a batch by ID — no email field there 😓.

When I started building the “Sold Honey” page, I realized:

“How do I show only the sales belonging to the logged-in user if sold_honey doesn’t even have an email?”

At first, I thought I’d just pull everything from Supabase and filter client-side.
But that quickly felt messy and inefficient — especially with pagination 💦.

That’s when I remembered 🔥: Supabase lets you write RPC (Postgres) functions.
They’re like serverless endpoints that live right inside your database.

✨ The Fix

Instead of sending multiple queries or writing big joins in React (Next.js), I created a tiny SQL function get_sold_honey_by_email that joins both tables internally and filters by email.

create or replace function get_sold_honey_by_email(p_email text)
returns setof sold_honey as $$
  select s.*
  from sold_honey s
  join honey_batches h on s.honey_batch_id = h.id
  where h.sender_email = p_email
  order by s.id desc;
$$ language sql;

That’s it — no sensitive fields, no extra logic in the frontend 👍.

⚛️ One Simple Call in Next.js

const { data, error } = await supabase.rpc("get_sold_honey_by_email", {
  p_email: user.email,
});

Done. I get back only the user’s data, already filtered and ready to render.

No where clauses in React, no accidental overfetching, no extra stress.

🥳🎉

💡 What I Learned

Supabase RPCs are perfect for moving business logic closer to your data.
They keep your frontend clean and your backend predictable.
Pagination, filtering, even validations — they can all live inside the database.

This small win made my data flow so much simpler that I started refactoring other pages the same way.
Sometimes the cleanest frontend is just a smart SQL function away 💛

Built with Supabase + Next.js on a real-world beekeeping data project 🐝
If you’re exploring Supabase, try writing one RPC function today — it might surprise you how much cleaner your code feels.

🚀 Leveling Up with Supabase RPC — My “Sell Honey” Transaction Journey

Egor — Tue, 21 Oct 2025 23:05:10 +0000

This week, I implemented a new feature for our beekeeping management platform 🐝:
When a beekeeper sells honey, the system must deduct the sold amount from the batch table and record the sale in a separate sold_honey table — atomically.

At first, I planned to try doing this with two Supabase SDK calls:

await supabase.from('honey_batches').update(...);
await supabase.from('sold_honey').insert(...);

But then I realized the problem —
What if the first succeeds but the second fails? 😬
That would leave my data inconsistent (a nightmare for anything involving quantities or money).

So I thought a lot and finally discovered a better way: Supabase RPC (Remote Procedure Call).

I wrote a Postgres function (sell_honey()) that:

Locks the batch row (FOR UPDATE) to prevent race conditions
Validates available stock
Deducts weight from the honey batch
Inserts a record into sold_honey
Runs all of this in one transaction — if anything fails, it rolls back automatically ✅

The full Supabase RPC function is as below:

create or replace function public.sell_honey(
  p_honey_batch_id bigint,
  p_batch_reference_id text,
  p_weight_sold numeric,
  p_price_kilo numeric,
  p_buyer text
)
returns table(sold_id bigint, remaining_weight text)
language plpgsql
as $$
declare
  v_current_weight numeric;
  v_remaining numeric;
begin
  if p_weight_sold is null or p_weight_sold <= 0 then
    raise exception 'weight_sold must be a positive number';
  end if;

  -- Lock the batch row to prevent concurrent modifications
  select
    coalesce(nullif(regexp_replace(h.weight, '[^0-9\.]', '', 'g'), ''), '0')::numeric
  into v_current_weight
  from public.honey_batches h
  where h.id = p_honey_batch_id
  for update;

  if v_current_weight is null then
    raise exception 'Batch with id % not found', p_honey_batch_id;
  end if;

  if v_current_weight < p_weight_sold then
    raise exception 'Insufficient honey in batch. Available: % kg', v_current_weight;
  end if;

  -- compute remaining
  v_remaining := v_current_weight - p_weight_sold;

  -- Update honey_batches.weight (store as text to match existing schema)
  update public.honey_batches
  set weight = trim(to_char(v_remaining, 'FM9999999990.00'))
  where id = p_honey_batch_id;

  -- Insert sold record
  insert into public.sold_honey (
    honey_batch_id,
    batch_reference_id,
    weight_sold,
    price_kilo,
    buyer,
    created_at
  )
  values (
    p_honey_batch_id,
    p_batch_reference_id,
    p_weight_sold,
    p_price_kilo,
    p_buyer,
    now()
  )
  returning id into sold_id;

  -- Return sold id and remaining weight (as text)
  remaining_weight := trim(to_char(v_remaining, 'FM9999999990.00'));
  return next;
end;
$$;

Then I integrated it in my Next.js frontend:

await supabase.rpc('sell_honey', {
  p_batch_id: id,
  p_weight_sold: 5.2,
  p_price_kilo: 18.0,
  p_buyer: 'John Bee',
});

Supabase executes the function securely on Postgres — no partial updates, no data corruption.

The cherry on top?

I learned how Row Level Security (RLS) interacts with RPCs — and how to safely use security definer so the function runs with elevated privileges but still restricts direct table writes.

🔍 Lesson learned:
Supabase RPCs are an underrated superpower.
They turn your database into a reliable logic layer — atomic, consistent, and secure.

💬 Curious if others have used Supabase RPCs or security definer for production logic?
Would love to hear your experiences.

Thanks for kind reading!