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.