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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?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.
1 | 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,
1 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | <?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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | <?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.
1 2 3 4 5 6 7 8 9 | <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.