Unable to insert a subscription into stripe wrapper

[mfissehaye]

Bug report

• [*] I confirm this is a bug with Supabase, not with my own application.
• [*] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I am trying to insert into the stripe.subscriptions table by doing this

const { data, error } = await supabase
      .schema("stripe")
      .from("subscriptions")
      .insert({
        customer,
        attrs: JSON.stringify({ items: [{ price: priceId }] }),
      });

I also tried directly inserting the data on the sql console

INSERT INTO stripe.subscriptions (customer, attrs)
VALUES (
    'customer_id',
    jsonb_build_object(
        'items', jsonb_build_array(
            jsonb_build_object('price', 'price_id')
        )
    )
);

However I am getting the following error.

{
  "code": "HV000",
  "details": "Wrappers",
  "hint": null,
  "message": "RETURNING is not supported"
}

Please provide more examples on how to insert data into the stripe foreign tables

System information

• OS: macOS
• Version of supabase-js: 2.43.4
• Version of Node.js: v20.3.0

[burmecia]

There are two things:

1. insert data into foreign table

   The foreign table cannot convert JSON column for API post use, so we need to create the column name exactly same as the API required. For example, to insert a subscription follow the Stripe docs:

-- create the subscription table for data insert
create foreign table stripe.subscriptions (
  id text,
  customer text,
  "items[0][price]" text  -- column name will be used in API Post request
)
  server stripe_server
  options (
    object 'subscriptions',
    rowid_column 'id'
  );

And then we can insert a subscription:

insert into stripe.subscriptions (customer, "items[0][price]")
values ('cus_Na6dX7aXxi11N4', 'price_1MowQULkdIwHu7ixraBm864M');

Note that table can only be used for data insertion, querying on it will need to create another 'normal' foreign table like this.

2. using API to access foreign table

   It is normally not suggested using API to access foreign table (docs), but if you really want to it is better to create a security definer function to encapsulate this operation like below example:

create function public.insert_stripe_subscription(customer text, price_id text)
   returns void
   language plpgsql
   security definer set search_path = public, extensions, pg_temp
   as $$
begin
     insert into stripe.subscriptions (customer, "items[0][price]")
     values (customer, price_id);
end;
$$;

And then call it from the client:

const { data, error } = await supabase
  .rpc('insert_stripe_subscription', { customer: 'cus_QMxTWax3UG0mYl', price_id: 'price_1Ojb7WJDPojXS6LNxZi0Z4eA' })
if (error) console.error(error)
else console.log(data)

[vickkhera]

FWIW, this works as well:

insert into stripe.subscriptions (customer,attrs) values ('cus_QUgT45Svmxz0Mt','{"items[0][price]":"price_1PQvBRAKKSGCtBGcJAf3nMHP"}');

Having the ability to RETURNING id would be immensely helpful as well, not just for this table but customers as well.