cutting N+1 queries in Next.js API routes with Supabase RPC

when a dashboard starts pulling relational data from Supabase, the naive approach is to fire a separate select per row. the result is a classic N+1 pattern that kills latency and spikes your usage bill.

why the N+1 shows up

nextjs api routes run in a serverless environment, so each request gets its own connection pool. if you fetch a list of orders and then loop to request order_items for each, you end up with dozens of round‑trips. the latency adds up fast, especially when you hit the edge.

using a stored procedure to collapse the joins

supabase lets you define a Postgres function that returns a JSON array. by moving the join into the database you get a single network hop and let the query planner optimize the join.

create or replace function public.get_orders_with_items(user_id uuid)
returns jsonb language sql stable as $$
  select jsonb_agg(
    jsonb_build_object(
      'order_id', o.id,
      'status', o.status,
      'items', (
        select jsonb_agg(
          jsonb_build_object(
            'product', i.product_id,
            'qty', i.quantity
          )
        )
        from order_items i where i.order_id = o.id
      )
    )
  )
  from orders o where o.user_id = $1;
$$;

calling the RPC from Next.js

the serverless handler becomes a single rpc call. note the use of select to keep type safety with @supabase/supabase-js.

import { createServerComponentSupabaseClient } from '@supabase/auth-helpers-nextjs';

export async function GET(req: Request) {
  const supabase = createServerComponentSupabaseClient({
    supabaseUrl: process.env.NEXT_PUBLIC_SUPABASE_URL,
    supabaseKey: process.env.SUPABASE_SERVICE_ROLE_KEY,
  });

  const { data, error } = await supabase.rpc('get_orders_with_items', {
    user_id: supabase.auth.getUser().data?.user?.id,
  });

  if (error) return new Response(error.message, { status: 500 });
  return new Response(JSON.stringify(data), { status: 200 });
}

tradeoffs to watch

• security: exposing a service‑role key in serverless means you must guard the function with auth checks; otherwise any client could invoke the RPC.
• payload size: collapsing joins returns a potentially large JSON blob. consider pagination or a limit param inside the function.
• debuggability: when errors surface, the stack trace points to the RPC rather than the individual query, so add explicit raise statements or log calls inside the function for easier tracing.

monitoring impact

in our prod env the endpoint dropped from ~150 ms (15 queries) to ~45 ms (single query) and reduced monthly bandwidth by ~30 %. the cost savings were immediate, but the real win was the predictable latency curve.

anyone tackled a similar N+1 scenario with Supabase and Next.js? how did you balance payload size vs. request count in your stack?