Multiple database in Drupal

Hi,

Recently I have been working with a distributed database architecture in Drupal 6, without the use of multisite. So thought of sharing in the blog. Multiple database is very important in Drupal when you have custom tables along with Drupal inbuilt tables. The best practice is to keep separate database for both. For creating multiple database in Drupal, first you need to follow the conventional Drupal installation process. One's that is done, create a seperate database and dump all the custom tables in that database.

Assume the name of the drupal database is "drupal_db" and the name of the custom database is "business_db". Now after you have successfully completed installing Drupal in your server, go to sites/default/settings.php file. There you will find the following or similiar looking lines:

$db_url = 'mysqli://root@localhost/drupal_db';
$db_prefix = 'drp_';

1st step to convert the current Drupal setup into multi database, is to replace the above two lines with the following:

$db_url['default'] = 'mysqli://root@localhost/drupal_db';

$db_prefix['default'] = 'drp_';

$db_url['business_data'] = 'mysqli://root@localhost/business_db';

Basically you just need to create an array of connections. In this, $db_url['default'] refers to the default connection if you are not explicitly mentioning from which database the data needs to be fetched. Most cases this is the drupal database. For the custom database you need to create another index of the same array with the details of that database. In this case I have named it as "business_data" in:

$db_url['business_data'] = 'mysqli://root@localhost/business_db';

Now the main part is to switch connections as needed. For that I had used db_set_active() function. Generally I have used this switching process just before and after performing any database transaction. This function takes 1 parameter, which is the array index name which I had created earlier i.e. "default" and "business_data".

e.g.

db_set_active("business_data");  // switching the database with custom tables

$details_res=db_query("SQL_QUERY_GOES_HERE");

$details=db_fetch_array($details_res);

.

.

.

db_set_active("default");  // switching back to the default database i.e. drupal database

You need to take care of switching the database back to default when the job is done, else it would continue to work with "business_data" database, which may result to design or CMS related issue. To avoid this, I made a small hack in Drupal core. Here it is:

This is not advisable, but it will work. Go to /includes/themes.inc file, search for the function function list_themes($refresh = FALSE). In the first line of the function body switch the database back to default by 

db_set_active("default");

This will ensure your themes and layouts will always point to the Drupal database, even if you have forgotten to switch to default database after your database transaction is complete. 

That's all for now then. Best of luck