Feature Request: Conditional Upsert in Postgrest-Dart

[BottlePumpkin]

Feature Request: Conditional Upsert in Postgrest-Dart

Is your feature request related to a problem? Please describe.
I'm always frustrated when I need to upsert data conditionally. Currently, the upsert method in Postgrest-Dart does not support conditional logic. This means that if I want to upsert a record only if certain conditions are met (e.g., the new value is greater than the existing value), I need to perform multiple steps: fetching the existing record, comparing the values, and then deciding whether to proceed with the upsert. This introduces unnecessary complexity and additional network requests.

Describe the solution you'd like
I would like to propose the addition of conditional upsert functionality to the Postgrest-Dart library. The upsert method should support conditional logic using comparison operators like less than (lt), greater than (gt), etc. This would streamline the process and reduce the number of steps needed to perform conditional upsert operations.

Example Usage:

final response = await client.from('table_name').upsert(data, onConflict: 'user_id', lt: {'score': currentScore});

In this example, the upsert will only proceed if the score in the existing record is less than the currentScore in the new data.

Describe alternatives you've considered
An alternative solution is to manually fetch the existing record, compare the values client-side, and then decide whether to proceed with the upsert. However, this approach introduces additional network overhead and complexity in the client-side code. Using RPC (remote procedure call) functions is another alternative, but it requires setting up and maintaining additional server-side logic, which can be cumbersome for simple conditional upsert operations.

Additional context
Adding conditional logic to the upsert method would significantly improve its functionality and flexibility, making it more useful for a variety of data operations. It would reduce the number of network requests, simplify client-side code, and enhance the efficiency of data operations. If the team agrees that this feature would be beneficial, I would be happy to contribute to the implementation and submit a pull request.

Thank you for considering this feature request.

[dshukertjr]

@BottlePumpkin Thanks for the suggestion!

In this example, the upsert will only proceed if the score in the existing record is less than the currentScore in the new data.

Upsert, as you know, is a method that inserts or updates depending on specified conditions, but in your explanation here of the proposal, it sounds like you never want to update the row. Is it pretty much a conditional insert? Or would there be a case where update is performed as well?

[BottlePumpkin]

@dshukertjr Thank you for your question and for considering the feature request.

The proposed feature is indeed for a conditional upsert, which includes both conditional insert and conditional update operations. The idea is to have the flexibility to specify conditions under which the upsert should proceed. This means that:

Insert Operation: If the row does not exist and the condition is met, a new row will be inserted.
Update Operation: If the row exists and the condition is met (e.g., the new value is greater than the existing value), the existing row will be updated.
Here's a more detailed breakdown:

Conditional Insert: If the specified condition is satisfied (e.g., the existing row's score is less than the new score), the new data will be inserted.
Conditional Update: If the row already exists and the specified condition is satisfied, the existing row will be updated with the new data.
In essence, this feature aims to combine the benefits of both conditional insert and update in a single upsert operation. It allows us to ensure that only records meeting specific criteria are inserted or updated, thereby reducing the need for multiple queries and improving efficiency.

I hope this clarifies the proposal. Please let me know if there are any further questions or if additional details are needed.

[dshukertjr]

Thanks for the additional explanations.

Insert Operation: If the row does not exist and the condition is met, a new row will be inserted.

Just to clarify, for inserts there will not be any conditions to evaluate, because there will be rows to evaluate the condition against, correct?

There was an issue on the PostgREST repo discussing this feature. Since Supabase uses PostgREST for our APIs, this feature needs to land on PostgREST before we can add the feature to the client library.
PostgREST/postgrest#2602

I will keep this issue open for anyone landing here with similar requests.

[BottlePumpkin]

Thank you for your response and for providing additional context regarding the insert operation and the limitations.

To clarify, you are correct that for new rows being inserted, there wouldn't be any existing rows to evaluate the condition against. The condition would only come into play when there is a potential conflict (i.e., when a row with the specified key already exists).

I understand that the Supabase client library relies on PostgREST and that the feature would need to be implemented there first. I appreciate you pointing me to the relevant issue on the PostgREST repository (PostgREST/postgrest#2602). I'll follow the progress there.