Scroll to top
© Copyright 2022 StageBit. | All Rights Reserved
Share
  • November 30, 2020
  • 4 MIN READ

Declarative Schema in Magento 2.3

~Written By Mitali Kundale

Magento 2
 

When I was creating a module I notice new thing in Magento 2.3. Magento 2.3 core modules used declarative schema approach instead of setup upgrade script. This is new recommended approach in Magento 2.3 and above.

Declarative schema reduces the unnecessary work of writing upgrade script for just little change in database. For example, If you want to rename column , you need not write setup upgrade schema script in next version of that module. You can change the schema in db_schema.xml and run setup upgrade command.

This allows the developer to declare final state of the database. As a result it will reduces the redundant operations. It reduces the process of writing code in next version. Because of this approach you can delete data when you uninstall the module.

How to create table using Declarative Schema in Magento 2.3

In this blog, process of creating table using declarative schema is explained.

First of all, Create db_schema.xml file under the “StageBit/DeclarativeSchema/etc” directory and write the following code.

<table name="my_custom_table" resource="default" engine="innodb" comment="My Custom Table">
    <column xsi:type="int" name="my_id" padding="11" unsigned="false" nullable="false" identity="true" comment="My ID"/>
    <column xsi:type="varchar" name="name" padding="11" nullable="false" comment="Name"/>
    <column xsi:type="datetime" name="updated_date" on_update="false" nullable="true" comment="Updated Date"/>
    
    <constraint xsi:type="primary" referenceId="PRIMARY">
       <column name="my_id"/>
   </constraint>
    <index referenceId="VENDOR_MODULE_ENTITY_INT_MY_ID" indexType="btree">
       <column name="my_id"/>
   </index>
</table>

Declarative Schema XML Node Description:

  • table: Each table node represent the table in database. Table node contains 3 types of sub node i.e column, constraint, index.
    When you run setup:upgrade command. It will create the table “my_custom_table”.
  • column: It defines the column in table. It requires own declaration.
  • constraint: It is used to defined the rules to allow or restrict what values to be stored in column. It has following attributes and values:
    1. type: One of primary, unique, or foreign.
      The primary and unique constraints are called “internal” constraints, because they applied only to the scope of the table where they are created.
    2. referenceId: A custom identifier that is used only for relation mapping in the scope of db_schema.xml files.
  • index: Indexes are used for speeding up DQL operations. The following attributes define an index:
    1. referenceId: A custom identifier that is used only for relation mapping in the scope of db_schema.xml files.
    2. indexType: The value must be btree, fulltext, or hash.

Adding New column to existing table :

The following example adds the “email” column to “my_custom_table”.

<table name="my_custom_table" resource="default" engine="innodb" comment="My Custom Table">
    <column xsi:type="int" name="my_id" padding="11" unsigned="false" nullable="false" identity="true" comment="My ID"/>
    <column xsi:type="varchar" name="name" padding="11" nullable="false" comment="Name"/>
    <column xsi:type="datetime" name="updated_date" on_update="false" nullable="true" comment="Updated Date"/>
    <column xsi:type="varchar" name="email" padding="11" nullable="false" comment="Email"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
       <column name="my_id"/>
   </constraint>
    <index referenceId="VENDOR_MODULE_ENTITY_INT_MY_ID" indexType="btree">
       <column name="my_id"/>
   </index>
</table>

Remove column from existing table :

If you want to remove the existing column then you can set disabled attribute to that column or simply delete that node. The following example remove the “email” column from “my_custom_table”.

    <column xsi:type="varchar" name="email" padding="11" nullable="false" comment="Email" disabled="true"/>

Change data type of column :

You can change the column type, changing its type attribute to int, varchar, text etc.

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.

onCreate=”migrateDataFrom(entity_id)”

Add Index to column :

    <index referenceId="INDEX_SEVERITY" indexType="btree">
        <column name="my_id"/>
    </index>

Create foreign key :

Refer the following code for creating foreign key constraint.

    <constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="my_custom_table" column="my_id" referenceTable="severities" referenceColumn="severity_identifier" onDelete="CASCADE"/>

Drop a foreign key :

Delete the constraint node for dropping the foreign key form the table.

Create a schema whitelist :

The StageBit/DeclarativeSchema/etc/db_schema_whitelist.json file provides a history of all tables, columns, and keys added with declarative schema. It is generated manually or created automatically with the following command:

php bin/magento setup:db-declaration:generate-whitelist StageBit_DeclarativeSchema

You must generate whitelist in each release that contains the changes in db_schema.xml. Following code shows the sample of db_schema_whitelist.json

{
    "my_custom_table": {
        "column": {
            "my_id": true,
            "name": true,
            "updated_date": true
        },
        "index": {
            "VENDOR_MODULE_ENTITY_INT_MY_ID": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

Hence I assume that you get an idea about declarative schema usage.That’s it for creating table using declarative schema in Magento 2.3

Related posts

Post a Comment

Your email address will not be published.