Schema Patches

Schema vs Data Patches

// Schema patch: modifies database structure (tables, columns, indexes)
class AddCustomColumn implements SchemaPatchInterface
{
    public function __construct(
        private ModuleDataSetupInterface $setup
    ) {}

    public function install(): void
    {
        $this->setup->startSetup();
        $connection = $this->setup->getConnection();
        $connection->addColumn(
            $this->setup->getTable('catalog_product_entity'),
            'custom_column',
            [
                'type' => Table::TYPE_TEXT,
                'length' => 255,
                'nullable' => true,
                'comment' => 'Custom Column'
            ]
        );
        $this->setup->endSetup();
    }
}

// Data patch: modifies data within tables
class PopulateCustomColumn implements DataPatchInterface
{
    public function install(): void
    {
        $this->setup->startSetup();
        $connection = $this->setup->getConnection();
        $connection->update(
            $this->setup->getTable('catalog_product_entity'),
            ['custom_column' => new \Zend_Db_Expr('"default_value"')]
        );
        $this->setup->endSetup();
    }
}

When to Use Each

Interface Methods

use Magento\Framework\Setup\Patch\SchemaPatchInterface;

interface SchemaPatchInterface
{
    /**
     * Run the patch
     */
    public function install(): void;

    /**
     * List of aliases for the patch
     */
    public function getAliases(): array;

    /**
     * List of dependencies
     */
    public function getDependencies(): array;
}

Complete Example

<?php
namespace Vendor\Module\Setup\Patch\Schema;

use Magento\Framework\Setup\Patch\SchemaPatchInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\DB\Ddl\Table;

class AddProductRatingColumn implements SchemaPatchInterface
{
    public function __construct(
        private ModuleDataSetupInterface $setup
    ) {}

    public function install(): void
    {
        $this->setup->startSetup();
        $connection = $this->setup->getConnection();

        $tableName = $this->setup->getTable('catalog_product_entity');

        // Check if column exists
        if (!$connection->tableColumnExists($tableName, 'avg_rating')) {
            $connection->addColumn(
                $tableName,
                'avg_rating',
                [
                    'type' => Table::TYPE_DECIMAL,
                    'scale' => 2,
                    'precision' => 12,
                    'unsigned' => false,
                    'nullable' => true,
                    'comment' => 'Average Rating'
                ]
            );

            // Add index for sorting by rating
            $connection->addIndex(
                $tableName,
                $this->setup->getIdxName('catalog_product_entity', ['avg_rating']),
                ['avg_rating']
            );
        }

        $this->setup->endSetup();
    }

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

    public function getDependencies(): array
    {
        return [
            \Magento\Catalog\Setup\Patch\Schema\CreateProductSentinelTable::class
        ];
    }
}

Adding Columns

$connection->addColumn(
    $this->setup->getTable('vendor_module_table'),
    'new_column',
    [
        'type' => Table::TYPE_VARCHAR,
        'length' => 255,
        'nullable' => true,
        'default' => null,
        'comment' => 'New Column'
    ]
);

Modifying Columns

// Change column type
$connection->modifyColumn(
    $this->setup->getTable('vendor_module_table'),
    'old_column',
    [
        'type' => Table::TYPE_TEXT,
        'length' => 65535,
        'nullable' => true,
        'comment' => 'Updated Column'
    ]
);

// Rename column
$connection->renameColumn(
    $this->setup->getTable('vendor_module_table'),
    'old_name',
    'new_name'
);

Adding Indexes

// B-tree index
$connection->addIndex(
    $this->setup->getTable('vendor_module_table'),
    $this->setup->getIdxName('vendor_module_table', ['column1', 'column2']),
    ['column1', 'column2']
);

// Unique index
$connection->addIndex(
    $this->setup->getTable('vendor_module_table'),
    $this->setup->getIdxName('vendor_module_table', ['sku'], Table::INDEX_TYPE_UNIQUE),
    ['sku'],
    Table::INDEX_TYPE_UNIQUE
);

// Fulltext index
$connection->addIndex(
    $this->setup->getTable('vendor_module_table'),
    $this->setup->getIdxName('vendor_module_table', ['name', 'description'], Table::INDEX_TYPE_FULLTEXT),
    ['name', 'description'],
    Table::INDEX_TYPE_FULLTEXT
);

Adding Constraints

// Foreign key
$connection->addForeignKey(
    $this->setup->getFkName('vendor_module_table', 'category_id', 'catalog_category_entity', 'entity_id'),
    $this->setup->getTable('vendor_module_table'),
    'category_id',
    $this->setup->getTable('catalog_category_entity'),
    'entity_id',
    Table::ACTION_CASCADE
);

// Remove foreign key
$connection->dropForeignKey(
    $this->setup->getFkName('vendor_module_table', 'category_id', 'catalog_category_entity', 'entity_id')
);

// Remove index
$connection->dropIndex(
    $this->setup->getTable('vendor_module_table'),
    $this->setup->getIdxName('vendor_module_table', ['old_column'])
);

Safety Checks

public function install(): void
{
    $this->setup->startSetup();
    $connection = $this->setup->getConnection();
    $tableName = $this->setup->getTable('vendor_module_table');

    // Check if table exists before modifying
    if ($connection->isTableExists($tableName)) {
        // Check if column exists before adding
        if (!$connection->tableColumnExists($tableName, 'new_column')) {
            $connection->addColumn($tableName, 'new_column', [...]);
        }

        // Check if index exists before adding
        $idxName = $this->setup->getIdxName($tableName, ['new_column']);
        if (!$connection->isTableExists($tableName) || 
            !$connection->getIndexList($tableName)[$idxName] ?? false) {
            $connection->addIndex($tableName, $idxName, ['new_column']);
        }
    }

    $this->setup->endSetup();
}

Patch Tracking

-- Schema version tracking
SELECT name, schema_version, data_version
FROM setup_module
WHERE name = 'Vendor_Module';

-- After running schema patch, version updates
-- schema_version: 1.0.0 → 1.0.1

Common Patterns

// Pattern 1: Safe column addition
if (!$connection->tableColumnExists($table, 'column')) {
    $connection->addColumn($table, 'column', [...]);
}

// Pattern 2: Conditional index
$idxName = $setup->getIdxName($table, ['column']);
if (!array_key_exists($idxName, $connection->getIndexList($table))) {
    $connection->addIndex($table, $idxName, ['column']);
}

// Pattern 3: Table creation with checks
if (!$connection->isTableExists($table)) {
    $tableObj = $connection->newTable($table)
        ->addColumn(...)
        ->setComment('Table Comment');
    $connection->createTable($tableObj);
}

Debugging Schema Patches

# Check patch status
bin/magento setup:patch:status

# Force reapply
bin/magento setup:upgrade --force

# Check database directly
mysql -u root -p -e "DESCRIBE catalog_product_entity;"

# Verify whitelist
bin/magento setup:db-declaration:compare-whitelist