Drupal 8: Use database replica as entity query target

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.

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

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

  class: Drupal\Core\Database\Connection
  factory: Drupal\Core\Database\Database::getConnection
  arguments: [default]
  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:

    class: Drupal\Core\Entity\Query\Sql\QueryFactory
    arguments: ['@database.replica']


Photo by panumas nikhomkhai from Pexels

Share This: