Creating tables in .install with schema APIfor Drupal 8 , 9 , 10 , and 11

Home » Module Development » Creating tables in .install with schema API
Last updated :  
8.x , 9.x , 10.x , 11.x

Think of your module's data like a filing cabinet you're installing in someone else's office. You can't just start throwing folders in — Drupal needs to know exactly how many drawers this cabinet has, how each folder is labeled, and what kind of paper goes in each one, before a single byte gets written. That's what hook_schema() is: a blueprint, declared in your module's .install file, that tells Drupal precisely how to build a custom database table the moment your module is installed.

What you'll learn in this lesson

  • Where table definitions live — the .install file, not your .module file
  • How hook_schema() describes columns, types, and keys as a plain PHP array
  • How hook_install() can seed a brand-new table with starting data
  • Why every column needs an explicit type, length, and "not null" default

Declaring the table with hook_schema()

Here's a real, working example from Drupal's own Examples project — a table that stores simple person records:

function dbtng_example_schema() {
  $schema['dbtng_example'] = [
    'description' => 'Stores example person entries for demonstration purposes.',
    'fields' => [
      'pid' => [
        'type' => 'serial',
        'not null' => TRUE,
        'description' => 'Primary Key: Unique person ID.',
      ],
      'uid' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
        'description' => "Creator user's {users}.uid",
      ],
      'name' => [
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ],
      'surname' => [
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ],
      'age' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
        'size' => 'tiny',
      ],
    ],
    'primary key' => ['pid'],
    'indexes' => [
      'name' => ['name'],
      'surname' => ['surname'],
      'age' => ['age'],
    ],
  ];
  return $schema;
}

The function name is the naming convention itself: {module_name}_schema(). Drupal scans every enabled module for a function matching this pattern the moment the module is installed, and builds the real SQL CREATE TABLE statement from this array for you — you never write raw DDL by hand, and the same array works identically across MySQL, MariaDB, PostgreSQL, and SQLite.

Why pid is a serial and everything else has a strict type

serial is Drupal's schema API shorthand for an auto-incrementing integer primary key — exactly what you'd reach for as a row's unique ID. Every other column declares a concrete type (int, varchar) plus a length where relevant and a default — this isn't busywork, it's what lets the Schema API generate a table that behaves identically and safely no matter which database backend the site actually runs on.

Seeding data with hook_install()

function dbtng_example_install() {
  $entries = [
    ['name' => 'John', 'surname' => 'Doe', 'age' => 0],
    ['name' => 'John', 'surname' => 'Roe', 'age' => 100, 'uid' => 1],
  ];
  $connection = \Drupal::database();
  foreach ($entries as $entry) {
    $connection->insert('dbtng_example')->fields($entry)->execute();
  }
}

hook_install() runs exactly once, the moment your module is enabled — right after Drupal has already created the table from your schema. This is the natural place to seed default or demo rows, exactly as this example does with two starter "person" records so the module has something to show immediately.

Common mistake: forgetting that changing hook_schema() after your module is already installed does nothing on its own. Drupal only reads the schema once, at install time. To alter an existing table's structure, you need a numbered hook_update_N() function that runs the actual ALTER TABLE logic — the schema array is the blueprint for a fresh install, not a live sync target.

Quick check: if you added a new column to the fields array in hook_schema() and ran drush cr on a site where this module is already installed, would the new column appear in the database? (No — the table already exists, so hook_schema() is never re-read for it. You'd need a hook_update_N() that calls db_add_field() or the equivalent Schema API method to actually add it.)

Key takeaways

  • Table definitions live in hook_schema() inside your module's .install file, named {module_name}_schema().
  • Every column needs an explicit type, and usually a length and default — this is what makes the same schema portable across database engines.
  • serial is the standard type for an auto-incrementing primary key.
  • hook_install() runs once at install time and is the right place to seed starter data via \Drupal::database()->insert().
  • Schema changes to an already-installed module require a hook_update_N(), not a change to hook_schema() alone.

Coming up next

You've got a real table with real rows in it. Next, you'll learn how to actually get data back out — using Database::getConnection() and the query builder to read those rows safely and efficiently.