Declarative Schema in Magento 2.3.x or later

Written by Payal Patel

Mar 22, 2021

296
Declarative Schema in Magento 2.3.x or later

In this tutorial, we teach you how to perform create, drop or other operations using Declarative Schema. Magento 2.3 implemented a new feature called Declarative Schema, in previous Magento versions create setup script for install, upgrade and uninstall table or data.

Declarative Schema facilitates the installation and upgrading of code that is contained within a single XML file. We can perform the following operations with DDL(Data Definition Language) and DML(Data Manipulation Language).

Step 1: Create Table

Create db_schema.xml file in app/code/Dolphin/MyModule/etc with the following code.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
        <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
   	</table>
</schema>

In the above code, the Table Node contains the following attributes,

  • Name: Name of the table name
  • Engine: This the SQL engine, must be required value InnoDB or memory
  • Resource: This value must be default, checkout or sales
  • Comment: Table comment

The table node three different types of subnodes,

  • column
  • constrains
  • index

Column: A column node can contain the following attributes,

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

Constrains: The constrains node can contain the following attributes,

  • Type: primary, unique and foreign key
  • ReferenceId: A custom identifier used only for relation mapping in db_schema.xml files

If you use Magento version 2.3 something then you can need to run the following command for db_whitelist_schema.json file.

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

db_whitelist_schema.json file created after run above command in app/code/Dolphin/MyModule/etc folder.

Now, run the following upgrade command,

php bin/magento setup:upgrade

Step 2: Drop Table

Let’s start with the drop table remove the entire table node in db_schema.xml file, follow the below code.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
-  	<table name="dolphin_mymodule_contact" resource="default" engine="innodb" comment="Dolphin Contact Table">
-		<column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
-		<column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
-		<column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
-		<column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
-		<column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
-		<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
-    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
-		<constraint xsi:type="primary" referenceId="PRIMARY">
-			<column name="id" />
-		</constraint>
-  	</table>
</schema>

Step 3: Rename Table

Let’s start with the rename table in db_schema.xml file, follow the below code.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact_new_tbl" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
        <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
   	</table>
</schema>

In this above code you can see onCreate attribute added in <table> attribute and migrateDataFromAnotherTable() function. Please pass the parameter with old table name in migrateDataFromAnotherTable().

Note: It is not possible to migrate data from another table and renaming columns at the same time.

Step 4: Add a column to the table

We are going to new add a column in the table in db_schema.xml file with the following code.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
        <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
+		<column xsi:type="varchar" name="company" nullable="false" length="255" comment="Company"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
   	</table>
</schema>

Step 5: Drop a column to the table

Now, we remove the company column by remove that column node.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
        <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
-		<column xsi:type="varchar" name="company" nullable="false" length="255" disable="true" comment="Company"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
   	</table>
</schema>

Step 6: Change the column type

We can change column type varchar to text.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
+		<column xsi:type="text" name="message" nullable="false" comment="Message" />
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
   	</table>
</schema>

Step 7: Rename the column name

Let’s we can change the column name with the following code.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
-		<column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
+		<column xsi:type="varchar" name="comment" onCreate="migrateDataFrom(message)" nullable="false" length="255" comment="Comment" />
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
   	</table>
</schema>

You can see in the above code the column name message to comment with the migrateDataFrom() function using the old column name message.

Step 8: Add an Index

In the following code add an Index to the entity_id.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" comment="Entity ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
        <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
+		<index referenceId="INDEX_REFERENCE_ID" indexType="btree">
+			<column name="entity_id"/>
+		</index>
   	</table>
</schema>

In the above code INDEX_REFERENCE_ID index to the custom_table table.

Step 9: Create a foreign key

In the below example, add a new foreign key using the constraint node.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" comment="Entity ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
        <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
+		<constraint xsi:type="foreign" referenceId="DOLPHIN_MYMODULE_CONTACT_ENTITY_ID_DOLPHIN_MYMODULE_CUSTOM_CUSTOM_ID"
            table="dolphin_mymodule_contact" column="entity_id" referenceTable="dolphin_mymodule_custom"
            referenceColumn="custom_id" onDelete="CASCADE"/>
   	</table>
</schema>

Step 10: Drop a foreign key

In the below example, remove the foreign key using ADD_REF_FOR_CUSTOM_TABLE by deleting the constraint node. But, when declaring the constraint node to the other module then use the disable attribute with value true.

<?xml version="1.0" encoding="UTF-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   	<table name="dolphin_mymodule_contact" onCreate="migrateDataFromAnotherTable(dolphin_mymodule_contact)"
   		resource="default" engine="innodb" comment="Dolphin Contact Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
        <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" comment="Entity ID" />
        <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name" />
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
        <column xsi:type="int" name="phone_no" nullable="false" length="11" comment="Phone Number" />
        <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message" />
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Creation Time"/>
    	<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
            comment="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id" />
        </constraint>
-		<constraint xsi:type="foreign" referenceId="DOLPHIN_MYMODULE_CONTACT_ENTITY_ID_DOLPHIN_MYMODULE_CUSTOM_CUSTOM_ID"
            table="dolphin_mymodule_contact" column="entity_id" referenceTable="dolphin_mymodule_custom"
            referenceColumn="custom_id" onDelete="CASCADE"/>
   	</table>
</schema>

 

You can see in the referenceId DOLPHIN_MYMODULE_CONTACT_ENTITY_ID_DOLPHIN_MYMODULE_CUSTOM_CUSTOM_ID is the ADD_REF_FOR_CUSTOM_TABLE.

Step 11: Recreate a foreign key

We have to add disable attribute as per Step 10. Check the following example.

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

We hope our guide is very effective for you. If any questions, please feel free to leave a comment below. In the next tutorial, you will help with how to create Models, ResourceModels, and Collections in Magento 2.

Payal Patel

Author

Dolphin Web Solution is a Top Rated Web And App Development Company

Over 12 years of work, we’ve helped over 140 Startups & Companies to Design & Build Successful Mobile and Web Apps.

We can help you with

  • Dedicated Team
  • Setup Extended Team
  • Product Development
  • Custom App Development
Contact Us

Schedule a Developer Interview And Get 7 Days Risk-Free Trial

Fill out This Form and one of Our Technical Experts will Contact you Within 12 Hours.

    Copyright © 2023 DOLPHIN WEB SOLUTION. All rights reserved.

    Terms & Condition Privacy Policy
    facebook linkedin instagram twitter behance dribbble
    sertificate sertificate sertificate sertificate sertificate sertificate sertificate sertificate sertificate sertificate
    TO TOP