How to add/remove column and other DB operations to table in Magento 2.3 using declarative schema?

Today I will show you how to add/remove a column from an existing table in Magento 2.3 using declarative schema.

Before Magento 2.3, developers were required to PHP script for installing and updating data in the database.

Various scripts were required for:

1. Installing and updating the schema.
2. Installing and updating data.
3. Managing other operations when Magento was installed or upgraded.

Now, Magento has introduced a new declarative schema feature with its latest Magento 2.3 version.

Read This: How to Display swatches instead of labeled product options on Checkout in Magento 2

What is Declarative Schema and Why should we use it ?

1. Declarative Schema files declare what the database structure should be, and Magento determines the differences between the current table structure and what it should be.
2. The declarative schema allows the developers to decide the final states of a particular database and then the system adjusts to those changes automatically; this saves the developers from performing a plethora of redundant functions.

What are the operation perform with Declarative Schema ?

1. Create a table
2. Drop a table
3. Rename a table
4. Add a column to the table
5. Drop a column to the table
6. Change column type
7. Rename a column
8. Add an Index
9. Create a foreign key
10. Drop a foreign key
11. Recreate a foreign key

Step 1: Create a new module.

so create it’s required files and give module name Dolphin_DeclarativeSchema.
File Path: app/code/Dolphin/DeclarativeSchema/etc/module.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Dolphin_DeclarativeSchema" setup_version="0.1.0"></module>
</config>

File Path: app/code/Dolphin/DeclarativeSchema/registration.php

<?php

use MagentoFrameworkComponentComponentRegistrar;

ComponentRegistrar::register(
    ComponentRegistrar::MODULE,
    'Dolphin_DeclarativeSchema',
    __DIR__
);

Now we see one by one some of database operation with db_schema.

Step 2: Create a new table using db_schema.xml.

File Path: app/code/Dolphin/DeclarativeSchema/etc/db_schema.xml

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   <table name="dolphin_declaration_schema_new" resource="default" engine="innodb" comment="Dolphin DeclarativeSchema">
      <column xsi:type="int" name="id" padding="10" unsigned="false" nullable="false" identity="true" comment="ID" />
      <column xsi:type="varchar" name="firstname" nullable="false" length="30" comment="Firstname" />
      <column xsi:type="varchar" name="lastname" nullable="false" length="30" comment="Lastname" disabled="true"/>
      <column xsi:type="varchar" name="email" nullable="false" length="45" comment="Email" />
      <column xsi:type="varchar" name="content" nullable="false" length="255" comment="Content" />
      <constraint xsi:type="primary" referenceId="PRIMARY">
         <column name="id" />
      </constraint>
   </table>
</schema>

Step 3: Add a column to the table using db_schema.xml.

In above table, we have, id, firstname, lastname, email and content columns, and I want to add mobile_number column in my existing table.
So I just edit my db_schema.xml file and added below line inside <table></table> tags.

<column xsi:type="varchar" name="mobile_number" nullable="false" length="20" comment="Mobile Number" />

whenever you edit this file you need to run below command in terminal to whishlist new column in DB.

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

then ,
php bin/magento setup:upgrade

Step 4: Drop a column to the table using db_schema.xml.

In above table, If you want to remove lastname column from my existing table dolphin_declaration_schema_new, So you just have to add disabled=”true” attribute in lastname column like this..

<column xsi:type="varchar" name="lastname" nullable="false" length="30" comment="Lastname" disabled="true"/>

It’s not enough to write just disabled=”true” , You must first generate a db_schema_whitelist.json file to remove a column from the table by using below command.

php bin/magento setup:db-declaration:generate-whitelist –module-name=Dolphin_DeclarativeSchema
then ,
php bin/magento setup:upgrade

After running above commands if you will check your database table column list in your MySQL, you can see lastname column is successfully removed and mobile_number is successfully added there.

NOTE : When dropping a table, do not remove it from the db_schema_whitelist.json file, otherwise it will not be dropped.

Step 5: Rename a table using db_schema.xml.

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="dolphin_declaration_schema_new_tbl" onCreate="migrateDataFromAnotherTable(dolphin_declaration_schema_new)" resource="default" engine="innodb" comment="Dolphin DeclarativeSchema New">
          <column xsi:type="int" name="id" padding="10" unsigned="false" nullable="false" identity="true" comment="ID" />
          <column xsi:type="varchar" name="firstname" nullable="false" length="30" comment="Firstname" />
          <column xsi:type="varchar" name="lastname" nullable="false" length="30" comment="Lastname" disabled="true"/>
          <column xsi:type="varchar" name="email" nullable="false" length="45" comment="Email" />
          <column xsi:type="varchar" name="content" nullable="false" length="255" comment="Content" />
          <column xsi:type="varchar" name="mobile_number" nullable="false" length="20" comment="Mobile Number" />      
          <constraint xsi:type="primary" referenceId="PRIMARY">
             <column name="id" />
          </constraint>
   </table>
</schema>

You can see migrateDatafromAnotherTable() function in which you can pass your old database table name as parameter value and set the new table name through the table tag name attribute as shown below.

onCreate=”migrateDataFromAnotherTable(dolphin_declaration_schema_new)

Before running the upgrade command you need to add your schema to db_schema_whitelist.json file by running the following command :

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

then ,
php bin/magento setup:upgrade

Step 6: Create a foreign key using db_schema.xml.

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   <table name="dolphin_declaration_schema_new" resource="default" engine="innodb" comment="Dolphin DeclarativeSchema">
          <column xsi:type="int" name="id" padding="10" unsigned="false" nullable="false" identity="true" comment="ID" />
          <column xsi:type="varchar" name="firstname" nullable="false" length="30" comment="Firstname" />
          <column xsi:type="varchar" name="lastname" nullable="false" length="30" comment="Lastname" disabled="true"/>
          <column xsi:type="varchar" name="email" nullable="false" length="45" comment="Email" />
          <column xsi:type="varchar" name="content" nullable="false" length="255" comment="Content" />
          <column xsi:type="varchar" name="mobile_number" nullable="false" length="20" comment="Mobile Number" />      
          <constraint xsi:type="primary" referenceId="PRIMARY">
             <column name="id" />
          </constraint>
       <constraint xsi:type="foreign" referenceId="ADD_REF_FOR_CUSTOM_TABLE" table="custom_table"
              column="referrence_id" referenceTable="reference_custom_table" referenceColumn="reference_column"
              onDelete="CASCADE"/>
    </table>
</schema>

NOTE: Foreign keys can only be added to tables when both tables were created using a declarative schema (db_schema.xml).

here we create a reference type as foreign in the constraint node, which indicates the foreign key for the custom_table from the reference table called reference_custom_table.That’s it. Happy Coding with magento2!! ? Feel free to comment if you have any issue.

Surbhi Gandhi

Author