Creating Database Tables

File Location

Vendor/Module/etc/db_schema.xml

Basic Table Definition

<?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_warranty" resource="default" engine="innodb"
           comment="Vendor Warranty">
        <column xsi:type="int" name="warranty_id" padding="10" unsigned="true"
                nullable="false" identity="true" comment="Warranty ID"/>
        <column xsi:type="varchar" name="name" nullable="false" length="255"
                comment="Warranty Name"/>
        <column xsi:type="int" name="duration" padding="10" unsigned="true"
                nullable="false" comment="Duration in Months"/>
        <column xsi:type="decimal" name="price" scale="4" precision="12"
                unsigned="false" nullable="false" comment="Price"/>
        <column xsi:type="text" name="description" nullable="true"
                comment="Description"/>
        <column xsi:type="varchar" name="sku" nullable="false" length="255"
                comment="SKU"/>
        <column xsi:type="smallint" name="status" padding="5" unsigned="true"
                nullable="false" comment="Status"/>
        <column xsi:type="datetime" name="created_at" nullable="true"
                comment="Created At"/>
        <column xsi:type="datetime" name="updated_at" nullable="true"
                comment="Updated At"/>

        <constraint referenceType="primary">
            <column name="warranty_id"/>
        </constraint>

        <index referenceType="index">
            <column name="sku"/>
        </index>

        <index referenceType="unique">
            <column name="sku"/>
        </index>
    </table>
</schema>

Table Attributes

Available Column Types

<!-- Integer -->
<column xsi:type="int" name="id" padding="10" unsigned="true"
        nullable="false" identity="true"/>

<!-- Small Integer -->
<column xsi:type="smallint" name="status" padding="5" unsigned="true"
        nullable="false"/>

<!-- Tiny Integer -->
<column xsi:type="tinyint" name="flag" padding="3" unsigned="true"
        nullable="false"/>

<!-- Big Integer -->
<column xsi:type="bigint" name="large_id" padding="20" unsigned="true"
        nullable="false"/>

<!-- Decimal -->
<column xsi:type="decimal" name="price" scale="4" precision="12"
        unsigned="false" nullable="false"/>

<!-- Varchar -->
<column xsi:type="varchar" name="name" nullable="false" length="255"/>

<!-- Text -->
<column xsi:type="text" name="description" nullable="true"/>

<!-- Boolean -->
<column xsi:type="boolean" name="is_active" default="1" nullable="false"/>

<!-- Datetime -->
<column xsi:type="datetime" name="created_at" nullable="true"/>

<!-- Date -->
<column xsi:type="date" name="start_date" nullable="true"/>

<!-- Blob -->
<column xsi:type="blob" name="image_data" nullable="true"/>

Column Attributes

Primary Key

<constraint referenceType="primary">
    <column name="warranty_id"/>
</constraint>

Unique Index

<constraint referenceType="unique" xsi:type="unique">
    <column name="sku"/>
</constraint>

Foreign Key

<constraint referenceType="foreign" xsi:type="foreign"
           referenceTable="catalog_product_entity"
           referenceColumn="entity_id"
           onDelete="CASCADE">
    <column name="product_id"/>
</constraint>

Foreign key actions:

• CASCADE: Delete related rows
• SET NULL: Set to NULL
• RESTRICT: Prevent deletion
• NO ACTION: Same as RESTRICT

Regular Index

<index referenceType="index">
    <column name="status"/>
</index>

<!-- Composite index -->
<index referenceType="index">
    <column name="status"/>
    <column name="created_at"/>
</index>

Fulltext Index

<index referenceType="fulltext">
    <column name="name"/>
    <column name="description"/>
</index>

Complete Example with All Constraints

<table name="vendor_warranty" resource="default" engine="innodb">
    <column xsi:type="int" name="warranty_id" padding="10" unsigned="true"
            nullable="false" identity="true"/>
    <column xsi:type="int" name="product_id" padding="10" unsigned="true"
            nullable="false"/>
    <column xsi:type="varchar" name="sku" nullable="false" length="255"/>
    <column xsi:type="smallint" name="status" padding="5" unsigned="true"
            nullable="false"/>

    <constraint referenceType="primary">
        <column name="warranty_id"/>
    </constraint>

    <constraint referenceType="unique">
        <column name="sku"/>
    </constraint>

    <constraint referenceType="foreign" referenceTable="catalog_product_entity"
                referenceColumn="entity_id" onDelete="CASCADE">
        <column name="product_id"/>
    </constraint>

    <index referenceType="index">
        <column name="status"/>
    </index>
</table>

Whitelist File

Generate the whitelist after creating schema:

bin/magento setup:db-declaration:generate-whitelist

This creates db_schema_whitelist.json to track allowed changes.

Data Patches Purpose

Data patches populate tables with initial data, migrate data, or perform one-time data operations.

Install Data Patch

<?php
namespace Amazon\Prep\Setup\Patch\Data;

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

class InstallWarrantyTypes implements DataPatchInterface
{
    public function __construct(
        private ModuleDataSetupInterface $moduleDataSetup
    ) {}

    public function apply(): void
    {
        $this->moduleDataSetup->startSetup();

        $table = $this->moduleDataSetup->getTable('vendor_warranty_type');

        $data = [
            ['code' => 'standard', 'name' => 'Standard', 'duration_months' => 12],
            ['code' => 'extended', 'name' => 'Extended', 'duration_months' => 24],
            ['code' => 'premium', 'name' => 'Premium', 'duration_months' => 36],
        ];

        foreach ($data as $row) {
            $this->moduleDataSetup->getConnection()->insert($table, $row);
        }

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

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

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

Data Patch with Dependencies

class AddWarrantyPrices implements DataPatchInterface
{
    public function __construct(
        private ModuleDataSetupInterface $moduleDataSetup,
        private WarrantyTypeRepositoryInterface $warrantyTypeRepository
    ) {}

    public function apply(): void
    {
        $this->moduleDataSetup->startSetup();

        $types = $this->warrantyTypeRepository->getList(
            $this->searchCriteriaBuilder->create()
        )->getItems();

        foreach ($types as $type) {
            $price = $this->calculatePrice($type);
            // Update price...
        }

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

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

Rollback

public function rollback(): void
{
    $this->moduleDataSetup->startSetup();

    $table = $this->moduleDataSetup->getTable('vendor_warranty_type');
    $this->moduleDataSetup->getConnection()->truncateTable($table);

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

Schema vs Data Patches

Running Patches

bin/magento setup:upgrade

This runs all pending data patches and applies schema changes.