Mutation Support for RPC Queries

[rgathmann]

Is your feature request related to a problem? Please describe.
I seem to be unable to perform a complex insert or update mutation using a Supabase DB Function and rpc call and have other queries invalidated. There may be a way to do this day, but I just can't seem to figure it out from the docs.

Describe the solution you'd like
I would like to be able to trigger and rpc mutation then invalidate existing queries.

Describe alternatives you've considered
None, I am stuck here.

Additional context
An example might look something like this:

  const { mutateAsync: insertAsync } = useInsertMutation(
    createClient().rpc('create_journey', { name: 'some_name_here'}),
    // createClient().from('journeys'),
    ['id'],
    'id',
    {
      revalidateTables: [{ schema: 'public', table: 'member_journeys' }],
      onSuccess: () => console.log('Insert success!'),
      onError: (err) => console.log(err),
    }
  );

[psteinroe]

Hey @rgathman, thanks for opening the issue.

Unfortunately, we cannot use rpcs in the useMutation hooks. But you can still do this:

first, setup the mutation using a plain useSWrMutation hook. Then, within onSucess, do custom cache updates via useUpsertItem.

I am on my phone, let me know if you need a code sample and I will send it later.

[kendrit]

Hey @rgathman, thanks for opening the issue.

Unfortunately, we cannot use rpcs in the useMutation hooks. But you can still do this:

first, setup the mutation using a plain useSWrMutation hook. Then, within onSucess, do custom cache updates via useUpsertItem.

I am on my phone, let me know if you need a code sample and I will send it later.

I think some RPC examples including something like this^ should be included in the docs. I have been spending lots of time trying to figure out useUpsertItem with RPC, and I spent another chunk of time just figuring out the "rpc/my_table" notation for the table names.

Using RPC with the regular useQuery func "just works" which is amazing, but it falls apart on any complexity past that due to the lack of documentation. RPC docs would also help us submit proper bug reports/issues.

[kendrit]

I got some working code for useUpsertItem on RPC function cache! It took loads of crawling through the debugger and the github to figure out what's wrong but here it is:

The constants (these didn't change in either implementation):

const memberObject = {id: crypto.randomUUID(), profile_picture: "", username: 'hey', bio: 'hi'}
const upsertMember = useUpsertItem({
    primaryKeys: ['id'],
    table: 'rpc/get_members',
    schema: 'public',
  });
return (
<button onClick={async () => {await upsertMember(memberObject)}}>
          Add Member RPC
</button>
)

What DID NOT work:

const {data} = useQuery(supabase.rpc('get_members', {_offset: 0}));

In an older version of this project, the above code would insert an empty object {} into the correct cache location.
After updating my sources today, that changed to inserting nothing into the cache (there was probably more edge case handling added in one of the recent updates).

What DID work:

const {data} = useQuery(supabase.rpc('get_members', {_offset: 0}).select('id, username, profile_picture, bio'));

The full flow:

const {data} = useQuery(supabase.rpc('get_members', {_offset: 0}).select('id, username, profile_picture, bio'));
const memberObject = {id: crypto.randomUUID(), profile_picture: "", username: 'hey', bio: 'hi'}
const upsertMember = useUpsertItem({
    primaryKeys: ['id'],
    table: 'rpc/get_members',
    schema: 'public',
  });
useEffect(() => {
    if (data) {
      setUsers(data);
    }
}, [data]);

return (
<button onClick={async () => {await upsertMember(memberObject)}}>
          Add Member RPC
</button>
)

The reason why I (and probably some other people) had so many issues is because I wasn't accustomed to using select filters with RPC functions in general, but that piece of the query is essential for how this project handles upsert / insert operations. With no .select filters, the internal functions had an empty "queryKey" and as a result an empty "path" and that would break stuff.

Sidenote: All of the code above was put together only to figure out the useUpsertItem function. It's use of useQuery and useEffect are not necessarily best practice.