Laravel Database Transactions: 3 Practical Examples

Database transactions are very useful for multiple database operations, and Laravel has functionality for them. But what would be the practical examples of WHEN you should use them?

In short, transactions are useful for multiple database operations, when you want to make sure that if either of them fails, all of them would be rolled back automatically.

In this article, I will show three typical examples, in Laravel:

• Creating a new record with many-to-many related records
• Deleting multiple records for a user
• Updating summary table after a new record

Let's get practical.

Example 1. Many-to-Many with Transaction.

Take a look at this typical Controller code:

1public function store(StoreUserRequest $request) {
2    $user = User::create($request->validated());
3    $user->roles()->attach($request->input('roles'));
4 
5    return redirect()->route('users.index');
6}

As you can see, there's a new User record, and then multiple roles are attached to the User. But what if something goes wrong in the second sentence?

Let's imagine that $request->input('roles') is passed not as array but as an invalid string. What happens then?

And the worst part is not about the error, but the fact that the User record has been actually saved to the database.

In the case of users, it may have a bad consequence of email being already taken, although the registration hasn't actually been finished, as the users.email field is unique on the database level.

That's why it's beneficial to use a Database Transaction here:

 1use Illuminate\Support\Facades\DB;
 2 
 3// ...
 4 
 5public function store(StoreUserRequest $request) {
 6    DB::transaction(function() use ($request) {
 7        $user = User::create($request->validated());
 8        $user->roles()->attach($request->input('roles'));
 9    }
10 
11    return redirect()->route('users.index');
12}

Notice: Keep in mind that you need to pass use ($request) or any other external variable that you need to use inside of the transaction function.

Now, don't get me wrong: you will still get the same error "Incorrect integer value: 'abcd' for column 'role_id' at row 1". But the User creation statement will be rolled back, and you won't see the user in the database.

Example 2. Deleting Multiple Records for User

Let's imagine you want to delete the record which has a lot of hasMany/belongsToMany relationships. You need to delete them as well, right? If you haven't set the cascadeOnDelete() on the DB level in migrations, you need to do it manually.

Something like this:

 1$profile->avatar->forceDelete();
 2MediaTag::whereProfileId($profile->id)->delete();
 3StatusHashtag::whereProfileId($profile->id)->delete();
 4DirectMessage::whereFromId($profile->id)->delete();
 5FollowRequest::whereFollowingId($profile->id)
 6    ->orWhere('follower_id', $profile->id)
 7    ->forceDelete();
 8Follower::whereProfileId($profile->id)
 9    ->orWhere('following_id', $profile->id)
10    ->forceDelete();
11Like::whereProfileId($profile->id)->forceDelete();
12 
13// ... only then delete the profile itself:
14$profile->delete();

Imagine what happens if some middle sentence in this code snippet fails. So we have deleted something but not everything?

Of course, compared to the previous example, the consequence isn't as harsh, because, well, we still want to delete those records anyway, we would just do it later.

But still, the profile would remain active but wouldn't see some of their data, like username without avatar. Not cool, right?

Just add a few lines of code:

 1DB::transaction(function() use ($profile) {
 2    $profile->avatar->forceDelete();
 3    MediaTag::whereProfileId($profile->id)->delete();
 4    StatusHashtag::whereProfileId($profile->id)->delete();
 5    DirectMessage::whereFromId($profile->id)->delete();
 6    FollowRequest::whereFollowingId($profile->id)
 7        ->orWhere('follower_id', $profile->id)
 8        ->forceDelete();
 9    Follower::whereProfileId($profile->id)
10        ->orWhere('following_id', $profile->id)
11        ->forceDelete();
12    Like::whereProfileId($profile->id)->forceDelete();
13 
14    $profile->delete();
15});

Example 3. Updating "Summary" Table

Imagine a project with users and financial operations. Usually, they would be called "Transactions", but to avoid confusion with the subject of the article, I will call them just "Expenses".

You need to track all Expenses and also the current balance of each user. So, after every purchase, you would do something like this:

1Expense::create($expenseDetails);
2$user->decrement('balance', $expenseDetails->amount);

Sounds trivial, but in more complex scenarios, you would also need to update some more summary data in other tables, for some reporting.

Here, the consequence of not using DB transactions is huge: users would have more money to spend than they should.

Let's fix this:

1DB::transaction(function() use ($expenseDetails, $user) {
2    Expense::create($expenseDetails);
3    $user->decrement('balance', $expenseDetails->amount);
4});

So, these are just three simple examples of DB Transactions. I hope they will push you towards making your data correct all the time.