Skip to content
Drupal Developer DocsPreview

How-to guideFor module developersDrupal 11

Custom database tables and update paths

Some data fits none of Drupal’s standard containers: high-volume rows you never render directly, lookup tables, counters. For those cases your module can own real database tables. This page covers the whole lifecycle: defining tables, querying them portably, coordinating concurrent writers, and changing the schema after release.

Goal

Section titled “Goal”

Your module defines its own tables, reads and writes them through the database abstraction layer so the code runs unchanged on MySQL, PostgreSQL, and SQLite, and ships update hooks when the schema changes. This is the wrong approach for most data: content belongs in entities (which gives you Views, access control, and JSON:API for free; see Create, load, and query entities), settings belong in config, and single machine-generated values belong in state (Config, content, state, and sessions). Reach for custom tables when none of those fit.

Prerequisites

Section titled “Prerequisites”

Steps

Section titled “Steps”

Define the schema in hook_schema()

Section titled “Define the schema in hook_schema()”

Implement hook_schema() in your module’s .install file. It returns an array describing each table: fields, primary key, unique keys, and indexes. Drupal creates the tables when the module is installed and drops them when it is uninstalled; you never write CREATE TABLE.

web/modules/custom/event_rsvp/event_rsvp.install
<?php


/**
 * @file
 * Install, update and uninstall functions for the Event RSVP module.
 */


declare(strict_types=1);


/**
 * Implements hook_schema().
 */
function event_rsvp_schema(): array {
  $schema['event_rsvp'] = [
    'description' => 'Stores one row per RSVP to an event node.',
    'fields' => [
      'id' => [
        'description' => 'Primary key: unique RSVP ID.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ],
      'nid' => [
        'description' => 'The {node}.nid of the event.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ],
      'uid' => [
        'description' => 'The {users}.uid of the attendee, 0 for anonymous.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ],
      'mail' => [
        'description' => "The attendee's email address.",
        'type' => 'varchar',
        'length' => 254,
        'not null' => TRUE,
        'default' => '',
      ],
      'created' => [
        'description' => 'Unix timestamp of when the RSVP was submitted.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ],
    ],
    'primary key' => ['id'],
    'unique keys' => [
      'rsvp_once' => ['nid', 'mail'],
    ],
    'indexes' => [
      'nid' => ['nid'],
    ],
  ];
  return $schema;
}

Two rules that surprise people:

  • hook_schema() is one of the hooks core requires to stay procedural, in the .install file. The #[Hook] attribute documentation in Hook lists it, together with every install and update hook, as not implementable in a class.
  • The schema arrays use generic types (int, varchar, serial, text); each database driver maps them to its own column types. The full key reference is the Schema API topic.

Query through the injected connection

Section titled “Query through the injected connection”

Inject the database service (class \Drupal\Core\Database\Connection) and build queries with its methods: select(), insert(), update(), delete(). The flagship module’s storage service shows the everyday patterns, including condition(), orderBy(), countQuery(), and the fetch methods:

web/modules/custom/event_rsvp/src/RsvpStorage.php
<?php


declare(strict_types=1);


namespace Drupal\event_rsvp;


use Drupal\Component\Datetime\TimeInterface;
use Drupal\Core\Cache\CacheTagsInvalidatorInterface;
use Drupal\Core\Database\Connection;


/**
 * Reads and writes RSVP records using the Database API.
 *
 * Registered as the "event_rsvp.storage" service in event_rsvp.services.yml.
 */
class RsvpStorage {


  /**
   * Constructs the RSVP storage.
   *
   * @param \Drupal\Core\Database\Connection $database
   *   The active database connection.
   * @param \Drupal\Component\Datetime\TimeInterface $time
   *   The time service.
   * @param \Drupal\Core\Cache\CacheTagsInvalidatorInterface $cacheTagsInvalidator
   *   The cache tags invalidator.
   */
  public function __construct(
    protected readonly Connection $database,
    protected readonly TimeInterface $time,
    protected readonly CacheTagsInvalidatorInterface $cacheTagsInvalidator,
  ) {}


  /**
   * Records an RSVP for a node.
   *
   * @param int $nid
   *   The event node ID.
   * @param int $uid
   *   The attendee's user ID, 0 for anonymous visitors.
   * @param string $mail
   *   The attendee's email address.
   *
   * @return int
   *   The ID of the new RSVP record.
   */
  public function add(int $nid, int $uid, string $mail): int {
    $id = $this->database->insert('event_rsvp')
      ->fields([
        'nid' => $nid,
        'uid' => $uid,
        'mail' => $mail,
        'created' => $this->time->getRequestTime(),
      ])
      ->execute();
    // Anything rendered from this data — like the RSVP count block — tags
    // itself with this cache tag, so invalidating it here keeps every
    // cached copy accurate. See RsvpStorage::cacheTag().
    $this->cacheTagsInvalidator->invalidateTags([static::cacheTag($nid)]);
    return (int) $id;
  }


  /**
   * Checks whether an email address already responded to a node.
   *
   * @param int $nid
   *   The event node ID.
   * @param string $mail
   *   The email address to check.
   *
   * @return bool
   *   TRUE if an RSVP exists for this node and email address.
   */
  public function exists(int $nid, string $mail): bool {
    $count = $this->database->select('event_rsvp', 'r')
      ->condition('r.nid', $nid)
      ->condition('r.mail', $mail)
      ->countQuery()
      ->execute()
      ->fetchField();
    return (int) $count > 0;
  }


  /**
   * Counts the RSVPs recorded for a node.
   *
   * @param int $nid
   *   The event node ID.
   *
   * @return int
   *   The number of RSVPs.
   */
  public function countForNode(int $nid): int {
    $count = $this->database->select('event_rsvp', 'r')
      ->condition('r.nid', $nid)
      ->countQuery()
      ->execute()
      ->fetchField();
    return (int) $count;
  }


  /**
   * Loads all RSVP records for a node, newest first.
   *
   * @param int $nid
   *   The event node ID.
   *
   * @return object[]
   *   RSVP rows with id, nid, uid, mail and created properties.
   */
  public function listForNode(int $nid): array {
    return $this->database->select('event_rsvp', 'r')
      ->fields('r', ['id', 'nid', 'uid', 'mail', 'created'])
      ->condition('r.nid', $nid)
      ->orderBy('r.created', 'DESC')
      ->execute()
      ->fetchAll();
  }


  /**
   * Deletes every RSVP recorded for a node.
   *
   * @param int $nid
   *   The event node ID.
   */
  public function deleteForNode(int $nid): void {
    $this->database->delete('event_rsvp')
      ->condition('nid', $nid)
      ->execute();
    $this->cacheTagsInvalidator->invalidateTags([static::cacheTag($nid)]);
  }


  /**
   * Returns the cache tag for one node's RSVP data.
   *
   * @param int $nid
   *   The event node ID.
   *
   * @return string
   *   The cache tag, for example "event_rsvp:node:42".
   */
  public static function cacheTag(int $nid): string {
    return 'event_rsvp:node:' . $nid;
  }


}

Never concatenate user input into SQL. The query builders parameterize values for you; that is most of the reason they exist.

Write static SQL with placeholders

Section titled “Write static SQL with placeholders”

For a fixed SELECT that needs no dynamic parts, query() is less code and faster than the builder. Three conventions make it portable and safe:

// Fragment: {braces} get the table prefix (a per-site string Drupal
// prepends to every table name), [brackets] quote
// identifiers, :placeholders parameterize values.
$result = $database->query(
  'SELECT [path], [count] FROM {docs_metrics_daily} WHERE [day] = :day',
  [':day' => $day],
);

Use query() only for SELECT. INSERT, UPDATE, and DELETE syntax differs between engines, so those always go through the builders.

Upsert with merge()

Section titled “Upsert with merge()”

When a row may or may not exist yet, checking first and then inserting is a race condition. Merge queries do “insert or update” atomically:

src/MetricsRecorder.php
/**
 * Counts one visit to a path, creating or updating the row as needed.
 *
 * @param string $path
 *   The visited path.
 */
public function recordVisit(string $path): void {
  // merge() is an atomic "insert or update": no separate existence
  // check, so two parallel requests cannot create duplicate rows.
  $this->database->merge('docs_metrics_daily')
    ->keys([
      'day' => date('Y-m-d', $this->time->getRequestTime()),
      'path' => $path,
    ])
    // Used when no row matches the keys yet.
    ->fields(['count' => 1])
    // Used instead when a row already exists. Brackets quote
    // identifiers portably across database engines.
    ->expression('count', '[count] + 1')
    ->execute();
}
Section titled “Group related writes in a transaction”

When several writes must succeed or fail together, wrap them in a transaction. Drupal’s transactions are scope-based: the transaction commits when the returned object is destroyed, and nested calls collapse into the outermost one.

src/MetricsRecorder.php
/**
 * Rolls one day of per-path counts into the archive table.
 *
 * @param string $day
 *   The day to archive, formatted Y-m-d.
 */
public function archiveDay(string $day): void {
  // The insert and the delete must succeed or fail together: an
  // archive row without the detail rows deleted would double-count.
  $transaction = $this->database->startTransaction();
  try {
    $query = $this->database->select('docs_metrics_daily', 'm');
    $query->addExpression('SUM([m].[count])', 'total');
    $total = (int) $query->condition('m.day', $day)
      ->execute()
      ->fetchField();


    $this->database->insert('docs_metrics_archive')
      ->fields(['day' => $day, 'total' => $total])
      ->execute();


    $this->database->delete('docs_metrics_daily')
      ->condition('day', $day)
      ->execute();
  }
  catch (\Exception $e) {
    // Undo everything since startTransaction(), then let the caller
    // decide what the failure means.
    $transaction->rollBack();
    throw $e;
  }
  // No explicit commit: the transaction commits when $transaction
  // goes out of scope at the end of this method.
}

Coordinate parallel processes with the Lock API

Section titled “Coordinate parallel processes with the Lock API”

Transactions protect data; they do not stop two cron runs or two web requests from doing the same expensive work twice. For that, Drupal has a cooperative lock service (the lock service, backed by DatabaseLockBackend by default). Core itself wraps every cron run in one.

src/MetricsRecorder.php
/**
 * Archives every day before today, guarded against parallel runs.
 */
public function archiveOldDays(): void {
  // Locks are cooperative: every process that wants to do this work
  // must ask for the same lock name first.
  if (!$this->lock->acquire('docs_metrics_archive', 60.0)) {
    // Another process is already archiving. Skipping is fine here;
    // the next cron run picks up whatever is left.
    return;
  }
  try {
    $today = date('Y-m-d', $this->time->getRequestTime());
    $days = $this->database->select('docs_metrics_daily', 'm')
      ->fields('m', ['day'])
      ->condition('m.day', $today, '<')
      ->distinct()
      ->execute()
      ->fetchCol();
    foreach ($days as $day) {
      $this->archiveDay($day);
    }
  }
  finally {
    // Always release, even if archiving threw: an abandoned lock
    // blocks every caller until its timeout expires.
    $this->lock->release('docs_metrics_archive');
  }
}

Locks are advisory: they only work if every participant asks for the same lock name before doing the work. A lock expires after its timeout even if never released, so a crashed process cannot jam the site forever, and the default backend additionally releases everything it acquired when its request ends. For a lock that must outlive the request that acquired it, use the lock.persistent service (PersistentDatabaseLockBackend) instead.

Change the schema later with hook_update_N()

Section titled “Change the schema later with hook_update_N()”

Once a release is out, you cannot edit hook_schema() and expect existing sites to follow: their tables already exist. Schema changes ship as numbered, one-time update functions in the .install file, which site owners run with drush updatedb (drush updb) or /update.php:

web/modules/custom/docs_snippets/docs_snippets.install
/**
 * Add the severity column to the docs_snippets_log table.
 */
function docs_snippets_update_11101(): void {
  // hook_update_N() runs once per existing site, in order, when the site
  // owner runs "drush updatedb" or visits update.php. It must change the
  // *schema and data*, never call other modules' high-level APIs: the
  // site is in an undefined state while updates run.
  $spec = [
    'type' => 'int',
    'size' => 'tiny',
    'not null' => TRUE,
    'default' => 0,
  ];
  \Drupal::database()->schema()->addField('docs_snippets_log', 'severity', $spec);
}

The rules, all enforced by convention and pain rather than code:

  • Update hook_schema() too, so new installs get the final shape directly. New installs never run update hooks.
  • The number N encodes core major, module major, and a sequence (11101 = Drupal 11, module 1.x, first update); it must always increase and may never be renumbered. UpdateHookRegistry records the highest number each site has run, which is how Drupal knows what is pending.
  • While updates run, the site is in an undefined state between code and database versions. Do not call other modules’ APIs, and never load or save entities here. Copy any schema fragments you need into the function body instead of calling hook_schema(), which may have changed again by the time the update runs. The full list of safe and unsafe operations is in the hook_update_N() documentation.

Update data with hook_post_update_NAME()

Section titled “Update data with hook_post_update_NAME()”

For changes to data rather than schema (re-saving entities, backfilling a field), use hook_post_update_NAME() in a MODULE.post_update.php file. Post updates run after every hook_update_N() on a fully booted site, so every API is safe to use. The NAME is any unique string; each function runs once per site.

web/modules/custom/docs_snippets/docs_snippets.post_update.php
<?php


/**
 * @file
 * Post update functions for the Docs snippets module.
 */


declare(strict_types=1);


/**
 * Re-save all articles so updated field defaults are applied.
 */
function docs_snippets_post_update_resave_articles(array &$sandbox): ?string {
  // Post updates run after all hook_update_N() implementations, on a fully
  // functional site — so unlike hook_update_N(), entity and other module
  // APIs are safe to use here.
  $storage = \Drupal::entityTypeManager()->getStorage('node');


  // First call: set up the batch in $sandbox, which persists between calls.
  if (!isset($sandbox['ids'])) {
    $sandbox['ids'] = $storage->getQuery()
      ->condition('type', 'article')
      ->accessCheck(FALSE)
      ->execute();
    $sandbox['total'] = count($sandbox['ids']);
  }


  // Process the next slice of 20.
  $ids = array_splice($sandbox['ids'], 0, 20);
  foreach ($storage->loadMultiple($ids) as $node) {
    $node->save();
  }


  // #finished below 1 means "call me again with the same $sandbox".
  $sandbox['#finished'] = $sandbox['total'] === 0
    ? 1
    : 1 - (count($sandbox['ids']) / $sandbox['total']);


  return $sandbox['#finished'] === 1
    ? t('Re-saved @count articles.', ['@count' => $sandbox['total']])->render()
    : NULL;
}

The $sandbox parameter turns a long update into a batch (the same mechanism as Background processing describes): keep state in $sandbox, process a slice per call, and set $sandbox['#finished'] to a value below 1 until done.

Verify

Section titled “Verify”

  1. Install the module and confirm its table exists. With the event_rsvp schema above (substitute your own module and table names):

    Terminal window
    ddev drush pm:install event_rsvp
    ddev drush sql:query "SELECT COUNT(*) FROM event_rsvp"

    The query returns 0 instead of an error.

  2. After adding an update hook, ddev drush updatedb lists it with the description from its doc comment, asks for confirmation, and reports success.

  3. The flagship module’s kernel test exercises its table through installSchema(); run it as described in Testing.

Variations

Section titled “Variations”
  • One value, not a table. A timestamp, a flag, or a serialized blob of working data needs the State API, not a table: \Drupal::state()->set('my_module.last_run', $time). See Config, content, state, and sessions.
  • Heavy read traffic. Sites with database replicas can send expensive reporting queries to the database.replica service instead of database. On sites without a replica it falls back to the primary connection, so injecting it costs nothing.
  • Retiring old updates. When you drop support for upgrades from very old versions, remove the stale functions and implement hook_update_last_removed() (and hook_removed_post_updates() for post updates) so sites that are too far behind get a clear error instead of a broken upgrade.
  • Other database engines. Core ships and tests the mysql, pgsql, and sqlite drivers; stick to schema types and query builders those three share. Additional engines and the experimental mysqli client arrive as separate driver modules.
Section titled “Related”

Was this page helpful?

Drupal is a registered trademark of Dries Buytaert.