Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 42 ยท Data and the Database

Declarative Schema (db_schema.xml)

Describe your Magento tables in db_schema.xml and let setup:upgrade work out the SQL. Learn columns, constraints, indexes, the whitelist and schema diffing.

Older Magento 2 modules created tables with PHP scripts: an InstallSchema class for the first install and UpgradeSchema classes with if (version_compare(...)) blocks for every later change. To know what a table looked like today you had to replay the whole history in your head. Since Magento 2.3 there is a better way: declarative schema. You write the table as it should be in one XML file, and Magento compares that description with the real database and generates the needed CREATE TABLE or ALTER TABLE statements itself.

This lesson shows the structure of etc/db_schema.xml, the role of the whitelist file, how declarative schema relates to schema and data patches, and what a "schema diff" is. The real Magento files are read-only. The runnable stand-in is a small schema differ on SQLite that works on the same principle: desired state in, minimal changes out. For SQL basics see the SQL tutorial.

The idea: desired state, not history

Declarative means you describe the end result. Compare the two approaches for adding a priority column to a table:

  • Script style (old): write an upgrade script that runs addColumn once, guarded by a version number. The state of the table is spread over many scripts.
  • Declarative style: add one <column> line to db_schema.xml. Run bin/magento setup:upgrade. Magento notices that the database lacks the column and adds it. If the column already exists, nothing happens.

Because the file always describes the whole table, it is also easy to read in review, and another module can alter a table you own just by declaring more columns for the same table name (the XML files of all modules are merged, as described in Areas and Configuration Files).

flowchart LR
    X["db_schema.xml files<br/>(all modules, merged)"] --> D["Desired schema"]
    DB[("Real database")] --> C["Current schema<br/>(read from the DB)"]
    D --> F["Diff"]
    C --> F
    F --> S["CREATE / ALTER statements"]
    S --> DB

setup:upgrade reads both states, computes the difference and applies only what is missing.

The structure of db_schema.xml

The file lives in the module's etc/ folder. Here is a typical example for a table of notes that belong to customers. It is real Magento XML, shown read-only.

XML
<!-- Acme/Note/etc/db_schema.xml (read-only) -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="acme_note" resource="default" engine="innodb" comment="Customer notes">
        <column xsi:type="int" name="note_id" unsigned="true" nullable="false" identity="true"
                comment="Note ID"/>
        <column xsi:type="int" name="customer_id" unsigned="true" nullable="false"
                comment="Customer ID"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="text" name="body" nullable="true" comment="Body"/>
        <column xsi:type="smallint" name="priority" unsigned="true" nullable="false" default="0"
                comment="Priority"/>
        <column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP"
                comment="Created at"/>

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="note_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="ACME_NOTE_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID"
                    table="acme_note" column="customer_id"
                    referenceTable="customer_entity" referenceColumn="entity_id"
                    onDelete="CASCADE"/>
        <index referenceId="ACME_NOTE_CUSTOMER_ID" indexType="btree">
            <column name="customer_id"/>
        </index>
    </table>
</schema>

The pieces, one by one:

  • <table> has a name (with your vendor prefix, so you never collide with core tables), a resource (default for the main database; checkout and sales exist for split-database setups), an engine and a comment.
  • <column> uses xsi:type for the data type, for example int, smallint, bigint, varchar, text, decimal, boolean, date, datetime and timestamp. Common attributes are nullable, default, unsigned, length (for varchar), precision and scale (for decimal), and identity="true" for an auto-increment column.
  • <constraint> has an xsi:type of primary, unique or foreign. A foreign key names its own column and the referenceTable and referenceColumn it points to, plus an onDelete action such as CASCADE, SET NULL or NO ACTION.
  • <index> speeds up lookups; its columns are listed inside. indexType is usually btree.
  • referenceId is the stable identity of a constraint or index. Magento uses it to match the XML element with the real object, which means you must not change it casually: a new referenceId is a different element, so the old one is dropped and a new one created. Foreign key names are long because the convention builds them from the table and column names, and tools can generate them for you.

Notice also what is not in the file: no INSERT statements and no PHP. Declarative schema describes structure only. Starting data is created by data patches, covered in Data Patches and Schema Patches.

The whitelist file

Next to db_schema.xml you will find etc/db_schema_whitelist.json. Do not write it by hand. You generate it:

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

The whitelist records every table, column, constraint and index that your module has declared. Its job is safety. Magento drops something from the database only if two things are true: it is no longer in the XML, and it is in the whitelist as something your module once owned. That is how Magento tells "the developer deleted this column" apart from "this column belongs to another module or to a manual change, so leave it alone". Without the whitelist, removing a column from the XML would simply do nothing.

A typical workflow:

  1. Edit db_schema.xml (add, change or remove elements).
  2. Regenerate the whitelist and commit both files together.
  3. Run bin/magento setup:upgrade on each environment.

The whitelist grows with your module's history. If you rename a column in the XML, the old name stays in the whitelist, which is exactly what allows Magento to drop it. Be careful: dropping a column drops its data, and renaming is a drop plus an add. For a real rename that keeps the data, use a patch, or declare the new column, copy the data in a patch and remove the old column in a later release.

Declarative schema, schema patches and the old scripts

Three mechanisms exist, and they have different jobs:

Mechanism Job Runs
db_schema.xml the structure of tables on every setup:upgrade, as a diff
Schema patch rare structural changes XML cannot express once per patch class
Data patch inserting or changing data once per patch class

The old InstallSchema, UpgradeSchema, InstallData and UpgradeData scripts still run in modules that have them, and a module may even mix them, but new code should use declarative schema and patches only. Do not add new version-guarded scripts to modern modules. When you maintain an old module, the usual migration is to describe the tables as they exist today in db_schema.xml, generate the whitelist, and move the data changes into patches.

Because the diff works from the desired state, setup:upgrade is idempotent: running it twice changes nothing the second time. That property makes deployments repeatable.

A schema differ you can run

The first stand-in does the core job on SQLite. Given a desired schema as a PHP array, it reads the actual columns with PRAGMA table_info, computes a list of operations and applies it. Real Magento also handles constraints, indexes and many column types, and uses a whitelist to decide about drops. This stand-in handles tables and columns only, and it reports (but never executes) drops, so you can see why dropping is a separate decision.

PHP Stand-in: diffing a desired schema against SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Watch the four runs. The first creates the table, the second adds one column, the third does nothing (idempotency), and the fourth sees that title left the XML and is whitelisted, so it would be dropped. The final run shows the other half of the whitelist's job: a column nobody declared is kept.

Seeing the SQL before it runs

Because the diff is computed, you can ask what it would do. The setup:upgrade command accepts a --dry-run=1 option for the declarative part, which writes the planned statements to a log instead of executing them. Run it on a copy of production data before a big release, and read the output with particular attention to any DROP. The exact output location depends on the Magento version, so check the command's help on your installation.

The stand-in's second table demonstrates the same habit, in a smaller form: separate computing the plan from applying it.

PHP Stand-in: plan first, apply later

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Practical advice

  • Prefix your tables with your vendor and module name (acme_note). Never alter a core table by editing core files; if you must add a column to a core table, declare it in your own db_schema.xml for that table, and understand that you now depend on that table's structure.
  • Prefer a related table over extra columns on core tables. A custom acme_note table with a foreign key to customer_entity is easier to maintain and upgrade.
  • Name foreign keys and indexes consistently and generate them with tooling. Long names are normal.
  • Commit the whitelist with the XML. A missing or stale whitelist is a classic cause of "I removed the column but it is still there".
  • Think before dropping. Removing a column deletes data; make a backup and do it in a separate release from the code that stops using it.
  • Foreign keys cost something. They protect integrity, but they also slow bulk operations, so use them where the relation is real.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does declarative schema describe?

    You describe the structure you want, and setup:upgrade computes the difference to the real database. History-based version scripts are the old approach.
  2. 2. What is db_schema_whitelist.json for?

    Magento only drops a table, column, constraint or index if it was removed from the XML and appears in the whitelist. This protects elements owned by other modules or added by hand.
  3. 3. Why is renaming a column in db_schema.xml dangerous?

    The XML says nothing about a rename. The old name disappears and a new one appears, so copy the data (in a patch or a staged release) before removing the old column.
  4. 4. What should you use to insert starting rows into a table you declared?

    Declarative schema is about structure only. Rows are created by data patches, which run once per patch class.
  5. 5. What is the effect of running bin/magento setup:upgrade twice in a row with no changes in between?

    Declarative schema is idempotent: with the database already matching the XML there is nothing to do.

Summary

  • etc/db_schema.xml describes tables, columns, constraints and indexes as the end state; Magento computes the SQL.
  • Elements are matched by name or referenceId, and XML from all modules is merged.
  • db_schema_whitelist.json is generated with setup:db-declaration:generate-whitelist and makes drops safe and deliberate.
  • Structure goes into declarative schema, data into data patches; the old Install/Upgrade scripts are legacy.
  • setup:upgrade is idempotent, and a dry run lets you review the planned SQL.
  • Prefix table names, prefer your own tables over changing core ones, and treat drops as data loss.

Next: Data Patches and Schema Patches