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

[JasonChiu-dev]

Feature request

Database Transaction handling for multiple database actions.

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

I need the transaction handling from supabase.io tech team as follow:
Regarding 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.
)

But I got answer from supabase.io tech team that no method to handle transaction. only rpc.

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?

A clear and concise description of what you want and what your use case is.

Describe the solution you'd like

Regarding 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.
)

A clear and concise description of what you want to happen.

Describe alternatives you've considered

A clear and concise description of any alternative solutions or features you've considered.

Additional context

Add any other context or screenshots about the feature request here.
Here is my Flutter sample code for multiple database actions:

void addProduct(double amountSum) {
  // Todo: Begin transaction here

  // insert data into the first table: table_name1
  final response = await supabase.from('table_name1').insert([
        {'id': '1001', 'name': 'Apple', ‘price’: 5},
        {'id': '1002', 'name': 'Orange', ‘price’: 3},
        {'id': '1003', 'name': 'Pine Apple', ‘price’: 15},
        {'id': '1004', 'name': 'Water Melon', ‘price’: 10},
        {'id': '1005', 'name': 'Sweet Fruit', ‘price’: 5}
      ]).execute();

  if (response.error != null) {
    // Error occurred
    // Todo: Rollback transaction
  } else {
    // update data to the second table: table_name2 corresponding to
    // the products in the first table: table_name_1

    for (int i = 0; i < items.length; i++) {
      double newQuantity = table_name2.items[i].quantity + 
        table_name1.products[i].quantity;

      // update the second table: table_name2
      final response = await supabase
            .from(table_name2)
            .update({'quantity': newQuantity})
            .eq('id', items[i].id)
            .execute();

      if (response.error != null) {
        // Error occurred
        // Todo: break the for loop and Rollback transaction
      } else {
        // update the third table: table_name3 using the data 
        // amountSum  from outside
        double newAmount = table_name3.amount + amountSum; 

        // update the third table: table_name3

        final response = await supabase
            .from('table_name3'
            .update({'amount': newAmount})
            .eq('id', table_name3.id)
            .execute();

        if (response.error != null) {
          // Error catch.
          // Todo: Rollback transaction
        } else {
          // the database actions are all succeed
          // Todo: Commit transaction
        }
      }
    }
  }
}

[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.