How to connect to multiple databases within Drupal

Drupal can connect to different databases with elegance and ease!

First define the database connections Drupal can use by editing the $db_url string in the Drupal configuration file (settings.php for 4.6 and above, otherwise conf.php). By default only a single connection is defined

 

<?php
$db_url = 'mysql://drupal:drupal@localhost/drupal';
?>

 

To allow multiple database connections, convert $db_url to an array.

 

<?php
$db_url['default'] = 'mysql://drupal:drupal@localhost/drupal';
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';
$db_url['db3'] = 'mysql://user:pwd@localhost/yetanotherdb';
?>

 

Note that database storing your Drupal installation should be keyed as the defaultconnection.

To query a different database, simply set it as active by referencing the key name.

 

<?php
db_set_active('mydb');

db_query('SELECT * FROM table_in_anotherdb');

//Switch back to the default connection when finished.
db_set_active('default');
?>

 

Make sure to always switch back to the default connection so Drupal can cleanly finish the request lifecycle and write to its system tables.

Note: It is particularly important to switch back to the active Drupal database prior to any calls to Drupal functions. Errors in the error log about not being able to find the 'system' table are an indication that calls to Drupal functions preceed switching back to the default database.

This only works with two databases of the same type. For example the following codewill not work.

<?php
// ... header of the settings.php file

$db_url = array (
"default" => "mysql://user:pass@host/db",
"second" => "pgsql://user:pass@host/db"
);

// ...
?>

Set up multiple database on the fly

<?php
global $db_url; // the internal variable that contains database link
if (!is_array($db_url)) {
  $default_db = $db_url;
  $db_url = array('default' => $default_db);
}
//set up the new database value
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';

db_set_active('mydb');    // activation & execution same as explained above
$results = db_query($sql); //sql represents the query to be executed
db_set_active('default'); // set back to original
?>

 

Drupal 7

In Drupal 7, first construct a database connection array, then connect to it to Drupal's front-controller, then execute queries using D7's database abstraction layer.

 

<?php
  $other_database = array(
      'database' => 'databasename',
      'username' => 'username', // assuming this is necessary
      'password' => 'password', // assuming this is necessary
      'host' => 'localhost', // assumes localhost
      'driver' => 'mysql', // replace with your database driver
  );
  // replace 'YourDatabaseKey' with something that's unique to your module
  Database::addConnectionInfo('YourDatabaseKey', 'default', $other_database);
  db_set_active('YourDatabaseKey');

  // execute queries here

  db_set_active(); // without the paramater means set back to the default for the site
  drupal_set_message(t('The queries have been made.'));
?>

 

For a complete reference of all Drupal Database Abstraction Layer functions seehttp://api.drupal.org/api/group/database.