The location of the schema.xsd file is: /vendor/magento/framework/Setup/Declaration/Schema/etc/schema.xsd

What is Table node?

We can create more than one table in the same db_schema.xml file, each table node creates a new table in the database. A table node can contain the following attributes:

• 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 table node contains three different types of subnodes:
• Column
• Constraints
• Index

What is Column node?

The column node defines inside the table node, each column node has its own declaration. A column node can contain the following attributes:

• Type: the column type should contain one of the blob (includes blob, mediumblob, longblob), boolean, date, datetime, int (includes smallint, bigint, tinyint), real (includes decimal, float, double, real), text (includes text, mediumtext, longtext), timestamp, varbinary, and varchar.
• Name: name of the column
• Padding: indicates the size of the integer column
• Unsigned: indicates if the column contains positive and negative values or only positive values
• Nullable: indicates if the column can be nullable
• Comment: indicates the comment of the column
• Length: indicates the length of a column

How to Rename a table?

  <table name="declarative_table">
   Changed as below
<table name="new_declarative_table" onCreate="migrateDataFromAnotherTable(declarative_table)">

How to Add a column to table?

The following example adds the date_closed column.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column .../> ...
+       <column xsi:type="timestamp" name="date_closed" padding="10" comment="Time of event"/>
        <constraint ..>...</constraint>
    </table>
</schema>

When adding a new column into table, remember to generate the db_schema_whitelist.json file.

How to Drop a column from a table?

The following example removes the date_closed column by deleting its column node. To drop a column declared in another module, redeclare it with the disabled attribute set to true.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="timestamp" name="time_occurred" padding="10" comment="Time of event"/>
-       <column xsi:type="timestamp" name="date_closed" padding="10" comment="Time of event"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

It is possible to drop a column only if it exists in the db_schema_whitelist.json file.

How to Change the column type?

The following example changes the type of the title column from varchar to tinytext.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column .../>
        <column .../>
-       <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
+       <column xsi:type="tinytext" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="timestamp" name="time_occurred" padding="10" comment="Time of event"/>
        <constraint ...>....</constraint>
    </table>
</schema>

How to Rename a column?

To 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. Use the following construction to migrate data from the same table.

onCreate="migrateDataFrom(entity_id)"

When renaming a column, remember to regenerate the db_schema_whitelist.json file so it contains the new name in addition to the old one.

How to Add an index?

The following example adds the INDEX_SEVERITY index to the declarative_table table.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column .... />
        <constraint ...> <column name="id_column"/> </constraint>
+       <index referenceId="INDEX_SEVERITY" indexType="btree">
+           <column name="severity"/>
+       </index>
    </table>
</schema>

How to Create a foreign key?

In the following example, the selected constraint node defines the characteristics of the FL_ALLOWED_SEVERITIES foreign key.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column ../> ...
        <constraint...> ...    </constraint>
+     <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="declarative_table"
+       column="severity" referenceTable="severities" referenceColumn="severity_identifier"
+       onDelete="CASCADE"></constraint>
    </table>
</schema>

How to Drop a foreign key?

The following example removes the FL_ALLOWED_SEVERITIES foreign key by deleting its constraint node. To drop a constraint declared in another module, redeclare it with the disabled attribute set to true.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
      ......
-  <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="declarative_table"
-  column="severity" referenceTable="severities" referenceColumn="severity_identifier"
-   onDelete="CASCADE"></constraint>
    </table>
</schema>

How to Recreate a foreign key?

In this example, Module A defines a new table with primary key id_column. Module B declares its own schema, in which it creates a new column (new_id_column) and changes the primary index to this column. Module B disables the original primary key and sets a new primary key with a referenceId value that is different from PRIMARY. Although this value is different, the real name of the primary key in the database remains PRIMARY.

Module A declaration

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

Module B declaration

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="new_id_column" padding="10" unsigned="true" nullable="false"
                comment="New Entity Id"/>
        <constraint xsi:type="primary" referenceId="PRIMARY" disabled="true"></constraint>
        <constraint xsi:type="primary" referenceId="NEW_PRIMARY">
            <column name="new_id_column"/>
        </constraint>
    </table>
</schema>

What is Data patch?

A class that contains data modification instructions. It can have dependencies on other data or schema patches.

What is Revertable data patch?

A patch that can be reverted as a module or path is uninstalled or deleted. Revertable operations are Data Query Language (DQL) and Data Manipulation Language (DML) operations: INSERT, UPDATE.

What is Migration?

A type of non-revertable data patch that can be applied, but not reverted. Any complex operation, such as one that contains an application layer (for example, Collections or Serializers) is non-revertable. SQL delete operations are non-revertable because they can cause triggering.

What is Schema patch & allow operations?

A class that contains custom schema modification instructions. Schema patches are used along with declarative schema, but these patches allow complex operations such as:

• Adding triggers, stored procedures, functions

• Performing data migration with inside DDL operations

• Renaming tables, columns, and other entities

• Adding partitions and options to a table

Magento 2 Certified Associate Developer Exam

(4.4 Demonstrate an ability to use declarative schema)

How do you add a column using declarative schema?

The following example adds the date_closed column.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column .../> ...
+       <column xsi:type="timestamp" name="date_closed" padding="10" comment="Time of event"/>
        <constraint ..>...</constraint>
    </table>
</schema>

When adding a new column into table, remember to generate the db_schema_whitelist.json file.

How do you modify a table added by another module?

Suppose we want add new column name=”referred_by” on core table customer_entity
etc/db_schema.xml

--<?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">
--This below content oly if exist declaration
    <table name="customer_entity">
    <column xsi:type="int" name="referred_by" padding="10" unsigned="true" nullable="false"
    comment="Referred By"/>
    </table>
--</schema>

Then run following command to generate db_schema_whitelist.json

php bin/magento setup:db-declaration:generate-whitelist --module-name=BDC_Declarative
php bin/magento setup:upgrade --dry-run=1 --keep-generated
php bin/magento setup:upgrade

You can modify code according to your requirement.

How do you delete a column?

The following example removes the date_closed column by deleting its column node. To drop a column declared in another module, redeclare it with the disabled attribute set to true.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="timestamp" name="time_occurred" padding="10" comment="Time of event"/>
-       <column xsi:type="timestamp" name="date_closed" padding="10" comment="Time of event"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

It is possible to drop a column only if it exists in the db_schema_whitelist.json file.

“Declarative Schema” vs “Extension Attributes” ?

“Declarative Schema” is used when you need to create a NEW table inside Magento. With declarative schema you have the advantages of mutations.
“Extension Attributes” are used to add new fields inside an EXISTING table. In this way you don’t extend the original model.So in the above example, the best approach is to use Extension Attributes. magento-2-what-are-extension-attributes

How do you add an index ?

Add an index: The following example adds the INDEX_SEVERITY index to the declarative_table table.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
        <column xsi:type="tinytext" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="timestamp" name="time_occurred" padding="10" comment="Time of event"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
+       <index referenceId="INDEX_SEVERITY" indexType="btree">
+           <column name="severity"/>
+       </index>
    </table>
</schema>

How do You add foreign key using declarative schema?

• Create a foreign key
  In the following example, the selected constraint node defines the characteristics of the FL_ALLOWED_SEVERITIES foreign key.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="timestamp" name="time_occurred" padding="10" comment="Time of event"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
+       <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="declarative_table"
+               column="severity" referenceTable="severities" referenceColumn="severity_identifier"
+               onDelete="CASCADE"></constraint>
    </table>
</schema>

• Drop a foreign key
  The following example removes the FL_ALLOWED_SEVERITIES foreign key by deleting its constraint node. To drop a constraint declared in another module, redeclare it with the disabled attribute set to true.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="timestamp" name="time_occurred" padding="10" comment="Time of event"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
-       <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="declarative_table"
-               column="severity" referenceTable="severities" referenceColumn="severity_identifier"
-               onDelete="CASCADE"></constraint>
    </table>
</schema>

It is possible to drop a foreign key only if it exists in the db_schema_whitelist.json file.

• Recreate a foreign key
  In this example, Module A defines a new table with primary key id_column. Module B declares its own schema, in which it creates a new column (new_id_column) and changes the primary index to this column. Module B disables the original primary key and sets a new primary key with a referenceId value that is different from PRIMARY. Although this value is different, the real name of the primary key in the database remains PRIMARY.

Module A declaration

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

Module B declaration

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="new_id_column" padding="10" unsigned="true" nullable="false"
                comment="New Entity Id"/>
        <constraint xsi:type="primary" referenceId="PRIMARY" disabled="true"></constraint>
        <constraint xsi:type="primary" referenceId="NEW_PRIMARY">
            <column name="new_id_column"/>
        </constraint>
    </table>
</schema>

How do you manipulate data using data patches?

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

What is the purpose of schema patches?

A schema patch contains custom schema modification instructions. These modifications can be complex. It is defined in a //Setup/Patch/Schema/.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.

Magento 2 Certified Professional Developer exam

4.4 Demonstrate an ability to use declarative schema

• How do you add a column using declarative schema?
• How do you modify a table added by another module? ||
• How do you delete a column? ||
• How do you add an index or foreign key using declarative schema? ||
• How do you manipulate data using data patches?
• What is the purpose of schema patches?

How to manipulate columns and keys using declarative schema?

• Add a column to table
  The following example adds the date_closed column.
  

• Drop a column from a table
  The following example removes the date_closed column by deleting its column node.
  To drop a column declared in another module, redeclare it with the disabled attribute set to true.
  

• Change the column type
  The following example changes the type of the title column from varchar to tinytext.
  

• Rename a column
  To rename a column, delete the original column declaration and create a new one.
  In the new declaration, use the onCreate attribute to specify which column to migrate data from.
  Use the following construction to migrate data from the same table.
  onCreate=”migrateDataFrom(entity_id)”

To migrate data from another table, specify a value similar to the following:
onCreate=”migrateDataFromAnotherTable(catalog_category_entity,entity_id)”

• Add an index
  The following example adds the INDEX_SEVERITY index to the table_name table.

• Create a foreign key
  In the following example, the selected constraint node defines the characteristics of the FL_ALLOWED_SEVERITIES foreign key.

• Drop a foreign key
  The following example removes the FL_ALLOWED_SEVERITIES foreign key by deleting its constraint node.
  To drop a constraint declared in another module, redeclare it with the disabled attribute set to true.
  

What is the purpose of whitelisting?

Purpose of whitelisting:
You will not be able to run a declarative mode without creating a schema whitelist.
Since backward compatibility must be maintained, declarative schema doesn’t automatically delete database tables, columns or keys not defined in db_schema.xml.This is one of the reasons we have db_schema_whitelist.json. It shows a history of all tables, columns and keys added with declarative schema and it’s required for drop operations.

Note: it is recommended to generate a new whitelist for every release for the double-check purposes. Before running the upgrade command you need to add your schema to db_whitelist_schema.json file by running the following command. For that, you need a //etc/db_schema_whitelist.json file that will store all the content added with declarative schema. To generate this file, run:

php bin/magento setup:db-declaration:generate-whitelist [options]
php bin/magento setup:db-declaration:generate-whitelist --module-name=vendor_module
php bin/magento setup:db-declaration:generate-whitelist --module-name=BDC_Declarative
php bin/magento setup:upgrade --dry-run=1 --keep-generated

Now, there are db_whitelist_schema.json file will be create in /vendor/module/etc folder.

There are options you can add at the end of that command. For instance, you can use “–module-name=YourModule” to specify the module you want to generate a whitelist for. Similarly, you could also set “–module-name=all” although it will generate a whitelist for all modules by default.

How to use Data and Schema patches?

• Data Patches:
  
  

• Schema Patches :
  

How to manage dependencies between patch files?

schema patch contains custom schema modification instructions. These modifications can be complex. It is defined in a //Setup/Patch/Schema/.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.

Optionally, if you plan to enable rollback for your patch during module uninstallation, then you must implement \Magento\Framework\Setup\Patch\PatchRevertableInterface.

Old scripts will work with new versions of Magento. However, if you want to convert your old scripts to the new format, implement \Magento\Framework\Setup\Patch\PatchVersionInterface. This interface allows you to specify the setup version of the module in your database. If the version of the module is higher than or equal to the version specified in your patch, then the patch is skipped. If the version in the database is lower, then the patch installs

How to EAV attributes Create via Declarative Schema ?

In Magento 2.3 and above, you can create attribute via DataPatch. For example, create a file: Vendor_Module\Setup\Patch\Data\ApplyNewAttribute.php

<?php
namespace Vendor\Module\Setup\Patch\Data;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\Patch\PatchVersionInterface;
use Magento\Eav\Setup\EavSetup;
use Magento\Eav\Setup\EavSetupFactory;
/**
 * Class ApplyNewAttribute
 * @package Vendor\Module\Setup\Patch\Data
 */
class ApplyNewAttribute implements DataPatchInterface, PatchVersionInterface
{
    /**
     * @var ModuleDataSetupInterface
     */
    private $moduleDataSetup;
    /**
     * @var EavSetupFactory
     */
    private $eavSetupFactory;
    /**
     * ApplyNewAttribute constructor.
     *
     * @param ModuleDataSetupInterface $moduleDataSetup
     * @param EavSetupFactory $eavSetupFactory
     */
    public function __construct(
        ModuleDataSetupInterface $moduleDataSetup,
        EavSetupFactory $eavSetupFactory
    ) {
        $this->moduleDataSetup = $moduleDataSetup;
        $this->eavSetupFactory = $eavSetupFactory;
    }
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function apply()
    {
        /** @var EavSetup $eavSetup */
        $eavSetup = $this->eavSetupFactory->create(['setup' => $this->moduleDataSetup]);
        $eavSetup->addAttribute(
            \Magento\Catalog\Model\Product::ENTITY,
            /** Add your attribute here.……….*/
       );
        $setup->endSetup();
    }
}

bsscommerce

Ref

• devdocs

• onilabblog