Opinionated: How to safely insert multiple records to more than one table in Laravel

There are many ways to kill a bird. Different people have their unique ways of doing things effectively, that was why I added OPINIONATED to the topic, this is my way of inserting multiple records to more than one table and running other services effectively.

For example, let's say you want to run a service that does these under-listed tasks in a registration controller:

• Check if the new user/potential customer exists in the database.
• Register A User (of course we must save this record in a table).
• Log the event/activity in a table.
• Log the new user email/phone number in a tokens_table for account verification.
• Dispatch Email containing a token that will expire in 10 minutes.
• Dispatch SMS containing a token that will expire in 10 minutes.

The main gist here is that we are running multiple services in a controller and they must all run successfully so we don't have a Partial Transaction issue.

A Partial Transaction can be described as a scenario where only some parts of a transaction are completed, leading to data inconsistency.

How do we make sure we guide against this?

We use the Database Transactions facade readily available to us in the Laravel framework.

To run a database transaction, we need to let the code executor know this is a database transaction.

DB::beginTransaction();

Then we create a try-catch block so we can catch errors easily and do the needful. The try block will be inserting into the database while the catch block will catch any errors encountered.

For the contents in the Try block, we will have

1. A service to check if a user exists.

$checkIfUserExists = $userService->userExists($request->email, $request->phoneNumber);

if ($checkIfUserExists) return errorResponseHelper('fail', 'User exists!');

1. A service to register a new user and log the activity.

 $userService->registerUser($request);

 LogActivity($request->email, $request->phoneNumber);

1. Generate a token, log it in the tokens table, and dispatch an event that is being listened to by two listeners VerificationEmailListener **and **VerificationSMSListener.

 $generateToken = generateTokenHelper();

$userService->tokenLog($request->email, $generateToken[0]);

event(new VerificationTokenDispatch($request->email, $request->PhoneNumber, $generateToken[1]));

Then, the most important part of this TRY block is to commit these changes if all the services run successfully and return a successful response.

 DB::commit();

return successResponseHelper('success', "OTP has been sent to your mobile/email, kindly note that OTP validity is 10 minutes");

If all the services in this try block are successful, the database commit will save these transactions into the database.

Now Let's look at the Catch block part.

If a transaction/service fails in the TRY block, it will come to the catch block. So we will call the DB facade again to rollback every transaction that have been inserted into the database like so:

DB::rollBack();

return errorResponseHelper('fail', "Operation not successful, please retry");

The DB::rollBack() facade will unsave/rollaback every transaction that have been inserted into the database without issues in milliseconds.

This is how I guide against data inconsistency especially when I am running more than one database transaction in Laravel.

The full code block:

use Illuminate\Support\Facades\DB;


 DB::beginTransaction();

        try {
            $checkIfUserExists = $userService->userExists($request->email, $request->phoneNumber);

            if ($checkIfUserExists) return errorResponseHelper('fail', 'User exists!');

            $registerUser = $userService->registerUser($request);

            LogActivity($request->email, $request->phoneNumber);

            $generateToken = generateTokenHelper(); // returns an array, the first is encrypted the second is not

            $userService->tokenLog($request->email, $generateToken[0]);

            event(new VerificationTokenDispatch($request->email, $request->PhoneNumber, $generateToken[1])); // both SMS listeners and email listeners are listening to this event

            DB::commit();

            return successResponseHelper('success', "OTP has been sent to your mobile/email, kindly note that OTP validity is 10 minutes");
        } catch (\Throwable $th) {
            DB::rollBack();
            return errorResponseHelper('fail', "Operation not successful, please retry");
        }

If you have any questions, do not hesitate to drop it.