Declarative schema

A module stores its data in its own tables. In Magento a table is not created with an SQL query: it is described in an XML file, and Magento brings the database to the described state. This approach is called declarative schema.

Where the file lives

The schema is described in the module's etc/db_schema.xml file. The file is created by hand. Starting with this lesson, the Acme_Hello module stores product reviews.

app/code/Acme/Hello/
└── etc/
    ├── db_schema.xml               new file
    ├── db_schema_whitelist.json    created by a command
    └── module.xml

Describing a table

<?xml version="1.0"?>
<!-- app/code/Acme/Hello/etc/db_schema.xml -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="acme_hello_review" resource="default" engine="innodb" comment="Product reviews">
        <column xsi:type="int" name="review_id" unsigned="true" nullable="false" identity="true"/>
        <column xsi:type="int" name="product_id" unsigned="true" nullable="false"/>
        <column xsi:type="varchar" name="author" length="100" nullable="false"/>
        <column xsi:type="smallint" name="rating" unsigned="true" nullable="false" default="5"/>
        <column xsi:type="text" name="content" nullable="true"/>
        <column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP"/>
 
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="review_id"/>
        </constraint>
    </table>
</schema>

The file describes the acme_hello_review table with six columns and a primary key. By convention a table name starts with the module name. The identity="true" attribute makes the column auto-incrementing.

Applying

bin/magento setup:db-declaration:generate-whitelist --module-name=Acme_Hello
bin/magento setup:upgrade

The first command creates the etc/db_schema_whitelist.json file, the second creates the table. You can check the result in any MySQL client: an empty acme_hello_review table appears in the database.

Column types

Changing a table

The file describes the desired state, not steps. To add a column, just add a line:

<!-- app/code/Acme/Hello/etc/db_schema.xml -->
<column xsi:type="boolean" name="is_approved" nullable="false" default="false"/>

After running the two commands again, Magento compares the file with the database and adds the is_approved column. A line removed from the file leads to the column being dropped together with its data.

An index and a foreign key

Inside the table tag you add indexes for fast lookups and foreign keys that link to other tables:

<!-- app/code/Acme/Hello/etc/db_schema.xml -->
<index referenceId="ACME_HELLO_REVIEW_PRODUCT_ID" indexType="btree">
    <column name="product_id"/>
</index>
 
<constraint xsi:type="foreign" referenceId="ACME_HELLO_REVIEW_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID"
            table="acme_hello_review" column="product_id"
            referenceTable="catalog_product_entity" referenceColumn="entity_id"
            onDelete="CASCADE"/>

The index speeds up finding reviews by product. The foreign key links a review to the products table, and onDelete="CASCADE" deletes reviews together with the product. The type and the unsigned flag of the column must match those of the column it refers to.

The whitelist

The db_schema_whitelist.json file lists the tables and columns the module manages through declarative schema. It is not written by hand: run the generate-whitelist command after every change to db_schema.xml and keep the file together with the module code.

Common mistake

A column was removed from db_schema.xml but stayed in the database. Magento drops and changes only what is listed in the whitelist: this protects against accidental data loss. Regenerate db_schema_whitelist.json before removing the line from the schema and run setup:upgrade after.