How do I manage read and write AWS Aurora host endpoints in CakePHP?

I am working with a CakePHP based API that utilizes AWS Aurora to manage our MySQL database. The CakePHP application has many large read queries that that requires a separate Reader Endpoint to not exhaust the database resources.

The way this works is that AWS gives you separate endpoints to use in the host field when you connect CakePHP to it.

I went ahead and configured it the following way, which works. The folowing datasources are set up in config/app.php, using the reader and cluster (default) endpoints for the host value:

<?php

// Other data-source and app settings are omitted, to keep things simple 
return [
    'Datasources' => [
        'default' => [
            'host' => 'mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com',
        ],
        'read' => [
            'host' => 'mydbcluster.cluster-ro-123456789012.us-east-1.rds.amazonaws.com',
        ],
    ]
];

Both the ‘read’ and ‘default’ data sources point to the same database, but so this additional data source is purely configured to support the read only host value.

At this point, all I have to do is overwrite the default connection to read on the queries that need the read connection:

<?php

/**
 * Class TaskNotesController
 *
 * @property AppModelTableTaskNotesTable $TaskNotes
 */
class TaskNotesController extends CakeControllerController
{

    public function index(): void
    {
        $taskNotes = $this->TaskNotes
            ->setConnection(CakeDatasourceConnectionManager::get('read'))
            ->find('all')
            ->where(['foo' => 'bar']);
    }

} 

However, I would like to apply the read endpoint to all read queries in the application. It seems like Cake would have a cleaner way of doing this, to avoid attaching the setConnection() call on every query.

Can you tell me if there is a more streamlined way of doing this in CakePHP?

Thanks in advance!

Answer

That topic comes up every once in a while, but the conclusion always has been that this isn’t something that the core wants to support: https://github.com/cakephp/cakephp/issues/9197

So you’re on your own here, and there’s many ways how you could solve this in a more DRY manner, but that depends to a non-negligible degree on your application’s specific needs. It’s hard to give any proper generic advice on this, as doing things wrong can have dire consequences.

Like if you’d blindly apply a specific connection to all read operations, you’d very likely end up very sad when your application will issue a read on a different connection while you’re in a transaction that writes something based on the read data.

All that being sad, you could split your models into read/write ones, going down the CQRS-ish route, you could use behaviors that provide a more straightforward and reusable API for your tables, you could move your operations into the model layer and hide the possibly a bit dirty implementation that way, you could configure the default connection based on the requested endpoint, eg whether it’s a read or a write one, etc.

There’s many ways to “solve” the problem, but I’m afraid it’s not something anyone here could definitively answer.