Using an Additional Database in Drupal 7

16th August 2011 | Tags:

One of the nice, lesser-​known fea­tures of Dru­pal 7 is the abil­ity to use addi­tional data­bases and switch at ease. This might be use­ful for exter­nal data­bases, or if you have another data­base in an alter­na­tive for­mat — per­haps you have an SQLite data­base that for per­for­mance rea­sons, you don’t wish to migrate. (Yes, Dru­pal 7 now sup­ports SQLite!) The con­fig­u­ra­tion can be a lit­tle con­fus­ing at first, so let’s look at a settings.php file set up to use two data­bases: drupal on Localhost, and db2 sit­u­ated 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 data­base is called drupal and the sec­ond data­base is keyed external (i.e., the con­nec­tion key). The con­fu­sion might lie in the nested default. In addi­tion to allow­ing addi­tional, sep­a­rate data­bases, Dru­pal now allows master/​slave con­fig­u­ra­tions. In the exam­ple above the sec­ond level default is the tar­get, and refers to the mas­ter server — as does the sec­ond. If you exam­ine the code it can become quite easy to con­fuse whether default refers to the default data­base, the mas­ter data­base or the addi­tional, mas­ter data­base. (per­haps master would have been a bet­ter term?) just remem­ber, con­nec­tion key fol­lowed by tar­get. Switch­ing data­bases in your code is simple:

db_set_active('external');

Any sub­se­quent data­base oper­a­tions (for exam­ple, db_select) will now take place on your sec­ond data­base. There’s a caveat — you must switch back to the default data­base when you’ve done. Oth­er­wise you’ll get tell-​tale error; a “Base table or view not found”, usu­ally some­thing like one of Drupal’s cache tables, or com­monly the block table — either way, the error mes­sage will tell you it’s your data­base as the table names will be pre­fixed with the schema in the error mes­sage. Switch­ing back to the default data­base 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

Links and images are allowed, but please note that rel="nofollow" will be automactically appended to any links.