While working with Magento 2, sometimes you need to add an extra column to your already existing Magento 2 table. Today, we are going to provide you with an optimal solution for the same.

<?php 
namespace Vendor\Module\Setup; 
use Magento\Framework\Setup\UpgradeSchemaInterface; 
use Magento\Framework\Setup\ModuleContextInterface; 
use Magento\Framework\Setup\SchemaSetupInterface; 
use Magento\Framework\DB\Ddl\Table; 

class UpgradeSchema implements UpgradeSchemaInterface { 
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) { 
         if (version_compare($context–>getVersion(), ‘1.0.1’) < 0) { 
            $connection = $setup–>getConnection();
            $connection–>addColumn(
                $setup–>getTable(‘sales_order’),
                ‘delivery_date’,
                [
                    ‘type’ => Table::TYPE_TEXT,
                    ‘length’ => 255,
                    ‘nullable’ => true,
                    ‘default’ => ”,
                    ‘comment’ => ‘Add New Filed’
                ]
            );
        }
    }
}

php bin/magento s:up

Now check your database and you will be able to find a new column ‘delivery_date’ in ‘sales_order’ table.

Method 2: USING DB SCHEMA IN MAGENTO 2

In the previous version of Magento, this was done by using a UpgradeSchema.php file.

But, in the Magento 2.3 version or later version, we can add a new column to an existing table using the db_schema.xml file.

Here’s an easy and step-by-step process for adding a new column to an existing table using db_schema.xml file in Magento 2.

Let’s say, we have table abc with columns id, title, email, description. And now we want to add column short_desctiption in this table ‘abc’.

Step 1: Create a db_schema.xml file

The first step of the process is to create a db_schema.xml file in the app/code/Vendor/Module/etc/ directory

app/code/Vendor/Module/etc/db_schema.xml

and Content for this file is..

<?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="abc" resource="default" engine="innodb" comment="ABC is the table name">
      <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID" />
      <column xsi:type="varchar" name="title" nullable="false" length="25" comment="Title" />
      <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email" />
      <column xsi:type="varchar" name="description" nullable="false" length="255" comment="Descrition" />
      <column xsi:type="varchar" name="short_description" nullable="false" length="255" comment="Short Description" />
      <constraint xsi:type="primary" referenceId="PRIMARY">
         <column name="id" />
      </constraint>
   </table>
</schema>

As you can see, we’ve added a new column short_description in the abc table.

Step 2: Add the schema to db_whitelist_schema.json file

Now, before executing the upgrade command, you’ll have to add the schema to db_whitelist_schema.json file. This provides a history of all tables, columns, and keys added with declarative schema. It is required to allow drop operations. It can be generated manually or created automatically with the following command:

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

Once you execute the above command, the db_whitelist_schema.json file will be created in the /Vendor/Module/etc directory.

Step 3: Run setup upgrade command

Lastly, you need to run the setup upgrade command given below after the db_schema_whitelist_json file has been successfully generated.

php bin/magento s:up && php bin/magento se:s:d -f

Now, check the output and you’ll see that a new column will be created in your database table.