1. 8.2.x core/lib/Drupal/Core/Database/database.api.php schemaapi
  2. 8.0.x core/lib/Drupal/Core/Database/database.api.php schemaapi
  3. 8.1.x core/lib/Drupal/Core/Database/database.api.php schemaapi
  4. 8.3.x core/lib/Drupal/Core/Database/database.api.php schemaapi
  5. 6.x includes/database.inc schemaapi
  6. 7.x includes/database/schema.inc schemaapi

API to handle database schemas.

A Drupal schema definition is an array structure representing one or more tables and their related keys and indexes. A schema is defined by hook_schema(), which usually lives in a modulename.install file.

By implementing hook_schema() and specifying the tables your module declares, you can easily create and drop these tables on all supported database engines. You don't have to deal with the different SQL dialects for table creation and alteration of the supported database engines.

hook_schema() should return an array with a key for each table that the module defines.

The following keys are defined:

  • 'description': A string in non-markup plain text describing this table and its purpose. References to other tables should be enclosed in curly-brackets. For example, the node_revisions table description field might contain "Stores per-revision title and body data for each {node}."
  • 'fields': An associative array ('fieldname' => specification) that describes the table's database columns. The specification is also an array. The following specification parameters are defined:

    • 'description': A string in non-markup plain text describing this field and its purpose. References to other tables should be enclosed in curly-brackets. For example, the node table vid field description might contain "Always holds the largest (most recent) {node_revision}.vid value for this nid."
    • 'type': The generic datatype: 'char', 'varchar', 'text', 'blob', 'int', 'float', 'numeric', or 'serial'. Most types just map to the according database engine specific datatypes. Use 'serial' for auto incrementing fields. This will expand to 'INT auto_increment' on MySQL.
    • 'mysql_type', 'pgsql_type', 'sqlite_type', etc.: If you need to use a record type not included in the officially supported list of types above, you can specify a type for each database backend. In this case, you can leave out the type parameter, but be advised that your schema will fail to load on backends that do not have a type specified. A possible solution can be to use the "text" type as a fallback.
    • 'serialize': A boolean indicating whether the field will be stored as a serialized string.
    • 'size': The data size: 'tiny', 'small', 'medium', 'normal', 'big'. This is a hint about the largest value the field will store and determines which of the database engine specific datatypes will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT). 'normal', the default, selects the base type (e.g. on MySQL, INT, VARCHAR, BLOB, etc.). Not all sizes are available for all data types. See DatabaseSchema::getFieldTypeMap() for possible combinations.
    • 'not null': If true, no NULL values will be allowed in this database column. Defaults to false.
    • 'default': The field's default value. The PHP type of the value matters: '', '0', and 0 are all different. If you specify '0' as the default value for a type 'int' field it will not work because '0' is a string containing the character "zero", not an integer.
    • 'length': The maximal length of a type 'char', 'varchar' or 'text' field. Ignored for other field types.
    • 'unsigned': A boolean indicating whether a type 'int', 'float' and 'numeric' only is signed or unsigned. Defaults to FALSE. Ignored for other field types.
    • 'precision', 'scale': For type 'numeric' fields, indicates the precision (total number of significant digits) and scale (decimal digits right of the decimal point). Both values are mandatory. Ignored for other field types.
    • 'binary': A boolean indicating that MySQL should force 'char', 'varchar' or 'text' fields to use case-sensitive binary collation. This has no effect on other database types for which case sensitivity is already the default behavior.

    All parameters apart from 'type' are optional except that type 'numeric' columns must specify 'precision' and 'scale', and type 'varchar' must specify the 'length' parameter.

  • 'primary key': An array of one or more key column specifiers (see below) that form the primary key.
  • 'unique keys': An associative array of unique keys ('keyname' => specification). Each specification is an array of one or more key column specifiers (see below) that form a unique key on the table.
  • 'foreign keys': An associative array of relations ('my_relation' => specification). Each specification is an array containing the name of the referenced table ('table'), and an array of column mappings ('columns'). Column mappings are defined by key pairs ('source_column' => 'referenced_column'). This key is for documentation purposes only; foreign keys are not created in the database, nor are they enforced by Drupal.
  • 'indexes': An associative array of indexes ('indexname' => specification). Each specification is an array of one or more key column specifiers (see below) that form an index on the table.

A key column specifier is either a string naming a column or an array of two elements, column name and length, specifying a prefix of the named column.

As an example, here is a SUBSET of the schema definition for Drupal's 'node' table. It show four fields (nid, vid, type, and title), the primary key on field 'nid', a unique key named 'vid' on field 'vid', and two indexes, one named 'nid' on field 'nid' and one named 'node_title_type' on the field 'title' and the first four bytes of the field 'type':

$schema['node'] = array(
  'description' => 'The base table for nodes.',
  'fields' => array(
    'nid'       => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
    'vid'       => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE,'default' => 0),
    'type'      => array('type' => 'varchar','length' => 32,'not null' => TRUE, 'default' => ''),
    'language'  => array('type' => 'varchar','length' => 12,'not null' => TRUE,'default' => ''),
    'title'     => array('type' => 'varchar','length' => 255,'not null' => TRUE, 'default' => ''),
    'uid'       => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'status'    => array('type' => 'int', 'not null' => TRUE, 'default' => 1),
    'created'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'changed'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'comment'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'promote'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'moderate'  => array('type' => 'int', 'not null' => TRUE,'default' => 0),
    'sticky'    => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'tnid'      => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
    'translate' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
  ),
  'indexes' => array(
    'node_changed'        => array('changed'),
    'node_created'        => array('created'),
    'node_moderate'       => array('moderate'),
    'node_frontpage'      => array('promote', 'status', 'sticky', 'created'),
    'node_status_type'    => array('status', 'type', 'nid'),
    'node_title_type'     => array('title', array('type', 4)),
    'node_type'           => array(array('type', 4)),
    'uid'                 => array('uid'),
    'tnid'                => array('tnid'),
    'translate'           => array('translate'),
  ),
  'unique keys' => array(
    'vid' => array('vid'),
  ),
  // For documentation purposes only; foreign keys are not created in the
  // database.
  'foreign keys' => array(
    'node_revision' => array(
      'table' => 'node_revision',
      'columns' => array('vid' => 'vid'),
     ),
    'node_author' => array(
      'table' => 'users',
      'columns' => array('uid' => 'uid'),
     ),
   ),
  'primary key' => array('nid'),
);

See also

drupal_install_schema()

File

includes/database/schema.inc, line 10
Generic Database schema code.

Functions

Namesort descending Location Description
db_add_field includes/database/database.inc Adds a new field to a table.
db_add_index includes/database/database.inc Adds an index.
db_add_primary_key includes/database/database.inc Adds a primary key to a database table.
db_add_unique_key includes/database/database.inc Adds a unique key.
db_change_field includes/database/database.inc Changes a field definition.
db_create_table includes/database/database.inc Creates a new table from a Drupal table definition.
db_drop_field includes/database/database.inc Drops a field.
db_drop_index includes/database/database.inc Drops an index.
db_drop_primary_key includes/database/database.inc Drops the primary key of a database table.
db_drop_table includes/database/database.inc Drops a table.
db_drop_unique_key includes/database/database.inc Drops a unique key.
db_field_exists includes/database/database.inc Checks if a column exists in the given table.
db_field_names includes/database/database.inc Returns an array of field names from an array of key/index column specifiers.
db_field_set_default includes/database/database.inc Sets the default value for a field.
db_field_set_no_default includes/database/database.inc Sets a field to have no default value.
db_find_tables includes/database/database.inc Finds all tables that are like the specified base table name.
db_index_exists includes/database/database.inc Checks if an index exists in the given table.
db_rename_table includes/database/database.inc Renames a table.
db_table_exists includes/database/database.inc Checks if a table exists.
drupal_get_complete_schema includes/bootstrap.inc Gets the whole database schema.
drupal_get_schema includes/bootstrap.inc Gets the schema definition of a table, or the whole database schema.
drupal_get_schema_unprocessed includes/common.inc Returns the unprocessed and unaltered version of a module's schema.
drupal_install_schema includes/common.inc Creates all tables defined in a module's hook_schema().
drupal_schema_fields_sql includes/common.inc Retrieves a list of fields from a table schema.
drupal_schema_field_types includes/common.inc Retrieves the type for every field in a table schema.
drupal_uninstall_schema includes/common.inc Removes all tables defined in a module's hook_schema().
drupal_write_record includes/common.inc Saves (inserts or updates) a record to the database based upon the schema.
hook_schema modules/system/system.api.php Define the current version of the database schema.
hook_schema_alter modules/system/system.api.php Perform alterations to existing database schemas.
_db_create_keys_sql includes/database/database.inc
_drupal_schema_initialize includes/common.inc Fills in required default values for table definitions from hook_schema().

Classes

Namesort descending Location Description
DatabaseSchema includes/database/schema.inc
DatabaseSchemaObjectDoesNotExistException includes/database/schema.inc Exception thrown if an object being modified doesn't exist yet.
DatabaseSchemaObjectExistsException includes/database/schema.inc Exception thrown if an object being created already exists.
DatabaseSchema_mysql includes/database/mysql/schema.inc
DatabaseSchema_pgsql includes/database/pgsql/schema.inc
DatabaseSchema_sqlite includes/database/sqlite/schema.inc
SchemaCache includes/bootstrap.inc Extends DrupalCacheArray to allow for dynamic building of the schema cache.

Comments

ywarnier’s picture

As of Drupal 7, a call to drupal_install_schema() is no longer necessary. See http://drupal.org/update/modules/6/7: "A module no longer should explicitly install or uninstall its database schema in hook_install() or hook_uninstall()."

lang14’s picture

Shouldn't the 'primary key' be 'primary keys' to follow along with 'foreign keys'?

ChrisFlink’s picture

The primary key is always singular, even though it can contain several fields (or keys), together they form one primary key.

rickburgess.me’s picture

you can also specifiy "mysql_engine" to change the mysql table engine. Useful if using datatypes not supported by InnoDB.

Atomox’s picture

It would be helpful to provide some examples/clarification of date and time related fields in this documentation. From a glace, it seems they were not included, which I'm sure is not the case.

tswaters’s picture

Seems to me the D6 version of this page lists "datetime" as a type -- but not so much here. I'm curious if this is possible?

A little searching reveals this: http://drupal.org/node/866340... which TBH is a little concerning. Pass the type prefaced with the database engine ( just hope people don't use your module with SQLite I guess? )

'mysql_type' => 'TIME',
'mysql_type' => 'DATE'
'mysql_type' => 'DATETIME'
'mysql_type' => 'TIMESTAMP'
'mysql_type' => 'YEAR'

I haven't tried it yet myself.. results may vary :)

arnoldbird’s picture

re: "See DatabaseSchema::getFieldTypeMap() for possible combinations."

I don't think the combinations are indicated there.

idflood’s picture

dave_______1’s picture

I'm looking for default => timestamp or something simlar too. Is this still out?

DeeZone’s picture

Due to Drupal's database abstraction layer timestamp is not supported as a default value. This is due to not all databases supporting timestamp or now() as a field default value as apposed to mySQL where it is supported. The solution / work around if you're using mySQL is to add a query to your .install that modifies the field default to now() where the field type is int. See the Data Type doc page for further discussion: http://drupal.org/node/159605

wouter99999’s picture

The following keys are missing in the documentation:
mysql_engine’, ‘mysql_character_set‘ and ‘collation’ .
These replace 'mysql_suffix' that was used in Drupal 6.

fmr’s picture

https://api.drupal.org/api/drupal/includes!database!mysql!schema.inc/7
Luckily the default character set is UTF8, which you would only need to change in rare circumstances.

minorOffense’s picture

db_insert, db_update and db_merge don't seem to trigger the auto serialization. But if you use drupal_write_record it works.

Similarly, db_select doesn't seem to unserialize the data. But I haven't tried db_query recently...

If I'm wrong, of if this is a bug please post further comments.

azinck’s picture

I don't know if this is a bug but I can confirm. Data inserted into field tables defined by hook_field_schema() doesn't get auto-serialized by field_sql_storage_field_storage_write() either.

tannerg’s picture

It is worth noting that you have to add a length when adding varchar fields. Otherwise, the sql that gets written is invalid and you get somewhat cryptic errors from the sql engine.

It would be cool if varchar defaulted to a length of 255, but it does not.

reescott’s picture

Note that defining foreign keys will not change anything in your MySQL database, it is for documentation purposes only. Hope this helps someone like me who is scratching their head when looking in the database and not seeing any foreign key relationships.

AubreyR’s picture

Hi reescott:
I'm fairly new to drupal (about 7 weeks in) and I did exactly this, and asked myself that exact question. But now I'm wondering why there are no relationship in the underlying DB? Is it because Drupal is supposed to be DB agnostic? I never even was exposed to non-relational databases in school (although I do have an advanced databases course coming up that I hope delves into this topic).