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 cram two inserts into one request, the client throws a generic error because it cannot map the payload to a single table:

{
  "status": 400,
  "message": "Invalid insert payload: object must contain a single table name"
}

The error appears 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 refuses to send a request that references more than one table. This validation happens client‑side, so you never see a PostgreSQL error; you see the 400 response shown above.

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 any valid SQL, including BEGIN … COMMIT blocks, multiple INSERTs, and error handling. The function runs inside the database, guaranteeing atomicity.

The relevant code path in the Supabase client looks roughly like this:

// node_modules/@supabase/supabase-js/dist/module/lib/SupabaseClient.js
function insert(table, values) {
  if (typeof values !== 'object' || Array.isArray(values) === false) {
    throw new Error('Invalid insert payload')
  }
  // PostgREST only accepts a single table name
  return this._request('POST', `${this.restUrl}/${table}`, { body: values })
}

Since the client never reaches the RPC layer, you cannot cheat by adding a second table name to the URL. The fix is to move the multi‑table logic out of the client and into a server‑side function.

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
  -- Start a transaction (implicit in function)
  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
    )
    returning *;
  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: The function runs inside PostgreSQL, so the INSERT into orders and the subsequent INSERTs into order_items are part of the same transaction. If any INSERT fails, the whole function rolls back automatically.

2. Grant the client permission to call the function#

Supabase uses Row Level Security (RLS). To let an authenticated user call the function, add a policy:

-- migrations/20260603_rp_policy_create_order.sql
create policy "allow authenticated users to call create_order_with_items"
  on function public.create_order_with_items(uuid, numeric, jsonb)
  for execute
  using (auth.role() = 'authenticated');

If you already have a blanket execute policy for all functions, you can skip this step. The policy mirrors the one described in the “Supabase client permission denied for schema public – fix” guide, which you can read for more context.

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, run the same command against the remote project:

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

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)