Updating Database Schema and/or Data in Drupal

If your module is making a data model change related to database schema that your module defines with hook_schema(), then you need to properly update your data model. The two steps are:

1. Update your hook_schema() code so that it reflects your new data model if the database table and field definitions have changed. This will make sure that people that install your module after you made the change will install the correct database tables. See the Schema API documentation for details on that.
2. Write a hook_update_N() function. This will update the database for existing users of your module who already had it installed before you made the change so that they can continue to function. This is described below.

General notes

Some notes on hook_update_N() functions:

• the hook_update_N() skeleton section on the parent page tells how/where to create your hook_update_N() function.
• You'll also need this at the top of your mymodule.install file:
  

  use Drupal\Core\Database\Database;
  

• Combine all your current data model updates into one hook_update_N() function, but don't combine them with other updates that were done previously.
• NEVER reference your current hook_schema() return value in your hook_update_N() function.
  • hook_update_N() functions should copy the schema at the same time the hook_update_N() was written.
  • If you use hook_schema() in a hook_update_N(), the following problem will exist:
    • User installs version 3.1.0 of example.module, does not upgrade to version 3.2.0, but does upgrade to version 3.3.0 when available.
    • In 3.2.0, the schema was updated (aka example_schema()), let's say a field X was added and then in 3.3.0. 
    • When the user upgrades to version 3.3.0 and runs update.php, the hook updates of both 3.2.0 and 3.3.0 run but example_schema() reflects the structure of 3.3.0 so the hook updates of 3.2.0 might fail, because field X in example_schema() does not exist.
• The sections below give examples of how to perform various database updates. For other schema changes (like deleting fields, etc.), follow the examples below, using other methods from the Schema class.

Adding a new column

Here's an example of what to put into your hook_update_N() function to add a new column to an existing database table:

  $spec = [
    'type' => 'varchar',
    'description' => "New Col",
    'length' => 20,
    'not null' => FALSE,
  ]; 
 $schema = Database::getConnection()->schema();
 $schema->addField('mytable1', 'newcol', $spec);

Adding a new table

Here's an example of what to put into your hook_update_N() function to add a new database table:

  $spec = [
    'description' => 'My description',
    'fields' => [
      'myfield1' => [
        'description' => 'Myfield1 description.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ],
      'myfield2' => [
        'description' => 'Myfield2 description',
        'type' => 'text',
        'not null' => TRUE,
      ],
    ],
    'primary key' => ['myfield1'],
  ]; 
 $schema = Database::getConnection()->schema();
 $schema->createTable('mytable2', $spec);

Adding primary keys or indexes

Here are examples of what to put into your hook_update_N() function to add a new index or primary key to an existing database table:

 $spec = [
  // Example partial specification for a table:
  'fields' => [
    'myfield1' => [
      'description' => 'An example field',
      'type' => 'varchar',
      'length' => 32,
      'not null' => TRUE,
      'default' => '',
    ],
  ],
  'indexes' => [
    'myfield1_normal_index' => ['myfield1'],
  ],
 ];
 $fields = ['myfield1'];
 $schema = Database::getConnection()->schema();
 // A normal index.
 $schema->addIndex('mytable', 'myfield1_normal_index', $fields, $spec);

 // A primary key.
 $schema->addPrimaryKey('mytable', $fields);
 // A unique key.
 $schema->addUniqueKey('mytable', 'myfield1_unique_key', $fields);

Updating data in a Table

Sometimes your data model changes mean that you need to update the data within a table, rather than (or in addition to) changing the database schema itself. Here's an example of what you'd put in your hook_update_N() function in this case:

$schema = Database::getConnection()->query(  [your query goes here] );

You could also use other Connection class methods such as update().

Altering the length of a field with data

You cannot change the specification of an existing field when it already has content. The reason is that the content itself might also need to be altered. Consider, if you wanted to decrease the size of a field, but had data that was longer than the new size; or possibly, you wanted to change the field to not allow nulls, while it still had NULL values. Drupal 8 makes the decision to automatically prevent this by checking for schema changes and throwing an exception.

There are some simple changes, though, that this prevents, such as simply increasing the size of a varchar field. Here's an example of how you might do it.

/**
 * Change length of a varchar entity field with data, safe with entity-updates.
 *
 * This updates the storage schema, the database schema, and the last
 * installed schema.
 *
 * The entity schema must also be changed in code in the entities
 * baseFieldDefinitions() or in an alter.
 *
 * @param string $entity_type_id
 *   The entity type.
 * @param string $field_name
 *   The field name to change.
 * @param int $field_length
 *   The new length of the field, must be larger than the previous value.
 */
function db_change_varchar_field($entity_type_id, $field_name, $field_length) {
  /** @var \Drupal\Core\Entity\EntityLastInstalledSchemaRepositoryInterface $schema_repository */
  $schema_repository = \Drupal::service('entity.last_installed_schema.repository');
  /** @var \Drupal\Core\Entity\EntityFieldManager $entity_field_manager */
  $entity_field_manager = \Drupal::service('entity_field.manager');
  $base_field_definitions = $entity_field_manager->getBaseFieldDefinitions($entity_type_id);
  $schema_repository->setLastInstalledFieldStorageDefinition($base_field_definitions[$field_name]);
  $field_storage_definitions = $schema_repository->getLastInstalledFieldStorageDefinitions($entity_type_id);

  // Update the serialized schema property.
  $rc = new \ReflectionClass($field_storage_definitions[$field_name]);
  $schema_property = $rc->getProperty('schema');
  $schema_property->setAccessible(TRUE);
  $schema = $field_storage_definitions[$field_name]->getSchema();
  $schema['columns']['value']['length'] = $field_length;
  $schema_property->setValue($field_storage_definitions[$field_name], $schema);

  // Update the field definition in the last installed schema repository.
  $schema_repository->setLastInstalledFieldStorageDefinitions($entity_type_id, $field_storage_definitions);

  // Update the storage schema.
  $key_value = \Drupal::keyValue('entity.storage_schema.sql');
  $key_name = $entity_type_id . '.field_schema_data.' . $field_name;
  $storage_schema = $key_value->get($key_name);
  // Update all tables where the field is present.
  foreach ($storage_schema as &$table_schema) {
    $table_schema['fields'][$field_name]['length'] = $field_length;
  }
  $key_value->set($key_name, $storage_schema);

  // Update the database tables where the field is part of.
  $db = Drupal::database();
  foreach ($storage_schema as $table_name => $table_schema) {
    $db->schema()->changeField($table_name, $field_name, $field_name, $table_schema['fields'][$field_name]);
  }
}

Field schema updates (ongoing core issue)

See https://www.drupal.org/project/drupal/issues/937442 for discussion and helpers to update field type schemas.

Useful drush commands while developing your hook_update_N

Checking the current schema version of a module

drush php:eval "echo \Drupal::service('update.update_hook_registry')->getInstalledVersion('admin_toolbar');"

Checking the current schema version of Drupal core

drush php:eval "echo \Drupal::service('update.update_hook_registry')->getInstalledVersion('system');"

Manually setting the current schema version of a module

drush eval "\Drupal::service('update.update_hook_registry')->setInstalledVersion('my_module', 9301);"

Setting number for custom update hook