How to connect to multiple databases within Drupal

Last updated April 2, 2014. Created 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

drupal Cannot redeclare db_status_report()

drupalfever commented May 19, 2011 at 1:55pm

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

Watch out for $db_prefix

mr.j commented June 20, 2011 at 4:45am

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

csdco commented November 26, 2011 at 9:26pm

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.
?>

This the right way to do it

hiphip commented December 24, 2012 at 12:49am

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

+1

Sleavely commented January 8, 2013 at 12:56pm

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

Best way to add a db connection

rizedr commented December 27, 2011 at 11:26am

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

anil.shrish commented September 27, 2013 at 5:26am

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

nevets commented September 27, 2013 at 5:42am

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

alexkb commented January 19, 2012 at 1:38am

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).

Issues

salientknight commented January 24, 2012 at 6:30pm

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.

sof.com has an answer

divbox commented January 24, 2012 at 9:57pm

here - http://stackoverflow.com/questions/4839063/multiple-concurrent-database-...

Thanks, but that only works

salientknight commented January 25, 2012 at 9:19pm

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

csdco commented January 25, 2012 at 9:43pm

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();.

Not yet working effectively.

dvandusen commented June 8, 2014 at 8:51am

see: https://drupal.org/node/2236983

The problem as I can see from the code is that several modules (devel?, obtaining cache or variables, processing exceptions) are invoked/might be called in the processing of the database query on the alternative DB, and those will attempt to use the primary default database, and then fail when the database connection was changed from the default to access an alternative, even if the actual call, for instance, to db_select, because it is explicitly bracketed by the db_set_active('...'); and db_set_active();. To fix that, the call to getConnection from db_select, must provide the information needed (the 'key') parameter, so that switching out of the connection with the default db is unnecessary.

The definition for getConnection cites a 'key' argument:

function getConnection($target = 'default', $key = NULL)

(
This is sadly different from the order in Database::addConnectionInfo() which is

public static function addConnectionInfo($key, $target, $info)

)

Also, in DB_select, the $key is not set as a parameter to getConnection, though it is in the options array:

function db_select($table, $alias = NULL, array $options = array()) {
  if (empty($options['target'])) {
    $options['target'] = 'default';
  }
  return Database::getConnection($options['target'])->select($table, $alias, $options);
}

while

final public static function getConnection($target = 'default', $key = NULL) {

so this implies that the 'master' or 'slave' or 'default' is always used as set, but not the key to the alternative database/schema, requiring the db_set_active('...'); and db_set_active(); around the db_select.

Since calls to other dbs can easily be required within the processing of the db_select (such as devel calls or calls in alters), this is inflexible design. I think it would be best to change the invocation line:

  return Database::getConnection($options['target'])->select($table, $alias, $options);

to:

  return Database::getConnection($options['target'], $options['key'])->select($table, $alias, $options);

to add the Key parameter (it is already spec'd as an argument!!) . If this is not done, you must use the db_set_active('...'); and db_set_active(); around the db_select, and rely upon luck not to have any call to the
default DB.

One advice, to set correctly the databases

pineiden commented December 9, 2014 at 3:44pm

Well, i set a password to my user database with '\'.
Then the system give an error because understand like a command, then i put "\\" to have the char.
@code
'password' => 'passwith\blabla',
@code

@code
'password' => 'passwith\\blabla',
@code

How to connect to multiple databases within Drupal

Drupal 7

Adding additional databases in your configuration

Setting up databases on the fly

Drupal 6 and older

Comments

drupal Cannot redeclare db_status_report()

Watch out for $db_prefix

I would like to add that in

This the right way to do it

+1

Best way to add a db connection

here is the new challenge

See the last line of

In the Drupal 7 example of

Issues

sof.com has an answer

Thanks, but that only works

You say:Up until recently

Not yet working effectively.

One advice, to set correctly the databases

Page status

About this page

Develop for Drupal

News Items

Our Community

Online Documentation

Drupal Code Base

Governance of Community

Search form

How to connect to multiple databases within Drupal

Drupal 7

Adding additional databases in your configuration

Setting up databases on the fly

Drupal 6 and older

Comments

Page status

About this page