Working with a Database in Drupal 7 - Lesson 2 - Database Configuration

The primary mechanism for defining a database connection in Drupal is the $databases array in settings.php. As the name implies, $databases allows you to define multiple database connections. It also supports defining multiple targets. A connection is not opened (i.e., a connection object is not created) until a piece of code executes a database query for the first time.

Connection Key

The connection key is a unique identifier assigned to the database connection. It must be unique across the site and should be set to default for the primary database. On most sites, there will be only one connection.

Connection Target

Each connection may define one or more targets. A target provides an optional mechanism to use an alternate database if available. If the requested target is not defined, Drupal will silently fall back to the default target, which must always be defined.

Targets are most commonly used for master/slave replication. By default, the target is the main SQL server. One or more slave targets can also be defined. Queries that are flagged for slave use will try to use an additional SQL server if possible. If not, Drupal will fall back to using the default (master) server.

$databases Syntax

The $databases array is a 3-level nested array. The first level defines the connection key. The second level defines the connection target. The value for each target is the connection information (driver, credentials, etc.). Here's an example:

 'mysql',
  'database' => 'drupaldb',
  'username' => 'username',
  'password' => 'secret',
  'host'     => 'localhost',
);
?>

This configuration defines a single connection using the key default and the target default. It uses the MySQL driver and connects to the database drupaldb on localhost with the specified credentials. This is the typical setup for most Drupal sites.

Master/Slave Setup

 '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 configuration defines one master (default) server and two slave servers. Note that the slave target is an array of connections. For each page request, one slave will be randomly selected for use. This means all queries for that page may go to dbserver2 or dbserver3.

Multiple Connections

 'mysql',
  'database' => 'drupaldb1',
  'username' => 'username',
  'password' => 'secret',
  'host'     => 'dbserver1',
);
$databases['extra']['default'] = array(
  'driver'   => 'sqlite',
  'database' => 'files/extradb.sqlite',
);
?>

This example defines a primary MySQL database and an additional SQLite database using the extra key. Note that the structure of the connection information may differ depending on the driver. SQLite, for example, only requires the path to the database file. Regardless of how many connections are defined, Drupal will not open a connection until it is actually used.

Drupal 7 Requires PDO

Drupal 7 requires the PHP PDO (PHP Data Objects) library. Be sure to use hosting that supports PDO, or install the PDO extension on your server to run Drupal.