Intro
In the realm of web development, maintaining data integrity and consistency is crucial.
Database transactions provide a solution by grouping multiple database operations into a single unit, ensuring that either all operations succeed or none do. This functionality is particularly useful in scenarios where data integrity is very important, such as financial transactions, e-commerce platforms, and collaborative systems.
Imagine doing heavy insert/update actions and your server action fails at some step. You’ll be left with half the records inserted/updater and the other half not. Thanks to database transactions, if your action fails, the changes will be reverted.
In this tutorial, we will explore the concept of database transactions and how they can be used in your Server Actions in Wappler.
NOTE: MySQL users, please be aware that database transactions are exclusively supported on the InnoDB engine in MySQL, not on MyISAM.
Database Transactions
In this tutorial we will show you an example use case, where we upload and import CSV data, then we loop through the CSV data and do multiple updates.
The first part of our logic is already added - a file upload step and a CSV import step:
In order to do multiple updates, we would need to add a repeat step, which loops through the CSV import step and then put the update step inside it.
This is the part where transactions are useful. Open the Database Actions and add the Transactions step in the server action steps:
The Transactions action is a group and you can add steps inside it. Add a name for the Transaction step and select your database connection from the dropdown:
Add a step inside the Transaction:
We first add the repeat step:
We select what to repeat:
In our case this is the CSV import step, so that we loop through all the records in the CSV file:
Then we add another step:
We select Database Update:
Setup the Database Update Options:
Select the table where you want to update the data:
Select the database table fields to update and the values for them and also setup the update conditions and click OK when you are done:
Save your server action and you are done:
If the database action fails for some reason, before completing successfully then the data will be reverted and an error will be returned by the Transaction step.