How to add new column to an existing table in Magento 2.3 using declarative schema?

Welcome to Kishan Savaliya's Magento 2 Blog.In this blog post, I will show you how to add new column to an existing table in Magento 2.3 using declarative schema. If you don't know how to create new table using Declarative Schema then this article can be useful to you!!

Before Magento 2.3, we can add new column to an existing table using UpgradeSchema.php file, but we can use declarative schema in Magento 2.3 or later versions to add new column(s) to an existing table.

It's quite easy to add new column to an existing table with the use of declarative schema in Magento 2.3 or later versions, so let's start about how to add new column to an existing table..

I'm assuming you had created table using declarative schema in your custom module, so just go to you Magento 2 installation directory and navigate inside your custom module directory, inside your module directory navigate to etc/ directory and open db_schema.xml file.

app/code/SK/DeclarativeSchema/etc/db_schema.xml

Content for this file is..

<?xml version="1.0"?>
<!--
/**
 * SK DeclarativeSchema Magento 2.3 Add new column to an existing table.
 */
-->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
   <table name="sk_declaration_schema_new" resource="default" engine="innodb" comment="SK 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" />
      <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>

I had, id, firstname, lastname, email and content columns previously, 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" />

After changing above file content I need to run below command once in terminal to whitelist new column in DB Declaration Schema. So open your terminal and run below command there.

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

Whenever you will run above command it'll whitelist your new column in db_schema_whitelist.json file, you can check that content here.

app/code/SK/DeclarativeSchema/etc/db_schema_whitelist.json

Content of this file is..

{
    "sk_declaration_schema_new": {
        "column": {
            "id": true,
            "firstname": true,
            "lastname": true,
            "email": true,
            "content": true,
            "mobile_number": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

You can see "mobile_number": true there in json whitelist file, so now we can upgrade setup, so let's run below command once.

php bin/magento setup:upgrade

Now you can check your new column in your database.

Hope you may like this article and can understand this easily. You can add comments below in case if you have any questions regarding this article or if I missed anything here. I will check and get back to you with proper solution.

If you enjoyed this blog post, share it with friends!