---
url: 'https://metadrop.net/en/articles/multiple-databases-single-instance-drupal-9'
title: 'Multiple databases on a single instance on Drupal 9'
author: 'Eduardo Morales'
date: '2021-10-27T11:12:58+00:00'
updated: '2026-06-04T07:14:29+00:00'
type: article
summary: 'How to host multiple databases on the same instance on Drupal 9 using a prefix for the secondary databases and how to define the database connection using Database API.'
tags:
  - 'Drupal Planet'
published: true
og:
  determiner: Automatic
  site_name: Metadrop
  'image:alt': 'Multiple databases on a single instance on Drupal 9'
  street_address: 'Calle Manuel Luna, 12, 3 Dcha'
  locality: Madrid
  region: Madrid
  postal_code: '28020'
  country_name: España
  email: hola@metadrop.net
  phone_number: '910053180'
schema:
  '@context': 'https://schema.org'
  '@graph':
    -
      '@type': Article
      '@id': 'https://metadrop.net/en/articles/multiple-databases-single-instance-drupal-9#article'
      name: 'Multiple databases on a single instance on Drupal 9'
      headline: 'Multiple databases on a single instance on Drupal 9'
      description: 'How to host multiple databases on the same instance on Drupal 9 using a prefix for the secondary databases and how to define the database connection using Database API.'
      datePublished: '2021-10-27T13:12:58+0200'
      dateModified: '2026-06-04T09:14:29+0200'
      author:
        '@type': Person
        name: 'Eduardo Morales'
      publisher:
        '@type': Organization
        '@id': 'https://metadrop.net/#organization'
      mainEntityOfPage: 'https://metadrop.net/en/articles/multiple-databases-single-instance-drupal-9'
    -
      '@type': Organization
      '@id': 'https://metadrop.net/#organization'
      url: 'https://metadrop.net/'
      name: Metadrop
      sameAs:
        - 'https://www.drupal.org/metadrop'
        - 'https://twitter.com/metadrop'
        - 'https://asociaciondrupal.es/partner/metadrop'
        - 'https://www.linkedin.com/company/metadrop'
      logo:
        '@type': ImageObject
        url: 'https://metadrop.net/themes/custom/mdrop_radix/logo-metadrop-500-500.jpg'
        width: '500'
        height: '500'
    -
      '@type': ItemPage
      '@id': 'https://metadrop.net/en/articles/multiple-databases-single-instance-drupal-9'
      breadcrumb:
        '@type': BreadcrumbList
        itemListElement:
          -
            '@type': ListItem
            position: 1
            name: Home
            item: 'https://metadrop.net/en'
          -
            '@type': ListItem
            position: 2
            name: 'Expert Drupal & Tech Articles'
            item: 'https://metadrop.net/en/articles'
      publisher:
        '@type': Organization
        '@id': 'https://metadrop.net/#organization'
---
 1. [Articles](https://metadrop.net/en/articles)
 
  

# Multiple databases on a single instance on Drupal 9

Wednesday, October 27, 2021

 

 



How to host multiple databases on the same instance on Drupal 9 using a prefix for the secondary databases and how to define the database connection using Database API.



   



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:
    
    ```plaintext
    drush sql-cli < my_database.sql
    ```
- Alter the tables to have a prefix and dump it into a file:
    
    ```plaintext
    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):
    
    ```plaintext
    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.
    
    ```plaintext
    drush sql-cli < tmp/prefix_db.sql
    ```
- It is possible to query the prefixed database without defining the connection on *settings.local.php:*
    
    ```plaintext
    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*:
    
    ```plaintext
    $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:
    
    ```plaintext
    
    $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());
    ```



- Eduardo Morales
    
    Senior Drupal developer
 
[Module development and third-party integrations with Drupal](https://metadrop.net/en/services/drupal/integrations " See Module development and third-party integrations with Drupal")

Metadrop connects Drupal to your CRM, ERP, payment, and marketing stack — Salesforce, Microsoft Dynamics, BBVA virtual POS, Workday, and Keepeek — with custom modules and secure, GDPR-compliant APIs.

 

 See more