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:

if (file_exists('/var/www/site-php')) { 
  global $conf; 
  
  // Do not autoconnect to database 
  $conf['acquia_hosting_settings_autoconnect'] = FALSE; 
  
  // Use your existing 'require' statement here 
  require('/var/www/site-php/AH_SITE_GROUP/AH_SITE_GROUP-settings.inc'); 
  
  // Set the MySQL variable values 
  // Make sure to include the array brackets, [] 
  $databases[DB_CONNECTION]['default']['init_commands'] = [ 
    'VARIABLE_NAME_1' => 'SET SESSION VARIABLE_NAME_1=VARIABLE_VALUE_1', 
    'VARIABLE_NAME_2' => 'SET SESSION VARIABLE_NAME_2=VARIABLE_VALUE_2', 
  ]; 
  
  // Connect to database 
  if (function_exists('acquia_hosting_db_choose_active')){ 
    acquia_hosting_db_choose_active( 
      $conf['acquia_hosting_site_info']['db'], 
      'default', 
      $databases, 
      $conf 
    ); 
  } 
}

if (file_exists('/var/www/site-php')) { 
  global $conf; 
  
  // Do not autoconnect to database 
  $conf['acquia_hosting_settings_autoconnect'] = FALSE; 
  
  // Use your existing 'require' statement here 
  require('/var/www/site-php/AH_SITE_GROUP/AH_SITE_GROUP-settings.inc'); 
  
  // Set the MySQL variable values 
  $databases['default']['default']['init_commands'] = [ 
    'wait_timeout' => 'SET SESSION wait_timeout=1200', 
    'transaction_isolation' => 'SET SESSION transaction_isolation="READ-COMMITTED"' 
  ]; 
  
  // Connect to database 
  if (function_exists('acquia_hosting_db_choose_active')){ 
    acquia_hosting_db_choose_active( 
      $conf['acquia_hosting_site_info']['db'], 
      'default', 
      $databases, 
      $conf 
    ); 
  } 
}

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());'

$ 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 
)

$ drush sqlq "SHOW VARIABLES LIKE 'VARIABLE_NAME';"

$ drush sqlq "SHOW VARIABLES LIKE 'wait_timeout';" 
wait_timeout 1200

 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:

1. Define the secondary database in your settings.php file
2. Switch to the secondary Database inside of your module
3. Query that database
4. 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.