Insert into multiple tables with one Supabase API call 2026

TL;DR#

If you're seeing the need to write to several tables but only have one Supabase request available, the cause is that the client’s insert() method targets a single table. Fix it by moving the multi‑table logic into a PostgreSQL stored procedure and invoking it with supabase.rpc().

If that doesn't work, scroll to verify the fix — there are two common variants this guide also covers.

What you'll see#

When you try to target two tables in one request, the fundamental constraint is that a single .from() call targets exactly ONE table. There is no client-side error message for this — the request simply goes to the wrong table or returns unexpected results. For atomic multi-table writes, the only supported path is a Postgres function called via supabase.rpc().

The issue becomes apparent as soon as you run code like:

await supabase.from('orders').insert([
  { id: 1, amount: 100 },
  { id: 1, product_id: 42 } // <-- trying to hit the order_items table in the same call
])

It happens during local development (npm run dev) and in production when the same endpoint is hit from a mobile app. The behavior is identical on Vercel, Railway, and Render because the Supabase client library enforces a one‑table contract before the request even reaches the server.

Root cause#

Supabase’s JavaScript client is a thin wrapper around PostgREST. PostgREST translates a request like POST /orders into a single INSERT statement against the orders table. The wrapper validates the payload and enforces that each request targets exactly one table. There is no cross-table operation at the HTTP layer; you only get a PostgreSQL error if a constraint inside a function is violated.

Because PostgREST cannot express a multi‑table transaction in a single HTTP request, the only supported path is to use Supabase’s RPC endpoint. The RPC endpoint forwards a call to a PostgreSQL function, and that function can contain multiple INSERTs and error handling. PostgREST automatically wraps the RPC call in a transaction, so all statements succeed or roll back together — BEGIN/END in PL/pgSQL are block delimiters, not transaction-control statements, and you do not write explicit COMMIT inside a function body.

The Supabase JS client is built on top of postgrest-js, which uses a query-builder pattern — there is no insert(table, values) function signature. Each chain starts with .from('table_name') and builds a PostgREST HTTP request to that single table. There is no way to express a cross-table operation through this query-builder. The fix is to move the multi‑table logic out of the client and into a server‑side Postgres function, then call it via supabase.rpc().

The fix#

1. Write a PostgreSQL function that inserts into both tables#

Create a function called create_order_with_items that receives the order data and an array of order‑item objects. The function opens a transaction, inserts the order, then inserts each item using the generated order ID.

-- migrations/20260603_create_order_with_items.sql
create or replace function public.create_order_with_items(
  p_user_id uuid,
  p_amount numeric,
  p_items jsonb
) returns setof public.order_items as $$
declare
  v_order_id uuid;
  v_item jsonb;
begin
  -- PostgREST wraps the RPC call in a transaction automatically
  insert into public.orders (user_id, amount, created_at)
  values (p_user_id, p_amount, now())
  returning id into v_order_id;
 
  -- Insert each item
  for v_item in select * from jsonb_array_elements(p_items) loop
    insert into public.order_items (order_id, product_id, quantity, price)
    values (
      v_order_id,
      v_item->>'product_id',
      (v_item->>'quantity')::int,
      (v_item->>'price')::numeric
    );
  end loop;
 
  -- Return the inserted items so the client can see them
  return query
    select * from public.order_items where order_id = v_order_id;
end;
$$ language plpgsql security definer;

Why this works: PostgREST wraps every RPC call in a transaction automatically. The INSERT into orders and the subsequent INSERTs into order_items therefore run in the same transaction — if any statement fails, the whole function rolls back automatically.

2. Grant the client permission to call the function#

Functions created with security definer run as their owner and must explicitly control who can call them. CREATE POLICY ... ON FUNCTION is not valid SQL — PostgreSQL does not support RLS on functions. Instead, control execution access with GRANT/REVOKE:

-- migrations/20260603_rp_policy_create_order.sql
-- Remove access from the default PUBLIC role, then grant to authenticated only
revoke execute on function public.create_order_with_items(uuid, numeric, jsonb) from public;
grant execute on function public.create_order_with_items(uuid, numeric, jsonb) to authenticated;

Note: auth.role() is deprecated in newer Supabase versions; use (select auth.jwt() ->> 'role') or rely on the authenticated role grant above. If you already grant execute to authenticated for all functions, you can skip this step. For more context, see the “Supabase client permission denied for schema public – fix” guide.

3. Call the function from the Supabase client#

Now replace the failing insert() call with an RPC invocation:

// src/lib/api.ts
import { supabase } from './supabaseClient'
 
export async function createOrder(userId: string, amount: number, items: {
  product_id: string
  quantity: number
  price: number
}[]) {
  const { data, error } = await supabase.rpc('create_order_with_items', {
    p_user_id: userId,
    p_amount: amount,
    p_items: items
  })
 
  if (error) {
    console.error('RPC error:', error)
    throw error
  }
 
  return data // array of inserted order_items
}

Notice the payload matches the function signature exactly: p_user_id, p_amount, and p_items. The p_items argument is sent as JSONB; the client automatically serialises the JavaScript array to JSON.

4. Deploy the migration and test#

If you use Supabase CLI:

supabase db push

The command applies the two new SQL files to your development database. In production, first link your local project to the remote ref, then push:

supabase link --project-ref your-project-ref
supabase db push

You should see output similar to:

Applying migration 20260603_create_order_with_items.sql... OK
Applying migration 20260603_rp_policy_create_order.sql... OK

Verify the fix#

Run the helper from a Node REPL or a test script:

node -e "require('./src/lib/api').createOrder(
  '550e8400-e29b-41d4-a716-446655440000',
  199.99,
  [
    { product_id: 'prod_1', quantity: 2, price: 49.99 },
    { product_id: 'prod_2', quantity: 1, price: 99.99 }
  ]
).then(console.log).catch(console.error)"

Expected output (formatted for readability):

[
  {
    id: 'a1b2c3d4-5678-90ab-cdef-1234567890ab',
    order_id: 'f1e2d3c4-b5a6-7890-1234-56789abcdef0',
    product_id: 'prod_1',
    quantity: 2,
    price: 49.99,
    created_at: '2026-06-03T12:34:56.789Z'
  },
  {
    id: 'b2c3d4e5-6789-0abc-def1-234567890abc',
    order_id: 'f1e2d3c4-b5a6-7890-1234-56789abcdef0',
    product_id: 'prod_2',
    quantity: 1,
    price: 99.99,
    created_at: '2026-06-03T12:34:56.789Z'
  }
]

Now check the database directly:

psql $SUPABASE_DB_URL -c "\dt+ public.orders public.order_items"

You should see a new row in orders and two rows in order_items with matching order_id. If the RPC call returns an error, double‑check the function name, argument names, and the RLS policy.

Variant A — Using supabase.from().upsert() for idempotent inserts#

Some developers try to cheat by sending an array that mixes columns from both tables and rely on upsert() to route rows. That approach still fails because PostgREST validates the target table before looking at the payload. The fix remains the same: move the logic to a function.

Variant B — Calling the function without security definer#

If you omit security definer in the function definition, the function runs with the caller’s privileges. With RLS enabled, an authenticated user may not have INSERT rights on order_items, causing a “permission denied for table order_items” error. Adding security definer (as shown) makes the function run with the owner’s rights, sidestepping the per‑table RLS checks while still respecting row‑level policies you define inside the function.

Why this happens (and how to avoid it next time)#

Supabase’s design goal is to keep the client API simple and stateless. By exposing only single‑table CRUD endpoints, the library avoids the complexity of parsing arbitrary SQL on the HTTP layer. The trade‑off is that any operation that touches more than one table must be expressed as a server‑side function. To avoid the same stumbling block in future features, treat the RPC layer as the “escape hatch” for anything that doesn’t fit the one‑table model. A good habit is to write a thin wrapper function for each multi‑table use case and keep the client code limited to supabase.rpc(). Adding a unit test that calls the RPC and asserts the transaction’s atomicity will catch regressions early.

For more on permission‑related pitfalls, see the post “Supabase client permission denied for schema public – fix”. If you need to create enum columns for status flags, the guide “Create an enum column in Supabase – 2026 guide” shows the exact CREATE TYPE syntax you can reuse inside functions. And if you ever notice the RPC call slowing down, the article “Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)” explains indexing strategies that apply equally to tables touched by functions.

Related#

• Supabase client permission denied for schema public – fix
• Create an enum column in Supabase – 2026 guide
• Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)