Drupal 6: Creating database schema for a Drupal module and inserting database records

Here’s a quick guide on how to create database schema for your module. The first thing you should do is get acquainted with the Schema API documentation.

You’ll need to create a file in your module directory called: MYMODULE.install. In this file, you’ll define hook_schema, hook_install, hook_uninstall, etc. Here’s an example that defines 3 columns, the primary key, and a unique key.

<?php
function MYMODULE_schema() {
  $schema = array();

  $schema['MYTABLE'] = array(
    'description' => t('MY TABLE DESCRIPTION'),
    'fields' => array(
      'MYCOLUMN1' => array(
        'description' => t('MY UNIQUE IDENTIFIER'),
        'type' => 'serial',
        'unsigned' => true,
        'not null' => true,
      ),
      'MYVARCHAR' => array(
        'description' => t('MY VAR CHAR'),
        'type' => 'varchar',
        'length' => 32,
        'not null' => true,
      ),
      'MYTIMESTAMP' => array(
        'description' => t('MY TIMESTAMP'),
        'type' => 'int',
        'not null' => true,
      ),
    ),
    'indexes' => array(
      'MYVARCHAR' => array('MYVARCHAR'),
    ),
    'primary key' => array('MYCOLUMN1'),
    'unique keys' => array(
      'MYCOLUMN1' => array('MYCOLUMN1'),
    ),
  );

  return $schema;

}
?>

A full list of data types can be found here. This documentation explains the structure of the schema array.

Now you can define how to install and uninstall the schema:

<?php
function MYMODULE_install() {
  drupal_install_schema('MYMODULE');
}

function MYMODULE_uninstall() {
  drupal_uninstall_schema('MYMODULE');
}
?>

More information on schema functions can be found here.

In your module code to write a database record you simply define an array/object containing your columns and the data you’d like to insert, and then use the function drupal_write_record:

<?php
$record = new StdClass();
$record->MYVARCHAR = 'BLAH';
$record->MYTIMESTAMP = strtotime('now');
drupal_write_record('MYTABLE', $record);
?>

More information on writing to the database can be found here.

Eric London