Have you ever run into scenarios where you write some code in your controller that writes a bunch of data to your database?

I've often built out API endpoints that accept a big payload of data and then we write a record to the database, followed by writing a bunch of related data to this new record using relationships.

Problem is, for this to all be stored correctly, your multiple database writes need to all be successful. Otherwise you're left with an incomplete representation of that entity.

There have been times when I've not validated something properly, or an exception occurs during the writing of related data for whatever reason. An exception is thrown, and your user is informed that there was a problem, but you've still persisted half of the information.

I had another case recently where I was strangling out a legacy project with a new one. Requests would be received by the new project, stored inside the new project, and then some of that data needed to be written back to the legacy project so that the legacy systems could still function. With this data needing to stay in sync between the two systems, if a write failed to either system, I needed all the changed records to be rolled back.

This is quite easily achieved using database transactions. Database transactions allow us to stage a series of changes, use this changed data, and then persist it when we are ready.

When you're doing this over multiple routes, you want a way to easily apply this logic to each route that needs it.

So how can we take the concept of database transactions and wrap requests up inside them?

With middleware!

We can write some middleware that will wrap the request inside a database transaction and commit the transaction if everything was successful.

Here's what this looks like:

 1use Illuminate\Support\Facades\DB;
 2 
 3class WrapRequestInDatabaseTransaction
 4{
 5    public function handle($request, Closure $next)
 6    {
 7        DB::beginTransaction();
 8 
 9        $response = $next($request);
10 
11        if ($response->exception) {
12            DB::rollBack();
13            return $response;
14        }
15 
16        DB::commit();
17 
18        return $response;
19    }
20}

Here's what we're doing:

1. Create a transaction in the database
2. Run the request and assign the response to $response - at this point the rest of the request has run and this is what we want to return to the user
3. Check if the $response is an exception
4. If it is, roll the transaction back and return the response. The user will see whatever it is they would've seen from the exception anyway, but any changes/writes/deletes etc to the database during this time are all rolled back and so they are never persisted.
5. If all went well and there were no exceptions, commit the changes/writes/deletes etc to the database and return the response

You can alter this to put transactions on specific connections too. In fact, we can wrap multiple database connections if we really need to. If we take my second example above where I am strangling out a legacy project, here's what the middleware would look like over multiple connections:

 1use Illuminate\Support\Facades\DB;
 2 
 3class WrapRequestInDatabaseTransaction
 4{
 5    public function handle($request, Closure $next)
 6    {
 7        DB::beginTransaction();
 8        DB::connection('another_connection')->beginTransaction();
 9 
10        $response = $next($request);
11 
12        if ($response->exception) {
13            DB::rollBack();
14            DB::connection('another_connection')->rollBack();
15            return $response;
16        }
17 
18        DB::commit();
19        DB::connection('another_connection')->commit();
20 
21        return $response;
22    }
23}

This has worked really well for the use cases I've needed it for and hopefully it can help you out too.

---

I hope you found this useful. If you have any feedback, thoughts, or different approaches to this then I'd love to hear from you; I'm @jryd_13 on Twitter.

Thanks!