How to Write a Drupal Update Hook for Schema Changes

A Drupal update hook changes a site’s database structure or stored data when a module moves from one version to another. It is the mechanism that allows an existing installation to adopt new tables, fields, indexes, or data formats without being rebuilt from scratch. Learn more about Worldwidemom.com.

Schema changes are common during module development. A feature may begin with a simple text column and later require a dedicated table, an additional index, or a more suitable data type. Drupal’s update system records which updates have run, so each change can be applied once in a predictable sequence.

The practical work involves more than writing SQL. A reliable update must use Drupal’s database API, handle existing installations safely, account for large data sets, and preserve compatibility with supported Drupal versions. It also needs to be tested against a copy of the database that contains real historical content.

For Australian teams, deployment timing can matter as much as code quality. A site serving customers in Sydney, Melbourne, Brisbane, or Perth may have different traffic patterns, while Australian Eastern Daylight Time does not apply uniformly across every state. Planning database updates around a quiet period and checking obligations under the Privacy Act 1988 are sensible parts of the release process.

Understand The Drupal Update System

A module update hook is usually named MODULE_update_N(), where MODULE is the machine name and N is a monotonically increasing number. For example, a module called store_tools might contain store_tools_update_10001(). The number identifies the update order; it does not have to match the module’s semantic version.

In a procedural module, place the function in the module’s .install file:

/**
 * Adds an index for the external reference column.
 */
function store_tools_update_10001(): void {
  $database = \Drupal::database();
  $schema = $database->schema();

  if ($schema->tableExists('store_tools_order')) {
    $schema->addIndex(
      'store_tools_order',
      'external_reference',
      ['external_reference'],
      ['description' => 'Improves external reference lookups.']
    );
  }
}

The update manager discovers pending hooks from enabled modules and records successful executions in Drupal’s key-value storage. Administrators can run them through /update.php, provided the site is placed in maintenance mode where appropriate, or with Drush using drush updatedb. The command-line approach is usually preferable for managed deployments because its output can be captured in build logs.

A hook should describe one discrete migration step. If a later release needs another column, create store_tools_update_10002() instead of editing 10001. Editing an update that may already have run creates inconsistent results between a new installation and a long-running production site. The .install file therefore becomes a historical record of database evolution.

Choose The Right Schema API

Drupal’s database abstraction layer should be used instead of hard-coded SQL wherever the operation can be expressed through the schema API. Methods such as createTable(), addField(), changeField(), addPrimaryKey(), and addIndex() make the intention clearer and improve portability across supported database engines.

A table creation update can define fields, keys, and indexes in one schema array:

function store_tools_update_10002(): void {
  $schema = \Drupal::database()->schema();

  if (!$schema->tableExists('store_tools_log')) {
    $schema->createTable('store_tools_log', [
      'description' => 'Stores synchronisation events.',
      'fields' => [
        'id' => [
          'type' => 'serial',
          'not null' => TRUE,
        ],
        'order_id' => [
          'type' => 'int',
          'unsigned' => TRUE,
          'not null' => TRUE,
        ],
        'event_name' => [
          'type' => 'varchar',
          'length' => 128,
          'not null' => TRUE,
        ],
        'created' => [
          'type' => 'int',
          'not null' => TRUE,
        ],
      ],
      'primary key' => ['id'],
      'indexes' => [
        'order_id' => ['order_id'],
        'created' => ['created'],
      ],
    ]);
  }
}

The tableExists() check can make an update safer when an installation has been manually altered or partially restored. It should not be used to hide every deployment error, however. If a table exists with the wrong structure, silently skipping the operation can leave the application in a broken state. Inspect the schema and decide whether the update should fail clearly.

For existing tables, use the schema definition already maintained by the module as a reference, but remember that hook_schema() describes the desired structure for new installations. It does not automatically modify tables that already exist. The update hook is responsible for bringing old installations up to that structure.

Use descriptive names for indexes and keep them purposeful. An index consumes storage and can slow writes, so adding one to every column is poor database design. Consider the queries used by the module, the size of the table, and whether a composite index matches the actual filtering order.

Write A Safe Data Migration

A schema alteration often needs a data migration as well. For example, adding a status_code column may require copying values from an older status column. Add the new field first, populate it, verify the result, and only remove the old field in a later update. This staged approach gives the application time to support both representations during the release.

Drupal’s database API supports placeholders and condition builders for data changes:

function store_tools_update_10003(): void {
  $connection = \Drupal::database();

  $connection->update('store_tools_order')
    ->expression('status_code', 'CASE WHEN status = :complete THEN :completed ELSE :pending END', [
      ':complete' => 'complete',
      ':completed' => 2,
      ':pending' => 0,
    ])
    ->isNotNull('status')
    ->execute();
}

For large tables, avoid loading every row into PHP. Process records in batches with a sandbox so the update can continue across multiple requests. The $sandbox parameter tracks progress, while #finished tells Drupal whether the operation is complete:

function store_tools_update_10004(&$sandbox): void {
  $connection = \Drupal::database();
  $limit = 250;

  if (!isset($sandbox['last_id'])) {
    $sandbox['last_id'] = 0;
    $sandbox['total'] = (int) $connection->select('store_tools_order', 'o')
      ->condition('id', 0, '>')
      ->countQuery()
      ->execute()
      ->fetchField();
    $sandbox['processed'] = 0;
  }

  $ids = $connection->select('store_tools_order', 'o')
    ->fields('o', ['id'])
    ->condition('id', $sandbox['last_id'], '>')
    ->orderBy('id')
    ->range(0, $limit)
    ->execute()
    ->fetchCol();

  foreach ($ids as $id) {
    $connection->update('store_tools_order')
      ->fields(['status_code' => 0])
      ->condition('id', $id)
      ->execute();

    $sandbox['last_id'] = $id;
    $sandbox['processed']++;
  }

  $sandbox['#finished'] = $sandbox['total']
    ? min(1, $sandbox['processed'] / $sandbox['total'])
    : 1;
}

The exact migration logic should reflect the business meaning of the old values. A content editor in Adelaide may have entered legacy statuses manually, while an automated integration used fixed codes. Data profiling before deployment can reveal unexpected values, empty strings, duplicate records, or dates stored in local time.

When a migration handles customer, membership, or contact data, minimise what is copied and protect backups. Australian organisations may need to consider the Privacy Act 1988 and the Australian Privacy Principles, particularly when a development database is created from production data. Use sanitised exports where possible and document how long migration copies are retained.

Test Updates Before Production

A schema update should be tested against both a fresh installation and an upgraded installation. A fresh install confirms that the current .install schema works, while an upgrade test proves that every historical update runs in the correct order. Skipping the second test is a common reason a deployment succeeds on a developer laptop but fails on an established site.

Create a database snapshot before testing and run the update with Drush:

drush updb -y
drush cr
drush status

Inspect the resulting tables, columns, constraints, and indexes directly. Run the module’s automated tests, check representative pages, and exercise the code paths that read and write the changed fields. If the update changes entity storage, verify entity queries, Views, forms, exports, and administrative reports as well.

Keep update hooks independent from normal page requests. Avoid relying on a custom service that may not exist in the older code being upgraded, and do not assume that configuration has already been imported. Drupal’s service container is available in many update contexts, but direct database access through \Drupal::database() is often the least fragile choice for a small schema operation.

If the migration also connects to a remote platform, separate the database update from the API call where possible. The design principles in this guide to third-party API services are useful when an external system must be coordinated with a Drupal release. A database update should remain repeatable and should not fail merely because an external endpoint is temporarily unavailable.

Drupal’s post_update_NAME() mechanism is intended for post-update tasks, often involving configuration or content adjustments after a module update. It is not a replacement for a schema update hook when a table or column must exist before the application runs. Select the mechanism according to whether the operation changes database structure, configuration, or stored content.

Deploy And Maintain The Migration

Commit the module code, database update, tests, and any deployment notes together. On a staging environment, restore a recent database copy, enable maintenance mode if needed, run drush updb, rebuild caches, and review logs. Confirm that the update number appears as completed before promoting the same release to production.

Use a pre-deployment backup and ensure that the database account has the permissions required for the alteration. Large indexes or table changes can lock tables, depending on the database engine and hosting configuration. For a busy Melbourne shop or a Sydney membership site, schedule the release during a low-traffic window and monitor query time, PHP memory, disk space, and application errors.

An update hook should generally fail loudly if its required operation cannot be completed. Do not catch an exception simply to report success. A failed update gives the team an opportunity to restore the backup, correct the migration, and rerun it. If an operation is genuinely safe to repeat, add checks that make it idempotent, such as testing for an existing column or using a precise condition before changing rows.

After deployment, check the Drupal status report, watchdog logs, queue workers, cron tasks, and key editorial workflows. Content teams may work at different hours across Australia, and a release that looks healthy immediately after midnight in Brisbane may still affect a Perth-based team beginning its morning shift. Keep the old column or compatibility code until the new version has been proven in production, then remove obsolete structures with a separate, deliberate update.

Finally, document what the update changes, how many records it processed, and which release introduced it. If the module is used across several sites, record database engine assumptions and any expected downtime. Good update hooks make future upgrades routine: each schema change has a clear number, a bounded purpose, tested migration logic, and a deployment path that respects both Drupal’s architecture and the realities of operating websites in Australia.