Data Integrity with Laravel Transactions
Transactions help us to safely handle a set of operations on the Database. Let's see how this method can literally save your day.

Transactions are an incredibly useful feature of SQL Relational Database Management Systems (RDBMS) like MySQL, MariaDB, PostgreSQL and SQL Server.

Let's say you have a list of operations to run on your database that are very tied together. If any exception arises at any step, you should not perform any operation in the chain and rollback everything, to avoid incomplete or inconsistent data on your database compromising its integrity.

#Understanding Transactions

If you are not yet familiar with the concept of transactions, let me clarify it with a practical example.

Consider a double-entry accounting system: every debit requires the recording of an associated **credit. Say that one spends $10 at a grocery store to buy some milk, food and a soda. A transactional double-entry accounting system must record two entries to cover the single transaction:

  • Debit $10 to the customer's account
  • Credit $10 to the grocery's account

This double-entry accounting system makes both entries pass or both entries fail. By treating the storing of multiple entries as a single atomic transactional unit, the system maintains the integrity of the data recorded.

In other words, we won't end up with a situation in which a debit is recorded but not its associated credit, or vice versa.

#Laravel's Database Transactions

Database Transactions come handy to commit a group of operations, like bulk inserts or updates, or simply rollback everything if any error occurs. This is quite an old concept and can be achieved in many ways, depending on your stack.

Laravel provides a very simple API to handle Database Transactions, using the DB facade.

#Manually handling Transactions

First, let's see how to manually handle a transactions to have a complete control over rollbacks and commits in your hands. This can be achieved with some DB facade's methods, like DB::beginTransaction(), DB::commit() and DB::rollback().

For sake of demonstration, let's simplify with a case of creating a new User, that has a relationship called UserProfile, in which we will store some additonal data, like his biography. If something goes wrong along the way, we will revert everything in a snap.

use Illuminate\Support\Facades\DB;
use App\User;
use App\UserProfile;

try {

    DB::beginTransaction(); 

    $user = User::create([
        'name' => 'John Doe',
        'email' => '[email protected]',
        'password' => \Hash::make('secret')
    ]);

    $userProfile = new UserProfile;
    $userProfile->user_id = $user->id;
    $userProfile->biography = 'Some informations about John and his hobbies...'; 
    $userProfile->save(); 

    DB::commit(); // Commits the two operations above

} catch(\Exception $e) { 

    DB::rollback(); // In case of errors, we rollback the previous operations
}

Looks very simple, isn't it?

#Handling Transactions with a Closure

Laravel's DB facade offers a handy transaction method which takes a Closure as its first parameter. In this case, you don't need to worry about manually committing or rolling back.

So, using the DB::transaction() method, the example above would look like this:

DB::transaction(function () {
    $user = User::create([
        'name' => 'John Doe',
        'email' => '[email protected]',
        'password' => \Hash::make('secret')
    ]);

    $userProfile = new UserProfile;
    $userProfile->user_id = $user->id;
    $userProfile->biography = 'Some informations about John and his hobbies...'; 
    $userProfile->save(); 
});

The transaction method also accepts an optional second argument which defines the number of times a transaction should be re-attempted. Once these attempts have been exhausted, it will just throw an exception.

DB::transaction(function () {

    $user = User::create([
        'name' => 'John Doe',
        'email' => '[email protected]',
        'password' => \Hash::make('secret')
    ]);

    $userProfile = new UserProfile;
    $userProfile->user_id = $user->id;
    $userProfile->biography = 'Some informations about John and his hobbies...'; 
    $userProfile->save(); 

}, 3); // in case of errors, retry this operation for a maximum of 3 times

#A slightly more complex example

Suppose that you already have an external service that is used to retrieve the User's biography. Now think about a store method in your UserController that gets the User data and one external_id parameter, that idetifies the User's id on the External Service.

Let's quickly build it:

/**
 * Register a new User along with some external data
 *
 * @param  \Illuminate\Http\Request $request
 * @return \Illuminate\Http\Response
 */
public function store(Request $request)
{
    $data = $request->validate([
        'name'          => 'required|string',
        'email'         => 'required|email',
        'password'      => 'required|confirmed|min:8'
        'external_id'   => 'required|integer'
    ]);

    $user = User::create($data);

    // Calling an external service to retrieve the User's biography...
    $biography = ExternalService::getUserBiography($data['external_id']);

    $user->profile()->create([
        'biography' => $biography
    ]);

    return response()->json([
        'message' => 'User successfully created'
    ], 201);
}

Now, you should be already saying *"Ok, what if the External Service fails to respond for some reason?"* we would have some incomplete data for our User, that's a pity! Instead, we could rollback the registration and... transactions will help us!

Let's build again our UserController's store method, but this time we'll take advantage of the useful DB:transaction:

/**
 * Register a new User along with some external data
 *
 * @param  \Illuminate\Http\Request $request
 * @return \Illuminate\Http\Response
 */
public function store(Request $request)
{
    $data = $request->validate([
        'name'          => 'required|string',
        'email'         => 'required|email',
        'password'      => 'required|confirmed|min:8'
        'external_id'   => 'required|integer'
    ]);

    try {

        DB::transaction(function() {

            $user = User::create($data);

            // Calling the external service...
            $biography = ExternalService::getUserBiography($data['external_id']);

            $user->profile()->create([
                'biography' => $biography
            ]);

        }, 3);  // Retry 3 times, then return an error...


    } catch (ExternalServiceException $exception) {

        // It seems that our External Service is having a bad day...
        return response()->json([
            'message' => 'Sorry, the ExternalService is currently down, please try again later.'
        ], 500);

    }

    return response()->json([
        'message' => 'User successfully created'
    ], 201);
}

As already said, the DB::transaction is used to commit a sequence of operations, in this case we are trying to complete the user registration along with some biography data that comes from an external service, which might not be available, for whatever reason. So we retry 3 times, until we give up and return an error message.

#Using Database Transactions while running tests

Oh yes! Database transactions might be useful also for testing purposes!

While creating some Integration Tests, you might need to test that a request is successfully handled and stored in your database.

There are a few options to do it, using some traits like DatabaseMigrations and DatabaseTransactions.

While DatabaseMigrations will migrate up and down your database on each test (which can be an arguably efficient solution and surely it can become incredibly slow), the DatabaseTransactions trait will help you wrapping each test into a transaction.

Before showing you an integration test example, let's have a quick look on how DatabaseTransactions trait works under the hood:

namespace Illuminate\Foundation\Testing;

trait DatabaseTransactions
{
    /**
     * Handle database transactions on the specified connections.
     *
     * @return void
     */
    public function beginDatabaseTransaction()
    {
        $database = $this->app->make('db');

        foreach ($this->connectionsToTransact() as $name) {
            $database->connection($name)->beginTransaction();
        }

        $this->beforeApplicationDestroyed(function () use ($database) {
            foreach ($this->connectionsToTransact() as $name) {
                $connection = $database->connection($name);

                $connection->rollBack();
                $connection->disconnect();
            }
        });
    }
}

So, by adding this trait, it will first run the "beginTransaction" operation, and then it will register a callback to run the "rollback" operation when the test is completed.

Here's a simple integration test example, using the DatabaseTransaction trait, so that everything is restored as soon as the test is done.

use Illuminate\Foundation\Testing\DatabaseTransactions;

class ExampleTest extends TestCase
{
    use DatabaseTransactions;

    public function testUserStore()
    {
        $response = $this->withHeaders([
            'Accept' => 'application/json',
        ])->json('POST', 'api/users', [
            'name' => $this->faker->firstName,
            'email' => $this->faker->unique()->safeEmail,
            'password' => 'secret',
            'password_confirmation' => 'secret',
            'external_id' => 12345,
        ]);
        $response
            ->assertStatus(201)
            ->assertSee('message');
    }
}

#Conclusion

Laravel's DB Transactions are great to handle a set of operations that require to be stritcly binded together. We've seen a "classic" implementation of Transactions and some more "creative" solutions. And you, how would you take advantage of this extremely useful feature?