Drupal Custom Module – Create database table during installation

In our previous article, we have shown how can you create your own drupal custom module. Now you’ll learn the advanced functionality for your custom module.

Assume that, your module needs the database tables for storing the module related data. In this tutorial, we’ll discuss the process of creating a table into the database during module installation. Your module needs a .install file, which helps your module to create required table when the module is first enabled and removed when the module is uninstalled. Also, you can update the database table based on your module’s updated version.

Creating .install file

In Drupal 7, a .install file is used to create database tables and fields, and inserting the data. .install file also is used to update the table structure and contents. .install file is a PHP file with different extension.

Create a .install file (mymodule.install) into the root directory of your module. Using this file, we’ll create a subscribers table into the database for storing the website subscriber’s data.

Define hook_schema()

hook_schema() holds an array structure for representing one or more tables and their related keys and indexes.

Define hook_schema for subscribers table and write the following code.

function mymodule_schema(){
    $schema['subscribers'] = array(
        'description' => 'The table for storing the subscriber data.',
        'fields' => array(
            'id' => array(
                'description' => 'The primary identifier for subscriber.',
                'type' => 'serial',
                'not null' => TRUE,
                'unsigned' => TRUE,
            ),
            'name' => array(
                'description' => 'Subscriber name.',
                'type' => 'varchar',
                'length' => 255,
                'not null' => TRUE,
                'default' => '',
            ),
            'email' => array(
                'description' => 'Subscriber email.',
                'type' => 'varchar',
                'length' => 255,
                'not null' => TRUE,
                'default' => '',
            ),
            'subscription_date' => array(
                'description' => 'Subscription date time(yyyy-mm-dd H:i:s).',
                'type' => 'varchar',
                'mysql_type' => 'DATETIME',
                'not null' => TRUE,
            ),
            'status' => array(
                'description' => 'Subscriber status(1=Unblock,0=Block).',
                'type' => 'int',
                'length' => 1,
                'not null' => TRUE,
                'default' => 1,
            ),
        ),
        'primary key' => array('id'),
    );
    return $schema;
}

hook_schema() is invoked before hook_install() and after hook_uninstall() is invoked. If the tables declared by hook_schema(), then the tables will be automatically created when the module is first enabled and removed when the module is uninstalled.

Using hook_update_N()

hook_update_N function is used to add, update or delete database tables, fields or contents at the time of updating your module. For example, In the latest version of your module you want to add a new column at the subscribers table.

hook_update_N function would be the following.

function mymodule_update_2() {
    $ret = array();
    db_add_field($ret, 'subscribers', 'country', array(
        'description' => 'Subscriber country.',
        'type' => 'varchar',
        'default' => '',
        'length' => 255,
        'not null' => TRUE,
    ));
    return $ret;
}