Transactional operations in the database in Laravel

▶

Depending on the type of application we want to build, it is often necessary to perform multiple operations on the database securely and it doesn't only apply to many-to-many relationships. For example, in the case of buying a book, the steps could be as follows:

1. Register the payment identifier in the database.
2. Deduct the quantities from the inventory.
3. Register the product in some customer's purchase list.

These are just some possible steps; there may be more, and they do not necessarily have to be executed in a specific order. There are many scenarios where several consecutive operations are required, and if any of them fails, all previous operations should be rolled back to avoid leaving the process in an intermediate state.

For example, if during step two we discover that there is no more stock available, the operation would fail. As a consequence, step three could not be completed, leaving the order in limbo, since step one would have been executed correctly.

The importance of transactions

This is where transactional operations come in. These allow a set of queries to be grouped into a single unit of work, ensuring that all of them execute correctly or none of them do.

In other words:

• If a query fails, all previously made modifications to the database are rolled back.
• This guarantees data integrity, preventing an order from being incomplete or in an inconsistent state.
• It ensures the atomicity of the operations, as they are all executed as a single unit of work.

In our book purchase example, if any problem occurs in any of the steps, Laravel will automatically roll back all operations. This way, the application remains secure and reliable, avoiding problems such as inventory inconsistencies, duplicate records, or incomplete transactions.

Here we have an example of its use:

use Illuminate\Support\Facades\DB;
try {
    // start the transaction
    DB::beginTransaction();
    // Realiza tus consultas aquí
    DB::table('users')->insert(['name' => 'John Doe']);
    DB::table('orders')->insert(['user_id' => 1, 'total_amount' => 100]);
    DB::commit(); // Commit changes to the database
} catch (\Exception $e) {
    DB::rollback(); // Revert changes in case of error
    return $e->getMessage();
}

In this example:

DB::beginTransaction() initializes the transaction. Within the try block, we perform all operations on the database using our models or similar.

• If all the operations we wanted to perform were resolved successfully, we called the DB::commit() method to commit the changes.
• If an error occurs, the catch block calls DB::rollback() to rollback any changes that may have been made.

It can also be implemented using a callback:

DB::transaction(function () {
    // Make your queries here
    DB::table('users')->insert(['name' => 'John Doe']);
    DB::table('orders')->insert(['user_id' => 1, 'total_amount' => 100]);
    DB::commit();
});

We have already learned how to perform many operations with the database, but the best thing is to do it optimally, learning how to perform queries with Eloquent efficiently.