Schema API quick start guide

A sample schema data structure

As an example, here is an excerpt from the schema definition for Drupal's 'node' table:

 $schema['node'] = array(
    'description' => 'The base table for nodes.',
    'fields' => array(
      'nid' => array(
        'description' => 'The primary identifier for a node.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE),
      'vid' => array(
        'description' => 'The current {node_revisions}.vid version identifier.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0),
      'type' => array(
        'description' => 'The {node_type} of this node.',
        'type' => 'varchar',
        'length' => 32,
        'not null' => TRUE,
        'default' => ''),
      'title' => array(
        'description' => 'The title of this node, always treated a non-markup plain text.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => ''),
      'created' => array(
        'description' => 'The Unix timestamp when the node was created.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0),
      'changed' => array(
        'description' => 'The Unix timestamp when the node was most recently saved.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0),
      ),
    'indexes' => array(
      'node_changed'        => array('changed'),
      'node_created'        => array('created'),
      ),
    'unique keys' => array(
      'nid_vid' => array('nid', 'vid'),
      'vid'     => array('vid')
      ),
    'primary key' => array('nid'),
  );

In this excerpt, the table 'node' has six fields (table columns) named 'nid', 'vid', 'type', 'title', 'created' and 'changed'. Each field specifies its type ('serial', 'int', or 'varchar' in this example) and some additional optional parameters, including a description.

The table's primary key is the single field 'nid'. There are two unique keys: one named 'vid' on the field 'vid' and another called 'nid_vid' on fields 'nid' and 'vid'. And there are two indexes, one named 'node_changed' on the field 'changed' and one named 'node_created' on the field 'created'.

Creating tables: hook_schema and .install files

For the Schema API to manage a module's tables, the module must have a .install file that implements hook_schema(). For example, my module's mymodule.install file might contain:

/**
 * Implements hook_schema().
 */
function mymodule_schema() {
  $schema['mytable1'] = array(
     // specification for mytable1
  );
  $schema['mytable2'] = array(
     // specification for mytable2
  );
  return $schema;
}

In Drupal 6.x, the module also must explicitly call drupal_install_schema() and drupal_uninstall_schema(), as follows:

/**
 * Implements hook_install().
 */
function mymodule_install() {
  drupal_install_schema('mymodule');
}

/**
 * Implements hook_uninstall().
 */
function mymodule_uninstall() {
  drupal_uninstall_schema('mymodule');
}

In Drupal 7.x, this is no longer necessary — those functions are called automatically.

Making sure your schema gets updated

If you release a new version of your module and it needs to have a new database schema, you need to do two things to make this change work. First, be sure to update the schema structure in mymodule_schema() so that new installations of your module get the new database table structure. Second, use a hook_update_N() function, as in previous versions of Drupal, to make sure that existing installations of your module can be updated to your new version. For example, suppose you add a new column called 'newcol' to table 'mytable1' in your module. You would need to add an update function to mymodule.install:

function mymodule_update_6100() {
  $schema = drupal_get_schema('mytable1');
  db_add_field('mytable1', 'newcol', $schema['newcol']);
}

Shortcut: Use the contributed schema.module to create an entire hook_schema() for you.

If you have an existing module that needs to begin making use of this new schema API structure, you do not have to manually create the schema definitions for your .install file. If you have the Schema module, it will do it for you. With schema.module installed, visit Administer > Site building > Schema, then the "Inspect" tab.

On the Inspect tab, the Schema module generates and displays schema data structures for all tables in the database. Tables that are owned by a module are listed under that module name. Other tables are listed under the name "Unknown." Since your module's tables are not yet defined by a hook_schema, they will be in the Unknown group. Just copy and paste the schema structure for each of your tables into your module's hook_schema function in the .install file and remember to return $schema; at the end.