Drupal Guides

Connecting to Multiple Databases Within Drupal

While the database abstraction layer makes remembering function names easier, it also adds built-in security to queries. Sometimes we need to connect to third-party or legacy databases, and it would be great to use Drupal's database API for this need as well and get the security benefits. The good news is, we can!

In the settings.php file, $db_url can be either a string (as it usually is) or an array composed of multiple database connection strings. Here's the default syntax, specifying a single connection string:

$db_url = 'mysql://username:password@localhost/databasename';

When using an array, the key is a shortcut name you will refer to while activating the database connection, and the value is the connection string itself. Here's an example where we specify two connection strings, default and legacy:

$db_url['default'] = 'mysql://user:password@localhost/drupal5'; $db_url['legacy'] = 'mysql://user:password@localhost/legacydatabase';

Note The database that is used for your Drupal site should always be keyed as default.

When you need to connect to one of the other databases in Drupal, you activate it by its key name and switch back to the default connection when finished.

// Get some information from a non-Drupal database. db_set_active('legacy');

$result = db_query("SELECT * FROM ldap_user WHERE uid = %d", $user->uid);

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

■Note Make sure to always switch back to the default connection so Drupal can cleanly finish the request life cycle and write to its own tables.

Because the database abstraction layer is designed to use identical function names for each database, multiple kinds of database back-ends (e.g., both MySQL and PostgreSQL) cannot be used simultaneously. However, see http://drupal.org/node/19522 for more information on how to allow both MySQL and PostgreSQL connections from within the same site.