Sticky database connections across requests with Laravel

When using read write connections in Laravel, you need to keep in mind the eventual consistency of the database, as replication is not synchronous.

User::create(['name' => 'Tim']);

// The user exists when querying the write connection
// The user may or may not exist when querying the read connection

The sticky option allows us to (mostly) forget about eventual consistency within an isolated request by ensuring that after a write has taken place, all subsequent queries will also use the write connection, even if they are reading data.

User::all();                     // read connection
User::create(['name' => 'Tim']); // write connection
User::all();                     // write connection

But the sticky option only applies to the current request; what about the next request?

Imagine we have the following controller and someone hits the store endpoint, creates an episode in the database, and is then redirected to the show endpoint:

<?php

namespace App\Http\Controllers;

use App\Http\Requests\StoreEpisodeRequest;
use App\Models\Episode;

class EpisodeController
{
    // ...

    public function store(StoreEpisodeRequest $request)
    {
        $episode = Episode::create($request->validated());

        return to_action([self::class, 'show'], $episode);
    }

    public function show(Episode $episode)
    {
        return view('episode.show', [
            'episode' => $episode,
        ]);
    }

    // ...
}

It is very possible that when the user hits the show endpoint, the episode created in the previous request has not yet replicated to the read database. In this example, the show endpoint only reads from the database, so the sticky option has no impact.

POST https://app.test/episodes    # Uses the write connection
                                  # Creates episode 45
                                  # Redirects to https://app.test/episodes/45

GET https://app.test/episodes/45  # Uses the read connection
                                  # Reads episode 45
                                  # May or may not find the episode

As the show endpoint uses the read connection, the initial request to view the episode may result in a 404 Not Found response. If the page was refreshed, it is possible the episode would then show correctly if the read replica was able to get in sync with the write database, e.g., the following flow is entirely possible:

POST https://app.test/episodes    # Redirects to https://app.test/episodes/45
GET https://app.test/episodes/45  # 404 Not Found
GET https://app.test/episodes/45  # 404 Not Found
GET https://app.test/episodes/45  # 200 OK

To handle this eventual consistency for an individual user's session, I created a custom middleware that allows the sticky option to persist across requests with a customisable expiration:

<?php

namespace App\Http\Middleware;

use Illuminate\Support\Facades\Date;
use Illuminate\Support\Facades\DB;

class StickyWriteConnections
{
    public function handle($request, $next)
    {
        $expiry = $request->session()->has('use_write_connection_until')
            ? Date::parse($request->session()->get('use_write_connection_until'))
            : null;

        $expiry?->isFuture()
            && DB::useWriteConnectionWhenReading();

        $response = $next($request);

        $expiry = DB::hasModifiedRecords()
            ? Date::now()->addSeconds(5)
            : $expiry;

        $expiry?->isFuture()
            ? $request->session()->put('use_write_connection_until', $expiry->toDateTimeString())
            : $request->session()->forget('use_write_connection_until');

        return $response;
    }
}

The general idea here is that we store an expiry timestamp in the use_write_connection_until session key. If the key exists and the timestamp it contains is in the future, we want the current request to always use the write connection.

If the key does not exist or the timestamp is in the past, we want Laravel to use the read and write connections normally while also respecting the sticky option like it normally would.

Let me walk you through how this middleware works.

First, if it exists, we retrieve the expiry from the session and convert it into a Carbon\Carbon instance. Converting to Carbon gives us the very lovely isFuture method we use throughout the middleware.

$expiry = $request->session()->has('use_write_connection_until')
    ? Date::parse($request->session()->get('use_write_connection_until'))
    : null;

If the expiry is set and the timestamp is in the future, we force usage of the write connection for all queries.

$expiry?->isFuture()
    && DB::useWriteConnectionWhenReading();

We then proceed to process the request and run the controller.

$response = $next($request);

Now the response has been generated and all the queries that we care about have been run, it is time to determine what we need to store in the session in order to communicate across requests.

We now check if we have modified records within the current request. If we have modified records, we want to continue using the write connection for any requests made within the next five seconds. If no records have been modified, we want to respect the existing expiry, which could be null or a timestamp set by a previous request.

$expiry = DB::hasModifiedRecords()
    ? Date::now()->addSeconds(5)
    : $expiry;

📌 When using the write connection purely for reading, DB::hasModifiedRecords method will always return false. It will only return true if a query has mutated the database. Laravel takes care of all that for us.

The last thing we need to do is persist the expiry to the session if it is in the future, or, if the expiry is in the past or there is no expiry, we want to forget the value from the session.

$expiry?->isFuture()
    ? $request->session()->put('use_write_connection_until', $expiry->toDateTimeString())
    : $request->session()->forget('use_write_connection_until');

With all of this in place, we now have sticky database connections across requests. Coming back to our previous example of the EpisodeController, when a user hits the store endpoint and creates a record, we instruct Laravel to use the sticky connection for any requests made in the next five seconds. This means, when the user is redirected to the show endpoint, the request will use the write connection to retrieve the episode and the page will be shown as expected.

POST https://app.test/episodes    # Redirects to https://app.test/episodes/45
GET https://app.test/episodes/45  # 200 OK Uses the write connection
GET https://app.test/episodes/45  # 200 OK Uses the write connection
GET https://app.test/episodes/45  # 200 OK Uses the write connection

# after 5 seconds from the store request...

GET https://app.test/episodes/45  # 200 OK Uses the *read* connection

This doesn't solve every problem related to eventual consistency, but it does give the individual user a more expected experience.

I opted to store this value in the session so that it doesn't need to communicate with another service within the middleware itself. Below I show you how you could use the cache instead, if you don't mind the overhead of connecting and interacting with another service.

How to apply the middleware

The above implementation relies on the session (see below for a cache based version), we need to ensure the middleware applied after the session has started. We also need to make sure it is applied before any middleware that may connect to the database, especially Illuminate\Routing\Middleware\SubstituteBindings.

Here is how we can configure the middleware in the bootstrap/app.php file to satisfy both of these requirements:

use App\Http\Middleware\StickyWriteConnections;
use Illuminate\Session\Middleware\StartSession;

// ...

->withMiddleware(fn ($middleware) => $middleware
    ->appendToPriorityList(
        after: StartSession::class,
        append: StickyWriteConnections::class,
    )
    ->web(append: [
        StickyWriteConnections::class,
        //...
    ]);
})

This will configure the database as soon as possible after the session middleware has run.

📌 If you are using the database driver for sessions, read write connections are not really possible. The session driver will always use the write connection, effectively meaning you only ever interact with the write connection and never with the read connection.

What about stateless APIs?

This middleware doesn't support a stateless API, simply because we didn't need it and using the session reduced some resource overhead. To add support for a stateless API, you could use the same approach but modify the implementation to use a shared cache instead of the session.

Assuming you only support authenticated requests to your API, the implementation could look something like the following:

<?php

namespace App\Http\Middleware;

use Illuminate\Support\Facades\Date;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;

class StickyWriteConnections
{
    public function handle($request, $next)
    {
        $user = $request->user()->getAuthIdentifier();

        $expiry = Cache::get("use_write_connection_until:{$user}");

        $expiry?->isFuture()
            && DB::useWriteConnectionWhenReading();

        $response = $next($request);

        $expiry = DB::hasModifiedRecords()
            ? Date::now()->addSeconds(5)
            : $expiry;

        $expiry?->isFuture()
            ? Cache::put("use_write_connection_until:{$user}", value: $expiry, ttl: $expiry)
            : Cache::forget("use_write_connection_until:{$user}");

        return $response;
    }
}

This implementation is a little cleaner, as I'm happy to store a serialized Carbon instance in the cache, but that felt icky in the session.

Thanks for reading.