Declarative schema in Magento 2.3+ - Paul Meștereagă
Magento 2

Declarative schema in Magento 2.3+

In this tutorial we will learn the how to configure a declarative schema in Magento 2.3, perform database operations using declarative schema, create a schema whitelist and create data and schema patches.

author

Paul Mestereaga

March 16, 2020

Why declarative schema?

Declarative schema in Magento 2.3 was introduced to simplify the Magento installation and upgrade processes. This replaces the use of various PHP classes such as InstallSchema, UpgradeSchema, InstallData and UpgradeData. For previous versions of Magento check this post.

The new declarative schema approach allows to:

  • Declare the final desired state of the database – the system will adjust to it automatically
  • Remove redundant operations
  • No longer being forced to write scripts for each new module version

In this tutorial we will learn the how to:

  1. Configure a declarative schema in Magento 2.3
  2. Perform data base operations using declarative schema
  3. Create a schema whitelist
  4. Create data and schema patches

Configure a declarative schema in Magento 2.3+

As mentioned before declarative schema in Magento 2.3 was introduced to simplify the Magento installation and upgrade processes.

Declarative setup is based on database structure declarations. The main component of this setup is the schema file which declares what the database structure should be. The differences between the current table structure and what it should be are automatically handled by Magento.

For your custom module the declarative schema file needs to be defined in <Vendor>/<Module>/etc/db_schema.xml

For instance the following is an example extracted from the Magento/Sales/etc/db_schema.xml file, and defines the sales_shipment_item table:

<?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="sales_shipment_item" resource="sales" engine="innodb" comment="Sales Flat Shipment Item">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity Id"/>
        <column xsi:type="int" name="parent_id" padding="10" unsigned="true" nullable="false" identity="false" comment="Parent Id"/>
        <column xsi:type="decimal" name="row_total" scale="4" precision="20" unsigned="false" nullable="true" comment="Row Total"/>
        <column xsi:type="decimal" name="price" scale="4" precision="20" unsigned="false" nullable="true" comment="Price"/>
        <column xsi:type="decimal" name="weight" scale="4" precision="12" unsigned="false" nullable="true" comment="Weight"/>
        <column xsi:type="decimal" name="qty" scale="4" precision="12" unsigned="false" nullable="true" comment="Qty"/>
        <column xsi:type="int" name="product_id" padding="11" unsigned="false" nullable="true" identity="false" comment="Product Id"/>
        <column xsi:type="int" name="order_item_id" padding="11" unsigned="false" nullable="true" identity="false" comment="Order Item Id"/>
        <column xsi:type="text" name="additional_data" nullable="true" comment="Additional Data"/>
        <column xsi:type="text" name="description" nullable="true" comment="Description"/>
        <column xsi:type="varchar" name="name" nullable="true" length="255" comment="Name"/>
        <column xsi:type="varchar" name="sku" nullable="true" length="255" comment="Sku"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="SALES_SHIPMENT_ITEM_PARENT_ID_SALES_SHIPMENT_ENTITY_ID"
                    table="sales_shipment_item" column="parent_id" referenceTable="sales_shipment"
                    referenceColumn="entity_id" onDelete="CASCADE"/>
        <index referenceId="SALES_SHIPMENT_ITEM_PARENT_ID" indexType="btree">
            <column name="parent_id"/>
        </index>
    </table>
 
</schema>

Let’s break down all the components of the file.

Top node

The schema node defines the location of the schema.xsd file.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<!-- table nodes here -->
</schema>

Table Node

Each schema file should contain at least one table node. This node represents a table in the database. A table node can contain the following attributes: name, engine (innodb or memory), resource (default, checkout, or sales), comment.

table node can contain three types of subnodes:

  • column
  • constraint
  • index

Column

The column subnode is straight forward, it defines the column in the table. For each column in the table you need to define a subnode.

Example:

<column xsi:type="varchar" name="sku" nullable="true" length="255" comment="Sku"/>

Constraint

The constraint subnode is used to define the constrains on a column, for example to define a primary or foreign key. It has the mandatory attributes type (One of primaryunique, or foreign) and referenceId.

Example:

<constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
</constraint>
<constraint xsi:type="foreign" referenceId="SALES_SHIPMENT_ITEM_PARENT_ID_SALES_SHIPMENT_ENTITY_ID" table="sales_shipment_item" column="parent_id" referenceTable="sales_shipment" referenceColumn="entity_id" onDelete="CASCADE"/>

Index

Constraints are used for defining limitations, but indexes are used for speeding up DQL operations.

Example:

<index referenceId="SALES_SHIPMENT_ITEM_PARENT_ID" indexType="btree">
        <column name="parent_id"/>
</index>

Perform database operations using declarative schema in Magento 2.3+

This section shows how to perform common database operations using declarative schema. In the examples we use git diff to illustrate how to perform these tasks.

Create a table

Add the table node with it’s content.

<?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="sample_declarative_table">
+        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
+        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
+        <constraint xsi:type="primary" referenceId="PRIMARY">
+            <column name="id_column"/>
+        </constraint>
+    </table>
</schema>

Drop a table

Just remove the table node.

<?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="sample_declarative_table">
-        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
-        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
-        <constraint xsi:type="primary" referenceId="PRIMARY">
-            <column name="id_column"/>
-        </constraint>
-    </table>
</schema>

Rename a table

Specifiy onCreate attribute on the table node.

<?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="new_declarative_table" onCreate="migrateDataFromAnotherTable(sample_declarative_table)">
-    <table name="sample_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

Add a column to table

Simply add a new column subnode to the table node.

<?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="new_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
+       <column xsi:type="varchar" name="subtitle" nullable="false" length="255" comment="Subtitle"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

Drop a column from a table

Simply remove a column subnode to the table node.

<?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="new_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
-       <column xsi:type="varchar" name="subtitle" nullable="false" length="255" comment="Subtitle"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

Change the column type

Changing varchar type to text for title column.

<?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="new_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
-        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
+        <column xsi:type="text" name="title" nullable="false" length="255" comment="Title"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

Rename a column

Delete the original column declaration and create a new one. In the new column declaration, use the onCreate attribute to specify which column to migrate data from.

<?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="new_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
-        <column xsi:type="text" name="title" nullable="false" length="255" comment="Title"/>
+        <column xsi:type="text" name="main_title" nullable="false" length="255" comment="Title" onCreate="migrateDataFrom(title)"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

Add an index

Add the index subnode to the table node

<?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="new_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="text" name="main_title" nullable="false" length="255" comment="Title"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
+       <index referenceId="INDEX_ID" indexType="btree">
+           <column name="entity_id"/>
+       </index>
    </table>
</schema>

Create a foreign key

Add a constraint subnode of type foreign.

<?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="new_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="text" name="main_title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
+       <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="new_declarative_table"
+               column="severity" referenceTable="severities" referenceColumn="severity_identifier"
+               onDelete="CASCADE"/>
    </table>
</schema>

Drop a foreign key

Remove the relevant constraint subnode of type foreign.

<?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="new_declarative_table">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="text" name="main_title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
-       <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="new_declarative_table"
-               column="severity" referenceTable="severities" referenceColumn="severity_identifier"
-               onDelete="CASCADE"/>
    </table>
</schema>

Create a schema whitelist 

To be able to maintain backward compatibility, declarative schema does not automatically delete tables, columns or keys not defined in db_schema.xml.

For that reason the whitelist was introduces. The file db_schema_whitelist.json shows a history of all tables, columns and keys added using declarative schema and for which drop operations are required.

You need to run the following command to generate it:

bin/magento setup:db-declaration:generate-whitelist [options]

[options] can be:

--module-name[=MODULE-NAME] specifies which module to generate a whitelist for. If no module name is specified, then the default behavior is to generate a whitelist for all modules. You can also explicitly set --module-name=all.

It is recommended to generate a new whitelist for each release that contains changes in the db_schema.xml.

Data and schema patches

A data patch is a class that contains data modification instructions. It is defined in a <Vendor>/<Module_Name>/Setup/Patch/Data/<Patch_Name>.php file and implements \Magento\Framework\Setup\Patch\DataPatchInterface.

A schema patch contains custom schema modification instructions. These modifications can be complex. It is defined in a <Vendor>/<Module_Name>/Setup/Patch/Schema/<Patch_Name>.php file and implements \Magento\Framework\Setup\Patch\SchemaPatchInterface.

Unlike the declarative schema approach, patches will only be applied once. A list of applied patches is stored in the patch_list database table. An unapplied patch will be applied when running the setup:upgrade from the Magento CLI.

Data patches have three important methods: getDependencies, getAliases, apply

Here is an example

<?php
namespace Vendor\Module\Setup\Patch\Data;
 
class SamplePatch implements DataPatchInterface
{
        /**
         * @var \Magento\Framework\Setup\ModuleDataSetupInterface
         */
        private $moduleDataSetup;
 
        /**
         * @param \Magento\Framework\Setup\ModuleDataSetupInterface $moduleDataSetup
         */
        public function __construct(
            \Magento\Framework\Setup\ModuleDataSetupInterface $moduleDataSetup
        ) {
            /**
             * If before, we pass $setup as argument in install/upgrade function, from now we start
             * inject it with DI. If you want to use setup, you can inject it, with the same way as here
             */
            $this->moduleDataSetup = $moduleDataSetup;
        }
        /**
         * {@inheritdoc}
         */
        public function apply()
        {
            $this->moduleDataSetup->getConnection()->startSetup();
            //The code that you want apply in the patch
            //Please note, that one patch is responsible only for one setup version
            //So one UpgradeData can consist of few data patches
            $this->moduleDataSetup->getConnection()->endSetup();
        }
 
        /**
         * {@inheritdoc}
         */
        public static function getDependencies()
        {
            /**
             * This is dependency to another patch. Dependency should be applied first
             * One patch can have few dependencies
             * Patches do not have versions, so if in old approach with Install/Ugrade data scripts you used
             * versions, right now you need to point from patch with higher version to patch with lower version
             * But please, note, that some of your patches can be independent and can be installed in any sequence
             * So use dependencies only if this important for you
             */
            return [
                SomeDependency::class
            ];
        }
 
 
 
        /**
         * {@inheritdoc}
         */
        public function getAliases()
        {
            /**
             * This internal Magento method, that means that some patches with time can change their names,
             * but changing name should not affect installation process, that's why if we will change name of the patch
             * we will add alias here
             */
            return [];
        }
}

Additionally your class can implement Magento\Framework\Setup\Patch\PatchRevertableInterface and have the revert method, that will revert all operations from apply method.

public function revert()
{
      $this->moduleDataSetup->getConnection()->startSetup();
      //Here should go code that will revert all operations from `apply` method
      //Please note, that some operations, like removing data from column, that is in role of foreign key reference
      //is dangerous, because it can trigger ON DELETE statement
      $this->moduleDataSetup->getConnection()->endSetup();
}

Another interface that can be used for backward compatibility with versioned style module installation is Magento\Framework\Setup\Patch\PatchVersionInterface

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Magento\Framework\Setup\Patch;
 
/**
 * For backward compatibility with versioned style module installation. Deprecated since creation.
 *
 * @deprecated 102.0.0
 */
interface PatchVersionInterface
{
    /**
     * This version associate patch with Magento setup version.
     * For example, if Magento current setup version is 2.0.3 and patch version is 2.0.2 then
     * this patch will be added to registry, but will not be applied, because it is already applied
     * by old mechanism of UpgradeData.php script
     *
     * @return string
     * @deprecated 102.0.0 since appearance, required for backward compatibility
     */
    public static function getVersion();
}

Conclusion

Magento 2 is heading to a new way of handling database updates and we need to start using them as soon as possible. If this tutorial was helpful please let me know in the comments section bellow. If you have any other questions let me know how can I help.

Please check also the official documentation for declarative schema here.

Is your Magento slow?

Wondered how you can make your Magento load faster? Wonder no more. Here is a FREE step by step guide that helps you.

Related Posts

How to create a product attribute using Data Patches in Magento 2.3

How to create a product attribute using Data Patches in Magento 2.3

26 Mar 2020

|

Paul Mestereaga

This is a short tutorial in which we will learn to create a product attribute using Data Patches in Magento 2.3

Is Magento 2 slow on localhost?

Is Magento 2 slow on localhost?

20 Feb 2020

|

Paul Mestereaga

A fresh install of Magento 2 slow on localhost? Here are some steps you can take to make your Magento 2 faster on your localhost.

optimize-magento-preview

FREE!

Magento 2
Optimization guide

Wondered how you can make your Magento load faster? Wonder no more. Here is a step by step guide that helps you.