Magento 2 Declarative Schema and Data Patches, Safely

Since Magento 2.3, database changes in modules are made with declarative schema and patches instead of the old InstallSchema and UpgradeData scripts with version checks. The new system is better, but it has sharp edges. It will drop a column if you remove it from the XML and the whitelist allows it. It will run a data patch exactly once, so a broken patch that half ran is your problem to clean up.

This article covers how the pieces work in Magento 2.4 and the habits that keep a schema change from turning into an outage.

How declarative schema works

Each module describes the final state of its tables in etc/db_schema.xml. During setup:upgrade, Magento reads every module's file, compares the result with the live database and generates the SQL needed to get there. You never write ALTER TABLE by hand.

<?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="vendor_order_export" resource="default" engine="innodb"
           comment="Order export status">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false"
                identity="true" comment="Entity ID"/>
        <column xsi:type="int" name="order_id" unsigned="true" nullable="false"
                comment="Sales order ID"/>
        <column xsi:type="varchar" name="status" length="32" nullable="false"
                default="pending" comment="Export status"/>
        <column xsi:type="timestamp" name="exported_at" nullable="true"
                comment="Exported at"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="unique" referenceId="VENDOR_ORDER_EXPORT_ORDER_ID">
            <column name="order_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="VENDOR_ORDER_EXPORT_ORDER_ID_SALES_ORDER_ENTITY_ID"
                    table="vendor_order_export" column="order_id"
                    referenceTable="sales_order" referenceColumn="entity_id"
                    onDelete="CASCADE"/>
        <index referenceId="VENDOR_ORDER_EXPORT_STATUS" indexType="btree">
            <column name="status"/>
        </index>
    </table>
</schema>

A module can also extend a core table by declaring the same table name with only the new columns. Magento merges the declarations.

The whitelist file

Because the XML describes a final state, removing a column from it could mean "drop this column" or "this column was never mine". Magento solves the ambiguity with etc/db_schema_whitelist.json. It lists every table, column, index and constraint the module has ever declared. Magento only drops or renames things that appear in the whitelist.

Generate it after every change to db_schema.xml and commit it with the change:

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

Two common mistakes come from this file. Forgetting to regenerate it means a column you removed from the XML stays in the database. Regenerating it carelessly on a branch where someone else's column is missing from the XML can authorize a drop you did not intend. Review the whitelist diff like any other code.

Removing and renaming

To remove a column, delete it from the XML or mark it with disabled="true". With the whitelist in place, the next setup:upgrade drops it, along with its data.

To rename a column and keep its data, declare the new column with onCreate:

<column xsi:type="varchar" name="export_status" length="32" nullable="false"
        default="pending" onCreate="migrateDataFrom(status)" comment="Export status"/>

For a table rename there is onCreate="migrateDataFromAnotherTable(old_table_name)" on the table node. Both only work when the old element is in the whitelist.

Dry run and safe mode

Before running a schema change against a real database, look at the SQL Magento intends to execute. The dry run mode writes it to a log file and changes nothing:

bin/magento setup:upgrade --dry-run=1
less var/log/dry-run-installation.log

Read that file for every release that touches the schema. Anything with DROP in it deserves a second look. It is also the fastest way to spot an unexpected change caused by another module or a whitelist mistake.

There is also a safe mode. setup:upgrade --safe-mode=1 dumps data from columns and tables that are about to be removed into var/declarative_dumps_csv, and setup:upgrade --data-restore=1 can load it back after a rollback of the code. It is a safety net, not a backup. Take a real database backup before any release with destructive changes.

Data patches

Schema describes structure. Data patches change content: config values, CMS blocks, attributes, default records. A data patch implements Magento\Framework\Setup\Patch\DataPatchInterface and lives in Setup/Patch/Data. Magento records applied patches by class name in the patch_list table and never runs the same class twice.

<?php
declare(strict_types=1);

namespace Vendor\OrderExport\Setup\Patch\Data;

use Magento\Framework\App\Config\Storage\WriterInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\Patch\PatchRevertableInterface;

class AddDefaultExportConfig implements DataPatchInterface, PatchRevertableInterface
{
    private const PATH = 'vendor_order_export/general/batch_size';

    public function __construct(
        private readonly ModuleDataSetupInterface $moduleDataSetup,
        private readonly WriterInterface $configWriter
    ) {
    }

    public function apply(): self
    {
        $this->moduleDataSetup->getConnection()->startSetup();
        $this->configWriter->save(self::PATH, '100');
        $this->moduleDataSetup->getConnection()->endSetup();

        return $this;
    }

    public function revert(): void
    {
        $this->moduleDataSetup->getConnection()->startSetup();
        $this->configWriter->delete(self::PATH);
        $this->moduleDataSetup->getConnection()->endSetup();
    }

    public static function getDependencies(): array
    {
        return [];
    }

    public function getAliases(): array
    {
        return [];
    }
}

Dependencies and aliases

getDependencies() returns other patch classes that must run first. Use it whenever a patch reads data created by another patch, including patches from other modules. Without it, the order is not something you should rely on.

getAliases() returns old class names for the same patch. If you rename or move a patch class that already ran in production, put the old name here, otherwise Magento treats it as a new patch and runs it again.

Revertable patches

Implementing PatchRevertableInterface adds a revert() method. It runs when the module is uninstalled with bin/magento module:uninstall. It does not run when you deploy an older version of the code or disable the module, so it is not a rollback mechanism for releases. Write it for modules that might be removed, and keep it honest: if the patch cannot be reverted cleanly, do not pretend it can.

Schema patches

SchemaPatchInterface, in Setup/Patch/Schema, has the same shape as a data patch. It runs before data patches and is meant for structural changes the XML cannot express. In practice most modules never need one. If you find yourself writing ALTER TABLE in a schema patch for something db_schema.xml supports, move it to the XML, otherwise the declarative comparison and your patch will fight each other.

Habits that prevent outages

Make changes backward compatible

During a deploy there is a window where the database has the new schema and the old code is still running, or the reverse if you have to roll back. Plan changes so both versions work:

• Add new columns as nullable or with a default. Code that does not know about them keeps working.
• Do not drop or rename a column in the same release that stops using it. Ship the code change first, then remove the column in a later release.
• Avoid adding NOT NULL without a default to a table that already has rows.

Watch large tables

An ALTER TABLE on sales_order, quote or a big EAV table can take a long time and may lock writes depending on the operation and the MySQL or MariaDB version. Test the upgrade against a copy of the production database with realistic size and measure it. If it is slow, schedule it with maintenance mode or split the change.

Keep data patches small and defensive

• One purpose per patch. Easier to name, review and depend on.
• Check before writing. Even though a patch runs once, a patch that checks whether the record already exists survives a manual retry after a failure.
• Do not load full collections of products or orders. Use the connection and batch the work.
• Inject dependencies through the constructor. Do not use the object manager directly.
• Never put environment specific values, like API keys or URLs, in patches. Those belong in env.php or config.php.

Know what is pending

bin/magento setup:db:status tells you whether the schema or data need an upgrade. Use it in the deploy script to decide whether maintenance mode is necessary at all. If you need to run only one part, setup:db-schema:upgrade and setup:db-data:upgrade exist as separate commands.

FAQ

What is db_schema_whitelist.json for?

It tells Magento which tables, columns and indexes a module has declared in the past, so it knows it is allowed to drop or rename them. Generate it with setup:db-declaration:generate-whitelist after every change to db_schema.xml.

How do I run a data patch again in Magento 2?

Magento skips any patch listed in the patch_list table. Deleting its row makes it run on the next setup:upgrade. Do this only when the patch is safe to run twice, and prefer writing a new patch for new changes.

Does revert() run when I roll back a deploy?

No. The revert method of a revertable patch runs when the module is uninstalled with bin/magento module:uninstall. Deploying older code does not undo patches or schema changes.

How can I see what SQL setup:upgrade will run?

Run bin/magento setup:upgrade --dry-run=1. The planned statements are written to var/log/dry-run-installation.log and the database is not changed.

I am Rodrigo Biazzi, a Magento Certified Developer working with Magento and Adobe Commerce for more than 12 years. I am available for freelance Magento work, from single fixes to longer projects. If this is the kind of problem you have on your store, get in touch.