How do I specify in which database my schema should be created?

Question

When using multiple databases in Drupal 7, how can I specify that a table is to be created in a different database on a different server?

By default, when you install a module, Drupal assumes that everything in hook_schema() should be installed in the default database. Is there a way to specify that a table should be created on a different database, or is there some kind of manual workaround I can use?

Answer 1

I don't think there is an official API; it doesn't make much sense to do this in general.

That said, all you need to do is give your hook_schema a different name than yourmodule_schema (basically whatever you want, like yourmodule_schema_otherdb) and then in hook_install(), first switch your database, then replicate what drupal_install_schema() does except that you call your custom schema definition function and then switch the database back to the default.

Also, remember to implement hook_uninstall().

No idea why you'd want to do this, though. :)

Answer 2

I have achieved this with the information provided by Berdir. My code looks like:

<?php
function mymodule_schema_otherdb() {
  $schema['mytable'] = array(
    'description' => 'My table description',
    'fields' => array(
      'myfield' => array(
        'description' => 'My field description',
        'type' => 'serial',
        'size' => 'medium',
        'not null' => TRUE,
        'unsigned' => TRUE,
      ),
    ),
    'primary key' => array('myfield'),
  );
  return $schema;
}

function mymodule_install() {
  db_set_active('otherdb');
  $schema = mymodule_schema_otherdb();
  foreach ($schema as $name => $table) {
    db_create_table($name, $table);
  }
  db_set_active();
}

function mymodule_uninstall() {
  db_set_active('otherdb');
  $schema = mymodule_schema_otherdb();
  foreach ($schema as $name => $table) {
    db_drop_table($name);
  }
  db_set_active();
}

Answer 3

A Drupal 8 version of the *.install file code provided by @Елин Й.:

Note: Database must exist and be specified in settings.php.

<?php
function mymodule_schema_otherdb() {
  $schema['mytable'] = array(
    'description' => 'My table description',
    'fields' => array(
      'myfield' => array(
        'description' => 'My field description',
        'type' => 'serial',
        'size' => 'medium',
        'not null' => TRUE,
        'unsigned' => TRUE,
      ),
    ),
    'primary key' => array('myfield'),
  );
  return $schema;
}

/**
 * Implements hook_install().
 */
function mymodule_install() {
  \Drupal\Core\Database\Database::setActiveConnection('otherdb');
  $connection = \Drupal\Core\Database\Database::getConnection();

  $schema = mymodule_schema_shared();
  foreach ($schema as $name => $table) {
    $connection->schema()->createTable($name, $table);
  }

  \Drupal\Core\Database\Database::setActiveConnection();
}

/**
 * Implements hook_uninstall().
 */
function mymodule_uninstall() {
  \Drupal\Core\Database\Database::setActiveConnection('otherdb');
  $connection = \Drupal\Core\Database\Database::getConnection();

  $schema = mymodule_schema_shared();
  foreach ($schema as $name => $table) {
    $connection->schema()->dropTable($name);
  }

  \Drupal\Core\Database\Database::setActiveConnection();
}

Here's a Gist.

Answer 4

In Settings.php
$db_url=array('default'=>$db_url, 'sec_db'=>$sec_db_url);
In hook_schema db_set_active('sec_db') will now connect to your another DB , not sure if this is recommended or not try it at your own risk. And you can make use of db_prefix to query from this secondary DB. You can make use db prefix in settings.php