Upserts with PostgREST or Supabase

I have a table with a UNIQUE INDEX on multiple columns, and I'd like to upsert new values to that table based on that unique constraint. Can I do this without major contortions?

Game on.

Users can have one pick per event, so the picks table has 4 columns pickid, userid, eventid, pick. Primary key is unique, and there's also a unique index on userid and eventid.

Now if a user wants to change their pick, I need an upsert (insert if that userid/eventid pair doesn't exist, otherwise update the pick column of the current record.)

Upserts in PostgreSQL are handled with ON CONFLICT, as follows:

INSERT INTO picks (userid, eventid, pick)
VALUES(20, 30,'My pick') 
ON CONFLICT (userid, eventid) 
DO 
   UPDATE SET pick = 'My pick'

PostgREST knows about upserts too, and does the ON CONFLICT part automagically for you. You can set the Prefer: resolution=merge-duplicates or Prefer: resolution=ignore-duplicates headers.

Things get a bit tricky when you want to upsert based on that UNIQUE INDEX based on multiple columns. The automagical upsert works only with primary keys. If you want it to use another column or multiple columns, pass the on_conflict get variable to the PostgREST api, like on_conflict=userid,eventid

One more thing though - I'm use the postgrestjs/supabasejs client, so how do I pass on_conflict? To find out, I heroicly ventured into the postgrest-js source code and found this wonderful PR to support on_conflict. So I just need to add an onConflict option like so:

const { data, error } = await supabase
  .from('picks')
  .insert([{ userid, eventid, pick}], { upsert: true, onConflict: "userid,eventid"})

Notice the camelCase instead of the under_line. (Mental note: it seems camelCase is kind of the preferred naming convention for Javascript libraries.)

That's it! It works nicely, encapsulating somewhat complex logic with clear, understandable and short code.