Introduced in Drupal 6, the Schema API allows modules to declare their database tables in a structured array (similar to the Form API) and provides API functions for creating, dropping, and changing tables, columns, keys, and indexes.
Database schemas and their abstractions are a large topic in computer science and software development. Drupal's Schema API 1 is a fairly simple approach. Even so, it provides several benefits:
- No more separate CREATE TABLE or ALTER TABLE statements for each database. Module developers only need to create a schema structure and/or use the Schema API functions, and Drupal takes care of the rest. This makes writing install and update functions much simpler. Use hook_schema_alter() to make changes to existing schema.
- Since the API makes it equally easy for module developers to support all databases, Drupal's support for PostgreSQL will improve substantially. Also, as new database engines such as Oracle, SQLite, or Microsoft SQL Server are supported, modules using the Schema API will automatically work with them.
- Several advanced capabilities, such as incremental database updates, a simple and consistent CRUD API, form scaffolding, simpler CCK and Views, schema and data validation, become much easier to implement in future enhancements.
Refer to https://api.drupal.org/api/drupal/includes!database!schema.inc/group/sch... for details on schema definition structures.