External Database Connections in Drupal 8 – The Right Way
External Database Connections in Drupal 8 – The Right Way
edmund
Thu, 04/16/2020 - 11:35
I recently needed to create a scheduled service to automatically import new content from an external database into a production Drupal 8 website. There was no API available, and building an API wasn’t an option. Instead, I needed to connect directly from Drupal 8 to an external database. Fortunately, Drupal 8 makes it easy to connect to external databases. But there is a catch: Passwords need to be stored securely.
Here’s a quick rundown on how to connect to external databases in Drupal 8 the secure way, plus specific recipes for Pantheon, Platform.sh, and Acquia.
Add External Database Connections to Drupal 8 in Settings.php
First, the easy part. You can add external connections as needed by including the following in settings.php (of course substitute ‘my_external_connection’ for your chosen connection name, [DATABASE NAME] with the name of your database, etc.).
$databases['my_external_connection']['default'] = [
'database' => '[DATABASE NAME]',
'username' => '[USER NAME]',
'password' => '[PASSWORD'], // shouldn't go in settings.php!
'prefix' => '',
'host' => '[DATABASE HOST]' // ex: localhost or 127.0.0.1,
'port' => '3306', // default port
'driver' => 'mysql', // default database type
];
Storing Passwords Securely
Passwords should never be stored directly in your application codebase or git repository. Passwords should be encrypted and stored in a private, secure location with carefully managed access. The problem is that many Drupal-specific host providers like Pantheon, Platform.sh, and Acquia do not provide a simple way to manage files directly on the server, outside of version control. In other words, you can’t just omit settings.php and upload it directly to the sites/default directory.
Securing Database Connections with Pantheon
To store sensitive information securely on Pantheon, use the Terminus Secrets Plugin (requires the Terminus CLI). Once Terminus Secrets is installed, you can save sensitive information using the secret:set command, which creates a protected “secrets.json” file on the server.
To save your database password securely on Pantheon with Terminus Secrets Plugin, type this in your favorite command line utility (replace site and env with your site name and environment from Pantheon):
terminus secret:set site.env password_name password_value
To retrieve your password in settings.php:
$secrets_file = $_SERVER['HOME'] . '/files/private/secrets.json';
if (file_exists($secrets_file)) {
$secrets = json_decode(file_get_contents($secrets_file), 1);
$password = $secrets[‘password_name’];
}
Extra credit: Connecting from one Drupal 8 website on Pantheon to another Drupal website on Pantheon
If you’re trying to connect to a remote database hosted with Pantheon, you’ll need to first export your remote database credentials, then add them to the Drupal 8 website using the Terminus Secrets Plugin. Here’s the full recipe:
Export the remote database connection from the command line (replace site and env with your site name and environment for the remote Pantheon database):
export D7_MYSQL_URL=$(terminus connection:info site.env --field=mysql_url)
Save the connection info to your Drupal 8 environment, where you will be connecting to the external database:
terminus secrets:set site.env migrate_source_db__url $D7_MYSQL_URL
Parse the connection info in settings.php:
$secrets_file = $_SERVER['HOME'] . '/files/private/secrets.json';
if (file_exists($secrets_file)) {
$secrets = json_decode(file_get_contents($secrets_file), 1);
if (!empty($secrets['migrate_source_db__url'])) {
$parsed_url = parse_url($secrets['migrate_source_db__url']);
if (!empty($parsed_url['port']) && !empty($parsed_url['host']) && !empty($parsed_url['pass'])) {
$databases['my_external_connection']['default'] = [
'database' => 'pantheon',
'username' => 'pantheon',
'password' => $parsed_url['pass'],
'host' => $parsed_url['host'],
'port' => $parsed_url['port'],
'driver' => 'mysql',
'prefix' => '',
'collation' => 'utf8mb4_general_ci',
];
}
}
}
Securing Database Connections with Acquia
To store passwords securely on Acquia, use environmental variables. Acquia provides an easy-to-use user interface for managing environmental variables in each hosting environment. In the sidebar menu, there is an option for Variables. Click on it and you can then set as many variables as you need.
Once set, the variables can be accessed in your code like this:
$password = $_ENV['password'] ?? getenv('password');
Your connection array in settings.php would look something like this:
$databases['my_external_connection']['default'] = [
'database' => $_ENV['db'] ?? getenv('db'),
'username' => $_ENV['user'] ?? getenv('user'),
'password' => $_ENV['password'] ?? getenv('password'),
'prefix' => '',
'host' => 'HOST',
'port' => '3306',
'driver' => 'mysql',
];
Securing Database Connections with Platform.sh
To store passwords securely on Platform.sh, also use environmental variables. Like Acquia, Platform.sh provides a UI for managing environmental variables. Click on the Settings tab, and then click ‘Variables’.
Once set, environmental variables can be accessed in a number of ways depending on the language you are working in. To access environmental variables in Drupal 8, use PHP’s builtin getenv() function..
With Platform.sh, your connection in settings.php would look like this:
$databases['my_external_connection']['default'] = [
'database' => getenv('db'),
'username' => getenv('user'),
'password' => getenv('password'),
'prefix' => '',
'host' => 'HOST',
'port' => '3306',
'driver' => 'mysql',
];
Other Options for Securely Storing Passwords in Drupal 8
Outside of providers like Pantheon, Platform.sh and Acquia, Drupal 8 has the ability to read a .env file, which should be stored outside of the document root. Protip: make sure .env files are omitted from your repo by adding this line to your .gitignore files:
.env
There are also services (like Lockr) for securely storing credentials that can be accessed via an API, or the Key module.
Wrapping Up
Drupal 8 makes it easy to connect to external databases, but it’s important to keep passwords safe and secure. It’s beyond the scope of this post, but I strongly recommend adopting good password security policies and using a password management tool like 1Password, LastPass, or Bitwarden. If you have questions or comments about securing database connections in Drupal 8, leave me a comment and I’ll follow up!