Database configuration

The primary way to define a database connection in Drupal is through the $databases array in settings.php. As the name suggests, $databases allows you to define multiple database connections. It also supports defining multiple targets. A connection to the database is not opened (i.e., the connection object is not created) until the first piece of code attempts to run a query against that database.

Connection Key

$databases['default'] // The database connection.

The connection key is a unique identifier for a given database connection. The connection key must be unique to the site, and there must always be a "default" connection that serves as Drupal’s primary database. For most sites, this will be the only defined connection.

Target

$databases['default']['default'] // The database target.

A connection key must have one or more targets. A target is a database that may be used if it is available. The "default" target must always be defined for each connection key. If a requested target is not defined, the system automatically falls back to the "default" target.

The main use case for targets is primary/replica replication. The default target is the primary SQL server. One or more "replica" targets may then be defined (note that in some cases, "replica" is the only valid alternative target, e.g., in static queries). Queries marked to attempt using a replica server will try to access the "replica" target. If it exists, the connection will be opened (if not already), and the query will run on the replica. If not, the query will run on the primary server instead. This provides transparent fallback, so code can benefit from replicas if available while still functioning without them.

$databases syntax

The $databases array is a three-level nested array at minimum. The first level defines database keys. The second defines database targets. The value of each target is the connection information for that key/target. Some examples clarify this.

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'drupaldb',
  'username' => 'username',
  'password' => 'secret',
  'host' => 'localhost',
);

The above $databases array defines one connection key ("default") with one target ("default"). This connection uses a MySQL database ("driver") on the localhost, named "drupaldb", with username "username" and password "secret". This is a typical setup for a Drupal installation and is sufficient for most sites.

For primary/replica configuration, you might define the following:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'drupaldb1',
  'username' => 'username',
  'password' => 'secret',
  'host' => 'dbserver1',
);
$databases['default']['replica'][] = array(
  'driver' => 'mysql',
  'database' => 'drupaldb2',
  'username' => 'username',
  'password' => 'secret',
  'host' => 'dbserver2',
);
$databases['default']['replica'][] = array(
  'driver' => 'mysql',
  'database' => 'drupaldb3',
  'username' => 'username',
  'password' => 'secret',
  'host' => 'dbserver3',
);

This configuration provides one "default" server and two "replica" servers. Note that the "replica" key is an array. If a target is defined as an array of connections, one server is randomly chosen per page request. So all replica queries on one page might go to dbserver2, while all replica queries on the next might go to dbserver3.

$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 one primary Drupal database and one additional database labeled "extra" that uses SQLite. Note the different structure of SQLite connection info compared to MySQL. Each driver may have different configuration requirements.

Remember: regardless of how many connections you define, Drupal won’t open a connection until it is actually used.

Required PDO

Since the PDO PHP library is required for Drupal’s database layer, your hosting plan must support PDO for Drupal to function.

PDO Options

PDO options and driver-specific PDO options can be specified in the database array using the 'pdo' key, for example:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'drupaldb',
  'username' => 'username',
  'password' => 'secret',
  'host' => 'dbserver1',
  'pdo' => array(PDO::ATTR_TIMEOUT => 2.0, PDO::MYSQL_ATTR_COMPRESS => 1),
);