Using an Additional Database in Drupal 7

One of the nice, lesser-known features of Drupal 7 is the ability to use additional databases and switch at ease. This might be useful for external databases, or if you have another database in an alternative format – perhaps you have an SQLite database that for performance reasons, you don’t wish to migrate. (Yes, Drupal 7 now supports SQLite!)

The configuration can be a little confusing at first, so let’s look at a settings.php file set up to use two databases: drupal on Localhost, and db2 situated at db.example.com.

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'drupal',
      'username' => 'username',
      'password' => 'password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
  'external' => 
  array (
    'default' => 
    array (
      'database' => 'db1',
      'username' => 'username2',
      'password' => 'password2',
      'host' => 'db.example.com',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

The file is fairly self-explanatory – the default database is called drupal and the second database is keyed external (i.e., the connection key). The confusion might lie in the nested default.

In addition to allowing additional, separate databases, Drupal now allows master/slave configurations. In the example above the second level default is the target, and refers to the master server – as does the second. If you examine the code it can become quite easy to confuse whether default refers to the default database, the master database or the additional, master database. (perhaps master would have been a better term?) just remember, connection key followed by target.

Switching databases in your code is simple:

db_set_active('external');

Any subsequent database operations (for example, db_select) will now take place on your second database.

There’s a caveat – you must switch back to the default database when you’ve done. Otherwise you’ll get tell-tale error; a “Base table or view not found”, usually something like one of Drupal’s cache tables, or commonly the block table – either way, the error message will tell you it’s your database as the table names will be prefixed with the schema in the error message.

Switching back to the default database is easy; no key is required as the default is assumed, thus:

db_set_active();

Hope this helps.