Last updated April 2, 2014. Created by WorldFallz on March 5, 2005.
Edited by Garrett Albright, Sleavely, aardvark92, beanluc. Log in to edit this page.

Drupal can connect to different databases with elegance and ease!

Drupal 7

There are two methods for accessing secondary databases in Drupal 7.

Adding additional databases in your configuration

Preferably you would add your configuration in the settings.php file for your site, so that all modules can interact with the new database.

In your settings.php:

<?php
$databases
= array();
$databases['default']['default'] = array(
 
// Drupal's default credentials here.
  // This is where the Drupal core will store its data.
);
$databases['my_other_db']['default'] = array(
 
// Your secondary database's credentials here.
  // You will be able to explicitly connect to this database from your modules.
);
?>

In your module:

<?php
// Use the database we set up earlier
db_set_active('my_other_db');
// Run some queries, process some data
// ...
// Go back to the default database,
// otherwise Drupal will not be able to access its own data later on.
db_set_active();
?>

Setting up databases on the fly

If your module will be alone in using the secondary database you can define the connection directly in your module:

<?php
  $other_database
= array(
     
'database' => 'databasename',
     
'username' => 'username', // assuming this is necessary
     
'password' => 'password', // assuming this is necessary
     
'host' => 'localhost', // assumes localhost
     
'driver' => 'mysql', // replace with your database driver
 
);
 
// replace 'YourDatabaseKey' with something that's unique to your module
 
Database::addConnectionInfo('YourDatabaseKey', 'default', $other_database);
 
db_set_active('YourDatabaseKey');
 
// execute queries here
 
db_set_active(); // without the paramater means set back to the default for the site
 
drupal_set_message(t('The queries have been made.'));
?>

See the Database Abstraction Layer for a complete reference of all Drupal database abstraction functions for version 7.

Drupal 6 and older

In Drupal 6 and older versions, first define the database connections Drupal can use by editing the $db_url string in the Drupal configuration file (settings.php for 4.6 and above, otherwise conf.php). By default only a single connection is defined

<?php
$db_url
= 'mysql://drupal:drupal@localhost/drupal';
?>

To allow multiple database connections, convert $db_url to an array.

<?php
$db_url
['default'] = 'mysql://drupal:drupal@localhost/drupal';
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';
$db_url['db3'] = 'mysql://user:pwd@localhost/yetanotherdb';
?>

Note that database storing your Drupal installation should be keyed as the default connection.

To query a different database, simply set it as active by referencing the key name.

<?php
db_set_active
('mydb');
db_query('SELECT * FROM table_in_anotherdb');
//Switch back to the default connection when finished.
db_set_active('default');
?>

Make sure to always switch back to the default connection so Drupal can cleanly finish the request lifecycle and write to its system tables.

Note: It is particularly important to switch back to the active Drupal database prior to any calls to Drupal functions. Errors in the error log about not being able to find the 'system' table are an indication that calls to Drupal functions preceed switching back to the default database.

This only works with two databases of the same type. For example the following code will not work.

<?php
// ... header of the settings.php file
$db_url = array (
"default" => "mysql://user:pass@host/db",
"second" => "pgsql://user:pass@host/db"
);
// ...
?>

Set up multiple database on the fly
<?php
global $db_url; // the internal variable that contains database link
if (!is_array($db_url)) {
 
$default_db = $db_url;
 
$db_url = array('default' => $default_db);
}
//set up the new database value
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';
db_set_active('mydb');    // activation & execution same as explained above
$results = db_query($sql); //sql represents the query to be executed
db_set_active('default'); // set back to original
?>

See the Database Abstraction Layer for a complete reference of all Drupal database abstraction functions for version 6.

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

I was getting the following error message;

drupal Cannot redeclare db_status_report()

I was trying to connect to an online MySQL server from my Drupal Developer Environment installation.

When I installed DAMP with Aquia Drupal Stack, the following entry was automatically created on the settings.php file:

$db_url = 'mysqli://my_user_name@127.0.0.1:55555/my_database_name';

Notice the "i" on the "mysqli" protocol statement. When I tried to connect to the database, I got the above error message. The error message is not very elucidating but I resolved the problem by using the "i" when stating my second database connection URL.

Now I got another problem that I am trying to resolve. After I fixed this little invisible "typo" on my URL and ran my connection PHP script again I was surprise to see that my website was saying that it was "offline for maintenance"! There was, however, a message with tinny little letters at the very bottom that was saying that the mysql connection could not be established.
I found out that I had to change the settings on my Godaddy Hosting account to be able to externally access the database.

But the point is that the message showing that the website was offline was very scary when in fact that was not the case. The website was not really offline. If I went to any other page on my site, the message would go away.

I just wanted to warn the next person that goes through the same problems not to get desperate. These error messages are scarier than the actual reasons behind them.

This is the site I crash often: http://www.drupalfever.com

Note that in drupal prior to version 7, $db_prefix is not database-specific!

So if you are switching between databases you have to manually change the $db_prefix. Something like this will do:

// Assuming 2 DBs set up in config.php: default and db2
global $db_prefix;
$original_db_prefix = $db_prefix;
$db_prefix = 'db2prefix_';
db_set_active('db2');
// do stuff...
$db_prefix = $original_db_prefix;
db_set_active('default');

More info:
#195416: Table prefixes should be per database connection

I would like to add that in Drupal 7 it's not necessary to set your credentials and instantiate Database::addConnectionInfo at runtime -- you can simply add an additional database entry in settings.php like so:

<?php
$databases
= array();
$databases['default']['default'] = array(
 
// Drupal's credentials here...
);
$databases['my_other_db']['default'] = array(
 
// My other database credentials here...
);
?>

and activate it via:

<?php
db_set_active
('my_other_db'); // Use my_other_db
// do stuff
db_set_active(); // Go back to Drupal's db.
?>

IMO, this info really should be in the main content!
Not in some comment that many people won't read.

+1

Edit: I added csdco's example to the main content.

I would say that the best way to add a db connection of the same type in drupal 7 is like this:

<?php
$databases
= array (
 
'default' =>  // main drupal db starts from here
 
array (
   
'default' =>
    array (
     
'database' => 'drupaldb', // main drupal db name
     
'username' => 'root', // main drupal db username
     
'password' => 'password', // main drupal db password
     
'host' => 'localhost',
     
'port' => '',
     
'driver' => 'mysql',
     
'prefix' => '',
    ),
  ),
'yourcustomdb' =>  // additional database starts here
 
array (
   
'default' =>
    array (
     
'database' => 'database', // additional database name
     
'username' => 'root', // additional database username
     
'password' => 'password', // additional database password
     
'host' => 'localhost',
     
'port' => '',
     
'driver' => 'mysql',
     
'prefix' => '',
    )
  ),
);
?>

And then we would call our second db like this:
<?php
db_set_active
('yourcustomdb');
$query = db_query('....');
db_set_active('default'); // We need to call the main (drupal) db back
?>

here is the new challenge, i have a different database (derby DB) how do i configure it.

i tried this

      'database' => 'derbydb',
      'username' => 'app',
      'password' => 'sa',
      'host' => 'localhost',
      'port' => '1527',
      'driver' => 'derby',
      'prefix' => '',

it throws an error

ERROR MESSAGE : " Fatal error: require_once(): Failed opening required '/var/www/drupal7/includes/database/derby/database.inc'"

i believe this means , we should have driver in drupal also .

any help is appreciated , Thank you.

See the last line of http://coffeecode.net/talks/show.php/derby-php/3, that suggests you want odbc for the driver.

In the Drupal 7 example of this handbook page, make sure you set db_set_active() straight after you've finished with all your queries, as I've found, if you start calling theme() functions and then go to set the active db back to drupal's native db, it gives you the following errors:

DatabaseTransactionNoActiveException: in DatabaseConnection->popTransaction() (line 1100 of #########\includes\database\database.inc).

Setting a different database as active, effectively removes the ability for Drupal to run queries against its own database. That makes this solution a non-starter. Up until recently you could create a php connection mysql_connect() and get around the problem that way. I went to run a script a wrote that did just this, and the mysql_connect has stopped working. The connection request is never even sent.

I cannot have my entire site going offline every-time I want to query another database. This is huge flaw and needs to be corrected. There needs to be a way to run concurrent connections.

Thanks, but that only works in D7. D6 is written differently.

1)D6 calls pg_query and that throws and error when I try to call it.

2,3) mysql_connect() and mysqli_connect() dont even ettempt to make a connection to the server they just die.

4) db_connect($url)or die ("Connection to Server failed"); Returns a connection object with all null values and so I have not been able to use this connection to run queries.

5) db_query cannot be used without changing the global value of $active_db -- and having side effect for active users...

6) Database::getConnection is D7

Is there another option for making a direct connection?

You say:

Up until recently you could create a php connection mysql_connect() and get around the problem that way. I went to run a script a wrote that did just this, and the mysql_connect has stopped working

What does "up until recently" mean, if you're referring to Drupal 6? What is the error being logged by PHP?

Also, this comment:

Setting a different database as active, effectively removes the ability for Drupal to run queries against its own database.

is kind of the whole point of changing the active database. Typically you would switch to another db to perform your external commands via the DB abstraction layer and then switch immediately back to Drupal's database via db_set_active();.