Insert into multiple tables with one Supabase API call 2026

The Supabase client will accept this call without complaining — and quietly do the wrong thing:

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
])

There is no client-side error message for this. The request simply goes to the wrong table or returns unexpected results, because a single .from() call targets exactly ONE table. It happens during local development (npm run dev) and in production when the same endpoint is hit from a mobile app, and 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.

For atomic multi-table writes, the only supported path is a Postgres function called via supabase.rpc(). This guide builds that function end to end for an orders + order_items pair.

Why .from() can never touch two tables

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.

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.

Because PostgREST cannot express a multi‑table transaction in a single HTTP request, the only supported path is 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.

• Constraint: Supabase’s high‑level insert() only talks to one table at a time.
• Pattern: Write a PostgreSQL function that does the inserts inside a transaction and call it via supabase.rpc().
• Guarantee: PostgREST wraps every RPC call in a transaction — all inserts succeed or roll back together.

Building create_order_with_items

Create a function that receives the order data and an array of order‑item objects. The function inserts the order, then inserts each item using the generated order ID — all inside the transaction PostgREST opens for the RPC call.

-- 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.

Locking down who can execute 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.

Swapping the client call to supabase.rpc()

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.

Pushing the migration

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

Confirming both tables got their rows

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.

Shortcuts that look like they should work (but don't)

Mixing columns and hoping upsert() routes them

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.

Dropping security definer from the function

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.

Treating RPC as the escape hatch

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)

---

Originally published at https://www.iloveblogs.blog