Shipping Magento Schema and Data Changes: An Architecture Note for Custom Modules

The deployment problem with ad-hoc module changes

Custom Magento modules often start with a quick InstallSchema class or a raw SQL statement inside a constructor. That approach works once, then breaks under deployment automation: setup:upgrade cannot tell what has already executed, and a failed deploy leaves the database in an unknown state. The smallest supported design for a module that needs new tables and reference data is declarative schema in etc/db_schema.xml plus idempotent data patch classes, applied through bin/magento setup:upgrade as the single upgrade path.

Smallest supported design: declarative schema plus data patches

A custom module needs three files to manage its own schema and initial data safely:

• etc/module.xml to register the module.
• etc/db_schema.xml to declare tables, columns, indexes, and constraints as DDL (Data Definition Language).
• Setup/Patch/Data classes that implement DataPatchInterface to perform DML (Data Manipulation Language) such as inserting reference rows.

Legacy InstallSchema and UpgradeSchema classes are deprecated for this purpose. Raw SQL in constructors is worse: it runs on every request, ignores the setup pipeline, and cannot be tracked or rolled back cleanly.

Declarative schema example

The following db_schema.xml fragment declares a simple table for a custom entity. It uses well-established Magento 2.4 declarative schema syntax.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="acme_widget" resource="default" engine="innodb" comment="Acme Widget">
        <column xsi:type="int" name="widget_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Widget ID"/>
        <column xsi:type="varchar" name="sku" nullable="false" length="64" comment="Widget SKU"/>
        <column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP" comment="Created At"/>
        <constraint xsi:type="primary" name="PRIMARY">
            <column name="widget_id"/>
        </constraint>
        <constraint xsi:type="unique" name="ACME_WIDGET_SKU">
            <column name="sku"/>
        </constraint>
    </table>
</schema>

Replace acme_widget and column names with your own identifiers. The resource="default" attribute targets the default database connection; split-database setups require additional care.

Idempotent data patch pattern

A data patch is a small class that runs exactly once and records itself in the patch_list table. Because setup:upgrade can be retried after a failure, the patch must tolerate partial reruns. The pattern below checks for existing rows before inserting.

<?php
namespace Acme\Widget\Setup\Patch\Data;

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

class InsertDefaultWidgets implements DataPatchInterface
{
    private $moduleDataSetup;

    public function __construct(ModuleDataSetupInterface $moduleDataSetup)
    {
        $this->moduleDataSetup = $moduleDataSetup;
    }

    public function apply()
    {
        $connection = $this->moduleDataSetup->getConnection();
        $table = $this->moduleDataSetup->getTable('acme_widget');

        $select = $connection->select()
            ->from($table, ['sku'])
            ->where('sku IN (?)', ['default-sku']);

        if (!$connection->fetchOne($select)) {
            $connection->insert($table, [
                'sku' => 'default-sku',
                'created_at' => date('Y-m-d H:i:s'),
            ]);
        }
    }

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

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

Use placeholders such as default-sku and your own module namespace. Do not hard-code store IDs, environment URLs, or customer data. For bulk data, prefer the import framework or a repository API rather than a patch.

Trust and data boundaries

Schema and data patches are privileged code. They execute with database credentials and can drop tables or delete rows. Treat them as deployment-only artifacts:

• Only CI systems or an administrator should run bin/magento setup commands.
• Runtime request handling must never trigger DDL. Constructors, observers, and controllers should not create or alter tables.
• Keep customer or PII data out of patch source files. Patches are version-controlled and often mirrored to multiple environments.
• Patches must be idempotent. A retry after a network failure or timeout should not duplicate rows or fail on an existing constraint.

Operational checks and deployment sequence

Apply changes during a controlled maintenance window. Run the following from the Magento root directory as the file-system owner (often the web-server user) so that var/ and generated/ remain writable.

1. bin/magento maintenance:enable — blocks storefront and admin writes.
2. Take a verified backup of the database and file system. Confirm the backup can be restored before proceeding.
3. bin/magento setup:upgrade — applies pending schema and data patches. This command reads app/etc/env.php for database credentials.
4. bin/magento setup:di:compile — required when generated code, factories, or interceptors are affected by the schema change.
5. bin/magento cache:flush — clears stale configuration and layout caches.
6. bin/magento indexer:reindex — needed if the new schema affects indexers.
7. bin/magento maintenance:disable — restore traffic after smoke tests pass.

After the upgrade, confirm no patch remains pending. On recent Magento 2.4 branches, bin/magento setup:db:status reports whether the database schema is current. You can also query the patch_list and setup_module tables to verify that your module version and patch classes are recorded.

Smoke test

Perform a targeted CRUD (create, read, update, delete) test on the affected entities through the admin UI or a REST/GraphQL client. For the example table above, create a widget with a unique SKU, read it back, update a field, and delete it. Confirm that duplicate SKU inserts are rejected by the unique constraint.

Common failure modes

Failure	Why it happens	Mitigation
Non-idempotent patch	Patch inserts rows without checking for existing data; a retry duplicates them or violates a unique key.	Guard every insert with a select or use INSERT ... ON DUPLICATE KEY UPDATE where appropriate.
Long DDL on large tables	Adding an index or column to a table with millions of rows can lock writes for minutes.	Use expand/contract: add nullable columns or new tables first, backfill in batches, then clean up in a later release.
Missing rollback	Declarative schema has no automatic down migration. Removing a column from db_schema.xml drops it on the next upgrade.	Keep a tested backup and restore plan. Do not rely on a generated rollback script.
Stale generated code	Factories or extension attributes generated before the schema change reference old definitions.	Run setup:di:compile after setup:upgrade.
Split-database assumptions	Open Source and Adobe Commerce differ around split database behavior; a patch may target the wrong connection.	Confirm the edition and branch before codifying connection names in CI.
Cron or queue writes during upgrade	Consumers insert rows into a table that is being altered, causing lock waits or partial data.	Stop cron and queue consumers before setup:upgrade and restart them after smoke tests.

When to choose a different design

This design fits small, bounded schema additions and reference data. Change the approach when:

• Large tables need online changes. For high-traffic production tables, use an expand/contract migration. Add a nullable column or a new table in one release, backfill existing rows in batches outside peak traffic, switch reads to the new structure, and remove the old column in a later release.
• Changes are configuration or content only. Store configuration, CMS blocks, and catalog attributes do not require schema patches. Use etc/config.xml, data patches for system configuration only when necessary, or the import framework for content.
• The module ships as a marketplace extension. Follow the same declarative pattern, but document patch classes clearly so merchants can audit what will run.

Limitations and practical verification

Exact CLI options, patch discovery rules, and split-database behavior vary by Magento version and edition. Confirm the exact branch before codifying commands in CI. The examples above illustrate the pattern; they are not tested output for your environment.

To verify the result, clone production-like data to a staging environment. Run the upgrade once on a fresh copy and once on an already-upgraded copy to prove idempotency. Compare the declared schema in etc/db_schema.xml against the actual tables using a schema diff tool or manual inspection. Check application logs for errors, and confirm that cron and queue consumers were not writing during the upgrade window.