Alter table structure on Drupal 8 | Drupal 8

Alter table structure on Drupal 8

Submitted by editor on Tue, 06/13/2017 - 11:59
Question

How to update or alter a table structure of drupal 8 , to add fields, remove fields, update fields, add tables, remove tables ... ... ?

To update a table you must create a custom module. Example , Here we use mymodule.

Note : Initially you must create a database table. Example on hook_schema() of mymodule.install :
/**
 * Schema.
 */
function mymodule_schema() {
  $schema['mymodule_table1'] = [
    'description' => 'My test table',
    'fields' => [
      'id' => [
        'type' => 'serial',
        'not null' => TRUE,
      ],
      'test_field_1' => [
        'type' => 'int',
      ],
    ],
    'primary key' => ['id'],
  ];
  return $schema;
}

Update a table from module update hook

Hook : hook_update_N().
You can update tables from anyware of your module, but it is strongly recomended to use this hook.

use Drupal\Core\Database\Database;
/**
 * Update Table mymodule_table1, add a field and remove a field.
 */
mymodule_update_8001(){
  $ret = [];
  $con = Database::getConnection();
  // Remove 'test_field_1' from mymodule_table1 DB.
  $ret[] = $con->schema()->dropField('mymodule_table1', 'test_field_1');
  // Add 'test_field_2' to mymodule_table1 DB.
  $spec = [
    'type' => 'text',
    'size' => 'tiny',
  ];
  $ret[] = $con->schema()->addField('mymodule_table1', 'test_field_2', $spec);
  return $ret;
}

Create a new table

$spec = array(
'description' => 'My description',
'fields' => array(
  'myfield1' => array(
    'description' => 'Myfield1 description.',
    'type' => 'varchar',
    'length' => 255,
    'not null' => TRUE,
    'default' => '',
  ),
  'myfield2' => array(
    'description' => 'Myfield2 description',
    'type' => 'text',
    'not null' => TRUE,
  ),
),
'primary key' => array('myfield1'),
);
$schema = Database::getConnection()->schema();
$schema->createTable('mytable2', $spec);

Adding primary keys or indexes

$spec = array('myfield1');
$schema = Database::getConnection()->schema();
// A normal index.
$schema->addIndex('mytable', $spec);
// A primary key.
$schema->addPrimaryKey('mytable', $spec);
// A unique key.
$schema->addUniqueKey('mytable', $spec);

 

Add new comment