Magento 2 Database Schema: Declarative Schema Guide

Since Magento 2.3, database structure is declared, not scripted: db_schema.xml describes the desired end state, and Magento computes the migration. Declarative schema killed an entire class of deployment bugs - but it has its own rules, especially around destructive changes and the whitelist file. Here is the working guide.

The Basic Shape

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <table name="acme_location" resource="default" engine="innodb">
        <column xsi:type="int" name="entity_id" unsigned="true"
                nullable="false" identity="true" comment="ID"/>
        <column xsi:type="varchar" name="name" nullable="false" length="255" comment="Name"/>
        <column xsi:type="decimal" name="latitude" scale="7" precision="10" nullable="true"/>
        <column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <index referenceId="ACME_LOCATION_NAME" indexType="btree">
            <column name="name"/>
        </index>
    </table>
</schema>

setup:upgrade diffs this against the database and applies the delta: create the table, add the column, build the index. No install scripts, no version comparison, no “did the 1.0.3 upgrade run before the 1.0.2 data patch” questions.

The Whitelist: Your Destructive-Change Safety Net

Destructive operations (dropping a column or table) require history. Generate the whitelist once:

bin/magento setup:db-declaration:generate-whitelist --module-name=Acme_Locations

This writes etc/db_schema_whitelist.json recording every declared element. Only whitelisted elements may later be removed - the whitelist is what lets Magento distinguish “you deleted this column on purpose” from “this column was never yours”. Commit it. Forgetting it makes future column drops silently refuse to apply.

Rules That Save Pain

1. Never edit db_schema.xml history carelessly: renaming a column is a drop-plus-create (data loss on that column). To preserve data: add the new column, data-patch the copy, remove the old in a later release
2. Foreign keys use <constraint xsi:type="foreign"> with onDelete behaviour chosen deliberately - CASCADE deletes are permanent and silent
3. Data still uses patches: declarative schema owns structure; data migrations remain Setup\Patch\Data classes
4. Old InstallSchema scripts: leave them for old installs until you convert; the SchemaPatchInterface era is superseded, not removed

Validating Before Deploy

bin/magento setup:db-declaration:generate-schema renders the effective schema SQL for inspection, and setup:upgrade --dry-run=1 (with safe-mode flags) previews DDL. In CI, a dry-run against a production-like database catches destructive diffs before Friday’s deploy.

Declarative schema turned Magento database work from procedural history into stated intent. Declare the end state, whitelist the removals, let the framework diff - and the whole category of “partially applied upgrade script” incidents disappears.