Declarative Schema in Magento 2

Declarative schema is a new concept introduced in Magento 2.3. It makes the smooth process of database schema operations with fewer coding and execution steps. Before Magento 2.3, the following script files are used in the Setup directory of the module to perform DB management:

InstallSchema.php: runs script when module is installed to the setup database structure
UpgradeSchema.php: runs script when module is upgraded to upgrade database structure
InstallData.php: runs script when module is installed to initialize data for database tables
UpgradeData.php: runs script when module is upgraded to upgraded data for database tables
Recurring.php: runs script after each schema installation and degradation
Uninstall.php: runs script when the module is uninstalled

The problem with the above approach was that the database will apply only what is written for the latest module version when we upgrade the Magento version. That means the DB changes for the previous module version will be lost and developers need to manage the schema version and module version. To solve this problem, Magento 2.3 introduced the Declarative Schema. The database tables column addition, deletion, update, and table structure changes are managed automatically using Declarative Schema without performing redundant operations. The developers don’t need to write the script for each version. Using this approach the data would be deleted when the module is uninstalled.

This blog describes how to manage database table operations using Declarative schema. db_schema.xml file is used to define the module’s database structure. The path for this file would be VendorName/ModuleName/etc/db_schema.xml. The following nodes are used in this file:

<schema> node

The schemanode is a top-level node located in /vendor/magento/framework/Setup/Declaration/Schema/etc/schema.xsd

<table> node

The tablenode represents a table of a database. db_schema.xml file contains more than once table nodes. Following are the attributes of table node:

name: the name of the table
engine: SQL engine. This value must be innodb or memory
resource: the database shard on which to install the table. This value must be default, checkout, or sales
comment: table comment

The tablenode contains three subnodes:

<column> subnode

A columnsubnode is defined as a column of a table, each column has required its own declaration. It has the following attributes:

xsi:type: the column type would be blob (includes blob, mediumblob, longblob), boolean, date, datetime, decimal, float, int (includes smallint, bigint, tinyint), json, real (includes decimal, float, double, real), smallint, text (includes text, mediumtext, longtext), timestamp, varbinary, varchar
name: the name of the column
unsigned: for numeric data types specifies whether the column can contain positive and negative values or only positive values
nullable: specifies whether the column can be nullable
identity: specifies whether a column is auto-incremented
length: the length of a column can be used for char, varchar, and varbinary data types
comment: the comment of the column
default: the default value for the column. It should have the same data type as defined in xsi:type attribute
disabled: disables or deletes the declared table, column, constraint, or index
padding: the size of an integer type column

<constraint> subnode

A constraintsubnode can have the following attributes:

type: the value should one of primary, unique, or foreign
referenceId: A custom identifier that is used only for relation mapping in the scope of db_schema.xml files. The real entity in the database has a system-generated name. The most convenient way to set the value of this attribute is to use the value that is written in the module’s db_schema_whitelist.json file when you run the generate-whitelist command.

<index> subnode

An indexsubnode is the same as constraintsubnode, while constraints are used for defining limitations, indexes are used for speeding up DQL operations. The indexsubnode contains the following attributes:

referenceId: A custom identifier that is used only for relation mapping in the scope of db_schema.xml files. The real entity in the database has a system-generated name. The most convenient way to set the value of this attribute is to use the value that is written in the module’s db_schema_whitelist.json file when you run the generate-whitelist command.
indexType: the value must be btree, fulltext, or hash

Let’s take an example of creating a new table named mage2_blogs.

Step 1: Create etc/db_schema.xml

You need to create db_schema.xml in your module at this path: VendorName/ModuleName/etc/db_schema.xml. As per our example, the file path would be Mage2/Blog/etc/db_schema.xmland add the following code:

<?xml version="1.0"?>

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="mage2_blogs" resource="default" engine="innodb" comment="Mage2 Blogs Table">
        <column xsi:type="smallint" name="blog_id" unsigned="false" nullable="false" identity="true"
                comment="Entity ID"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Blog Title"/>
        <column xsi:type="mediumtext" name="content" nullable="true" comment="Blog Content"/>
        <column xsi:type="varchar" name="image" nullable="false" length="255" comment="Blog Image"/>
        <column xsi:type="int" name="sort_order" unsigned="true" nullable="false" identity="false"
                default="0" comment="Sort Order"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
                comment="Blog Creation Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="blog_id"/>
        </constraint>
        <index referenceId="CMS_BLOCK_TITLE_CONTENT" indexType="fulltext">
            <column name="title"/>
            <column name="content"/>
        </index>
    </table>
</schema>

Step 2: Generate db_schema_whitelist.json

You need to run the following command to generate the db_schema_whitelist.json file for a specific module:

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

This command will generate the db_schema_whitelist.json file at the same place where db_schema.xml was created, VendorName/ModuleName/etc/db_schema_whitelist.json. As per our example, the file path would be Mage2/Blog/etc/db_schema_whitelist.json. This file contains table structure in the following way:

{
    "mage2_blogs": {
        "column": {
            "blog_id": true,
            "title": true,
            "content": true,
            "image": true,
            "sort_order": true,
            "created_at": true
        },
        "index": {
            "MAGE2_BLOGS_TITLE_CONTENT": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

Step 3: Upgrade the module

Now you need to run setup:upgrade command to create a table in the database.

bin/magento setup:upgrade

Output:

Once you run setup:upgrade command, the mage2_blogs table will be created in the database like the following screenshot of PhpStorm editor.

We hope this blog may understandable and useful to you. You can email us at mage2developer@gmail.com if we missed anything or want to add any suggestions. We will respond to you as soon as possible. Happy to help 🙂