Skip to main content
add new column

Best way to add new columns to custom table

Often times it is needed to create module specific custom tables to store persistent data. Drupal offers a set of hooks in connection to this. Most commonly used among these are hook_schema() and hook_update_N(). First hook, hook_schema() holds the structure of table in the form of PHP array. And since Drupal 7 drupal_install_schema()/drupal_uninstall_schema() are no longer needed to be called explicitly in hook_install()/hook_uninstall(). When the module is enabled for the first time, the structure of table defined in hook_schema is parsed and the needed table(s) are created for intended use.

However the real time use case at times requires adding column to a table (or altering the table) after the module has been enabled. In such cases hook_update_N() comes into play.

Let's take the below code for instance,

function my_module_update_7001(&$sandbox) {
  $spec = array(
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => TRUE,  
  );
  db_add_field('my_table', 'my_column', $spec);
}
This code simply adds an integer column named `my_column` to table `my_table`. On running update.php (https://example.com/update.php). The database layer API does the needful to alter the table as needed (column adding in this case).
 
It is to be noted that the same column definition need to be added to hook_schema() as well. Since there are chances that the module will be installed freshly again in another site.
 
The best way to go about this case is to define the schema array in hook_schema() mainly and reuse the same in hook_update_N().
/**
 * Implements hook_schema().
 */
function my_module_schema() {
  $schema = array();
  $schema['my_module_node_properties'] = array(
    'fields' => array(
      'nid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'vid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'my_column' => array(
        'type' => 'int',
        'size' => 'tiny',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'new_column1' => array(
        'type' => 'int',
        'size' => 'tiny',
      ),
      'new_column2' => array(
        'type' => 'int',
      ),
    ),
    'primary key' => array('vid'),
    'indexes' => array('table_id' => array('vid', 'nid')),
  );
  return $schema;
}

/**
 * Add column1 and column2 fields to table {my_module_node_properties}
 */
function my_module_update_7004() {
  $table = 'my_module_node_properties';
  $schema = drupal_get_schema_unprocessed('my_module', $table);  
  foreach (array('new_column1', 'new_column2') as $field) {
    db_add_field($table, $field, $schema['fields'][$field]);
  }
}
The code above can be considered as best relevant for the subject we have been talking about. The table {my_module_node_properties} initially had nid, vid and my_column as fields. Later it was needed to add new_column1 and new_column2. Definition of the same have been added to hook_schema and in hook_update() using drupal_get_schema_unprocessed() API we get the field spec from unified source and eventually we pass it to db_add_field(). Of course we could use module_invoke() API as well to get schema but drupal_get_schema_unprocessed() is a wrapper function besides calling module_invoke() to get schema, it does schema value checks as well.

 

Comments

tenken (not verified)

Thu, 05/23/2013 - 18:20

Interesting idea -- I like setting up the DB info in 1 spot, hook_schema().

I recently was using hook_update_n to add Indexes to columns in various drupal and other 3rd party DBs my settings.php knows about ....... so i'm not sure how clean it would be to try to add _including_ an index on a new or existing db schema field.

I really like the idea, but i'm not sure how flexible it is.

torotil (not verified)

Fri, 05/24/2013 - 09:10

Using the field as specified in hook_schema() for hook_update_N() might be a bad idea as the schema definition might change later on - imagine you rename the very same column in hook_update_7005() - now you update from 7003 to 7005. In this case 7004 will fail to find your new columns.

@torotil, you are right when the column definition is changing then it need to defined in hook_update_N(). But the case I'm trying to highlight in the blog post is adding new column so you are talking about different case.