Needed: Database Transaction handling for multiple database actions if any error occurred Rollback for data consistency. about supabase-dart

[bdlukaa]

This is a valid request, but I don't think this will be addressed any time soon

[JasonChiu-dev]

This is a valid request, but I don't think this will be addressed any time soon

1. Thank you for your reply. The transaction is a key function for database handling. How long will it release?
2. I don't know how to really implement the correct rpc function for multiple database actions with complex arguments for database actions, including insert to the first table by multiple rows and then update the second table for different rows with different id and then update the third table for updating the data by key, if there is any error occurred during the THREE TABLEs transaction then ROLLBACK, otherwise COMMIT the transaction.

Is there Any solution or suggestion for writing the correct rpc function to handle the multiple tables' operations(insert, update, ...) in one transaction which can make sure the data consistency?

[bdlukaa]

In my opinion, creating a rpc function is the best option.

There are some examples out there in the internet.

• https://medium.com/geekculture/using-stored-procedures-rpc-in-supabase-to-increment-a-like-counter-9c5b2293a65b
• https://supabase.io/docs/reference/dart/rpc

if there is any error occurred during the THREE TABLEs

My question is: why would there be any error in the transaction?

[JasonChiu-dev]

In my opinion, creating a rpc function is the best option.

There are some examples out there in the internet.

• https://medium.com/geekculture/using-stored-procedures-rpc-in-supabase-to-increment-a-like-counter-9c5b2293a65b
• https://supabase.io/docs/reference/dart/rpc

if there is any error occurred during the THREE TABLEs

My question is: why would there be any error in the transaction?

The app is running on cloud and multi-users environment. I don't what kind of error will occurred but for data consistency, it is needed to check any error especially the THREE TABLES' actions are in ONE transaction.

[JasonChiu-dev]

I do need the feature: BEGIN / COMMIT / ROLLBACK on Supabase, like:

await supabase.transaction(
supabase.from(...).insert(table1...),
// if any error, rollback.
supabase.from(...).update(table2...)
// if any error, rollback.
supabase.from(...).updata(table3...)
// if no error, Commit.
)

BANKing Transaction is a very good example for why I need the function as I mentioned above.

[JasonChiu-dev]

I don't what kind of error will occurred but for data consistency, it is needed to check any error especially the THREE TABLES' actions are in ONE transaction.

correct typo: I don't know what kind of error will occurred during the transaction. But for data consistency, it is needed to check any error and rollback, especially the THREE TABLES' actions are in ONE transaction and it has to commit when the THREE TABLEs actions are all succeed.

[bdlukaa]

As I said, this could be achieved as a rpc function. Supabase uses Postgre, which support transactions.

See https://www.postgresql.org/docs/8.3/tutorial-transactions.html

Unfortunately Supabase is still in beta and doesn't have all these features yet. Tho, this can be achived with a rpc function.

Tho this a valid request to make to the main team

[steve-chavez]

it is needed to check any error especially the THREE TABLES' actions are in ONE transaction.

@JasonChiu-dev I've added an example for how to do the above on supabase/supabase#3732 (comment). Basically you just need to do RAISE on the function.

I'll close this one, a more general transactions interface can be discussed at supabase/postgrest-js#219.