Needs better description of the mutation syntax

[geoguide]

Describe the bug
The documents don't fully explain how the mutation hooks work or I am too dense to unerstand them.

Additional context
The docs show things like this over and over:

import { useInsertMutation } from '@supabase-cache-helpers/postgrest-react-query'
import { createClient } from "@supabase/supabase-js";
import { Database } from './types'
 
const client = createClient<Database>(
    process.env.SUPABASE_URL,
    process.env.SUPABASE_ANON_KEY
);
 
function Page() {
  const { mutateAsync: insert } = useUpsertMutation(
    client.from('contact'),
    ['id'],
    'ticket_number',
    {
      onSuccess: () => console.log('Success!'),
    }
  );
  return <div>...</div>;

but never explain what 'ticket_number' is in the third argument. The types show query: Q which is a string. The docs could give better examples to make it obvious how to use these mutations. The library is awesome but I'm shooting in the dark trying to figure out how to get this to work. For instance, I'm using this useUpsertMutation and I'm getting duplicate key error. I thought maybe that 3rd argument was the unique column but that doesn't seem to work, maybe it's the fields that the query should return to determine which fields are getting updated but that doesn't affect it and coul be determined by the arguments passed to it? If someone can explain to me i'm happy to open a PR to add more documentation.

Thanks for all the hard work!

[geoguide]

I found an article somewhere else that explain it better but my issue still remains so I think maybe I need a more full example in the docs?

Also for reference here's my unique index:

CREATE UNIQUE INDEX emergency_contacts_crew_member_id_key ON public.emergency_contacts USING btree (crew_member_id)

And here's what I'm trying to do (It's not my best attempt but i'm just messing around trying to get it to work):

const { mutateAsync: updateEmergencyContact } = useUpsertMutation(
		supabaseBrowserInstance.from('emergency_contacts'),
		['id'], // Primary key columns
		'*',
		{
			onSuccess: () => {
				notifications.show({
					title: 'Emergency Contact Updated',
					message: 'This person has been updated',
				})
				onSave()
				setActiveModal(undefined)
			},
			onError: (error) => {
				toastAndReportError(error, 'Error updating emergency contact')
			},
		},
	)

	const saveEmergencyContact = async (data: UpdateEmergencyContactType) => {
		try {
			const { id, ...rest } = data
			await updateEmergencyContact([
				{ crew_member_id: person.id, team_id: person.team_id, ...rest },
			])
		} catch (error) {
			console.error(error)
		}
	}

[geoguide]

Update:

It looks like the useUpsert can only upsert on conficts of primary keys.

I think the only way to do this is to check for existing data and use insert and update hooks to conditionally save one or the other.

Here's how I'm doing it which requires 2 useXMutation hooks:

const { mutateAsync: updateEmergencyContact } = useUpdateMutation(
		supabaseBrowserInstance.from('emergency_contacts'),
		['id'], // Primary key columns
		null,
		{
			onSuccess: () => {
				notifications.show({
					title: 'Emergency Contact Updated',
					message: 'This person has been updated',
				})
				onSave()
				setActiveModal(undefined)
			},
			onError: (error) => {
				toastAndReportError(error, 'Error updating emergency contact')
			},
		},
	)

	const { mutateAsync: insertEmergencyContact } = useInsertMutation(
		supabaseBrowserInstance.from('emergency_contacts'),
		['id'], // Primary key columns
		null,
		{
			onSuccess: () => {
				notifications.show({
					title: 'Emergency Contact Updated',
					message: 'This person has been updated',
				})
				onSave()
				setActiveModal(undefined)
			},
			onError: (error) => {
				toastAndReportError(error, 'Error updating emergency contact')
			},
		},
	)

	const saveEmergencyContact = async (data: UpdateEmergencyContactType) => {
		try {
			const { id, ...rest } = data
			if (emergencyContactInfo?.id) {
				await updateEmergencyContact({
					id: emergencyContactInfo.id,
					crew_member_id: person.id,
					team_id: person.team_id,
					...rest,
				})
			} else {
				await insertEmergencyContact([
					{ crew_member_id: person.id, team_id: person.team_id, ...rest },
				])
			}
		} catch (error) {
			console.error(error)
		}
	}

I also think it'd be good to have the rest of the example for these hooks to show using the mutateAsync since for update with supabase you do

supabase.from('table').upate({ ...data }).matches({ id })

but with the cache helpers you include the match in the data and rely on the primary key to match the row.

[psteinroe]

hey @geoguide, thanks for opening the issue. You are right, cache helpers only works on primary keys. But support for multiple sets of unique columns would be a great enhancement.

I would appreciate support with the documentation a lot!

  const { mutateAsync: insert } = useUpsertMutation(
    client.from('contact'),
    ['id'],
    'ticket_number', // -> here
    {
      onSuccess: () => console.log('Success!'),
    }
  );

the ticket_number is what is passed to the select of the mutation, e.g.

supabase.from("contact").insert({...}).select("ticket_number")