Your Custom Table Is a Black Box (And That's Your Fault)

When a Custom Table Is the Right Call

Before touching a line of code, the architecture decision matters more than the implementation.

Custom entities are Drupal's preferred pattern for most structured data, and for good reason. You get the Field API, entity cache integration, REST endpoints, and Views support out of the box. If your data has an editorial lifecycle — draft, publish, revise — a custom entity is almost always the right answer.

But there's a class of data where entity overhead becomes the wrong trade. High-volume transactional records like ratings, audit logs, event tracking, or computed aggregates don't need revision history, they don't need field formatters, and they don't need a content moderation workflow. They need fast writes and flexible queries. A full custom entity for a table that ingests 50,000 rows a day is engineering ego, not pragmatism.

Custom tables win when:

*   The data has no editorial lifecycle (no draft/publish cycle, no revisions)
*   Write volume is high and entity cache overhead would be felt
*   The schema is relationally structured and maps cleanly to a few typed columns
*   You don't need REST or JSON:API endpoints without additional scaffolding

Here's what you sacrifice in that trade. You lose Drupal's entity cache automatically. You lose REST/JSON:API out of the box. And you lose Views integration — unless you build it yourself with hook_views_data(). Go in knowing that, and the decision is clean. Skip hook_views_data() and you've just built a Friday phone call waiting to happen.

Building the Table the Drupal-Native Way

Schema definition belongs in the PHP file custom_rate.install. Not a raw CREATE TABLE in a controller, not a hook_update_N() hack on day one — hook_schema(). Drupal calls it on module install and drops it cleanly on uninstall.

/**
* Implements hook_schema().
*/
function custom_rate_schema() {
 $schema['custom_rate'] = [
   'description' => 'Stores product ratings submitted by users.',
   'fields' => [
     'id' => [
       'type' => 'serial',
       'unsigned' => TRUE,
       'not null' => TRUE,
       'description' => 'Primary key.',
     ],
     'nid' => [
       'type' => 'int',
       'unsigned' => TRUE,
       'not null' => TRUE,
       'description' => 'The node ID this rating belongs to.',
     ],
     'uid' => [
       'type' => 'int',
       'unsigned' => TRUE,
       'not null' => TRUE,
       'default' => 0,
       'description' => 'The user who submitted the rating.',
     ],
     'rating' => [
       'type' => 'int',
       'unsigned' => TRUE,
       'not null' => TRUE,
       'default' => 0,
       'description' => 'Numeric rating value.',
     ],
     'region' => [
       'type' => 'varchar',
       'length' => 64,
       'not null' => FALSE,
       'description' => 'Geographic region identifier.',
     ],
     'created' => [
       'type' => 'int',
       'not null' => TRUE,
       'default' => 0,
       'description' => 'Unix timestamp of submission.',
     ],
   ],
   // Primary key and indexes live outside the fields array.
   'primary key' => ['id'],
   'indexes' => [
     'nid' => ['nid'],
   ],
 ];
 return $schema;
}

Note that primary key and indexes are declared at the schema array level, not nested inside fields. This trips up developers copying from examples regularly.

CRUD via the Database API

\Drupal::database() returns a Connection object. For service classes, inject database via the container — but for procedural code or hooks, the static call is fine.

Insert:

\Drupal::database()
 ->insert('custom_rate')
 ->fields(['nid', 'uid', 'rating', 'region', 'created'])
 ->values([$nid, $uid, $rating, $region, \Drupal::time()->getRequestTime()])
 ->execute();

Update and Delete follow the same method chain pattern with ->condition() to scope the operation. What most developers miss entirely is merge — Drupal's upsert:

\Drupal::database()
 ->merge('custom_rate')
 ->keys(['nid' => $nid, 'uid' => $uid])
 ->insertFields(['rating' => $rating, 'region' => $region, 'created' => time()])
 ->updateFields(['rating' => $rating, 'region' => $region])
 ->execute();

merge() checks for a matching row on the keys columns. If it exists, it runs the updateFields values. If not, it inserts using insertFields. That eliminates the select-then-branch dance that pollutes a lot of custom table code.

Making the Data Visible: hook_views_data()

This is the hook that closes the loop, and it lives in PHP file custom_rate.module. Implement it, and your custom table shows up in the Views UI like any core table. Skip it, and you've built the black box.

The return array has a specific hierarchy. Get the structure wrong and Views silently ignores your table.

/**
* Implements hook_views_data().
*/
function custom_rate_views_data() {
 $data = [];
 // Top-level table registration.
 $data['custom_rate']['table']['group'] = t('Custom Rate');
 $data['custom_rate']['table']['provider'] = 'custom_rate';
 // Mark this as a Views base table — omit this block for join-only tables.
 $data['custom_rate']['table']['base'] = [
   'field' => 'id',
   'title' => t('Custom Rate'),
   'help' => t('Product ratings submitted by users.'),
 ];
 // Implicit join to node_field_data on nid.
 $data['custom_rate']['table']['join'] = [
   'node_field_data' => [
     'left_field' => 'nid',
     'field' => 'nid',
   ],
 ];
 // Field definitions.
 $data['custom_rate']['id'] = [
   'title' => t('Rating ID'),
   'help' => t('The unique ID of the rating record.'),
   'field' => ['id' => 'numeric'],
   'sort' => ['id' => 'standard'],
   'filter' => ['id' => 'numeric'],
   'argument' => ['id' => 'numeric'],
 ];
 $data['custom_rate']['rating'] = [
   'title' => t('Rating Value'),
   'help' => t('The numeric rating submitted.'),
   'field' => ['id' => 'numeric'],
   'sort' => ['id' => 'standard'],
   'filter' => ['id' => 'numeric'],
   'argument' => ['id' => 'numeric'],
 ];
 $data['custom_rate']['region'] = [
   'title' => t('Region'),
   'help' => t('Geographic region identifier.'),
   'field' => ['id' => 'standard'],
   'sort' => ['id' => 'standard'],
   'filter' => ['id' => 'string'],
   'argument' => ['id' => 'string'],
 ];
 $data['custom_rate']['created'] = [
   'title' => t('Submitted'),
   'help' => t('Unix timestamp of submission.'),
   'field' => ['id' => 'date'],
   'sort' => ['id' => 'date'],
   'filter' => ['id' => 'date'],
 ];
 // Relationship from this table's nid FK back to node_field_data.
 $data['custom_rate']['nid'] = [
   'title' => t('Node'),
   'help' => t('The node this rating is attached to.'),
   'relationship' => [
     'base' => 'node_field_data',
     'base field' => 'nid',
     'id' => 'standard',
     'label' => t('Related node'),
   ],
 ];
 return $data;
}

A few things worth calling out here.

The provider key — $data['custom_rate']['table']['provider'] = 'custom_rate' — is the most commonly skipped line in every Views data implementation I've seen. Without it, cache dependency tracking breaks in subtle ways. Views may not invalidate correctly when the module updates. Set it. It costs nothing.

Handler plugin IDs are exact strings, not class names. 'id' => 'numeric' for integers, 'id' => 'standard' for plain text fields, 'id' => 'date' for timestamps stored as integers. Getting these wrong produces Views fields that silently fail to render or filter.

If your custom table is a junction or log table — something that only makes sense in relation to another base table — omit the base block entirely. The table will only appear in Views via a relationship, which is the correct behaviour. A ratings log probably should be its own base table. A many-to-many pivot table probably shouldn't.

Relationships in the Other Direction

hook_views_data() handles adding a relationship _from_ your table pointing outward. If you want to add a relationship in the reverse direction — say, adding a "ratings" relationship onto node_field_data so node-based views can reach your table — that belongs in hook_views_data_alter(). It lets you extend another table's Views definition without touching that module's hook.
 

Contrib Shortcuts and When to Reach for Them

Two modules come up whenever custom table visibility gets discussed, and they serve genuinely different purposes.

Views Database Connector

views_database_connector (drupal/views_database_connector ^2.0, supports D8.8+/D11) is the right tool when you need to pull data from an external database defined in settings.php — a legacy system, a third-party data warehouse, a non-Drupal application. It requires access to information_schema on the remote database and handles the type mapping automatically. If you're using it to avoid writing hook_views_data() for your own schema, you're using it wrong.

Views Custom Table

view_custom_table (drupal/view_custom_table ^2.0, supports D10.1+/D11) provides a UI-driven approach for mapping existing tables into Views without code. It auto-maps MySQL column types to handler IDs and works reasonably well for straightforward tables. It also carries 12 open bug reports at time of writing. If you're building something client-facing and expect to maintain it past the next Drupal minor, know what you're installing.

Neither module is a substitute for understanding the hook. If you inherit a site using them, the first thing to verify is whether the Views integration is actually doing what someone thought it was.

The whole lifecycle — hook_schema() to create, Database API to query, hook_views_data() to expose — takes maybe two hours to wire up properly. Skipping the last step saves you thirty minutes once and costs every future developer hours of frustration, every site builder their autonomy, and every client a phone call to someone with SSH access.