Calling .rpc Functions for Transactions in Supabase

[jtkeyva]

I need to upsert multiple tables in a single transaction so i make supabase functions.

how can i call .rpc with brick and have it work offline?

this is a very simple rpc (not a multi table transaction)

final response =
                    await supabase.rpc('insert_brick_test', params: {
                  'id': uuid,
                  'title': title,
                });

-- Function to insert data into brick_test
CREATE OR REPLACE FUNCTION public.insert_brick_test(id uuid, title text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO public.brick_test (id, title) VALUES (id, title);
END;
$$;

-- Function to begin a transaction
CREATE OR REPLACE FUNCTION public.begin_transaction()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  PERFORM set_config('transaction.isolation_level', 'read committed', false);
END;
$$;

-- Function to commit a transaction
CREATE OR REPLACE FUNCTION public.commit_transaction()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  COMMIT;
END;
$$;

-- Function to rollback a transaction
CREATE OR REPLACE FUNCTION public.rollback_transaction()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  ROLLBACK;
END;
$$;

[tshedor]

@jtkeyva If you've initialized Supabase with the offline client, invoking .rpc normally (Supabase.instance.client.rpc()) will put the request through the offline queue.

If you want to directly receive the .rpc request and make sure that it does not go through the offline queue, include /rpc in the ignorePaths argument.

[jtkeyva]

Thanks that's great to know. So then how do I (or do I even need to) make a model for my RPC that inserts into several tables?

Do i need to make a model for it and include business_data, contact_data, lead_data, place_data??

Here's a more realistic function:

CREATE OR REPLACE FUNCTION public.insert_lead_transaction9(
  business_data jsonb,
  contact_data jsonb,
  lead_data jsonb,
  place_data jsonb
) RETURNS void LANGUAGE plpgsql AS $$

BEGIN
  -- Insert into business table
  INSERT INTO public.business (
    business_id, name, website, address, address_2, city, state, zip
  ) VALUES (
    (business_data->>'business_id')::uuid,
    business_data->>'name',
    business_data->>'website',
    business_data->>'address',
    business_data->>'address_2',
    business_data->>'city',
    business_data->>'state',
    business_data->>'zip'
  );

  -- Insert into contact table
  INSERT INTO public.contact (
    contact_id, first_name, last_name, email, phone, mobile_phone, note
  ) VALUES (
    (contact_data->>'contact_id')::uuid,
    contact_data->>'first_name',
    contact_data->>'last_name',
    contact_data->>'email',
    contact_data->>'phone',
    contact_data->>'mobile_phone',
    contact_data->>'note'
  );

  -- Insert into place table (if provided)
  IF place_data IS NOT NULL THEN
    INSERT INTO public.place (
      place_id, name, address, phone, website
    ) VALUES (
      (place_data->>'place_id')::uuid,
      place_data->>'name',
      place_data->>'address',
      place_data->>'phone',
      place_data->>'website'
    );
  END IF;

  -- Insert into lead table (make sure columns and values match)
  INSERT INTO public.lead (
    lead_id, project_id, contact_id, business_id, status, priority, notes
  ) VALUES (
    (lead_data->>'lead_id')::uuid,
    (lead_data->>'project_id')::uuid,
    (lead_data->>'contact_id')::uuid,
    (lead_data->>'business_id')::uuid,
    lead_data->>'status',
    lead_data->>'priority',
    lead_data->>'notes',
  );
END;

$$;

[tshedor]

@jtkeyva That's a question outside the scope of Brick since it's Supabase/SQL. Since you have a RETURNS void, nothing will be delivered to Brick.

[jtkeyva]

So sounds like Brick will not take care of this offline? I wrote my own for this purpose. I wonder if I can try to wedge it into Brick?

I mean can't I just make a model and pass anything into Brick to put in it's queue? And the queue just calls the .rpc and shoves the data into my supabase function?

If not, how do you recommend doing an upsert that spans multiple tables?
thx

[tshedor]

@jtkeyva If you've initialized Supabase with the offline client, invoking .rpc normally (Supabase.instance.client.rpc()) will put the request through the offline queue.

Like I said earlier, Brick will put the RPC through the offline queue.

If not, how do you recommend doing an upsert that spans multiple tables?

Personally, I would not. I would make a single upsert for each model that needs to be inserted; the risk of your function is taking raw, unsanitized and potentially dangerous data from a client and inserting directly to a table without any guarantee on the type/structure of the payload. Supabase's client handles the sanitization and error handling for mismatched types.

[jtkeyva]

@tshedor ok thanks. i just want to learn here. so you are saying if i have a schema:
contact:
contact_id
name

business:
business_id
name

business_contact:
business_id PK
contact_id PK

how would you do this if you were creating a contact and attaching it to a business (that may or may not exist)?
make in upsert to contact, then wait for a success callback
make an upsert to business(if not already in table), wait for callback
THEN make a 3rd upsert to business_contact?

thanks