Categories: Magento 2

Declarative Schema in Magento 2.3

Declarative Schema in Magento

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

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

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

How to create a table using Declarative Schema in Magento 2.3

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

First of all, Create a 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 the database. Table node contains 3 types of sub-nodes i.e column, constraint, index.
    When you run the setup: upgrade command. It will create the table “my_custom_table”.
  • column: It defines the column in the table. It requires its own declaration.
  • constraint: It is used to define the rules to allow or restrict what values to be stored in the column. It has the following attributes and values:
    1. type: One of primary, unique, or foreign.
      The primary and unique constraints are called “internal” constraints because they are 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 a New column to the 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 an existing table :

If you want to remove the existing column then you can set the disabled attribute to that column or simply delete that node. In 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 from 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 the 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 a whitelist in each release that contains the changes in db_schema.xml. The 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 tables using declarative schema in Magento 2.3

Share
Leave a Comment