Connecting and Using Additional/External Databases

While many Drupal developers rely solely on using the default database/schema for their Drupal installation, there are many projects that require the use of external and non-default databases. Let's look at how we did that in Drupal 7 and then learn how to do it in Drupal 8 and beyond.

settings.php

The Old Way

In Drupal 7, in order to inform Drupal about your database, you would define the connection in settings.php in an array:

// ...
$databases['my_database']['default'] = array(
  'database' => 'my_database',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'port' => '3306',
  'driver' => 'mysql',
);

The New Way

In Drupal 8, the process is remarkably similar:

// ...
$databases['my_database']['default'] = [
  'database' => 'my_database',
  'username' => 'username',
  'password' => 'password',
  'host' => '127.0.0.1',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
];

Let's look at a couple of key differences:

• host | There are some instances where using localhost can cause issues. If your database is hosted on the same server as your Drupal installation, it is recommended that you use the localhost IP address instead.
• namespace | Drupal 8-11 require that you provide a namespace for the database driver.
• Shorthand array notation | This is more of an aside, but using shorthand array notation ([ ] vs array()) is the standard these days.
• Other configuration options | There are many more options available for database configuration, this page is a good place to start.

Implementation

The Old Way

The procedural methodologies of Drupal 7 were largely washed away with the advent of Drupal 8. When we wanted to query one of our database, this is how we did it before:

// We use the global db_set_active() function to switch our
// database connection temporarily.
db_set_active('my_database');

$result = db_select('my_table', 'a')
  ->fields('a')
  // ideally you use some conditions...
  ->execute()
  ->fetch();
  
// Immediately after we are done using that connection,
// we have to use db_set_active() again to return the
// connection to the default schema.
db_set_active();

If we used another schema and did not reset the connection afterward, we got an exception that broke everything in the request. Fortunately, there is a better way to address this in newer versions of Drupal.

The New Way

This time around, we're going to use the Database service, and we're going to use Dependency Injection to make it available for use in our code. The example I am going to show here is injecting the database service into a custom service, but technically this can be done in controllers, forms, and other types of code. I believe that queries should be kept in services for a more compartmentalized and decoupled design pattern.

For instructions on how to implement a database connection statically using the global service container, see this guide. Note, this should be avoided when possible.

my_module.services.yml:

What's happening in this file? If you're unfamiliar with the creation of services, this YAML file is where we inform Drupal that our services exist, where to find their code, and other options. In this situation, we're defining a service for our connection to an external database so that we can inject it into any other service that requires it. When creating these, I prefer to name them in a *.db.* format. 

You can see in the my_module.my_service section how the my_module.db.my_database service is injected. Next, we'll look at how to receive that service as an argument.

services:
  my_module.db.my_database:
    class: Drupal\Core\Database\Connection
    factory: Drupal\Core\Database\Database:getConnection
    arguments: [default, my_database]
  
  my_module.my_service:
    class: Drupal\my_module\Services\MyService
    arguments: ['@my_module.db.my_database']

src/Services/MyService.php:

Since we are injecting the database connection as a service into our MyService class, we need to accept it as a constructor argument. Notice how we do not have to manually create the connection, nor do we have to manually reset it. Drupal 8+ has the ability to work with multiple connection objects simultaneously and it is best handled through the service container, which is one reason why using dependency injection is so important.

<?php

namespace Drupal\my_module\Services;

use Drupal\Core\Database\Connection;

class MyService {

  public function __construct(private Connection $db) {}
  
  public function getData() {
    $result = $this->db->select('my_table', 'a')
      ->fields('a')
      // ideally you use some conditions...
      ->execute()
      ->fetch();
      
    return $result;
  }

}

Additional Thoughts

• It may be advantageous to name your arguments something that identifies the connection as specific to the database it's for. For example, instead of $db, you might call it $db_mydb. 
• You can inject as many different connections as you need (one per database/schema). This is where the above suggestion would come in handy.
• Static implementation via the global service container should only be used where dependency injection is not possible, e.g.: Inside of hooks.