Laravel: A Database Transaction to rule them all

Photo by Tim Gouw on Unsplash

Sometimes you need to do a series of SQL queries, and every one of these are so tied together that, if one fails for whatever reason, st*t can happen.

Just picture this:

* Complete the registration of the User
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
public function completeRegister(Request $request)
$validated = $request->validate([
// Bunch of rules
$user = User::create($validated); $this->createDefaultPreferences($user); // Get the API keys from an external service through Internet
$keys = ExternalService::getKeysForUser($user->id);
$user->keys()->create([ 'keys' => $keys ]); return response()->view('user-created', ['user' => $user]);

And let’s say the External Service fails miserably, because it’s down, and the keys are not retrieved nor saved. An error occurs, and the user is registered without keys, and can’t use the site. Now you have to retrieve them manually, or create a mechanism to get the keys.

You could say “Hey, put the keys thing first before proceeding to save the user”, “ping the service”, or “make it so the keys aren’t a requirement”, which are totally valid, but I want to make an example of something that could fail. Even the database can do sometimes.

For these problems, the transactions are for.

Transactions: saving your database, one statement at a time

Transactions are a incredible nice feature of SQL RDBMS like MySQL, MariaDB, PostgreSQL and SQL Server. It allows the database to “rollback” a whole set of statements. I will let the documentation speak for itself:

You may use the method on the facade to run a set of operations within a database transaction. If an exception is thrown within the transaction , the transaction will automatically be rolled back. If the executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the method.

So, let’s solve our keys problem using a transaction.

DB::transaction(function() use ($validated) {    $user = User::create($validated);    $this->createDefaultPreferences($user);    // Get the API keys from an external service
$keys = ExternalService::getKeysForUser($user->id);
$user->keys()->create([ 'keys' => $keys ]);});

If the , which does an HTTP Request through the Internet, fails doing what it must, an will be thrown. When that happens, the whole transaction will be “rolled back”, like nothing happened.

This happens because the method uses a block. If an exception is catched, it will rollback the transactions automatically before proceeding to throw the exception.

But hey, I want to try X number of times!

Thankfully, the transaction accepts a second argument, which is the number of retries. If the External Service can’t connect because of heavy load, we can try again 3 more times.

DB::transaction(function() {    // ... all over again}, 3);  // Retry 3 more times before failing miserably

What if the External Service is DEAD?

Let’s say the External Service is under DDoS attack, someone couldn’t pay the electricity bill because it used it all his money buying levels for his Dota 2 Battle Pass, is just down for maintenance, or whatever event that blocks us for reaching the service. We have depleted our tries, so what we can do? We can catch the exception.

try {
DB::transaction(function() {
// ... all over again }, 3); // Retry 3 more times before failing miserably
} catch (ExternalServiceException $exception) {
return 'Sorry, the External Service is down and you cannot complete the registration without the keys from it'.}

As said before, if the transaction fails, it will return whichever was thrown first by the code inside the Closure.

By the way, you don’t need to use the block. Laravel has a nice exception handling, and the report and render methods to do what their names imply.

For example, if your External Service throws a particular exception, you can render it to something the user can understand, like “The service is down, click here to remind you to register when it’s up”, and use the report method to send you an email and see what the hell happened.

So, yeah, no need to hack your controllers with walls of code.

Graphic Designer graduate. Full Stack Web Developer. Retired Tech & Gaming Editor.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store