15

Can anyone help me to setup connection with multiple databases connection in Drupal 8? I have database on the same server and I want to access it along with default Drupal 8 database.

0
18

This is done in the same way as in Drupal 7: You can add the database credentials in your settings.php file.

$databases['default']['default'] = array(
  'database' => 'drupal8',
  'username' => 'username',
  'password' => 'password',
  'prefix' => '',
  'host' => 'localhost',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);

$databases['external']['default'] = array(
  'database' => 'external',
  'username' => 'username',
  'password' => 'password',
  'prefix' => '',
  'host' => 'localhost',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);

You will now have two connection options, default and external. You can tell Drupal to use the external connection using \Drupal\Core\Database\Database::setActiveConnection('external') and switch back to the default one with \Drupal\Core\Database\Database::setActiveConnection() once you don't need anymore the external connection.

6
  • Where would you be making these switches... from local to production for instance. – TikaL13 Sep 12 '16 at 20:57
  • @TikaL13 You switch when you need to fetch/put data from/to external source and you switch back when you are done with the fetch/put. – googletorp Sep 13 '16 at 12:59
  • @googletorp When external database is not available than got internal server error 500. IDK drupal trying to make connection at bootstrap or what. How can make quit gracefully? Thanks a lot – Mudassar Ali Sep 20 '16 at 14:53
  • @MudassarAli You might be able to do something in a try/catch statement where you actually switch database. It's not an error I'm familiar with though so not sure what happens – googletorp Sep 20 '16 at 15:23
  • 1
    Changing the active connection is not necessary. Instead, use Database::getConnection('external') and avoid messing with global state. – Pierre Buyle Sep 20 '16 at 17:25
8

In addition to retrieving a database connection to the external database using Database::getConnection(), you can also use dependency injection in your code to retrieve the connection as a dependency and declare your connection in a module's services YAML file:

database.external:
  class: Drupal\Core\Database\Connection
  factory: 'Drupal\Core\Database\Database::getConnection'
  arguments: ['external']

external_database_dependent_service:
  class: Drupal\MODULE\Some\Class\For\A\ServiceUsingTheExternalDatabase
  arguments: ['@database.external']
4
  • This is a nice trick, but requires you to actually do the queries yourself. This wont work if you need to let Drupal internal functions do stuff like loading entities and saving entities. (Fx if sites has shared content types) – googletorp Sep 26 '16 at 10:10
  • 1
    Drupal's DB is managed by Drupal itself, the entity system manage most of your content tables. Drupal is not made to share content at the database level. Doing so seems very fragile (ie. i depends on too many things you can't control). Even in alpha, something like drupal.org/project/replication seems much safer. – Pierre Buyle Sep 26 '16 at 12:19
  • 1
    This is an excellent solution, but I noticed that at least as of 8.3 you need to provide two parameters to the connection factory in the reverse order than I expected: arguments: ['default', 'external'] – acrosman Jul 19 '17 at 16:53
  • @acrosman Database::getConnection() always required two parameters, but the second has a default value of NULL. You can still pass a single argument, as long as it matches with the connection information. – apaderno May 18 at 10:35
5

thank you very much, @googletorp!

here's a slightly more complete example - my code to select users from a D7 database who have created nodes:

\Drupal\Core\Database\Database::setActiveConnection('external');

// Get a connection going
$db = \Drupal\Core\Database\Database::getConnection();

$query = $db->select('users', 'u');
$query->fields('u', array('uid', 'name'));
$query->join('node', 'n', 'n.uid = u.uid');
$query->orderBy('uid');
$users = $query->execute()->fetchAllKeyed();

\Drupal\Core\Database\Database::setActiveConnection();
0
2

Update on this topic for the most recent version of Drupal 8 at this time (8.9.13):

As mentioned by @acrosman in a comment, you do need to provide two parameters, and not in the order that you might expect. In settings.php, the format is like:

$databases['default']['default'] = array(
  'database' => 'database',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'localhost',
  'port' => '3306',
  'driver' => 'mysql',
  'prefix' => '',
  'collation' => 'utf8mb4_general_ci'
);

So if you've added a second database like:

$databases['external']['default'] = array(
  'database' => 'database',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'localhost',
  'port' => '3306',
  'driver' => 'mysql',
  'prefix' => '',
  'collation' => 'utf8mb4_general_ci'
);

And you want to get a connection to the second database:

$connExternal = \Drupal\Core\Database\Database::getConnection('default', 'external');
$results = $connExternal->select('users', 'u')

... and so on until ->execute() and fetching the data.

Note that the keys are in reverse order. This is also not set globally with ::getConnection(), so subsequent DB queries will revert to the previous default database.

0

Adding the settings for a new connection is possible by editing the settings.php file used for the site. The successive step is letting Drupal know when it needs to use the new connection, and this depends on which module needs to use it and for what data.

In the case of a custom module which uses procedural code to connect to the new connection (which I assume doesn't contain data accessed from Drupal core or Drupal core modules) for accessing its own database tables, I would use code similar to the following one.

$db = Database::getConnection('default', 'external');
$data = $db->->select('table_name','t')->fields('t')->execute();

I use Database::getConnection() because it allows to use any function/method that could (directly or indirectly) access a database table without first setting the default connection back with Database::setActiveConnection().
There is still the risk that Drupal core invokes a hook when the database is queried (for example, hook_query_TAG_alter()), and that hook implementation assumes the database used for the connection contains all the Drupal data.

Instead of relying on the connection being defined in the settings.php file, the module could add the connection information with code similar to the following one.

$connection = [
  'database' => 'database',
  'username' => 'user',
  'password' => 'password',
  'host' => 'localhost',
  'port' => '3306',
  'driver' => 'mysql',
  'prefix' => '',
  'collation' => 'utf8mb4_general_ci'
];

Database::addConnectionInfo('external', 'default', $connection);

Note that the order of the first two parameters used by Database::getConnection() is different from the order of the first two parameters used by Database::addConnectionInfo(): For Database::addConnectionInfo(), $key is the first parameter.

For a module that uses the external database to store its own entities, the module should define its own storage handler (extending ContentEntityStorageBase), possibly passing to its constructor the key and the target to use for the database connection. In this way, it can use a different connection for its entity data and the field data associated to its entity.

Not the answer you're looking for? Browse other questions tagged or ask your own question.