The purpose of the Schema API is to allow the definition of database table structures in PHP and to have Drupal interact with the database engine and turn these definitions into a reality. Apart from the fact that we don't ever have to see things such as CREATE TABLE, we ensure that our table structures can be applied to multiple types of databases. If you remember, in Chapter 1, Developing for Drupal 8, it was mentioned that Drupal can work with MySQL, PostgreSQL, SQLite, and others, if they support PDO, so the Schema API ensures this cross-compatibility.

The central component of the Schema API is hook_schema(). This is used to provide the initial table definitions of a given module. Implementations of this hook belong in the *.install file of the module and are fired when the module is first installed. If alterations need to be made to the existing database tables, there are a number of methods that can be used inside update hooks to make these changes.

In this section, we will create a new module called sports in which we want to define two tables--players and teams. The records in the former can reference records in the latter, as each player can be part of only one team at a time. This is a simple example, and one which could, and should, be implemented using entities. However, for the purpose of demonstrating the database API, we will stick with the manual setup.

So, in our sports.install file, we can implement hook_schema() as follows:

/**
 * Implements hook_schema().
 */
function sports_schema() {
  $schema = [];

  $schema['teams'] = [
    'description' => 'The table that holds team data.',
    'fields' => [
      'id' => [
        'description' => 'The primary identifier.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ],
      'name' => [
        'description' => 'The team name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
      ],
      'description' => [
        'description' => 'The team description.',
        'type' => 'text',
        'size' => 'normal',
      ],
    ],
    'primary key' => ['id'],
  ];

  $schema['players'] = [
    'description' => 'The table that holds player data.',
    'fields' => [
      'id' => [
        'description' => 'The primary identifier.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ],
      'team_id' => [
        'description' => 'The ID of the team it belongs to.',
        'type' => 'int',
        'unsigned' => TRUE,
      ],
      'name' => [
        'description' => 'The player name.',
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
      ],
      'data' => [
        'description' => 'Arbitrary data about the player.',
        'type' => 'blob',
        'size' => 'big',
      ],
    ],
    'primary key' => ['id'],
  ];

  return $schema;
}

Implementations of this hook need to return an associative array keyed by the table name whose values are an array that defines the respective table. The table definition consists of various types of information, particularly the individual column definitions (fields), and also things such as which fields represent the primary key, foreign keys (strictly for documentation purposes), unique keys, and indexes. For a full reference of all the available options, check out the Drupal.org (https://www.drupal.org/) documentation pages for the Schema API.

In our preceding example, we defined the two tables we mentioned and defined their fields inside the fields array. The primary key indicates which of the tables will be used for that purpose, opting for the standard id field for both. Speaking of which, the latter is a field of the type serial, which means that it is an integer that has an auto-increment option to it. For number fields such as integer, float, and numeric, the unsigned option means that numbers cannot go below 0. Also, the not null is pretty easy to understand--it prevents the column from ever being empty.

For the team and player name, we opted for a simple varchar field that takes a maximum of 255 characters (a pretty standard table column definition), and these, too, cannot be null. The description field, on the other hand, is of the text type with the normal size (as opposed to tiny, small, medium, or big). In here, we want to store strings that are longer than 255 characters. At the time of writing this book, there is no full documentation for the available data types (and their options) for Drupal 8; however, the D7 version (https://www.drupal.org/docs/7/api/schema-api/data-types) is a good indicator and will pretty much work exactly the same.

Lastly, for the player, we also have a team_id, which is a simple integer field, and a data column, in which we want to store some arbitrary serialized data. This is a blob type, which can also be big or normal.

That is pretty much all for our schema definitions. Installing the sports module will create these tables for us automatically, according to these definitions. Also, just as important, uninstalling the module will delete these tables, so we don't need to do any kind of handling. However, if our module is already enabled and we have added this implementation afterward, it won't get fired. Instead, we will need to implement an update hook and use the drupal_install_schema() function, which will trigger it, as follows:

drupal_install_schema('sports');

We will see more about update hooks soon.