The $databases variable in Drupal is a fundamental item to Drupal site configuration and is used to set the database connection details. With this variable, you can define one or more database connections. For more information, visit Database Configuration.

You might need to change the default Drupal settings to accommodate different database configurations, including the setting of values for MySQL variables and working with databases external to Drupal. You can use and modify the $databases variable in the settings.php file to:

• Override MySQL variable settings
• Work with databases external to Drupal

Overriding MySQL variable settings

You might need to override MySQL default variable settings for variables like wait_timeout and transaction_isolation in Cloud Platform or Site Factory. While you cannot implement an override on the MySQL server directly as this is centrally managed, you can override that setting on a per session basis, for example, for every PHP process that bootstraps Drupal.

Note the following variables present in the code snippet included for the various use cases described later in this document:

Configuring for your use case

The following tabs provide details for configuring the MySQL variable values, depending upon your application installation configuration use case:

Verifying configuration for your use case

After you implement the preceding changes based on your use case, you must verify that the override settings are applied. You can do that by connecting to the database and checking the appropriate MySQL variable through the following drush command.

drush eval 'print_r(Drupal\Core\Database\Database::getConnection(DB_CONNECTION, "default")->query("SHOW VARIABLES LIKE '\'VARIABLE_NAME\'';")->fetchObject());'

 Working with databases external to Drupal

You might need to access one or more databases, in addition to the Drupal configured database. This is different to a Drupal multisite setup. The $databases variable is set up so as to use the first key as the identifier for each database, known as the Connection Key. For more information, visit Database Configuration. With this key, you can define a second or more databases that Drupal can access. Ensure that there is at least one default key, which defines the primary database. For example, a second database connection is identified as extra:

$databases['default']
$databases['extra']

You can connect to external databases through the following primary steps:

• Define the secondary database in your settings.php file
• Switch to the secondary Database inside of your module
• Query that database
• Swap back to the default Database when finished

The following example details the process of modifying the default Drupal database and a second, additional database in a custom module and querying the second database:

Defining the secondary database in your settings.php file

The default.settings.php file in the View Source section defines the format for the $databases variable. The first key indicates the database name and the second key indicates the replication database name, or a "target" name, if you want to use primary or replica database replication for your site.

$databases['database_name']['database_target']

To access a different database in addition to your Drupal database, use the first key name for the $databases variable. For example, the following code snippet defines the configurations for the default Drupal database and an additional database, named "second_db". Note the keys, default and second, used for the $databases variable: 

// Default Drupal database configuration
$databases['default']['default'] = array (
  'database' => 'drupal',
  'username' => 'drupal',
  'password' => 'drupal',
  'prefix' => '',
  'host' => 'localhost',
  'port' => '3306',
  'isolation_level' => 'READ COMMITTED',
  'driver' => 'mysql',
  'namespace' => 'Drupal\\mysql\\Driver\\Database\\mysql',
  'autoload' => 'core/modules/mysql/src/Driver/Database/mysql/',
);
// Second database configuration
$databases['second']['default'] = array(
  'database' => 'second_db',
  'username' => 'drupal',
  'password' => 'drupal',
  'host' => 'localhost',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
  'prefix' => '',
);

For more information, visit:

• Adding additional databases to your configuration

• Database Configuration

Switching to the database inside your module

Switch to the additional database by using the Database::setActiveConnection() and Database::getConnection() static methods. For the example,

// Change the active connection to the additional database
Database::setActiveConnection('second');
// Get the database connection for the additional database
$second_db_conn = Database::getConnection();

Querying the database

Perform the query to the database that is not the default Drupal database as follows:

// Execute a query on tables in the additional database
$result = $second_db_conn->select('some_table', 't')
      ->fields('t', ['id', 'column_name'])
      ->range(0, 1)
      ->execute()
      ->fetchAll();
// Do something with the result
// ...

Swapping back to the default database when finished

Change the connection to the default Drupal database connection as follows:

// Switch back to the default Drupal database.
Database::setActiveConnection();

You have implemented a change between your default Drupal database and an additional database.

Overriding Drupal $databases settings

drush eval 'print_r(Drupal\Core\Database\Database::getConnection(DB_CONNECTION, "default")->query("SHOW VARIABLES LIKE '\'VARIABLE_NAME\'';")->fetchObject());'

$databases['default']
$databases['extra']

$databases['database_name']['database_target']

// Default Drupal database configuration
$databases['default']['default'] = array (
  'database' => 'drupal',
  'username' => 'drupal',
  'password' => 'drupal',
  'prefix' => '',
  'host' => 'localhost',
  'port' => '3306',
  'isolation_level' => 'READ COMMITTED',
  'driver' => 'mysql',
  'namespace' => 'Drupal\\mysql\\Driver\\Database\\mysql',
  'autoload' => 'core/modules/mysql/src/Driver/Database/mysql/',
);
// Second database configuration
$databases['second']['default'] = array(
  'database' => 'second_db',
  'username' => 'drupal',
  'password' => 'drupal',
  'host' => 'localhost',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
  'prefix' => '',
);

// Change the active connection to the additional database
Database::setActiveConnection('second');
// Get the database connection for the additional database
$second_db_conn = Database::getConnection();

// Execute a query on tables in the additional database
$result = $second_db_conn->select('some_table', 't')
      ->fields('t', ['id', 'column_name'])
      ->range(0, 1)
      ->execute()
      ->fetchAll();
// Do something with the result
// ...

// Switch back to the default Drupal database.
Database::setActiveConnection();

For example, you an check the value for the wait_timeout variable as follows:

$ drush eval 'print_r(Drupal\Core\Database\Database::getConnection("default", "default")->query("SHOW VARIABLES LIKE '\'wait_timeout\'';")->fetchObject());' 
stdClass Object 
( 
    [Variable_name] => wait_timeout 
    [Value] => 1200 
)

For example, you an check the value for the wait_timeout variable as follows:

$ drush eval 'print_r(Drupal\Core\Database\Database::getConnection("default", "default")->query("SHOW VARIABLES LIKE '\'wait_timeout\'';")->fetchObject());' 
stdClass Object 
( 
    [Variable_name] => wait_timeout 
    [Value] => 1200 
)