Drupal 8/9/10 Field type schema column change helpers.

field_type_schema_change_helper.md

Drupal issue

See discussion at https://www.drupal.org/project/drupal/issues/937442

TODO:

• Check if this can be done better with https://www.agoradesign.at/blog/why-running-drupalentitydefinitionupdatemanager-applyupdates-update-hooks-bad
• https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Entity%21EntityDefinitionUpdateManager.php/function/EntityDefinitionUpdateManager%3A%3AupdateFieldStorageDefinition/8.2.x

Current dirty workaround:

This workaround is based on code from "Address" contrib module: https://www.drupal.org/project/address Thanks to @bojanz who initially wrote it here: https://git.drupalcode.org/project/address/-/blob/8.x-1.x/address.install

/**
 * Helper function for HOOK_Update to update the field schema columns.
 *
 * Based on address.install (thanks to the maintainer!)
 *
 * @param $field_type The field type id.
 * @param array $columns_to_add array of the column names from schema() function.
 */
function _field_type_schema_column_add_helper($field_type, array $columns_to_add = array()) {
  $processed_fields = [];
  $field_type_manager = \Drupal::service('plugin.manager.field.field_type');
  $field_definition = $field_type_manager->getDefinition($field_type);
  $field_item_class = $field_definition['class'];

  $schema = \Drupal::database()->schema();
  $entity_type_manager = \Drupal::entityTypeManager();
  $entity_field_manager = \Drupal::service('entity_field.manager');
  $entity_field_map = $entity_field_manager->getFieldMapByFieldType($field_type);
  // The key-value collection for tracking installed storage schema.
  $entity_storage_schema_sql = \Drupal::keyValue('entity.storage_schema.sql');
  $entity_definitions_installed = \Drupal::keyValue('entity.definitions.installed');

  foreach ($entity_field_map as $entity_type_id => $field_map) {
    $entity_storage = $entity_type_manager->getStorage($entity_type_id);

    // Only SQL storage based entities are supported / throw known exception.
    //    if (!($entity_storage instanceof SqlContentEntityStorage)) {
    //      continue;
    //    }

    $entity_type = $entity_type_manager->getDefinition($entity_type_id);
    $field_storage_definitions = $entity_field_manager->getFieldStorageDefinitions($entity_type_id);
    /** @var Drupal\Core\Entity\Sql\DefaultTableMapping $table_mapping */
    $table_mapping = $entity_storage->getTableMapping($field_storage_definitions);
    // Only need field storage definitions of our field type:
    /** @var \Drupal\Core\Field\FieldStorageDefinitionInterface $field_storage_definition */
    foreach (array_intersect_key($field_storage_definitions, $field_map) as $field_storage_definition) {
      $field_name = $field_storage_definition->getName();
      try {
        $table = $table_mapping->getFieldTableName($field_name);
      } catch (SqlContentEntityStorageException $e) {
        // Custom storage? Broken site? No matter what, if there is no table
        // or column, there's little we can do.
        continue;
      }
      // See if the field has a revision table.
      $revision_table = NULL;
      if ($entity_type->isRevisionable() && $field_storage_definition->isRevisionable()) {
        if ($table_mapping->requiresDedicatedTableStorage($field_storage_definition)) {
          $revision_table = $table_mapping->getDedicatedRevisionTableName($field_storage_definition);
        }
        elseif ($table_mapping->allowsSharedTableStorage($field_storage_definition)) {
          $revision_table = $entity_type->getRevisionDataTable() ?: $entity_type->getRevisionTable();
        }
      }
      // Load the installed field schema so that it can be updated.
      $schema_key = "$entity_type_id.field_schema_data.$field_name";
      $field_schema_data = $entity_storage_schema_sql->get($schema_key);

      $processed_fields[] = [$entity_type_id, $field_name];
      // Loop over each new column and add it as a schema column change.
      foreach ($columns_to_add as $column_id) {
        $column = $table_mapping->getFieldColumnName($field_storage_definition, $column_id);
        // Add `initial_from_field` to the new spec, as this will copy over
        // the entire data.
        $field_schema = $field_item_class::schema($field_storage_definition);
        $spec = $field_schema['columns'][$column_id];

        // Add the new column.
        $schema->addField($table, $column, $spec);
        if ($revision_table) {
          $schema->addField($revision_table, $column, $spec);
        }

        // Add the new column to the installed field schema.
        if (!empty($field_schema_data)) {
          $field_schema_data[$table]['fields'][$column] = $field_schema['columns'][$column_id];
          $field_schema_data[$table]['fields'][$column]['not null'] = FALSE;
          if ($revision_table) {
            $field_schema_data[$revision_table]['fields'][$column] = $field_schema['columns'][$column_id];
            $field_schema_data[$revision_table]['fields'][$column]['not null'] = FALSE;
          }
        }
      }

      // Save changes to the installed field schema.
      if (!empty($field_schema_data)) {
        $entity_storage_schema_sql->set($schema_key, $field_schema_data);
      }
      if ($table_mapping->allowsSharedTableStorage($field_storage_definition)) {
        $key = "$entity_type_id.field_storage_definitions";
        if ($definitions = $entity_definitions_installed->get($key)) {
          $definitions[$field_name] = $field_storage_definition;
          $entity_definitions_installed->set($key, $definitions);
        }
      }
    }
  }
}

/**
 * Helper function to update the field schema to current.
 *
 * Updates the field schema of all fields from the given $field_type_id
 * to the current schema, preserving existing data by recreating the
 * field tables & field revision tables.
 * Hopefully this will one day be replaced by a similar core helper function.
 * See https://www.drupal.org/project/entity_access_by_role_field/issues/336357
 * core issue for future replacement field schema helper implementation.
 *
 * @param string $field_type_id
 *   The @FieldType id, e.g. "entity_access_by_role_field".
 */
function _field_type_schema_column_spec_change_helper(string $field_type_id): void {
  $entity_field_manager = \Drupal::service('entity_field.manager');
  $entity_field_map = $entity_field_manager->getFieldMapByFieldType($field_type_id);

  $entity_type_manager = \Drupal::entityTypeManager();
  foreach ($entity_field_map as $entity_type_id => $field_map) {
    // This is always an SQL Entity Storage Interface:
    /** @var \Drupal\Core\Entity\SqlEntityStorageInterface $entity_storage*/
    $entity_storage = $entity_type_manager->getStorage($entity_type_id);
    $entity_type = $entity_type_manager->getDefinition($entity_type_id);
    $field_storage_definitions = $entity_field_manager->getFieldStorageDefinitions($entity_type_id);
    /** @var Drupal\Core\Entity\Sql\DefaultTableMapping $table_mapping */
    $table_mapping = $entity_storage->getTableMapping($field_storage_definitions);
    // Only need field storage definitions of our field type:
    /** @var \Drupal\Core\Field\FieldStorageDefinitionInterface $field_storage_definition */
    foreach (array_intersect_key($field_storage_definitions, $field_map) as $field_storage_definition) {
      $field_name = $field_storage_definition->getName();
      $tables = [];
      try {
        $table = $table_mapping->getFieldTableName($field_name);
        $tables[] = $table;
      }
      catch (SqlContentEntityStorageException $e) {
        // Custom storage? Broken site? No matter what, if there is no table
        // there's little we can do.
        continue;
      }
      // See if the field has a revision table.
      $revision_table = NULL;
      if ($entity_type->isRevisionable() && $field_storage_definition->isRevisionable()) {
        if ($table_mapping->requiresDedicatedTableStorage($field_storage_definition)) {
          $revision_table = $table_mapping->getDedicatedRevisionTableName($field_storage_definition);
          $tables[] = $revision_table;
        }
        elseif ($table_mapping->allowsSharedTableStorage($field_storage_definition)) {
          $revision_table = $entity_type->getRevisionDataTable() ?: $entity_type->getRevisionTable();
          $tables[] = $revision_table;
        }
      }

      $database = \Drupal::database();
      $existing_data = [];
      foreach ($tables as $table) {
        // Get the old data.
        $existing_data[$table] = $database->select($table)
          ->fields($table)
          ->execute()
          ->fetchAll(PDO::FETCH_ASSOC);

        // Wipe it.
        $database->truncate($table)->execute();
      }

      $manager = \Drupal::entityDefinitionUpdateManager();
      $manager->updateFieldStorageDefinition($manager->getFieldStorageDefinition($field_name, $entity_type_id));

      // Restore the data.
      foreach ($tables as $table) {
        if (empty($existing_data[$table]) || !is_array($existing_data[$table])) {
          // Skip if there are no rows:
          continue;
        }
        $first_row = reset($existing_data[$table]);
        if (empty($first_row)) {
          // Skip if there is no data:
          continue;
        }
        $fields = array_keys($first_row);
        $insert_query = $database
          ->insert($table)
          ->fields($fields);
        foreach ($existing_data[$table] as $row) {
          $insert_query->values(array_values($row));
        }
        $insert_query->execute();
      }
    }
  }
}

/**
 * Helper function for HOOK_Update to remove columns from the field schema.
 *
 * @param $field_type The field type id e.g. "drowl_paragraphs_settings"
 * @param array $columns_to_remove array of the column names from schema() function, e.g. ["style_textalign"]
 */
function _field_type_schema_column_remove_helper($field_type, array $columns_to_remove = array()) {
  $processed_fields = [];
  $field_type_manager = \Drupal::service('plugin.manager.field.field_type');
  $field_definition = $field_type_manager->getDefinition($field_type);
  $field_item_class = $field_definition['class'];

  $schema = \Drupal::database()->schema();
  $entity_type_manager = \Drupal::entityTypeManager();
  $entity_field_manager = \Drupal::service('entity_field.manager');
  $entity_field_map = $entity_field_manager->getFieldMapByFieldType($field_type);
  // The key-value collection for tracking installed storage schema.
  $entity_storage_schema_sql = \Drupal::keyValue('entity.storage_schema.sql');
  $entity_definitions_installed = \Drupal::keyValue('entity.definitions.installed');

  foreach ($entity_field_map as $entity_type_id => $field_map) {
    $entity_storage = $entity_type_manager->getStorage($entity_type_id);

    // Only SQL storage based entities are supported / throw known exception.
    //    if (!($entity_storage instanceof SqlContentEntityStorage)) {
    //      continue;
    //    }

    $entity_type = $entity_type_manager->getDefinition($entity_type_id);
    $field_storage_definitions = $entity_field_manager->getFieldStorageDefinitions($entity_type_id);
    /** @var Drupal\Core\Entity\Sql\DefaultTableMapping $table_mapping */
    $table_mapping = $entity_storage->getTableMapping($field_storage_definitions);
    // Only need field storage definitions of our field type:
    /** @var \Drupal\Core\Field\FieldStorageDefinitionInterface $field_storage_definition */
    foreach (array_intersect_key($field_storage_definitions, $field_map) as $field_storage_definition) {
      $field_name = $field_storage_definition->getName();
      try {
        $table = $table_mapping->getFieldTableName($field_name);
      } catch (SqlContentEntityStorageException $e) {
        // Custom storage? Broken site? No matter what, if there is no table
        // or column, there's little we can do.
        continue;
      }
      // See if the field has a revision table.
      $revision_table = NULL;
      if ($entity_type->isRevisionable() && $field_storage_definition->isRevisionable()) {
        if ($table_mapping->requiresDedicatedTableStorage($field_storage_definition)) {
          $revision_table = $table_mapping->getDedicatedRevisionTableName($field_storage_definition);
        }
        elseif ($table_mapping->allowsSharedTableStorage($field_storage_definition)) {
          $revision_table = $entity_type->getRevisionDataTable() ?: $entity_type->getRevisionTable();
        }
      }
      // Load the installed field schema so that it can be updated.
      $schema_key = "$entity_type_id.field_schema_data.$field_name";
      $field_schema_data = $entity_storage_schema_sql->get($schema_key);

      $processed_fields[] = [$entity_type_id, $field_name];
      // Loop over each new column and add it as a schema column change.
      foreach ($columns_to_remove as $column_id) {
        $column = $table_mapping->getFieldColumnName($field_storage_definition, $column_id);
        // Add `initial_from_field` to the new spec, as this will copy over
        // the entire data.
        $field_schema = $field_item_class::schema($field_storage_definition);
        $spec = $field_schema['columns'][$column_id];

        // Add the new column.
        $schema->dropField($table, $column);
        if ($revision_table) {
          $schema->dropField($revision_table, $column);
        }

        // Remove the column from the installed field schema.
        if (!empty($field_schema_data)) {
          unset($field_schema_data[$table]['fields'][$column]);
          if ($revision_table) {
            unset($field_schema_data[$revision_table]['fields'][$column]);
          }
        }
      }

      // Save changes to the installed field schema.
      if (!empty($field_schema_data)) {
        $entity_storage_schema_sql->set($schema_key, $field_schema_data);
      }
      if ($table_mapping->allowsSharedTableStorage($field_storage_definition)) {
        $key = "$entity_type_id.field_storage_definitions";
        if ($definitions = $entity_definitions_installed->get($key)) {
          $definitions[$field_name] = $field_storage_definition;
          $entity_definitions_installed->set($key, $definitions);
        }
      }
    }
  }
}

I pasted my worked code, pease reference for others.
DON'T use field_definition_add_helper and change schema->dropField to drop a field, because it works.

/**
 * Add a new column for fieldType
 * @param string $field_type
 * @param sring $new_property
 */
function field_definition_add_helper($field_type, $new_property) {
  
  $manager   = \Drupal::entityDefinitionUpdateManager();
  $field_map = \Drupal::service('entity_field.manager')->getFieldMapByFieldType($field_type);
  
  foreach ($field_map as $entity_type_id => $fields) {
    
    foreach (array_keys($fields) as $field_name) {
      $field_storage_definition = $manager->getFieldStorageDefinition($field_name, $entity_type_id);
      $storage = \Drupal::entityTypeManager()->getStorage($entity_type_id);
      
      if ($storage instanceof \Drupal\Core\Entity\Sql\SqlContentEntityStorage) {
        $table_mapping = $storage->getTableMapping([
          $field_name => $field_storage_definition,
        ]);
        $table_names = $table_mapping->getDedicatedTableNames();
        $columns = $table_mapping->getColumnNames($field_name);
        
        foreach ($table_names as $table_name) {
          $field_schema = $field_storage_definition->getSchema();
          $schema = \Drupal::database()->schema();
          $field_exists = $schema->fieldExists($table_name, $columns[$new_property]);
          $table_exists = $schema->tableExists($table_name);
          
          if (!$field_exists && $table_exists) {
            $schema->addField($table_name, $columns[$new_property], $field_schema['columns'][$new_property]);
          }
        }
      }
      $manager->updateFieldStorageDefinition($field_storage_definition);
    }
  }
  
}

/**
 * Remove a column of field_type
 * @param string $field_type FieldTypeId in your definition
 * @param string $property column name
 */
function field_definition_delete_helper($field_type, $property) {
  $field_map = \Drupal::service('entity_field.manager')->getFieldMapByFieldType($field_type);
  foreach ($field_map as $entity_type_id => $fields) {
    foreach (array_keys($fields) as $field_name) {
      $storage = \Drupal::entityTypeManager()->getStorage($entity_type_id);
      _field_property_definition_delete($entity_type_id, $field_name, $property);
    }
  }
  
}

/**
 * Inner function, called by field_definition_delete_helper
 * @param string $entity_type_id
 * @param string $field_name
 * @param string $property
 */
function _field_property_definition_delete($entity_type_id, $field_name, $property) {
  $entity_type_manager  = \Drupal::entityTypeManager();
  $entity_update_manager = \Drupal::entityDefinitionUpdateManager();
  $entity_storage_schema_sql    = \Drupal::keyValue('entity.storage_schema.sql');
  $entity_definitions_installed = \Drupal::keyValue('entity.definitions.installed');
  
  $entity_type = $entity_type_manager->getDefinition($entity_type_id);
  //$field_storage_definitions = $entity_field_manager->getFieldStorageDefinitions($entity_type_id);
  $field_storage_definition = $entity_update_manager->getFieldStorageDefinition($field_name, $entity_type_id);
  $entity_storage = \Drupal::entityTypeManager()->getStorage($entity_type_id);
  /** @var Drupal\Core\Entity\Sql\DefaultTableMapping $table_mapping */
  $table_mapping = $entity_storage->getTableMapping([
    $field_name => $field_storage_definition,
  ]);
  
  // Load the installed field schema so that it can be updated.
  $schema_key = "$entity_type_id.field_schema_data.$field_name";
  $field_schema_data = $entity_storage_schema_sql->get($schema_key);
  
  //get table name and revision table name, getFieldTableName NOT WORK, so use getDedicatedDataTableName
  $table = $table_mapping->getDedicatedDataTableName($field_storage_definition);
  //try/catch
  $revision_table = NULL;
  if ($entity_type->isRevisionable() && $field_storage_definition->isRevisionable()) {
    if ($table_mapping->requiresDedicatedTableStorage($field_storage_definition)) {
      $revision_table = $table_mapping->getDedicatedRevisionTableName($field_storage_definition);
    }
    elseif ($table_mapping->allowsSharedTableStorage($field_storage_definition)) {
      $revision_table = $entity_type->getRevisionDataTable() ?: $entity_type->getRevisionTable();
    }
  }
  
  // Save changes to the installed field schema.
  if ($field_schema_data) {
    $_column = $table_mapping->getFieldColumnName($field_storage_definition, $property);
    //Update schema definition in database
    unset($field_schema_data[$table]['fields'][$_column]);
    if ($revision_table) {
      unset($field_schema_data[$revision_table]['fields'][$_column]);
    }
    $entity_storage_schema_sql->set($schema_key, $field_schema_data);
    //Try to drop field data
    \Drupal::database()->schema()->dropField($table, $_column);
  }
}

Another field type schema upgrade example can be found in date_recur https://www.drupal.org/project/date_recur/issues/3018101

@robbin2012: Could you please clarify your comment

I pasted my worked code, pease reference for others.
DON'T use field_definition_add_helper and change schema->dropField to drop a field, because it works.

I don't get what you mean...

@JPustkuchen I think @robbin2012 was referring to errors in 'field_definition_add_helper', such as hard coded references to the "recipient" property.

Thanks @rwohleb for pointing this out, indeed "recipient" shouldn't be hard coded here! I corrected that, must have been a long night when I wrote that code...

Best would be to proceed in https://www.drupal.org/project/drupal/issues/937442 and get things done there. Please help to test and improve the code over there (and here, until it's solved).

I had an issue with a table without data. So I added a check for this ( end(existing_data[table]) gives only false)

index d1de976..5a36a6d 100644
--- a/src/FieldTypeUpdateUtil.php
+++ b/src/FieldTypeUpdateUtil.php
@@ -178,9 +178,21 @@ class FieldTypeUpdateUtil {
   
         // Restore the data.
         foreach ($tables as $table) {
+          if (
+            !isset($existing_data[$table])
+            || !is_array($existing_data[$table])
+            || !count($existing_data[$table])
+          ) {
+            continue;
+          }
+          $last_row = end($existing_data[$table]);
+          if ($last_row == false) {
+            continue;
+          }
+          $fields = array_keys($last_row);
           $insert_query = $database
             ->insert($table)
-            ->fields(array_keys(end($existing_data[$table])));
+            ->fields($fields);
           foreach ($existing_data[$table] as $row) {
             $insert_query->values(array_values($row));
           }

I had an issue with a table without data. So I added a check for this ( end(existing_data[table]) gives only false)
Thanks, I fixed this in the Gist (with a bit different implementation)