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?

Laravel Database Transaction Error

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.

avatar

this is good summary for using db transaction. thank you.

two things, can i know how to use the DB transaction without the function closure. whenever i use without function closure, it often doesn't save in the db.

secondly, how to handle if the code within the db transaction function closure doesnt execute?

avatar

Hi Samuel, not sure about "often doesn't save in the db" or "doesn't execute", would need to debug the exact code and exact case, to answer that.

avatar

Good day, Samuel. Fortunately, you can utilise a DB transaction without closure it.

try { DB::beginTransaction();

// Do something and save to the db...

// Commit the transaction

DB::commit();

} catch (\Exception $e) {

// An error occured; cancel the transaction...

DB::rollback();

// and throw the error again.

throw $e;

}

👍 1
avatar

Hey Povilas, great rework, love the new design. Is there a way to 👍🏻 or ❤️ an article to show appreciation/engagement instead of cluttering comments?

Ps> this artile was 👍🏻. just the thing i needed.

avatar

Hi, I don't believe in the Likes on the articles anymore, looked at Dev.to articles with 1-3 likes. The best engagement/appreciation is on social media, so the best way would be to share the article somewhere on your profile on social networks.

P.S. Glad the article helped!

avatar

thank you Povilas for all the work and contribution that you do for this community! i can't say that enough!

👍 1
avatar

by the way Povilas, how does one edit his profile picture here? i see that some have their profile picture set up already.

avatar

Good question, I don't know to be honest. I use laravel-comments.com by Spatie here and I think they use Gravatar to take the pictures from, by your email.

👍 1

Like our articles?

Become a Premium Member for $129/year or $29/month

Written by

You might also like