Multiple databases on a single instance on Drupal 9

Sometimes we want to host several sites with their own database on the same web server. This can be useful when the storage capacity is not fully utilized, so by using a single instance we can reduce costs. Also to increase efficiency or simply for convenience, as for example if we have a custom database and we want to have it available from the web page. Another situation in which it can be useful is in Drupal migrations, when the data source comes from another platform such as Wordpress, Drupal 7, Joomla etc., and we need to migrate with the same database instance due to limitations of the web provider. 

Before getting down to work, it is important to keep in mind the space limitations in order not to reach the contracted limit. It is recommended that once the migrations are finished, we delete the database that has been migrated and leave only the new one to avoid excessive size. If a responsible use is made and the indexes, periodic cleaning of logs and database maintenance are good, it should not affect the response time. The speed will depend a lot on the server, processors, contracted memory and type of disks, so it is necessary to consider all scenarios and available resources.

How to

• Load the future prefixed database in an empty instance:
  

  drush sql-cli < my_database.sql

• Alter the tables to have a prefix and dump it into a file:
  

  drush sql-query "SELECT Concat('ALTER TABLE \`', TABLE_NAME, '\` RENAME TO \`prefix_', TABLE_NAME, '\`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name';" > /tmp/prefix_db.sql

• Clear the current database instance and load the main database (without prefix):
  

  drush sql-drop -y
  cat main_database.sql | drush sql-cli

• Load the prefixed database in the same instance as the "main" database.
  It will keep the tables from the main database and create new tables with the prefix.

  drush sql-cli < tmp/prefix_db.sql

• It is possible to query the prefixed database without defining the connection on settings.local.php:
  

  use Drupal\Core\Database\Database;
  
  // Connection info from default database.
  $connection_info = Database::getConnectionInfo();
  
  // Setup the prefix.
  $connection_info[$key]['prefix'][$key] = '_prefix';
  // It is necessary to define the driver.
  $connection_info[$key]['driver'] = 'mysql';
  
  // Create connection.
  Database::addConnectionInfo('migrate_d8', 'migrate_d8', $connection_info[$key]);
  $connection = Database::getConnection('migrate_d8', 'migrate_d8');
  
  // Query to the new connection.
  $query = $connection->select('node', 'nd');
  $query->addField('nd', 'nid');
  $query->range(0, 2);
  $result = $query->execute();
  
  print_r($result->fetchAll());
  

• Example of how to configure the prefix connection on settings.local.php:
  

  $databases['db_with_prefix']['default'] = [
    'prefix' => 'prefix_',
    'driver' => 'mysql',
    'database' => 'db',
    'username' => 'db',
    'password' => 'db',
    'host' => 'db',
    'port' => '3306',
  ];
  

• Example of query to the database with prefix:
  

  
  $connection = Database::getConnection('db_with_prefix', 'default');
  $query = $connection->select('node', 'nd');
  $query->addField('nd', 'nid');
  $query->range(0, 2);
  $result = $query->execute();
  print_r($result->fetchAll());