In our latest project, we’ve already configured primary/replica database configuration. You can visit https://www.drupal.org/docs/8/api/database-api/database-configuration if you haven’t set it up yet.

Now we can target those replica(s) when makes a query in our custom code.

$database = \Drupal::database();
$result = $database->query("SELECT id, example FROM {mytable}", [], [
  'target' => 'replica',
  'fetch' => PDO::FETCH_ASSOC,
]);

But what about query in core or contributed modules that we can’t manually set the target?

Our project uses Drupal as a backend in decoupled architecture. Most of the requests are API requests with JSON:API specification. Drupal 8 JSON:API module works based on entity, so when fetching data, it will use \Drupal\Core\Entity\Query\QueryInterface. Drupal’s core defined this entity query as a service in core.services.yml.

entity.query.sql:
  class: Drupal\Core\Entity\Query\Sql\QueryFactory
  arguments: [ '@database' ]
  tags:
    - { name: backend_overridable }

As we can see, the first argument for those services is @database which defined as

database:
  class: Drupal\Core\Database\Connection
  factory: Drupal\Core\Database\Database::getConnection
  arguments: [ default ]
database.replica:
  class: Drupal\Core\Database\Connection
  factory: Drupal\Core\Database\Database::getConnection
  arguments: [ replica ]

We can also see, there is another service database.replica that has the same definition as database except for the argument. If we look at \Drupal\Core\Database\Database::getConnection

/**
 * Gets the connection object for the specified database key and target.
 *
 * @param string $target
 *   The database target name.
 * @param string $key
 *   The database connection key. Defaults to NULL which means the active key.
 *
 * @return \Drupal\Core\Database\Connection
 *   The corresponding connection object.
 */
final public static function getConnection($target = 'default', $key = NULL) {
  ...
}

The $target argument is the database target, in database.replica’s case, the target is the replica. So we can use database.replica as argument in entity.query.sql service.

There are a couple of things to replace those service argument. It works around Symfony’s dependency injection. A straightforward way to achieve this is by overriding the backend of entity.query.sql service. Assuming the database’s driver is mysql, here is how to override the service’s backend in the services.yml:

services:
  mysql.entity.query.sql:
    class: Drupal\Core\Entity\Query\Sql\QueryFactory
    arguments: ['@database.replica']

References:

• Backend specific services now have a standard way to override
• Altering existing services, providing dynamic services
• Drupal Database Configuration

Photo by panumas nikhomkhai from Pexels