How to connect to multiple databases within Drupal

Drupal can connect to different databases with elegance and ease!

Drupal 8

Adding additional databases to your configuration

Preferably you would add your configuration to the settings.php file for your site so that all modules can interact with the new database.

In your settings.php:

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

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

Connect to the correct data base:

//To get the default database key "default"
$con = \Drupal\Core\Database\Database::getConnection();

//To get another database (here : 'second')
$con = \Drupal\Core\Database\Database::getConnection('default','second');

//To set the active connection
$conn = \Drupal\Core\Database\Database::setActiveConnection('second');

Drupal 7

There are two methods for accessing secondary databases in Drupal 7.

Adding additional databases to your configuration

Preferably you would add your configuration to the settings.php file for your site so that all modules can interact with the new database.

In your settings.php:

$databases = array();
$databases['default']['default'] = array(
  // Drupal's default credentials here.
  // This is where the Drupal core will store its data.
);
$databases['my_other_db']['default'] = array(
  // Your secondary database's credentials here.
  // You will be able to explicitly connect to this database from your modules.
);

In your module:

// Use the database we set up earlier
db_set_active('my_other_db');

// Run some queries, process some data
// ...

// Go back to the default database,
// otherwise Drupal will not be able to access its own data later on.
db_set_active();

Setting up databases on the fly

If your module will be alone in using the secondary database you can define the connection directly in your module:

  $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.'));

See the Database Abstraction Layer for a complete reference of all Drupal database abstraction functions for version 7.

Drupal 6 and older

In Drupal 6 and older versions, 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

$db_url = 'mysql://drupal:drupal@localhost/drupal';

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

$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 default connection.

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

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 proceed to switch back to the default database.

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

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

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

// ...

Set up multiple databases on the fly

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

See the Database Abstraction Layer for a complete reference of all Drupal database abstraction functions for version 6.