Declarative Schema

Declarative Schema Overview

What is Declarative Schema?

Magento 2.3+ introduced declarative schema as an alternative to InstallSchema/UpgradeSchema:

<!-- Traditional approach (InstallSchema.php) -->
public function install()
{
    $installer = $this;
    $installer->startSetup();
    $table = $installer->getConnection()->newTable($installer->getTable('custom_table'))
        ->addColumn('entity_id', 
            Table::TYPE_INTEGER, null, 
            ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
            'Entity ID')
        ->addColumn('name', 
            Table::TYPE_TEXT, 255, 
            ['nullable' => false],
            'Name')
        ->setComment('Custom Table');
    $installer->getConnection()->createTable($table);
    $installer->endSetup();
}

<!-- Declarative approach (db_schema.xml) -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="custom_table">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="varchar" name="name" length="255" nullable="false" comment="Name"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
    </table>
</schema>

Benefits

Clearer: XML is easier to read than PHP
Maintainable: No PHP code to maintain
Reversible: Tables can be removed by removing XML
Conflicts: Easier to detect and resolve merge conflicts

Column Definitions

Column Types

<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_custom">
        <!-- Integer columns -->
        <column xsi:type="int" name="entity_id" unsigned="true" 
                nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="smallint" name="status" unsigned="true" 
                nullable="false" default="0" comment="Status"/>
        <column xsi:type="tinyint" name="is_active" 
                nullable="false" default="1" comment="Is Active"/>
        
        <!-- String columns -->
        <column xsi:type="varchar" name="name" length="255" 
                nullable="false" comment="Name"/>
        <column xsi:type="text" name="description" nullable="true" 
                comment="Description"/>
        <column xsi:type="text" name="description_html" nullable="true" 
                length="64k" comment="Description HTML"/>
        
        <!-- Numeric columns -->
        <column xsi:type="decimal" name="price" scale="4" precision="12" 
                unsigned="true" nullable="false" default="0.0000" comment="Price"/>
        <column xsi:type="float" name="weight" nullable="true" comment="Weight"/>
        
        <!-- Date columns -->
        <column xsi:type="datetime" name="created_at" nullable="true" 
                comment="Created At"/>
        <column xsi:type="datetime" name="updated_at" nullable="true" 
                comment="Updated At"/>
        
        <!-- Other types -->
        <column xsi:type="blob" name="image_data" nullable="true" comment="Image Data"/>
        <column xsi:type="json" name="metadata" nullable="true" comment="Metadata"/>
    </table>
</schema>

Column Properties

Property	Description	Example
xsi:type	Column type	int, varchar, text, decimal
name	Column name	entity_id
length	Max length (varchar)	255
nullable	Allow NULL	true/false
default	Default value	0, 'value'
unsigned	Unsigned integer	true/false
identity	Auto-increment	true/false
scale	Decimal scale	4
precision	Decimal precision	12
comment	Column comment	'Entity ID'

Indexes and Constraints

Index Types

<table name="vendor_module_custom">
    <column xsi:type="int" name="entity_id" unsigned="true" 
            nullable="false" identity="true"/>
    <column xsi:type="varchar" name="sku" length="255" nullable="false"/>
    <column xsi:type="int" name="category_id" unsigned="true" nullable="false"/>
    <column xsi:type="int" name="status" unsigned="true" nullable="false"/>
    
    <!-- Primary key -->
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="entity_id"/>
    </constraint>
    
    <!-- Unique index -->
    <constraint xsi:type="unique" referenceId="VENDOR_MODULE_SKU">
        <column name="sku"/>
    </constraint>
    
    <!-- Index -->
    <index referenceId="VENDOR_MODULE_CATEGORY_ID" indexType="btree">
        <column name="category_id"/>
    </index>
    
    <!-- Composite index -->
    <index referenceId="VENDOR_MODULE_STATUS_CATEGORY">
        <column name="status"/>
        <column name="category_id"/>
    </index>
    
    <!-- Fulltext index -->
    <index referenceId="VENDOR_MODULE_SEARCH" indexType="fulltext">
        <column name="name"/>
        <column name="description"/>
    </index>
</table>

Foreign Key Constraints

<!-- Reference to catalog_category_entity -->
<constraint xsi:type="foreign" referenceId="VENDOR_MODULE_CATEGORY_ID_CATEGORY"
            table="vendor_module_custom" column="category_id"
            referenceTable="catalog_category_entity" referenceColumn="entity_id"
            onDelete="CASCADE"/>

<!-- Reference to customer_entity -->
<constraint xsi:type="foreign" referenceId="VENDOR_MODULE_CUSTOMER_ID_CUSTOMER"
            table="vendor_module_custom" column="customer_id"
            referenceTable="customer_entity" referenceColumn="entity_id"
            onDelete="SET NULL"/>

Reference Actions

Action	Description
CASCADE	Delete/update children when parent is deleted/updated
SET NULL	Set foreign key to NULL when parent is deleted
RESTRICT	Prevent deletion if references exist
NO ACTION	Similar to RESTRICT

Table Operations and Whitelist

Table Operations

<!-- Remove a table -->
<table name="old_unused_table" resource="deprecated"/>

<!-- Rename a table -->
<table name="old_name" resource="default"/>
<!-- Use setup:db-declaration:compare-whitelist to detect renames -->

<!-- Add comment -->
<table name="vendor_module_custom" resource="default" comment="Custom Module Table">
    <!-- columns -->
</table>

Whitelist Management

# Generate whitelist of managed tables
bin/magento setup:db-declaration:generate-whitelist

# Output: app/code/Vendor/Module/etc/db_schema_whitelist.json
{
    "tables": {
        "vendor_module_custom": {
            "column": {
                "entity_id": true,
                "name": true,
                "sku": true
            },
            "constraint": {
                "PRIMARY": true,
                "VENDOR_MODULE_SKU": true
            },
            "index": {
                "VENDOR_MODULE_CATEGORY_ID": true
            }
        }
    }
}

# Compare whitelist with actual schema
bin/magento setup:db-declaration:compare-whitelist

# Remove deprecated tables from whitelist
bin/magento setup:db-declaration:remove-old-table vendor_module_custom

Complete Module Example

<!-- app/code/Vendor/Module/etc/db_schema.xml -->
<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_post" resource="default" comment="Blog Post">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" 
                identity="true" comment="Entity ID"/>
        <column xsi:type="varchar" name="title" length="255" nullable="false" 
                comment="Title"/>
        <column xsi:type="varchar" name="url_key" length="255" nullable="false" 
                comment="URL Key"/>
        <column xsi:type="text" name="content" nullable="true" comment="Content"/>
        <column xsi:type="smallint" name="status" unsigned="true" nullable="false" 
                default="1" comment="Status"/>
        <column xsi:type="int" name="author_id" unsigned="true" nullable="true" 
                comment="Author ID"/>
        <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 xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="unique" referenceId="VENDOR_MODULE_URL_KEY">
            <column name="url_key"/>
        </constraint>
        <index referenceId="VENDOR_MODULE_STATUS">
            <column name="status"/>
        </index>
        <index referenceId="VENDOR_MODULE_AUTHOR_ID">
            <column name="author_id"/>
        </index>
    </table>
</schema>

Flashcards

Question

What is declarative schema?

Click to reveal answer

Answer

XML-based database structure definitions in db_schema.xml

Question

What is the whitelist?

Click to reveal answer

Answer

JSON file tracking managed tables, columns, indexes, constraints

Question

How to generate whitelist?

Click to reveal answer

Answer

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

Question

What column types are available?

Click to reveal answer

Answer

int, varchar, text, decimal, datetime, blob, json

Question

How to remove a table?

Click to reveal answer

Answer

Remove table definition from db_schema.xml and update whitelist

Revision Notes

Key Takeaways

1. Declarative schema uses db_schema.xml instead of PHP InstallSchema
2. Supports all column types: int, varchar, text, decimal, datetime, blob, json
3. Constraints: primary, unique, foreign, index, fulltext
4. Whitelist tracks managed schema elements
5. Tables can be removed by deleting from db_schema.xml

Interview Tips

• Compare declarative schema with traditional InstallSchema approach
• Explain how the whitelist prevents accidental deletions
• Discuss handling schema conflicts in multi-developer environments

Cheat Sheet

db_schema.xml:
  <table name="table_name">
    <column xsi:type="int" name="col" identity="true"/>
    <column xsi:type="varchar" name="col" length="255"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
      <column name="col"/>
    </constraint>
    <index referenceId="IDX_NAME">
      <column name="col"/>
    </index>
  </table>

Commands:
  bin/magento setup:db-declaration:generate-whitelist
  bin/magento setup:db-declaration:compare-whitelist
  bin/magento setup:upgrade