Drupal 8: Remote Database Services

I recently completed a Drupal 8 project that required pulling data from a remote database.  The actual data is not terribly complicated, so Drupal’s role in this case is mostly to provide an abstraction layer that converts the database into a (cacheable) JSON response. Pulling all the pieces together took a little more research and guessing than I expected so I figured I might save a few people time by writing it up. This is more of an intermediate than a beginner project and so I’m going to skip over lots of detail that important to making it all really work. To really understand what’s happening here you’ll want a basic understanding of Drupal 8’s controllers and database services.

What we’re doing here is creating a database service and a controller to provide a JSON endpoint. We’ll define the database connection, the Drupal service, and then the controller.

Drupal allows us to define database connections in the main settings file. This allows easy access to Drupal’s database services and query classes.

Connection Definition

The first step is to define the database connection in settings.php:

<?php
$databases['remote']['default'] = [
  'database' => 'extra_data',
  'username' => 'accessingUser',
  'password' => 'UseGoodPasswordsIn2017&Beyond',
  'prefix' => '',
  'host' => '10.10.1.1',
  'port' => '',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
  // I'll explain this detail later.
  'pdo' => [
    \PDO::ATTR_TIMEOUT => 5,
  ],
];

Services

Next we need to create a new database service using the new connection information. Drupal core’s database service can be leveraged to connect to additional databases by just defining your new connection as a service in your module’s services.yml file.

services:
  mydataservice.database:
    class: Drupal\Core\Database\Connection
    factory: 'Drupal\Core\Database\Database::getConnection'
    arguments: ['default', 'remote']

Notice the arguments from the database service are the array keys (in reverse order) from the settings.php definition of the connection.

That’s all it takes to create a new service that wraps around your database.

The Controller

That service is all well and good as far as it goes, but if we want to actually to send the data to the browser we need a controller to leverage our new service and send the response.

Using dependency injection I attached the service to the controller and it looked like it worked great.

<?php
  /**
 * Constructs a new DataSearchController object.
 */
public function __construct(ConfigFactory $config_factory, Connection $dataservice) {
  $this->config = $config_factory->get('mydataservice.datasettings');
  $this->database = $dataservice;
}

/**
 * {@inheritdoc}
 */
public static function create(ContainerInterface $container) {
  return new static(
    $container->get('mydataservice.database')
  );
}

public function getData(Request $request) {
  $query = $this->database->select('mytable', 'mt');
  // From here you gather your data and send your response...
}

In fact it did work flawlessly all the way through initial testing. Using the database service to get the data and the cacheable JSON response technique I’d worked out previously everything came together quickly. You could make a request of the controller with your search terms and the browser gets back a list of objects for display.

Then just before launch the client physically relocated the database server and didn’t tell us it would be offline. Turns out we hadn’t tested what happens to an injected database service when there is no response from the remote database server. The request would wait for the database connection to time out and then throw an exception that didn’t get handled in my code at all. There was no place to add a nice error message and it was incredibly slow since the timeout was 30 seconds.

So at the last minute I had two more problems to solve: trap the error and shorten the timeout on PDO connections.

Drupal 8 database services attempt their connection when the service itself created. It you use dependency injection that means exceptions need to be caught in create(). But create() cannot send a response to the browser, that has to happen later when the function that corresponds to the active route is called by the kernel.

My solution was to make the database service an optional parameter on the controller, and adjust the static returned by create based on the exception thrown:

<?php
  /**
 * Constructs a new DataSearchController object.
 */
public function __construct(ConfigFactory $config_factory, Connection $dataservice = null) {
  $this->config = $config_factory->get('mydataservice.datasettings');
  $this->database = $dataservice;
}

/**
 * {@inheritdoc}
 */
public static function create(ContainerInterface $container) {
    try {
      return new static(
        $container->get('config.factory'),
        $container->get('mydataservice.database')
      );
    }
    catch (\Exception $e) {
      return new static(
        $container->get('config.factory')
      );
    }

  public function getData(Request $request) {

    if (!$this->database) {
      throw new HttpException(404, $this->config->get('database_offline_message'));
    }
    $query = $this->database->select('mytable', 'mt');
    // From here you gather your data and send your response... 
  }
}

The other way to handle the exception would be to load the connection from Drupal’s service container when you need it in place of using dependency injection for that service.

Also, notice we’re throwing a 404 error. Ideally it would return a 5xx type error, but those trigger other behaviors that prevented me from providing nice errors for the JavaScript application to process easily. Our controller also had a page display (to send the JavaScript libraries and base markup for the actual interface on application startup), which meant that we needed to create a reasonably well themed response in that function as well:

<?php
    // If the database is offline, then send error message.
    if (!$this->database) {
      \Drupal::service('page_cache_kill_switch')->trigger();
      $message = $this->config->get('database_offline_message');

      $error = [
        '#theme' => 'dataservice_error_page',
        '#attributes' => [
          'class' => ['dataservice', 'database-offline'],
          'id' => 'dataservice-error',
        ],
        '#message' => [
          '#type' => 'processed_text',
          '#text' => $message['value'],
          '#format' => $message['format'],
          '#filter_types_to_skip' => [],
        ],
        '#title' => $this->t('Database Offline'),
      ];

      return $error;
    }

Settings revisited

So that fixed the errors, but still meant we had a really long wait for the database connection to time out before the connection error is even thrown in the first place. And now we come back to that PDO section of the database connection definition.

<?php
$databases['remote']['default'] = [
  'database' => 'extra_data',
  'username' => 'accessingUser',
  'password' => 'UseGoodPasswordsIn2017&Beyond',
  'prefix' => '',
  'host' => '255.255.255.255',
  'port' => '',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
  // Now is when I explain this
  'pdo' => [
    \PDO::ATTR_TIMEOUT => 5,
  ],
];

PDO expects you to override settings at connection time not in a settings file. So I couldn’t just update my PHP.ini and call it a day but I also couldn’t find any documentation on how to change any PDO settings. Leveraging the power of open source I started to follow the code paths, actually reading through how Drupal establishes MySQL connections and, found I it.

If you add a subarray in your connection definition keyed to ‘pdo’ Drupal will load and apply those settings instead of the defaults. There are a couple settings that Drupal insists on being right about for performance, stability, and security reasons, but timeout and many others are fair game.

We can do better

This week, for the second time in a year, the unacceptable behavior of a high profile man in the Drupal community has been the topic of public discussion and debate. This time the organizations involved acted more clearly and rapidly, if imperfectly. The issue came to the forefront during #metoo campaign, and again showed that the Drupal community reflects the world around us. I listened to friends and colleagues respond in various ways to the events and to the recognition of several men that they had been allowing this behavior to go on right in front of them for years without intervention. It is clear that in Drupal, like in all parts of our society we can – and must – do better.

As I reflected on these discussions this weekend I read back through some posts from Danah Boyd I’d read a while ago and stashed with my list of ideas of topics for blog posts. In particular I read her comments from last March on how failures to understand people’s hate fuels it and then a piece I had initially missed from July on change in the tech community. Her experiences and perspective are worthy of a few minutes read in their own right, but this week her views seem particularly timely.

One of the things that struck me about Boyd’s piece from July was her clear simple ask:

…what I want from men in tech boils down to four Rs: Recognition. Repentance. Respect. Reparation.

To me the first two are painfully obvious and most men who care about these issues have been working through those for a while now (too many men still need to learn to care at all). I count myself among the group of men who care, and the this post is mostly directed at that group of peers.

More and more you will hear men acknowledge they believe women are telling the truth, recognizing there are more stories we don’t hear than we hear, and apologizing for their own actions or inactions in the past. But of course just believing people and saying sorry doesn’t get us very far. The next two on Boyd’s list are the places where real forward looking change comes from.

Respect should be easy, but too often it is the first place we get into trouble. It is the part her call to action that will always be true no matter what future progress we make on these issues. Respect is an ongoing act requiring constant care, attention, and effort. Meaning to be respectful is not the same as actually being respectful. It requires actively listening to the ideas of women and people of color and considering them as fully as you do anyone else’s. It means tracking in yourself when you fail to do listen and making the personal change required to do better going forward. It includes monitoring our own behavior in meetings, hallway interactions, and one-on-one discussions to make sure you understand how you are being perceived differently by different people – your friendly or silly gesture to one colleague could be insulting or threatening to another. Respect is not something special that women and people of color are suddenly asking for, it’s something that we all already knew we should be extending to all our colleagues but too often fail to show. And when we fail to show true respect for coworkers – regardless of why we failed or which demographic categories they fall into – it’s our responsibility to recognize it and repent.

Finally Boyd also calls for Reparations. Reparations is a word that lots of us fear for no particularly defendable reason since it’s just about attempt to undo some of the harm we’ve benefited from. And in this case her ask is so direct, plain, and frankly easy that I’m giving her the last words:

Every guy out there who wants to see tech thrive owes it to the field to actively seek out and mentor, support, fund, open doors for, and otherwise empower women and people of color. No excuses, no self-justifications, no sexualized bullshit. Just behavior change. Plain and simple. If our sector is about placing bets, let’s bet on a better world. And let’s solve for social equity.