How do I add column to existing table on module installation?

Question

I want to add an extra field to the users table from a custom module. I am not sure how exactly can it be done. I'm sure it is done in mymodule.install file.
Should I use db_add_field, or is there any other way? And where should I implement it? hook_schema (but the schema is already defined), or hook_install?

Answer 1

For the sake of giving a (hopefully) complete answer:

• You will need to alter the schema of an existing module, signalling the change to the system. So implement hook_schema_alter().
• You will need to implement hook_install() to actually add the field to the database.
• You will need to implement hook_uninstall() as well, to remove the extra field when the module is uninstalled. You might need to do extra cleanup at this stage.

An example which alters the Webform module's webform_submitted_data table:

function MYMODULE_schema_alter(&$schema) {
  $schema['webform_submitted_data']['fields']['extra'] = array(
    'description' => 'Some random extra info.',
    'type' => 'text',
    'size' => 'big',
  );
}

function MYMODULE_install() {
  db_add_field('webform_submitted_data', 'extra', array(
    'description' => 'Some random extra info.',
    'type' => 'text',
    'size' => 'big',
  ));
}

function MYMODULE_uninstall() {
  db_drop_field('webform_submitted_data', 'extra');
}

Answer 2

Maybe, I went too early to put this as question, but I've figured it out on my local drupal installation. Just added the following code to MYMODULE.install file. Used hook_install() and db_add_field().

function MYMODULE_install() {
  db_add_field('users', 'encuid', array(
    'type' => 'varchar', 
    'length' => 60,
    'not null' => TRUE, 
    'default' => 0,
  ));
}

Answer 3

I think that best method for not loose data is create a hook_update in your custom module and make a drush updb.

/**
 * Implements hook_update_7084().
 * Add new field in custom table.
 */
function my_module_update_7084() {
  db_add_field('mytable', 'my_new_field', array(
    'type' => 'varchar', 
    'length' => 24,
    'not null' => TRUE, 
    'default' => 'favorite',
  ));
}

Regards... ;) Miguel