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).
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,
The table node three different types of subnodes,
Column: A column node can contain the following attributes,
Constrains: The constrains node can contain the following attributes,
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
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>
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.
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>
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>
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>
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.
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.
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>
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.
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.
Click one of our contacts below to chat on WhatsApp