Magento 2 - Add column to a table by using the declarative schema

Question

I try to add two columns to the sales_order_grid table by following the declarative schema.

Attempt:

<?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="sales_order_grid" resource="default">
        <column xsi:type="varchar" name="customer_note" length="1024" nullable="true" comment="Customer Note"/>
        <column xsi:type="varchar" name="allocated_sources" length="256" nullable="true" comment="Allocated Sources"/>
    </table>
</schema>

But if I call php bin/magento setup:upgrade then the columns are not getting added.

So I changed the version in MODULE/etc/module.xml from 1.0.0 to 1.0.1 and tried it again, but it still does not work.

In the documentation it says:

When adding a new column into table, remember to generate the db_schema_whitelist.json file.

So I generated the whitelist with php bin/magento setup:db-declaration:generate-whitelist --module-name=Company_Sales

MODULE/etc/db_schema_whitelist.json

{
    "sales_order_grid": {
        "column": {
            "customer_note": true,
            "allocated_sources": true
        }
    }
}

... and tried php bin/magento setup:upgrade again. But it still fails.

I even deleted the entry Company_Sales from the table setup_module but it makes no difference too.

What am I missing?

---

Magento Version: 2.4.2

Answer 1

Check your extension order, It should be after Magento_Sales.

etc/module.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="{{Vendorname}}_{{Modulename}}">
        <sequence>
            <module name="Magento_Sales"/>
        </sequence>
    </module>
</config>

etc/db_schema.xml.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="sales_order_grid">
        <column name="customer_note" nullable="true" xsi:type="varchar" comment="Customer Note" length="1024"/>
        <column name="allocated_sources" nullable="true" xsi:type="varchar" comment="Allocated Sources" length="1024"/>
    </table>
</schema>

Answer 2

I figured it out. It actually worked all the time !!!

The problem was my SQL Viewer Software HeidiSQL (v12.0.0.6468) which has a bug and is not showing new data even after refreshing, you need to close the software and open it again to make it work or respectively close the connection and reopen it.