Need some help with your project? Contact me

How to connect to external databases in your Drupal 7 site

In this article I am going to show you how to query for information in a database other than the default one you set up when you installed Drupal. There are 2 main ways to do this properly and choosing one over the other depends of course on your needs.

One of the ways is mostly for when multiple modules interact with an external database. This is prefered and helpful as you don’t need to always add the db information in the code of every module. The other way is mostly for when you have a single module that needs a one-off interaction with an external database: it connects, performs operations, disconnects and that’s it.

Please disregard the .php opening and closing tags as they are only for the purpose of formatting and displaying the code snippets on this page.

Option number 1

Let’s first see how to configure the Drupal site to quickly allow connections to another database to all modules. So in a typical Drupal 7 installation, you’ll see that in the settings.php file (where the db information is stored), there is a big array like this:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'db-name',
      'username' => 'db-username',
      'password' => 'db-password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

If it’s not exactly like this for you, don’t worry. The username, password and database name should all be there and that is how Drupal knows how to connect to its default database. To allow modules to quickly make connections to other databases, you’ll need to add additional information to the $databases array:

$databases['your_other_db']['default'] = array (
  'database' => 'other-db-name',
  'username' => 'other-db-username',
  'password' => 'other-db-password',
  'host' => 'localhost',
  'driver' => 'mysql',
);

As you can see, this code defines another database identified by the array key your_other_db. So when you need to query this other database in your modules, you have to switch the connection to it with this function:

db_set_active('your_other_db');

When you are finished, you have to close it and revert back to the default database connection in order for Drupal to be able to access its data:

db_set_active();

As you don’t pass a parameter to this function, it will set the default database active and everything goes back to normal.

Option number 2

This option is more for when you need to connect to another database only in one module. So let’s see how it works.

To perform operations on it, first you need to store the connection data in an array:

$database_info = array(
  'host' => 'localhost', 
  'database' => 'db-name',
  'username' => 'db-username', 
  'password' => 'db-password', 
  'driver' => 'mysql',
);

In case you are using a PostgreSQL, just replace the driver with pgsql.

Next up, you need to add the database connection info using the following line:

Database::addConnectionInfo('your_unique_key', 'default', $database_info);

The function takes three parameters. First, you need to set a unique database key so that you can later activate it. The second parameter is the target database name. You can leave this as ‘default’ since you only need to connect to one database. The third parameter is the database connection information that you stored in the $database_info array.

And now, if you want to perform operations on the new database, simply do like in option 1:

db_set_active('your_unique_key');

After you are done with it, close the connection and reactivate the default Drupal database:

db_set_active();

And you are done. Hope this helps anyone.

Comments

You don't necessarily need to use the db_set_active() call before the query. You can select the database as part of the query:

$query = Database::getConnection('default', 'your_other_db')
->select('tablename', 'alias')...

Works, and means you don't need to worry about resetting the active database afterwards. I found this in the docs to the Migrate module (https://drupal.org/node/1152150) a while ago, and have been using it since.

db_set_active() isn't optimal, especially if you're intermixing calls to the external database with calls to the default Drupal database (as when you're importing data from that external database to insert as Drupal users/nodes/etc.). It's important to understand that your standard db_select (for example) is really just a shortcut for

<?php
Database::getConnection('default', 'default')->select($table_name, $table_alias)
?>

So, if you substitute the key for your external database as the second parameter to getConnection, you can query it directly without the pair of db_set_active calls around it:

<?php
$query = Database::getConnection('default', 'your_unique_key')
->select('external_table', 'et')
->fields('et', array('id', 'content'));
?>

Check out this new project that provides this ability through the user interface: Remote content user interface

It is great to hear a new development of remote content UI for users. As I am building a CMS for scholars, it can extend the arm to retrieve the contents from different databases.

Can it have ajax function?

Since you are connected to a db, you should be able to use the Ajax framework. I have not tried ajax with external db, but don't see why it would be a problem.

Noted and thanks.

Can i fetch data from two database in single query at same time.

Great Article!! exactly what I am looking for. I'm new to Drupal and getting a little frustrated. Do you have an example full module that pulls data from a database and displays it? where the connection is in the module? charlesstratton4@gmail.com

Thanks!

i want to connect to reshift databse how can i connect to this

Add new comment

You can post comments in Markdown and basic HTML tags.
For code blocks, wrap your code within '~~~'. For example:
~~~
$var = 'my variable';
~~~