Declarative Schema and Patches

<?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="pocketcoffee_lot" resource="default" engine="innodb" comment="Pocket Coffee roast lots">
        <column xsi:type="int" name="lot_id" unsigned="true" nullable="false" identity="true" comment="Lot ID"/>
        <column xsi:type="int" name="product_id" unsigned="true" nullable="false" comment="Product ID"/>
        <column xsi:type="varchar" name="sku" nullable="false" length="64" comment="SKU of the product"/>
        <column xsi:type="varchar" name="origin" nullable="false" length="32" comment="Coffee origin"/>
        <column xsi:type="date" name="roasted_at" nullable="false" comment="Roast date"/>
        <column xsi:type="decimal" name="weight_kg" scale="3" precision="8" unsigned="true" nullable="false" default="0" comment="Weight roasted in kilograms"/>
        <column xsi:type="smallint" name="status" unsigned="true" nullable="false" default="0" comment="0 roasting, 1 ready, 2 sold out"/>
        <column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP" on_update="false" comment="Created at"/>
        <column xsi:type="timestamp" name="updated_at" nullable="false" default="CURRENT_TIMESTAMP" on_update="true" comment="Updated at"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="lot_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="POCKETCOFFEE_LOT_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID" table="pocketcoffee_lot" column="product_id" referenceTable="catalog_product_entity" referenceColumn="entity_id" onDelete="CASCADE"/>
        <constraint xsi:type="unique" referenceId="POCKETCOFFEE_LOT_SKU_ROASTED_AT">
            <column name="sku"/>
            <column name="roasted_at"/>
        </constraint>
        <index referenceId="POCKETCOFFEE_LOT_ORIGIN" indexType="btree">
            <column name="origin"/>
        </index>
        <index referenceId="POCKETCOFFEE_LOT_STATUS_ROASTED_AT" indexType="btree">
            <column name="status"/>
            <column name="roasted_at"/>
        </index>
    </table>
</schema>

$ bin/magento setup:db:status
Validator Magento\Framework\Setup\Declaration\Schema\UpToDateDeclarativeSchema isUpToDate: false
Declarative Schema is not up to date
Details: {
    "total_differences": 1,
    "by_change_type": {
        "create_table": 1
    },
    "affected_tables": {
        "pocketcoffee_lot": {
            "create_table": 1
        }
    },
    "changes": [
        {
            "operation": "create_table",
            "index": 0,
            "table": "pocketcoffee_lot",
            "name": "pocketcoffee_lot",
            "type": "table"
        }
    ]
}
Validator Magento\Framework\Setup\Patch\UpToDateSchema isUpToDate: true
Validator Magento\Framework\Setup\Patch\UpToDateData isUpToDate: true
Validator Magento\Framework\Setup\OldDbValidator isUpToDate: true
Run 'setup:upgrade' to update your DB schema and data.

$ bin/magento setup:upgrade --dry-run=1
$ cat var/log/dry-run-installation.log
CREATE TABLE `pocketcoffee_lot` (
`lot_id` int UNSIGNED NOT NULL  AUTO_INCREMENT COMMENT "Lot ID",
`product_id` int UNSIGNED NOT NULL   COMMENT "Product ID",
`sku` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL  COMMENT "SKU of the product",
`origin` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL  COMMENT "Coffee origin",
`roasted_at` date NOT NULL COMMENT "Roast date",
`weight_kg` decimal(8, 3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "Weight roasted in kilograms",
`status` smallint UNSIGNED NOT NULL DEFAULT 0  COMMENT "0 roasting, 1 ready, 2 sold out",
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT "Created at",
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "Updated at",
CONSTRAINT  PRIMARY KEY (`lot_id`),
CONSTRAINT `POCKETCOFFEE_LOT_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`)  ON DELETE CASCADE,
CONSTRAINT `POCKETCOFFEE_LOT_SKU_ROASTED_AT` UNIQUE KEY (`sku`,`roasted_at`),
INDEX `POCKETCOFFEE_LOT_ORIGIN` (`origin`),
INDEX `POCKETCOFFEE_LOT_STATUS_ROASTED_AT` (`status`,`roasted_at`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci COMMENT="Pocket Coffee roast lots"

$ mariadb magento -e "show create table pocketcoffee_lot\G"
CREATE TABLE `pocketcoffee_lot` (
  `lot_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Lot ID',
  `product_id` int(10) unsigned NOT NULL COMMENT 'Product ID',
  `sku` varchar(64) NOT NULL COMMENT 'SKU of the product',
  `origin` varchar(32) NOT NULL COMMENT 'Coffee origin',
  `roasted_at` date NOT NULL COMMENT 'Roast date',
  `weight_kg` decimal(8,3) unsigned NOT NULL DEFAULT 0.000 COMMENT 'Weight roasted in kilograms',
  `status` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT '0 roasting, 1 ready, 2 sold out',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Created at',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Updated at',
  PRIMARY KEY (`lot_id`),
  UNIQUE KEY `POCKETCOFFEE_LOT_SKU_ROASTED_AT` (`sku`,`roasted_at`),
  KEY `POCKETCOFFEE_LOT_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` (`product_id`),
  KEY `POCKETCOFFEE_LOT_ORIGIN` (`origin`),
  KEY `POCKETCOFFEE_LOT_STATUS_ROASTED_AT` (`status`,`roasted_at`),
  CONSTRAINT `POCKETCOFFEE_LOT_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Pocket Coffee roast lots'

$ cat var/declarative_dumps_csv/pocketcoffee_lab_column_grind.csv
lab_id,grind
1,espresso

$ bin/magento setup:upgrade --data-restore=1
$ mariadb magento -e "select * from pocketcoffee_lab"
lab_id	name	size_g	grind
1	Single-origin beans	espresso	250

$ bin/magento setup:db-declaration:generate-patch --revertable=true PocketCoffee_Lot AddFirstLots
Patch 10as been successfully generated.

<?php
/**
 * Copyright 2018 Adobe
 * All Rights Reserved.
 */
declare(strict_types=1);

namespace PocketCoffee\Lot\Setup\Patch\Data;

use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\Patch\PatchRevertableInterface;

/**
* Patch is mechanism, that allows to do atomic upgrade data changes
*/
class AddFirstLots implements
    DataPatchInterface,
    PatchRevertableInterface
{
    /**
     * @var ModuleDataSetupInterface $moduleDataSetup
     */
    private $moduleDataSetup;

    /**
     * @param ModuleDataSetupInterface $moduleDataSetup
     */
    public function __construct(ModuleDataSetupInterface $moduleDataSetup)
    {
        $this->moduleDataSetup = $moduleDataSetup;
    }

    /**
     * Do Upgrade
     *
     * @return void
     */
    public function apply()
    {
    }

    /**
     * @inheritdoc
     */
    public function revert()
    {
    }

    /**
     * @inheritdoc
     */
    public function getAliases()
    {
        return [];
    }

    /**
     * @inheritdoc
     */
    public static function getDependencies()
    {
        return [];
    }
}

<?php
declare(strict_types=1);

namespace PocketCoffee\Lot\Setup\Patch\Data;

use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;

/**
 * Seeds three roast lots for the product POCKET-001 (skipped when the product does not exist). It inserts rows, so it
 * is not revertable: reverting an insert needs a DELETE, which the revert contract forbids.
 */
class AddFirstLots implements DataPatchInterface
{
    private const SKU = 'POCKET-001';

    private const LOTS = [
        ['origin' => 'Ethiopia', 'roasted_at' => '2026-09-14', 'weight_kg' => '12.500', 'status' => 1],
        ['origin' => 'Colombia', 'roasted_at' => '2026-10-05', 'weight_kg' => '20.000', 'status' => 1],
        ['origin' => 'Brazil', 'roasted_at' => '2026-10-26', 'weight_kg' => '15.250', 'status' => 0],
    ];

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

    /**
     * @inheritDoc
     */
    public function apply()
    {
        $connection = $this->moduleDataSetup->getConnection();
        $connection->startSetup();

        $productId = $connection->fetchOne(
            $connection->select()
                ->from($this->moduleDataSetup->getTable('catalog_product_entity'), 'entity_id')
                ->where('sku = ?', self::SKU)
        );
        if ($productId) {
            $rows = [];
            foreach (self::LOTS as $lot) {
                $rows[] = ['product_id' => (int) $productId, 'sku' => self::SKU] + $lot;
            }
            $connection->insertOnDuplicate(
                $this->moduleDataSetup->getTable('pocketcoffee_lot'),
                $rows,
                ['weight_kg', 'status']
            );
        }

        $connection->endSetup();
        return $this;
    }

    /**
     * @inheritDoc
     */
    public function getAliases()
    {
        return [];
    }

    /**
     * @inheritDoc
     */
    public static function getDependencies()
    {
        return [];
    }
}

$ bin/magento setup:upgrade
...
Unable to apply data patch PocketCoffee\LotRevert\Setup\Patch\Data\FailingPatch for module PocketCoffee_LotRevert. Original exception message: Roaster is on fire
Upgrade failed: Unable to apply data patch ...

<?php
declare(strict_types=1);

namespace PocketCoffee\Lot\Setup\Patch\Data;

use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\Patch\PatchRevertableInterface;
use PocketCoffee\Lot\Api\Data\LotInterface;

/**
 * Marks the lots roasted before 2026-09-30 as sold out. It must run after AddFirstLots, and it only uses UPDATE, which
 * is what the revert contract allows: revert() puts the same lots back to ready.
 */
class MarkOldLotsSoldOut implements DataPatchInterface, PatchRevertableInterface
{
    private const CUTOFF = '2026-09-30';

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

    /**
     * @inheritDoc
     */
    public function apply()
    {
        $connection = $this->moduleDataSetup->getConnection();
        $connection->startSetup();
        $connection->update(
            $this->moduleDataSetup->getTable('pocketcoffee_lot'),
            ['status' => LotInterface::STATUS_SOLD_OUT],
            ['roasted_at < ?' => self::CUTOFF, 'status = ?' => LotInterface::STATUS_READY]
        );
        $connection->endSetup();
        return $this;
    }

    /**
     * @inheritDoc
     */
    public function revert()
    {
        $connection = $this->moduleDataSetup->getConnection();
        $connection->startSetup();
        $connection->update(
            $this->moduleDataSetup->getTable('pocketcoffee_lot'),
            ['status' => LotInterface::STATUS_READY],
            ['roasted_at < ?' => self::CUTOFF, 'status = ?' => LotInterface::STATUS_SOLD_OUT]
        );
        $connection->endSetup();
    }

    /**
     * @inheritDoc
     */
    public function getAliases()
    {
        return [];
    }

    /**
     * @inheritDoc
     */
    public static function getDependencies()
    {
        return [AddFirstLots::class];
    }
}

$ bin/magento setup:upgrade
...
Cyclomatic dependency during patch installation
Upgrade failed: Cyclomatic dependency during patch installation

<?php
declare(strict_types=1);

namespace PocketCoffee\LotRevert\Setup\Patch\Data;

use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;

/**
 * Former name: AddNote. The alias keeps databases that applied the old name from running the patch again.
 */
class AddNoteRenamed implements DataPatchInterface
{
    public function __construct(private readonly ModuleDataSetupInterface $setup)
    {
    }

    public function apply()
    {
        // inserts into a table that no longer exists: it fails if the patch runs again
        $this->setup->getConnection()->insert($this->setup->getTable('pocketcoffee_revert'), ['note' => 'applied']);
        return $this;
    }

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

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

$ mariadb magento -e "select patch_name from patch_list where patch_name like '%LotRevert%'"
PocketCoffee\\LotRevert\\Setup\\Patch\\Data\\AddNote
PocketCoffee\\LotRevert\\Setup\\Patch\\Data\\AddNoteRenamed

<?php
declare(strict_types=1);

namespace PocketCoffee\Lot\Setup\Patch\Schema;

use Magento\Framework\Setup\Patch\SchemaPatchInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

/**
 * Creates a MySQL view over pocketcoffee_lot. db_schema.xml cannot declare views, so this is a schema patch.
 * Schema patches are never reverted by the Framework, so the view stays when the module is uninstalled.
 */
class AddLotSummaryView implements SchemaPatchInterface
{
    private const VIEW = 'pocketcoffee_lot_summary';

    public function __construct(private readonly SchemaSetupInterface $schemaSetup)
    {
    }

    /**
     * @inheritDoc
     */
    public function apply()
    {
        $this->schemaSetup->startSetup();
        $view = $this->schemaSetup->getTable(self::VIEW);
        $lots = $this->schemaSetup->getTable('pocketcoffee_lot');
        $this->schemaSetup->getConnection()->query(
            "CREATE OR REPLACE VIEW {$view} AS "
            . "SELECT origin, COUNT(*) AS lots, SUM(weight_kg) AS total_kg, MAX(roasted_at) AS last_roasted_at "
            . "FROM {$lots} GROUP BY origin"
        );
        $this->schemaSetup->endSetup();
        return $this;
    }

    /**
     * @inheritDoc
     */
    public function getAliases()
    {
        return [];
    }

    /**
     * @inheritDoc
     */
    public static function getDependencies()
    {
        return [];
    }
}

$ mariadb magento -e "select * from pocketcoffee_lot_summary"
origin	lots	total_kg	last_roasted_at
Brazil	1	15.250	2026-10-26
Colombia	1	20.000	2026-10-05
Ethiopia	1	12.500	2026-09-14

$ bin/magento module:uninstall --non-composer --remove-data -n PocketCoffee_Lot
$ mariadb magento -e "select lot_id, origin, status from pocketcoffee_lot"
lot_id  origin    status
1       Ethiopia  1            <- was 2 before; MarkOldLotsSoldOut::revert() ran
...
$ mariadb magento -e "select patch_name from patch_list where patch_name like '%Lot%Patch%'"
PocketCoffee\\Lot\\Setup\\Patch\\Schema\\AddLotSummaryView
PocketCoffee\\Lot\\Setup\\Patch\\Data\\AddFirstLots

<?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="pocketcoffee_legacy_lot" resource="default" engine="innodb" comment="Legacy roast lots">
    <column xsi:type="int" name="lot_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Lot ID"/>
    <column xsi:type="varchar" name="sku" nullable="false" length="64" comment="SKU"/>
    <column xsi:type="date" name="roasted_at" comment="Roast date"/>
    <column xsi:type="decimal" name="weight_kg" scale="3" precision="8" unsigned="false" nullable="false" default="0" comment="Weight in kg"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
      <column name="lot_id"/>
    </constraint>
    <index referenceId="POCKETCOFFEE_LEGACY_LOT_SKU" indexType="btree">
      <column name="sku"/>
    </index>
  </table>
</schema>

<?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="pocketcoffee_legacy_lot" resource="default" engine="innodb" comment="Legacy roast lots">
    <column xsi:type="int" name="lot_id" unsigned="true" nullable="false" identity="true" comment="Lot ID"/>
    <column xsi:type="varchar" name="sku" nullable="false" length="64" comment="SKU"/>
    <column xsi:type="date" name="roasted_at" nullable="false" comment="Roast date"/>
    <column xsi:type="decimal" name="weight_kg" scale="3" precision="8" unsigned="false" nullable="false" default="0" comment="Weight in kg"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
      <column name="lot_id"/>
    </constraint>
    <index referenceId="POCKETCOFFEE_LEGACY_LOT_SKU" indexType="btree">
      <column name="sku"/>
    </index>
  </table>
</schema>