Updating Database Schema and/or Data in Drupal 9

Last updated on
6 March 2024

This documentation needs work. See "Help improve this page" in the sidebar.

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:

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 eval "echo \Drupal::service('update.update_hook_registry')->getAvailableUpdates('my_module');"

Manually setting the current schema version of a module

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

Help improve this page

Page status: Needs work

You can: