Connect to Multiple Databases from Drupal

Drupal has the ability to connect to multiple databases, allowing you to use Drupal’s built in database abstraction layer on more than just Drupal’s primary database.

The “proper” way to do it (according to Pro Drupal Development and the Module Developer’s Guide) is to change $db_url in your settings.php file from a string to an array, e.g.:

$db_url['default'] = 'mysqli://user:password@localhost/drupal';
$db_url['alternate'] = 'mysqli://user:password@localhost/alternate_db';

Then you can switch from one database to another quite easily:

db_set_active('alternate');
// do stuff...
db_set_active('default');

What if you want your alternate database to be configurable through a settings page, though? You can’t set that in the settings.php file. The solution is pretty simple, actually: just edit $db_url to add in your new database.

global $db_url;
if ( !is_array($db_url) ) {
  $db_url = array( 'default' => $db_url );
}
$protocol = 'mysqli'; // or use variable_get() to get the variable you set on your settings page
$user = 'user';
$pass = 'password';
$host = 'localhost';
$db = 'alternate_db';
$db_url['alternate'] = "$protocol://$user:$pass@$host/$db";
$previous = db_set_active('alternate'); // returns the previous db name
// do stuff...
db_set_active($previous); // return to whatever database was active before

Drupal will check $db_url every time db_set_active is called, so you can edit it whenever you need.

Categories: Coding

Tags: ,

Jonathan Brinley | 2009-11-05 10:43

Comment | Trackback

5 Responses to "Connect to Multiple Databases from Drupal"

  1. Thanks! How are you using this…or should I just read the books?

    Jason | 2009-11-06 14:25 | Permalink

  2. Doesn’t that raise some security issues though, in terms of having db settings available via Drupal in the administrative settings?

    K | 2009-11-06 16:48 | Permalink

  3. @Jason: I’m working on a site for a client that uses a WordPress blog but Drupal for everything else. This code is going into a module that let’s Drupal pull post info out of WordPress’s database rather than relying on the Aggregator module to harvest the RSS feed.

    Jonathan Brinley | 2009-11-06 19:30 | Permalink

  4. @K: So long as Drupal’s database is secure and only appropriate users have access to the settings page, this shouldn’t add any additional security concerns. Granted, for my purposes, I could just put the connection info into settings.php, but I like keeping the code for my module entirely separate.

    Jonathan Brinley | 2009-11-06 19:32 | Permalink

  5. Hi,

    Nice trick but for me it is not working. I am using dupal 6.x and try to use your code in my module. let me explain what am i exactly doing so you might help me. in my module i am creating one database and after that fetching values from .sql file and trying to insert it into newly created database. Now with my module i have made one database but to insert new query in that database i have to switch db and run query. When i try to use your trick it simple give me error that Access denied for module and logout from website. can you help me ? you can directly email me also if you have some solution.

    Tejas | 2009-12-17 02:26 | Permalink

Leave a Reply