Planning Database Migrations with AI Assistance

1. Why database migrations in Magento demand extra care

A database migration in Magento rarely touches an isolated table. The EAV structure of the product catalog, foreign keys between orders, customers and payment data, and the indexer pipeline all react sensitively to schema changes. Removing a column or changing a data type can unintentionally break custom modules, third-party extensions, or reporting queries that rely on the original structure. This is exactly the environment where Claude can be a genuinely useful tool: it quickly produces a first, technically sound draft for a db_schema.xml change or a data patch based on a plain-language description of the intended change.

The crucial point is that this speed must never be mistaken for safety. An AI-generated suggestion knows neither the actual data distribution in the production database nor all the dependencies that accumulated over years in a grown Magento project. Migrations that touch production data therefore deserve the same review process as any other critical code change, regardless of how convincing the suggestion looks at first glance.

2. Understanding db_schema.xml: declarative schema over InstallSchema

Since Magento 2.3, the declarative schema in db_schema.xml has replaced the old imperative InstallSchema and UpgradeSchema scripts. Instead of writing commands that alter a table step by step, you describe the desired end state declaratively. When bin/magento setup:upgrade runs, Magento automatically compares this target state against the current database schema and generates the necessary ALTER and CREATE statements itself. That reduces boilerplate significantly and makes schema changes across multiple modules easier to follow.

For this automation to work reliably, every change also has to be registered in db_schema_whitelist.json, otherwise Magento silently ignores it during the upgrade. This is where Claude is genuinely practical: given the existing content of db_schema.xml and the desired end state as context, the model can propose both the matching XML diff and the corresponding whitelist entry in one step, saving manual lookups of attribute ordering, column types, and constraint naming conventions.

3. From a plain-language requirement to a schema proposal

The quality of an AI-generated schema proposal depends almost entirely on the context you provide. A vague request like "add a warranty attribute" leads to a generic, often imprecise suggestion. Much better results come from pasting the relevant excerpt of the existing db_schema.xml, naming the exact column name, data type, nullable status, and any indexes, and explicitly asking for a complete diff including the whitelist entry.

Another important building block is asking Claude specifically about risks instead of only about the solution: "Which existing rows could become invalid because of this change?" or "Does this column need a default value so existing rows don't break?" often produces more valuable insight than the code suggestion alone. In Claude Code, the existing db_schema.xml can also be referenced directly as a file, keeping the suggestion consistent with the actual project structure instead of relying on training-data assumptions about a generic Magento installation.

4. Drafting data patches with AI assistance

While db_schema.xml only describes structure, data patches (DataPatchInterface) handle the actual data migration: converting existing values, copying from an old column into a new one, or setting default values for rows that already exist. Claude can turn a description like "copy the value from custom_price into the new discounted_price column, but only if it is smaller than price" into a complete patch class skeleton, including getDependencies() and getAliases().

One point AI suggestions regularly underestimate is the size of the table being migrated. A naive patch that runs unbatched UPDATE statements against a table with millions of rows can cause table locks and stall the storefront during migration. A good prompt therefore explicitly requests batch processing with a configurable batch size and progress logging, so the patch runs safely on large production databases rather than only working on a small test database.

<!-- db_schema.xml: add nullable warranty_months column via a separate extension 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">

    <table name="mironsoft_product_warranty" resource="default" engine="innodb" comment="Product Warranty Extension">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="false" comment="Product ID"/>
        <column xsi:type="smallint" name="warranty_months" padding="5" unsigned="true" nullable="true" identity="false" comment="Warranty in months, nullable until backfill patch runs"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="MIRONSOFT_WARRANTY_PRODUCT_ENTITY_ID_CPE_ENTITY_ID"
                    table="mironsoft_product_warranty" column="entity_id"
                    referenceTable="catalog_product_entity" referenceColumn="entity_id"
                    onDelete="CASCADE"/>
    </table>
</schema>

5. Why AI suggestions on production data must never be applied blindly

An AI model can produce syntactically flawless, stylistically convincing code that is nonetheless wrong in substance, particularly when it comes to actual data distribution. Claude knows neither how many rows are currently NULL, nor whether a supposedly unique column contains duplicates in practice, nor which custom modules silently depend on the affected table. A confidently worded suggestion is therefore no substitute for checking it against the real database, and how persuasive the wording sounds says nothing about how correct it is.

In practice, this means every AI-generated schema or data proposal goes through the same review process as any other change that touches production data, with no shortcut along the lines of "the AI already checked it." That includes a code review by someone familiar with the project's grown structure, a test run against a current copy of the production database, and an explicit check of the number of affected rows before and after the migration. These steps are standard practice for hand-written migrations, and they are equally mandatory for AI-generated ones, no matter how sound the suggestion appears.

6. Practical example: a new required field plus data migration, step by step

A typical scenario: a product attribute warranty_months should be introduced and eventually become a required field (NOT NULL). A direct NOT NULL step with no default would make setup:upgrade fail immediately as soon as a single existing row contains NULL. The safe path consists of two separate steps: first the column is created as nullable, then a data patch runs that fills existing rows with a sensible value, and only in a later release is the column switched to NOT NULL.

Claude is well suited to proposing exactly this two-stage structure when explicitly asked for a production-safe migration strategy rather than the fastest solution. The example below shows the corresponding data patch skeleton with batch processing, the kind of output such a request can produce, together with the commands needed to verify the result from the command line.

<?php
declare(strict_types=1);

namespace Mironsoft\ProductWarranty\Setup\Patch\Data;

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

/**
 * Backfills warranty_months for existing products in batches.
 */
class BackfillWarrantyMonths implements DataPatchInterface
{
    private const BATCH_SIZE = 500;
    private const DEFAULT_WARRANTY_MONTHS = 24;

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

    /**
     * Applies the backfill in batches to avoid table locks on large catalogs.
     *
     * @return void
     */
    public function apply(): void
    {
        $connection = $this->moduleDataSetup->getConnection();
        $table = $this->moduleDataSetup->getTable('mironsoft_product_warranty');
        $productTable = $this->moduleDataSetup->getTable('catalog_product_entity');

        $lastEntityId = 0;
        do {
            $select = $connection->select()
                ->from(['e' => $productTable], ['entity_id'])
                ->joinLeft(['w' => $table], 'w.entity_id = e.entity_id', [])
                ->where('w.entity_id IS NULL')
                ->where('e.entity_id > ?', $lastEntityId)
                ->order('e.entity_id ASC')
                ->limit(self::BATCH_SIZE);

            $entityIds = $connection->fetchCol($select);
            foreach ($entityIds as $entityId) {
                $connection->insert($table, [
                    'entity_id' => $entityId,
                    'warranty_months' => self::DEFAULT_WARRANTY_MONTHS,
                ]);
                $lastEntityId = (int) $entityId;
            }
        } while (count($entityIds) === self::BATCH_SIZE);
    }

    /**
     * @return string[]
     */
    public static function getDependencies(): array
    {
        return [];
    }

    /**
     * @return string[]
     */
    public function getAliases(): array
    {
        return [];
    }
}

#!/usr/bin/env bash
# Generate the declarative schema diff and verify it before applying
set -euo pipefail

# Show pending schema changes without applying them
bin/magento setup:db:status

# Recreate the whitelist after adding a new table or column
bin/magento setup:db-declaration:generate-whitelist --module-name=Mironsoft_ProductWarranty

# Apply schema changes and run data patches
bin/magento setup:upgrade

# Verify affected row count before and after the data patch runs
bin/mysql -e "SELECT COUNT(*) AS total, SUM(warranty_months IS NULL) AS still_null FROM mironsoft_product_warranty;"

{
    "mironsoft_product_warranty": {
        "column": {
            "entity_id": true,
            "warranty_months": true
        },
        "constraint": {
            "PRIMARY": true,
            "MIRONSOFT_WARRANTY_PRODUCT_ENTITY_ID_CPE_ENTITY_ID": true
        }
    }
}

7. Rollback strategy: backups, reversible patches and dry runs

Declarative schema management can remove a column from the database once it is removed from db_schema.xml, but it never automatically restores the data that column previously held. A "rollback" at the schema level is a rollback of structure, not of data. That is why a full database backup with mysqldump or a snapshot copy of the database volume belongs in front of every migration that changes production data, regardless of whether the change was designed by a person or with AI assistance.

For data patches, it is also worth making the migration itself reversible: copy old values into a temporary backup column or backup table before overwriting them, instead of overwriting directly. That allows a targeted reversal if something goes wrong, without having to restore the entire backup. A dry run against a staging copy, where the number of affected rows is logged before the actual execution and compared against expectations, catches most wrong assumptions before they reach the production database.

#!/usr/bin/env bash
# Backup before migration, plus rollback procedure if verification fails
set -euo pipefail

# 1. Full backup before any schema or data change touches production
bin/mysqldump --single-transaction --routines --triggers magento > "backup-$(date +%Y%m%d-%H%M%S).sql"

# 2. Dry-run on a staging copy first, log the affected row count
bin/mysql staging_db -e "SELECT COUNT(*) FROM catalog_product_entity WHERE entity_id NOT IN (SELECT entity_id FROM mironsoft_product_warranty);"

# 3. If verification fails after deploy, restore from the backup
bin/mysql magento < backup-20260712-090000.sql

# 4. Schema-only rollback: remove the table definition and regenerate the whitelist
# (does NOT restore data - the backup above is the actual safety net)
bin/magento setup:db-declaration:generate-whitelist --module-name=Mironsoft_ProductWarranty
bin/magento setup:upgrade

8. Testing pipeline: staging copy, comparison and verification

A reliable testing pipeline for database migrations starts with a current, anonymized copy of the production database on a staging environment, not a small test database with a handful of sample rows. Only a realistic data volume reliably reveals problems such as missing indexes, unexpected NULL values, or runtime issues on large tables. bin/magento setup:upgrade runs against staging first, followed by targeted SQL queries that compare row counts, value ranges, and samples before and after the migration.

Automated tests complement manual verification, but they do not fully replace it. An integration test can check that the new column exists and has the expected type, but not whether the migrated values are correct from a business standpoint. Verification should therefore always include a spot-check by someone who understands what the data actually means, combined with a comparison of totals or checksums before and after the migration to catch systematic errors early.

9. AI assistance compared: task, risk, review effort

Not every migration task carries the same risk, and the required review effort should scale accordingly. The following overview ranks typical tasks by the risk an uncritically applied AI suggestion carries when it touches production data.

What stands out is that structurally simple changes, like an additional nullable column, genuinely carry low risk and can be reviewed with modest effort, while data migration, foreign key changes, and dropping columns or tables justify a considerably higher level of scrutiny. A blanket rule of "always manually test AI suggestions" is correct, but the depth of that review should scale with the actual risk of the specific task.

10. Summary

Database migrations with AI assistance solve a real problem: drafting a db_schema.xml diff or a data patch from a plain-language description is noticeably faster with Claude than manually looking up attribute syntax and whitelist conventions. This is especially true for two-stage migrations, such as a nullable column followed by a backfill patch and a later NOT NULL constraint, where a well-formed prompt provides a solid starting point.

The decisive factor, however, remains manual verification. No AI model knows the real data distribution, all the historically grown dependencies, or the actual table size in production. A backup before the migration, a dry run against a current staging copy, a code review by someone with project knowledge, and a clear rollback plan belong to every migration that touches production data, no matter how convincing the AI suggestion looks.

11. FAQ: Database Migrations with AI Assistance