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:[email protected]calhost/drupal';
$db_url['alternate'] = 'mysqli://user:[email protected]/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.

8 Responses to Connect to Multiple Databases from Drupal

  1. Jason says:

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

  2. K says:

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

  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.

  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.

  5. Tejas says:

    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.

  6. Is this a perm change, a session change (single user), or for the single instance when a function is called.

    • @Griffin Hernandez: Drupal will switch back to the original database when you call db_set_active($previous);. If you never call it, then the change will last until the script is finished running (and not affect any concurrent requests).

  7. @jonathan: excellent idea!
    I had the db_set_active modified as an array in the settings.php file of the site, just like Drupal said to do it, and it was working good for my dev site. BUT when I went to a multi-site installation with subdomains, “ny.example.com”, “nj.example.com”, etc… the array method would break and drupal would default to the database for the main site “example.com”. This drove me crazy for hours, trying to figure out what was happening, was the subdomain calling the correct settings.php file, etc….?

    I saw your posting but it didn’t sink in until an hour later. Just modify the $db_url in the module code! Brilliant!

    This way I don’t have to instruct users to modify their db_url in settings, and even if they do, I can still add to the array using your code.

    So for others with my problem, this is what is happening in Jon’s code:

    // put this section of php code at the top of your .module file
    //access the php variable $db_url from it's global scope.
    global $db_url;

    /* if it's not an array already, make it an array, and set the key named 'default' to be the original value of the db_url
    */
    if ( !is_array($db_url) ) {
    $db_url = array( 'default' => $db_url );
    }

    // set your values for the alternative database
    $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';

    /* then add an element to the db_url array building the proper format for a db connection string
    */
    $db_url['alternate'] = "$protocol://$user:[email protected]$host/$db";

    // that is the end of the php code you need at the top of the .module file.

    Note that code chunk will be called each time a page is requested. but it’s small and elegant code so it won’t hurt performance.

    Now when you are writing functions in your module files and you need to switch to your alternate database, use the rest of the code snippet as follows:


    //inside your function

    /* call the db_set_active function and pass it the value of the key in the db_url array that you setup before. that function will get the value of the previous db_url and return it and then switch to the alternative db_url value.
    */
    $previous = db_set_active('alternate');

    // do stuff in your function with the alternative database

    /* now switch back to whatever database was active before you switched to your alternative database
    */
    db_set_active($previous);

    /* you can also call that function without any arguments, and it will connect to the database specified by your key named "default"
    */
    // db_set_active();

    Sorry for writing such a long book, but I can’t stand the lack of good documentation in Drupal, so I wanted to help others. Let’s hope Drupal 7 won’t be too painful…