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.

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.

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

Now, run the following upgrade command,

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.

Step 3: Rename Table

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

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.

Step 5: Drop a column to the table

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

Step 6: Change the column type

We can change column type varchar to text.

Step 7: Rename the column name

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

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.

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.

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.

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.

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.