Support bulk update

[churichard]

Feature request

Is your feature request related to a problem? Please describe.

I'd love to have the ability to update multiple rows at once, with different data for each row. It seems that right now update only accepts one filter and updates all the rows that match that filter with the same data. For my use case, I'd like to update different rows with different data in one query. The only way that I can do that right now is to make multiple network requests, but that doesn't seem very efficient (especially if I need to potentially update dozens of rows).

Describe the solution you'd like

A way to update multiple rows with different data for each row in one network request.

Describe alternatives you've considered

It seems like it's possible to do this using raw SQL (https://stackoverflow.com/a/18799497), or you can make multiple network requests, as I mentioned earlier. Unfortunately, both are non-ideal solutions. It would be great to have this functionality be supported directly in supabase-js.

[steve-chavez]

Interesting feature @churichard, thanks a lot for proposing it and suggesting a possible implementation.

We'll definitely consider supporting it out-of-the-box. But meanwhile, you can create a SQL function that will allow you to use the update..from syntax. Example:

-- example table
create table projects(
  id int
, name text
);

insert into projects values(1, 'project-1'), (2, 'project-2');

-- function that wraps the update..from syntax
create or replace function update_many_projects(payload json) returns setof projects as $$
  update projects as p set name = x.name 
  from (
    select * from json_populate_recordset(null::projects, payload)
  ) as x(id, name)
  where p.id = x.id
  returning p.*;      
$$ language sql;

And then you can use the function through supabase.rpc. It'd be a single request:

const { data, error } = await supabase
  .rpc('update_many_projects', 
  {payload: [{"id": 1, "name": "updated-project-1"}, {"id": 2, "name": "updated-project-2"}]})

[steve-chavez]

@churichard I forgot about this option, but in fact, you can already do what you want with an upsert.

const { data, error } = await supabase
  .from('projects')
  .upsert([{"id": 1, "name": "updated-project-1"}, {"id": 2, "name": "updated-project-2"}])

[steve-chavez]

@churichard The upsert is cleaner IMO. I'll close this for now, but let me know if that doesn't work you.

[churichard]

I've tried out the upsert, and it seemed to work at first glance. However, because upsert creates a new record if it doesn't exist, it requires me to pass in all of the properties that are needed for the record. This can result in a race condition where if one of the properties is updated elsewhere, then it might be overwritten by the old property. It also requires me to know all of the properties for the particular record that I'm updating, which is not ideal.

I still think a dedicated update would be better because it would allow me to pass in only the properties that I would need, and it would return an error if the record doesn't exist (rather than creating the record). The race condition, in particular, is a case where update would be strictly better than upsert.

[steve-chavez]

I still think a dedicated update would be better because it would allow me to pass in only the properties that I would need, and it would return an error if the record doesn't exist (rather than creating the record).

Ah, that's not true. Nor a regular UPDATE nor your proposed multi-row UPDATE above will fail or return an error when the id is not found, it will simply succeed with "no row updated"(UPDATE 0).

also requires me to know all of the properties for the particular record that I'm updating

I think this one will be lifted once #173 is solved.

This can result in a race condition where if one of the properties is updated elsewhere, then it might be overwritten by the old property.

To solve concurrent updates I think we need an etag. But this is a separate issue being tracked at: PostgREST/postgrest#1069.

[steve-chavez]

I still think a dedicated update would be better because it would allow me to pass in only the properties that I would need, and it would return an error if the record doesn't exist

Hm, the only way I see we could err is by ensuring that the number of rows updated is the same as the number of rows passed in the payload.

[steve-chavez]

I still think a dedicated update would be better because it would allow me to pass in only the properties that I would need, and it would return an error if the record doesn't exist (rather than creating the record).

I do agree in that UPSERT will not be of help if you don't want to create another record and instead fail(ideally) or succeed silently ignoring non-matches.

[nasimnow]

I think currently upsert doesn't work. this worked for me
const { data, error } = await supabase .from('order_products') .insert([{ some_column: 'someValue' },{ some_column: 'someValue' }], { upsert: true })

[soedirgo]

@muhammednasimda Hmm, you might be using an older version of supabase-js. The upsert function was added since v1.7.0 and does the same thing as insert with { upsert: true }.

[steve-chavez]

Should be solved when PostgREST/postgrest#1959 is implemented.

New requests for this feature:

• Upsert: Only update columns in upsert object supabase#3447

[tconroy]

How do you upsert() when the target table has multiple indices (enforcing some unique constrains)? I started this discussion, but would love to hear input from this thread too. It seems like there isn't a good way to "swap" values that have a unique constraint applied.