Examples for database update scripts using hook_update_N (how to do one time configuration programmatically)

Contents of this page

• Concept
• Create a custom database update module
• How to "revert" a custom module's update 'N value'
• How to purge the .install field when it becomes too long
• What if you run out of numbers?
• Code snippets to use in the updatedb.install file (how to do configuration programmatically)
• Additional resources

Concept

To deploy settings and configuration on a Drupal 7 live site, the following is most common:

1. Settings and configuration not intended to be changed through the UI on the live site are rolled into code using Features together with Strongarm and others. Usually, in a deploy script with drush features-revert-all (drush fra -y) those are activated on the live site with each deploy, therefore, overwriting any changes made on the live site. A typical use case is the addition of a field to a content type.
2. Settings and configuration we would like to be changed and/or controlled on the live site are rolled into the code and put in an update hook in the .install file of a custom module. This way they are only executed once (on first deploy using drush updb -y or running update.php) and will not overwrite site changes on next deploys. Typical use cases are the enabling/disabling/uninstalling of a module or the introduction of a new View.
3. Anything else should be performed manually and included in site deploy related Release Notes. Those usually include references to all solved issues (e.g. JIRA stories) and any manual steps that might be involved.

This post focusses on #2. The "mechanism" behind it is the function hook_update_N. The single update code is wrapped in this sequential numbered function (hence the N) that is tracked in the database to avoid execution if already performed. As said, this is the way to "push" configuration to your production site without overwriting modifications on the live site on a next deploy (as opposed to Features).

Create a custom Update Database module 

As an example, we'll create a custom module named Update Database (machine name: updatedb). In reality, you would like to prefix the machine name with your project name to avoid possible future conflicts (e.g. acme_updatedb).

updatedb.info file

name = Update Database
description = Purpose is to run the hook_update on every release to implement one-time execution of code
core = 7.x
package = Custom

updatedb.module file

A module file should exist but can be left empty.

<?php

/**
 * @file
 * Update Database module.
 */

/**
 * A module file must exist, therefore empty.
 */

updatedb.install file

This is the file that actually contains the update hooks we need to be run once for the next deployment. Note that the function comments are actually used and should, therefore, describe what action is executed (e.g. 'Enables module X.').

<?php

/**
 * @file
 * Install file for the Update Database module.
 */
 
/**
 * Update function for 1st deployment. NOTE: This code comment is used in the list of updates and should be descriptive.
 */
function updatedb_update_7000() {
  // Some code.
}

How to "revert" a custom module's update 'N value'

D7 only: If you need to execute an update hook another time when testing it locally you can "revert" a custom module's update 'N value' with:

UPDATE system SET schema_version = [last_successful_update_ID] WHERE name = '[name_of_module]';

Replace the square brackets plus its content. See https://drupal.stackexchange.com/a/69841/19480.

Using drush through a Drupal API function, both valid for D7 and D8:

drush ev "drupal_set_installed_schema_version('[name_of_module]', [last_successful_update_ID])"

Or you could install the drush uroll module and do

drush uroll --module=[name_of_module] --version=[last_successful_udpate_ID]

How to purge the .install field when it becomes too long

As you now push many one-time configuration changes through the updatedb.install file it might get too big for your liking, especially if you use it also for Views changes (consider to use a separate viewsupdatedb module for that). If you remove some update functions from your updatedb.install file, you should notify Drupal of those missing functions. This way, Drupal can ensure that no update is accidentally skipped.

The below example snippet would be inserted before function updatedb_update_7255. 

function hook_update_last_removed() {
  return 7254;
}

You can leave one or several of the latest update hooks without problems. Whatever you might expect still needs to run on existing older code bases e.g. on developer machines that did not pull the latest code for a while.

What if you run out of numbers?

The second digit of the N number is officially for the major version of the release of your custom module named Update Database but that leaves you with only 2 digits for sequential counting. Starting with 00 that is good for 100 update hooks. If you don't use the second digit for a major release version (makes sense using it only for deployment) then you have 1000 update hooks available before running out of numbers (until 7999). In case you reached a thousand update hooks you should reset it to be able to continue:

• make sure all your environments (including local development installs) have run the update.php with the latest code
• remove all functions updatedb_update_N() and updatedb_update_last_removed() (see previous paragraph) from the updatedb.install file
• make sure all environments pull the latest again
• reset the schema_version to 7000 on all environments as described above.

You should now use function updatedb_update_7001(&$sandbox) as the next update hook.

Code snippets to use in the updatedb.install file (how to do configuration programmatically)

Below are the code snippets that can be used in the file updatedb.install of a custom deployment module named updatedb to run one-time automatic database updates on the next deploy. When copy-pasting to your own module make sure to change the module machine name and the number to use (sequential following the one of last update hook ).

Some snippets target a contributed module like Features of CKEditor.

Enable modules

/**
 * Enable the modules some_module and some_other_module.
 */
function updatedb_update_7000(&$sandbox) {
  $module_list = array(
    'some_module',
    'some_other_module',
  );
  module_enable($module_list, TRUE);
}

D8: 

\Drupal::service('module_installer')->install($module_list);

Disable modules

**
 * Disable the modules some_module and some_other_module.
 */
function updatedb_update_7001(&$sandbox) {
  $module_list = array(
    'some_module',
    'some_other_module',
  );
  module_disable($module_list, TRUE);
}

Enable a theme

**
 * Enable some_theme.
 */
function updatedb_update_7001(&$sandbox) {
  $theme_list = array(
    'some_theme',
  );
  theme_enable($theme_list);
}

Disable and uninstall modules

/**
 * Disable and uninstall the modules some_module and some_other_module.
 */
function updatedb_update_7002(&$sandbox) {
  $module_list = array(
    'some_module',
    'some_other_module',
  );
  module_disable($module_list, TRUE);
  drupal_uninstall_modules($module_list, TRUE);
}

For Drupal 8  the usual workflow is that at you uninstall the module manually locally, using drush or the UI, and then export the config and deploy that. Source: Drupal 8 - YML For Enabling a Contrib Module - Drupal Answers. It will remove the module from the core.extenions.yml file.

However, there are some cases where using an update hook would still be needed. For example, a custom module that adds an entity type and has content for those entities. It can't be uninstalled because the content would get in the way of the uninstall safety checks. The update hook can then be used to first delete all the content and then uninstall the module.

/**
 * Uninstall MY_MODULE (machine names).
 */
function updatedb_update_8001() {
  // Delete all nodes of a certain type.
  $content_type = \Drupal::entityManager()->getStorage('node_type')->load('MACHINE_NAME_OF_TYPE');
  $content_type->delete();
  // Uninstall the module that created that content type.
  \Drupal::service('module_installer')->uninstall(['MY_MODULE']);
}

Set a module's weight

Set a low weight (including negative numbers) to get your module to execute before others. Set a high weight to execute after other modules. If no weight is defined modules get a default weight of 0.

/**
 * Set weight of the My Funky Module module to 13.
 */
function my_funky_module_update_7100() {
    db_update('system')
    ->fields(array('weight' => 13))
    ->condition('name', 'my_funky_module', '=')
    ->execute();
}

Source: How to update a module's weight | Drupal.org

Revert a Feature

Usually during a deploy update_hooks run before the drush features-revert-all. That means that update hooks that contain code that relies on certain features being present won't work. A typical use case is the creation of a vocabulary through Features where it should exist before creating terms in it.

/**
 * Revert feature myfeaturemodule.
 */
function updatedb_update_7003(&$sandbox) {
  features_revert_module('myfeaturemodule');
}

Alternatively to revert only for example the Field component of that Feature:

/**
 * Revert fields in the feature myfeaturemodule.
 */
function updatedb_update_7003(&$sandbox) {
  features_revert(array('myfeaturemodule' => array('field')));
}

Create taxonomy terms in a vocabulary

/**
 * Create taxonomy terms for myvocabulary.
 */
function updatedb_update_7004(&$sandbox) {
 $vocab = taxonomy_vocabulary_machine_name_load('myvocabulary');
  $terms = array(
    array('name' => 'myterm1', 'weight' => '0'),
    array('name' => 'myterm2', 'weight' => '1'),
    array('name' => 'myterm3', 'weight' => '2'),
  );
  foreach ($terms as $key => $term) {
    $term = (object) array(
          'vid' => $vocab->vid,
          'name' => $term['name'],
          'weight' => $term['weight'],
    );
    taxonomy_term_save($term);
  }
 }

Delete field instance from content types

**
 * Delete field field_mycustomfield field from content types.
 */
function updatedb_update_7005(&$sandbox) {
  $types = array(
    'content_type1',
    'content_type2',
  );
  foreach ($types as $type) {
    $instance = field_info_instance('node', 'field_mycustomfield', $type);
    field_delete_instance($instance);
  }
}

Change a field instance attribute for a content type

**
 * Change the body field submission guideline for some_content_type.
 */
function updatedb_update_7005(&$sandbox) {
  $instance = field_read_instance('node', 'body', 'some_content_type');
  $instance['description'] == 'Some submission guideline.';
  field_update_instance($instance);
}

Modify a field value

As an example, we show a common use case of replacing certain absolute URLs in the body field with a relative one.

**
 * Replace absolute URLs to root with a relative one.
 */
function updatedb_update_7005(&$sandbox) {
  db_update('field_data_body')
      ->condition('body_value', '%href="http://www.mywebsite.com/%', 'LIKE')
      ->expression('body_value', 'REPLACE(body_value, :oldtext, :newtext)', array(
        ':oldtext' => 'href="http://www.mywebsite.com/',
        ':newtext' => 'href="/',
      ))
      ->execute();
}

Add new fields

See https://www.drupal.org/node/150215.

Delete a field

/**
 * Delete field field_myfield.
 */
function updatedb_update_7006(&$sandbox) {
  field_delete_field('myfield');
  // Note: only run field_purge_batch if you want to remove immediately. 
  // Otherwise it will be removed from next cron run.
  field_purge_batch(1);
}

Delete nodes and content types

/**
 * Delete nodes and content types mycontenttype1, mycontenttype2.
 */
function updatedb_update_7007(&$sandbox) {
  $types = array(
    'mycontenttype1',
    'mycontenttype2',
  );
  foreach ($types as $type) {
    // First, delete nodes
    $results = db_select('node', 'n')
        ->fields('n', array('nid'))
        ->condition('type', $type)
        ->execute();
    $nids = $results->fetchCol();
    if (!empty($nids)) {
      node_delete_multiple($nids);
      drupal_set_message(t('%count nodes have been deleted.', array('%count' => count($nids))));
    }
    // Then, delete content type
    if (node_type_load($type)) {
      node_type_delete($type);
      variable_del('field_bundle_settings_node__' . $type);
      variable_del('language_content_type_' . $type);
      variable_del('menu_options_' . $type);
      variable_del('menu_parent_' . $type);
      variable_del('node_options_' . $type);
      variable_del('node_preview_' . $type);
      variable_del('node_submitted_' . $type);
      drupal_set_message(t('%type content type has been deleted.', array('%type' => $type)));
    }
  }
  node_types_rebuild();
  menu_rebuild();
}

Source: https://api.drupal.org/comment/61128#comment-61128

The menu item at node/add/[content-type] probably still needs to be 'Reset' at admin/structure/menu/manage/navigation.
@TODO: See how to do that programmatically (function menu_reset_item($link) in modules/menu/menu.module?).

Note that if the content type is included in a Feature you have to remove it there first, recreate the Feature and include in the code below as the first line:

features_revert_module('myfeaturemodule');

Not doing that results in only the nodes being deleted but not the content type.

Create or update a View

You can export any View from admin/structure/views/view/[view_machine_name]/export. Just copy-paste the code into an update hook and end with $view->save(); (not included in the export). If the View doesn't exist it will create it. It overwrites an existing one with the same name.

/**
 * Create or update the existing view.
 */
function updatedb_update_7008(&$sandbox) {
  $view = new view();
  $view->name = 'my_simple_view_of_articles';
  $view->description = '';
  $view->tag = 'default';
  $view->base_table = 'node';
  $view->human_name = 'My Simple View of Articles';
  $view->core = 7;
  $view->api_version = '3.0';
  $view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

  /* Display: Master */
  $handler = $view->new_display('default', 'Master', 'default');
  $handler->display->display_options['title'] = 'My Simple View of Articles';
  $handler->display->display_options['use_more_always'] = FALSE;
  $handler->display->display_options['access']['type'] = 'perm';
  $handler->display->display_options['cache']['type'] = 'none';
  $handler->display->display_options['query']['type'] = 'views_query';
  $handler->display->display_options['exposed_form']['type'] = 'basic';
  $handler->display->display_options['pager']['type'] = 'full';
  $handler->display->display_options['pager']['options']['items_per_page'] = '10';
  $handler->display->display_options['style_plugin'] = 'default';
  $handler->display->display_options['row_plugin'] = 'node';
  /* Field: Content: Title */
  $handler->display->display_options['fields']['title']['id'] = 'title';
  $handler->display->display_options['fields']['title']['table'] = 'node';
  $handler->display->display_options['fields']['title']['field'] = 'title';
  $handler->display->display_options['fields']['title']['label'] = '';
  $handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
  $handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
  /* Sort criterion: Content: Post date */
  $handler->display->display_options['sorts']['created']['id'] = 'created';
  $handler->display->display_options['sorts']['created']['table'] = 'node';
  $handler->display->display_options['sorts']['created']['field'] = 'created';
  $handler->display->display_options['sorts']['created']['order'] = 'DESC';
  /* Filter criterion: Content: Published */
  $handler->display->display_options['filters']['status']['id'] = 'status';
  $handler->display->display_options['filters']['status']['table'] = 'node';
  $handler->display->display_options['filters']['status']['field'] = 'status';
  $handler->display->display_options['filters']['status']['value'] = 1;
  $handler->display->display_options['filters']['status']['group'] = 1;
  $handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
  /* Filter criterion: Content: Type */
  $handler->display->display_options['filters']['type']['id'] = 'type';
  $handler->display->display_options['filters']['type']['table'] = 'node';
  $handler->display->display_options['filters']['type']['field'] = 'type';
  $handler->display->display_options['filters']['type']['value'] = array(
    'article' => 'article',
  );

  /* Display: Page */
  $handler = $view->new_display('page', 'Page', 'page');
  $handler->display->display_options['path'] = 'my-simple-view-of-articles';
  $translatables['my_simple_view_of_articles'] = array(
    t('Master'),
    t('My Simple View of Articles'),
    t('more'),
    t('Apply'),
    t('Reset'),
    t('Sort by'),
    t('Asc'),
    t('Desc'),
    t('Items per page'),
    t('- All -'),
    t('Offset'),
    t('« first'),
    t('‹ previous'),
    t('next ›'),
    t('last »'),
    t('Page'),
  );
  // Save the view.
  // Note: This is not present in the exported view code.
  $view->save();
}

Delete {system} records for lost modules

Fix for the warning message "The following module is missing from the file system...". See https://www.drupal.org/node/2487215.

/**
 * Delete {system} records for lost modules.
 * Refer: https://www.drupal.org/node/2487215
 */
function updatedb_update_7009(&$sandbox) {
  $modules = array(
    'mylostmodule',
  );
  db_delete('system')
      ->condition('name', $modules, 'IN')
      ->condition('type', 'module')
      ->execute();
}

Delete blocks

/**
 * Remove unused blocks.
 */
function updatedb_update_7010(&$sandbox) {
  $blocks_array = array(
    array('module' => 'block', 'delta' => '45'),
    array('module' => 'block', 'delta' => '66'),
    array('module' => 'block', 'delta' => '30'),
  );

  $log_data = '';
  foreach ($blocks_array as $key => $block_arr) {
    $block = block_load($block_arr['module'], $block_arr['delta']);
    if (is_object($block) && !empty($block->bid)) {
      db_delete('block_custom')
          ->condition('bid', $block->delta)
          ->execute();
      db_delete('block')
          ->condition('module', 'block')
          ->condition('delta', $block->delta)
          ->execute();
      db_delete('block_role')
          ->condition('module', 'block')
          ->condition('delta', $block->delta)
          ->execute();
      $log_data .= $block_arr['delta'] . PHP_EOL;
    }
    else {
      $log_data .= $block_arr['delta'] . " not loaded and not deleted from DB | " . PHP_EOL;
    }
  }
  cache_clear_all('*', 'cache_block', TRUE);
  watchdog("Deleted Blocks: ", $log_data);
}

Hide or show a block in a theme

As an example, we disable and move to the 'Disabled' section the user login block.

/**
 * Hide login block.
 */
function updatedb_update_7011(&$sandbox) {
  db_update('block')
      ->fields(array(
        'region' => -1, // Change to e.g. 'sidebar_first' to place the block in this region.
        'status' => 0, // Change to 1 to enable the block instead.
      ))
      ->condition('module', 'user')
      ->condition('delta', 'login')
      ->condition('theme', 'bartik')
      ->execute();
}

Disable text formats

/**
 * Disable text format.
 */
function updatedb_update_7011(&$sandbox) {
  $formats = filter_formats();
  filter_format_disable($formats['some_text_format']);
}

Disable CKEditor profiles

/**
 * Disable CKEditor profiles.
 */
function updatedb_update_7012(&$sandbox) {
  module_load_include('inc', 'ckeditor', 'includes/ckeditor.admin');
  ckeditor_profile_delete('some_profile');
}

Create a menu item

As an example, we add a common use case of adding the link to a View to the Content admin menu to show certain content separately. plid = parent link id (mlid of the parent)

/**
 * Create a menu item.
 */
function updatedb_update_7013(&$sandbox) {
  $item = array(
    'link_title' => "Some View title",
    'link_path' => 'admin/content/some_view',
    'menu_name' => 'management',
    'weight' => 1,
    'plid' => 9,
  );
  menu_link_save($item);
}

Change existing language fields

/**
 * Perform the queries to switch DB all language fields from NL to UND.
 */
function updatedb_update_7001(&$sandbox) {
  $table_list = array(
    'node',
  );
  foreach ($table_list as $table) {
    db_update($table)
      ->fields(array(
        'language' => 'und',
      ))
      ->condition('language', 'nl', '=')
      ->execute();
  }
}

Drop a database table

/**
 * Drop {some_table} from the database.
 */
function updatedb_update_7013(&$sandbox) {
  if (db_table_exists('some_table')) {
    db_drop_table('some table');
  }
}

Change a specific column value in the database

/**
 * Switch in some_column from old_value to new_value in some_table and some_other_table.
 */
function updatedb_update_7001(&$sandbox) {
  $table_list = array(
    'some_table',
    'some_other_table',
  );

  foreach ($table_list as $table) {
    db_update($table)
      ->fields(array(
        'some_column' => 'new_value',
      ))
      ->condition('some_column', 'old_value', '=')
      ->execute();
  }
}

Remove duplicate rows from a database table based on two identical column values

/**
 * Remove duplicate rows where column3 = 'some_value' comparing two columns with identical value.
 *
 * Example column1: entity_id, column2: revision_id, column3: language (taken for metatag table).
 */
function updatedb_update_7001(&$sandbox) {
  $query = db_query("
    SELECT entity_id
    FROM metatag
       INNER JOIN (SELECT revision_id
                   FROM   metatag
                   GROUP  BY revision_id
                   HAVING COUNT(revision_id) > 1) dup
               ON metatag.revision_id = dup.revision_id
    WHERE language = 'nl'
  ");
  $results = $query->fetchAllAssoc('entity_id');
  foreach ($results as $key => $value) {
    $num_deleted = db_delete('metatag')
      ->condition('entity_id', $key)
      ->condition('language', 'nl')
      ->execute();
  }
}

Update url patterns for node types using pathauto module.

function updatedb_update_7001(&$sandbox) {
  variable_set('pathauto_node_mynodetype1_pattern', '[node:menu-link:menu:name]/[node:title]');
  variable_set('pathauto_node_mynodetype2_pattern', '[node:menu-link:menu:name]/[node:menu-link:parent:root]/[node:title]');
}

Feel free to add other snippets

From the API documentation itself

Add a column to a table

/**
 * Add a column the database table {some_table}.
 */
function updatedb_update_7014(&$sandbox) {
  db_add_field('some_table', 'newcol', array('type' => 'int', 'not null' => TRUE, 'description' => 'My new integer column.'));
}

Alternatively:

/**
 * Add newcol field to {mytable1} table.
 */
function updatedb_update_7014() {
  $spec = array(
    'type' => 'varchar',
    'description' => "New Col",
    'length' => 20,
    'not null' => FALSE,
  ); 
  db_add_field('mytable1', 'newcol', $spec);
}

For operations that may take a long time hook into the Batch API

/**
 * Update 3 users at a time to have an exclamation point after their names..
 */
function updatedb_update_7015(&$sandbox) {
  if (!isset($sandbox['progress'])) {
    $sandbox['progress'] = 0;
    $sandbox['current_uid'] = 0;
    // We'll -1 to disregard the uid 0...
    $sandbox['max'] = db_query('SELECT COUNT(DISTINCT uid) FROM {users}')->fetchField() - 1;
  }

  $users = db_select('users', 'u')
    ->fields('u', array('uid', 'name'))
    ->condition('uid', $sandbox['current_uid'], '>')
    ->range(0, 3)
    ->orderBy('uid', 'ASC')
    ->execute();

  foreach ($users as $user) {
    $user->name .= '!';
    db_update('users')
      ->fields(array('name' => $user->name))
      ->condition('uid', $user->uid)
      ->execute();

    $sandbox['progress']++;
    $sandbox['current_uid'] = $user->uid;
  }

  $sandbox['#finished'] = empty($sandbox['max']) ? 1 : ($sandbox['progress'] / $sandbox['max']);
}

Display a message to the user when the update has completed

Just add inside at the end of the update hook:

  return t('The update did what it was supposed to do.');

In case of an error, simply throw an exception with an error message

Just add inside at the end of the update hook:

  throw new DrupalUpdateException('Something went wrong; here is what you should do.');

Additional resources

• https://www.drupal.org/docs/7/api/schema-api/updating-tables-hook_update...

• http://befused.com/drupal/site-deployment-module

• https://www.metaltoad.com/blog/how-write-drupal-update-scripts

• http://blog.dcycle.com/blog/44/what-site-deployment-module