Using an Additional Database in Drupal 7

16thAugust 2011 | Tags:

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.

Comments

    may is know how to use multiple database to share user table ?

    5th January 2012
    Anonymous
    Anonymous

    Thank you. I am new to drupal 7 (used drupal 6 at my last job, but never set up anything from scratch). This solved the problem I was having.

    22nd January 2012
    Allison
    Allison

    Lukas,
    Thanks so much for posting this, could you possibly help a newbie out and show exactly how this whole string would look when added to my settings.php file? I am confused as to where the connection key goes and the statement to switch back to default would go.
    Thank You Again

    29th April 2012
    kc6ape
    kc6ape

    Thak you so much, you are my hero.

    11th August 2012
    Cesar
    Cesar

    There’s a pretty big gotcha with switching databases though - you need to make sure you don’t call any functions which rely on your default database while you’re switched to the external database. t() is a particularly easy one to miss, and theme functions can give all kinds of strange errors. I wrote more about this at http://www.sparrowtail.com/dangers-multiple-drupal-database-connections.

    21st August 2012
    Mark
    Mark

    Hi
    I have small issue coming when i was migrate drupal 6 to drupal 7 site..
    My drupal 6 site in one server and drupal 7 site in other server…
    Now how can i migrate sites …………………
    plzzzzzzzzzzzzzzzzzzzzzz replay me
    If any possible to do the issue…

    9th May 2013
    Anonymous
    Anonymous