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:

$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:

// 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:

  $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

$db_url = 'mysql://drupal:drupal@localhost/drupal';

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

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

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.

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

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

drupalfever’s picture

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.

mr.j’s picture

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

charlie-s’s picture

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.
?>
dxvargas’s picture

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

Sleavely’s picture

+1

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

rizedr’s picture

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

$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:

db_set_active('yourcustomdb');
$query = db_query('....');
db_set_active('default'); // We need to call the main (drupal) db back
thatwebguy’s picture

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.

nevets’s picture

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

alexkb’s picture

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).
salientknight’s picture

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.

salientknight’s picture

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?

charlie-s’s picture

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

dvandusen’s picture

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.

D

pineiden’s picture

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

to

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

suresh.senthatti’s picture

Does anyone know how to connect External Database in D8

F.E.M’s picture

This should be updated for D8

rohantapiyawala1988’s picture

can anyone guide me with the procedure to connect mssql database from my current site, while my drupal site is still connected and working on sqlite database???