Last updated June 3, 2011. Created by Crell on September 18, 2008.
Edited by ignaciogutierrez, Sheldon Rampton, bekasu. Log in to edit this page.
The primary means of defining a database connection is via the $databases array in settings.php. As its name suggests, $databases allows for the definition of multiple database connections. It also supports the definition of multiple targets. A database connection is not opened (the connection object is not created) until the first time some piece of code tries to run a query against that database.
Connection key
A connection key is a unique identifier for a given database connection. The connection key must be unique for a given site, and there must always be a connection of "default" that will be the primary Drupal database. On most sites, it will be the only connection defined.
Target
A given connection key must have one or more targets. A target is an optional alternate database that may be used if available. If the requested target is not defined, the system will silently fall back to a target of "default", which must always be defined.
The primary use of targets is for master/slave replication. The "default" target is the master SQL server. One or more "slave" targets may then be defined. Queries that are flagged to try and use a slave server if available will attempt to access the "slave" target. If one is available, that connection will be opened (if it is not already) and the query will run against the slave server. If not, the query will run against the "default" (master) server instead. That allows for a transparent fallback, so code can be written to take advantage of a slave server if it is available but will still run without one with no modification.
$databases syntax
The $databases array is a nested array of at least three levels. The first level defines the database keys. The second defines the database targets. The value of each target is the connection information for that key/target. Some examples should make that clearer.
<?php
$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'drupaldb',
'username' => 'username',
'password' => 'secret',
'host' => 'localhost',
);
?>The above $databases array defines a single connection key ("default"), with a single target ("default"). That connection uses a MySQL database (the "driver" key) on localhost named "drupaldb" with a username of "username" and a password of "secret". The above example is the typical case for a single-SQL server Drupal install, and will be sufficient for the vast majority of sites.
For a master/slave configuration, one would define the following:
<?php
$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'drupaldb1',
'username' => 'username',
'password' => 'secret',
'host' => 'dbserver1',
);
$databases['default']['slave'][] = array(
'driver' => 'mysql',
'database' => 'drupaldb2',
'username' => 'username',
'password' => 'secret',
'host' => 'dbserver2',
);
$databases['default']['slave'][] = array(
'driver' => 'mysql',
'database' => 'drupaldb3',
'username' => 'username',
'password' => 'secret',
'host' => 'dbserver3',
);
?>This definition provides a single "default" server and two "slave" servers. Note that the "slave" key is an array. If any target is defined as an array of connection information, one of the defined servers will be selected at random for that target for each page request. That is, on one page request all slave queries will be sent to dbserver2 while on the next they may all be sent to dbserver3.
<?php
$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'drupaldb1',
'username' => 'username',
'password' => 'secret',
'host' => 'dbserver1',
);
$databases['extra']['default'] = array(
'driver' => 'sqlite',
'database' => 'files/extradb.sqlite',
);
?>This configuration defines a single main Drupal database and one additional database labeled "extra" that uses SQLite. Note that the SQLite connection information is structured differently than MySQL's. Each driver may have different configuration depending on what is appropriate for it.
Remember that no matter how many connections you define, Drupal will not open a connection to that database until it is actually used.
PDO Required
Since PHP's PDO library is now required by the Drupal database layer, you will need a hosting plan that includes it to run Drupal.
Comments
Proposed changes to "Target" section and "PDO required" section
The "Target" section of this page is a bit confusing and seemingly contradictory. It currently states:
This passage initially states that a connection key must have one or more targets, and then states that a target is "optional" and that a target of "default ... must always be defined." If a target must always be defined, it isn't really optional. If I'm understanding the concept correctly, I think it would be more clear if this section were reworded as follows:
As for the "PDO required" section, it seems to duplicate information that also appears at the beginning the "General comments." I would therefore suggest deleting this section from this page, and merging any necessary information from here into the "General comments" page. The only new information here is Drupal needs a hosting plan that includes PDO. But is that advice actually necessary? According to the PHP documentation, PDO ships with PHP 5.1, and since Drupal 7.x requires PHP 5.2, doesn't that imply that PDO will be included in any hosting plan that has a recent enough PHP version to support Drupal?
----------------
IT consultant, web designer, writer and researcher
http://www.sheldonrampton.com/portfolio
where to write Database API code
As we see in the FormAPI, all the function are written in the file moule_name.module. What about database API. where we have to write it and use.
Thank you
Jayakumar
Connecting to a d6
I recently needed to mount a Drupal 6 database inside of my Drupal 7 install so that I could compare data, and in certain instances, import. I am not mounting a slave copy of the database, but mounting a different data source. Hope this helps others to save time too.
Settings.php array (in addition to the default/default):
$databases = array (
'default' => array (
'default' => array (
'database' => 'drupal_7_database_name',
'username' => 'drupal',
'password' => 'p',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
);
$databases['d6']['default'] = array(
'database' => 'drupal_6_database_name',
'username' => 'drupal',
'password' => 'p',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
);
In my module I make certain I checked that the database was the correct one:
$connection = Database::getConnection('default', 'd6');
$connectionOptions = $connection->getConnectionOptions();
if($connectionOptions['database'] != 'drupal_6_database_name'){
print '<div>Wrong database connection: ' . $connectionOptions['database'] . '<br /><strong>STOPPING</strong></div>';
print '<div><a id="refresh" href="?set=' . ($set) . '&t=' . time() . '">Try again.</a></div>';
exit(0);
}
$total_count = $connection->query("SELECT COUNT(nid) FROM node n")->fetchField();
$resultset = $connection->query("SELECT n.type, n.nid, n.title, n.created, n.changed FROM node n WHERE 1 ORDER BY n.nid ASC");
foreach ($resultset as $result) {
//do something
}
//make certain we switch back to the Drupal 7 database
$connection = Database::getConnection('default', 'default');
//do more stuff