DB Schema & Database Tables

Declarative schema places the structure of the database into XML. This provides the benefit of making upgrades easier in that the instructions for the upgrade come from one source.

This configuration is found in your module's etc/db_schema.xml configuration file.

The XSD file for db_schema.xml files is found in:

This is where you can see all the available options.

Table Element

The <table> element identifies the table that is created (or modified).

Required Attributes:

• name: Determines the name of the table in the database

To add a table to the database, specify its configuration in db_schema.xml.

<?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_module_entity" resource="default" engine="innodb" comment="Custom Entity Table">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" 
                identity="true" comment="Entity ID"/>
        <column xsi:type="varchar" name="name" nullable="false" length="255" comment="Name"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" 
                default="CURRENT_TIMESTAMP" comment="Created At"/>
        
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
    </table>
</schema>

To remove a table, remove the table from where it is declared in db_schema.xml.

Column Element

The <column> element configures columns to be added to the database.

You can add a column into an existing Magento table. Example: adding a delivery_date column into the Magento quote 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="quote">
        <column xsi:type="date" name="delivery_date" nullable="true" comment="Delivery Date"/>
    </table>
</schema>

Each column must have:

• name: The name of the column
• xsi:type: The type of column

Available Column Types:

You can specify other attributes:

To rename a column, use the onCreate="migrateDataFrom(old_column_name)" attribute.

<column xsi:type="int" name="new_entity_id" unsigned="true" nullable="false" 
        onCreate="migrateDataFrom(entity_id)" comment="Entity ID"/>

To delete a column, use the disabled="true" attribute.

Example: Remove the coupon_code column from the quote table:

<table name="quote">
    <column xsi:type="varchar" name="coupon_code" disabled="true"/>
</table>

You would most likely be adding a column, as removing or changing the column's name will have possible serious consequences.

Example: etc/module.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Vendor_Module">
        <sequence>
            <module name="Magento_Quote"/>
        </sequence>
    </module>
</config>

Constraint Element

Use the <constraint> tag for indexes and foreign keys.

There are two types of constraints in schema:

• Indexes - utilize the constraint tag with column class
• Foreign keys - specify details as attributes in the constraint tag

Indexes utilize the constraint tag but have a column element to build the structure of the index.

<constraint xsi:type="primary" referenceId="PRIMARY">
    <column name="entity_id"/>
</constraint>

<index referenceId="VENDOR_MODULE_ENTITY_NAME" indexType="btree">
    <column name="name"/>
</index>

Unique Index:

<constraint xsi:type="unique" referenceId="VENDOR_MODULE_ENTITY_EMAIL_UNIQUE">
    <column name="email"/>
</constraint>

Foreign keys do not utilize the column tag but rather specify all their details as attributes in the constraint tag.

<constraint xsi:type="foreign" 
            referenceId="VENDOR_MODULE_ENTITY_CUSTOMER_ID_CUSTOMER_ENTITY_ID" 
            table="vendor_module_entity" 
            column="customer_id" 
            referenceTable="customer_entity" 
            referenceColumn="entity_id" 
            onDelete="CASCADE"/>

Foreign Key Attributes:

• table: The table containing the foreign key
• column: The column in the table
• referenceTable: The referenced table
• referenceColumn: The referenced column
• onDelete: Action on delete (CASCADE, SET NULL, NO ACTION)

What are Data Patches?

Patches run incremental updates against the database. They perform operations that are not possible to do in the XML declaration.

You can initialize a patch with the CLI command:

bin/magento setup:db-declaration:generate-patch Vendor_Module PatchName

Data patches must be in your module's Setup/Patch/Data directory.

Data patches must implement \Magento\Framework\Setup\Patch\DataPatchInterface.

Three Required Methods:

• apply(): Takes action (performs the patch)
• getDependencies() (static): Returns an array of patches that this patch depends on
• getAliases(): If this patch ever changes names, this returns other names for the patch

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

use Magento\Framework\Setup\Patch\DataPatchInterface;

class AddSampleData implements DataPatchInterface
{
    private $moduleDataSetup;
    
    public function __construct(
        \Magento\Framework\Setup\ModuleDataSetupInterface $moduleDataSetup
    ) {
        $this->moduleDataSetup = $moduleDataSetup;
    }
    
    public function apply()
    {
        $this->moduleDataSetup->getConnection()->startSetup();
        
        // Your data manipulation logic here
        $connection = $this->moduleDataSetup->getConnection();
        $tableName = $this->moduleDataSetup->getTable('vendor_module_entity');
        
        $connection->insert($tableName, [
            'name' => 'Sample Entity',
            'status' => 1
        ]);
        
        $this->moduleDataSetup->getConnection()->endSetup();
        
        return $this;
    }
    
    public static function getDependencies()
    {
        return []; // Or return array of dependent patch class names
    }
    
    public function getAliases()
    {
        return []; // Or return array of old patch names
    }
}

If you wish to make a patch able to be rolled back, implement \Magento\Framework\Setup\Patch\PatchRevertableInterface.

<?php
class RevertablePatch implements DataPatchInterface, PatchRevertableInterface
{
    public function revert()
    {
        // Logic to revert the patch (run when module is uninstalled)
    }
    
    // ... other required methods
}

If you need to convert upgrade scripts to DB Schema and ensure the patch was only run once, use \Magento\Framework\Setup\Patch\PatchVersionInterface.

<?php
class VersionedPatch implements DataPatchInterface, PatchVersionInterface
{
    public static function getVersion()
    {
        return '2.0.1'; // Associate patch with specific version
    }
    
    // ... other required methods
}

What are Schema Patches?

Schema patches allow for intricate updates to the schema.

The process is very similar to data patches with two exceptions:

1. The patch should reside in your module's Setup/Patch/Schema directory
2. The patch should implement \Magento\Framework\Setup\Patch\SchemaPatchInterface

The whitelist file contains a history of all tables, columns, keys, and constraints that have been created or modified via declarative schema.

This file is auto-generated when you run:

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

• Configure Declarative Schema
• Develop Data and Schema Patches
• Declarative Schema Configuration