Applying Declarative Schema Changes in Magento 2.4+

Desired outcome

Apply a repeatable, version‑controlled database change (e.g., add a column to a custom table) using Magento’s declarative schema mechanism. The change is defined in etc/db_schema.xml, tracked by db_schema_whitelist.json, and applied by running bin/magento setup:upgrade without manual SQL.

Prerequisites

• Magento Open Source or Commerce 2.4+ installed with declarative schema support.
• A custom module (e.g., Vendor_Custom) with registration.php and etc/module.xml already present.
• SSH access to the web server and sufficient privileges to run Magento CLI commands.
• Database user with ALTER, CREATE, and DROP permissions.
• A recent database backup (or a snapshot) taken before starting.
• Ability to put the store into maintenance mode during the upgrade window.

Focused procedure

1. Place the instance in maintenance mode (optional but recommended for production):

   bin/magento maintenance:enable

2. Define the schema change in the module’s etc/db_schema.xml. Example: add a nullable VARCHAR column named custom_field to the sales_order table.

   <?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">
       
   </schema>

3. Bump the module version in etc/module.xml so Magento knows a new schema version exists. If the current version is 1.0.0, change it to 1.0.1:

   <module name="Vendor_Custom" setup_version="1.0.1">
       <sequence>
           <module name="Magento_Sales"></module>
       </sequence>
   </module>

4. Run the upgrade to apply the declarative change:

   bin/magento setup:upgrade

   The command should output a line similar to: Schema creation/updates: followed by your module name.
5. Compile DI and flush caches (required after schema changes):

   bin/magento setup:di:compile
   bin/magento cache:flush

6. Disable maintenance mode if you enabled it:

   bin/magento maintenance:disable

Expected checks

• Review the console output of setup:upgrade for a line indicating your module’s schema update was applied.
• Inspect the database directly (e.g., via MySQL client) to confirm the column exists:

  SHOW COLUMNS FROM sales_order LIKE 'custom_field';

  Expect a row with Field = custom_field and Null = YES.
• Verify that app/etc/db_schema_whitelist.json (or var/db_schema_whitelist.json in developer mode) now contains an entry for the new column under the sales_order table.
• Check var/log/system.log and var/log/exception.log for any errors triggered during or after the upgrade.
• Perform a basic storefront and admin workflow that touches the sales order (e.g., view an order) to ensure no fatal errors appear.
• Re‑run setup:upgrade on a second, identical environment to confirm the change is idempotent (no further schema alterations reported).

Recovery options

If the upgrade produces unexpected results or you need to revert the change:

1. Restore the database from the backup taken before starting. This is the safest rollback for any unintended data impact.
2. Revert the module version in etc/module.xml back to the previous value (e.g., 1.0.0) and run setup:upgrade again. Declarative schema will attempt to return the schema to the prior version, dropping the added column.
3. Remove the column definition from etc/db_schema.xml, keep the module version unchanged, and run setup:upgrade. The declarative engine will detect the mismatch and drop the column.
4. If the module is no longer needed, disable it via bin/magento module:disable Vendor_Custom and run setup:upgrade to clean up any remaining schema artifacts.

Limitations and practical verification

Declarative schema behavior varies between Magento 2.3, 2.4, and Commerce editions; the steps above assume 2.4+ where full support exists. Always test the procedure on a staging clone first. Large schema changes can cause table locks on high‑traffic stores; schedule the upgrade during low‑traffic periods and consider using maintenance mode to avoid concurrent writes.

To verify the change in an automated way, you can add a simple script that checks the column’s presence and logs success or failure:

#!/bin/bash
DB_NAME="magento"
TABLE="sales_order"
COLUMN="custom_field"
if mysql -N -e "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_NAME='$TABLE' AND COLUMN_NAME='$COLUMN';" | grep -q "^$COLUMN$"; then
    echo "Column $COLUMN exists."
else
    echo "Column $COLUMN missing."
    exit 1
fi

Run this script after setup:upgrade; a zero exit status confirms the column is present.