Declaring Custom Database Tables in Magento 2 with db_schema.xml

The problem: install scripts are fragile for custom tables

You need to store loyalty point transactions in a new Magento 2 database table. The older approach is to write an InstallSchema or UpgradeSchema PHP script. That works, but PHP install scripts are easy to get out of sync with the actual database, and they run only once per version. If a teammate runs setup:upgrade on a stale database, the script may fail or silently skip changes. Magento 2.3 and later support declarative schema: you describe the table structure in XML, and setup:upgrade applies the difference automatically.

Takeaway: declare structure in etc/acme_loyalty/db_schema.xml, use DataPatch for data, list removed columns in dbschema_whitelist.json, and verify in staging before production.

How declarative schema works

Declarative schema is a structure-only feature. It creates and alters tables and columns during bin/magento setup:upgrade. It does not insert data, run custom SQL, or remove columns automatically. If you delete a column from the XML, you must also add it to etc/acme_loyalty/dbschema_whitelist.json, otherwise setup:upgrade can report a schema inconsistency. Do not mix declarative schema and InstallSchema or UpgradeSchema scripts for the same table; overlapping changes can conflict.

Version assumption: this behavior exists in Magento 2.3 and later, but XML attribute support varies by release. Confirm your installed version before relying on a specific attribute.

Worked example: add a loyalty transaction table

Suppose you are building a custom module named Acme_Loyalty. Create the module registration files first, then add etc/acme_loyalty/db_schema.xml. The directory name under etc should match the module name in lowercase.

<schema xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
        xsi:noNamespaceSchemaLocation='urn:magento:setup:declarative-schema:etc/db_schema.xsd'>
  <table name='acme_loyalty_transaction' resource='default'>
    <column
        name='transaction_id'
        xsi:type='integer'
        nullable='false'
        identity='true'
        unsigned='true'
        comment='Transaction ID'/>
    <column
        name='customer_id'
        xsi:type='integer'
        nullable='false'
        unsigned='true'
        comment='Customer ID'/>
    <column
        name='points'
        xsi:type='integer'
        nullable='false'
        default='0'
        comment='Loyalty points'/>
    <constraint name='PRIMARY' xsi:type='primary' referenceFields='transaction_id'/>
    <constraint name='ACME_LOYALTY_TRANSACTION_CUSTOMER_ID'
                xsi:type='foreign'
                referenceTable='customer_entity'
                referenceFields='entity_id'
                fields='customer_id'
                onDelete='CASCADE'/>
    <index indexName='customer_id' referenceFields='customer_id'/>
  </table>
</schema>

Run the upgrade from the Magento root directory as the file system owner, not as root, to avoid permission problems on generated files:

bin/magento setup:upgrade

Expected check: the command completes without errors, and the table exists in the database. You can verify with a database client:

SHOW COLUMNS FROM acme_loyalty_transaction;

You should see transaction_id, customer_id, and points. Also confirm the module is active:

bin/magento module:status Acme_Loyalty

Risk: if the module already has an InstallSchema script that creates acme_loyalty_transaction, running setup:upgrade may try to create the same table twice or apply conflicting changes. Remove or disable the old script before switching to declarative schema for that table.

Removing a column safely

Later you decide to drop the points column. Delete the column element from db_schema.xml, then add the column to etc/acme_loyalty/dbschema_whitelist.json:

{
  "tables": [
    {
      "name": "acme_loyalty_transaction",
      "columns": [
        "points"
      ]
    }
  ]
}

Run bin/magento setup:upgrade again and inspect the command output and Magento logs for whitelist-related errors. If you omit the whitelist entry, setup:upgrade may report that the schema is inconsistent because the XML no longer declares a column that still exists in the database.

Limitations and what to check

• Declarative schema defines structure only. Use a DataPatch class to insert default rows or migrate existing data.
• It cannot run arbitrary custom SQL. For complex transformations, combine db_schema.xml with a DataPatch.
• Attribute support differs across Magento 2.3 and 2.4 releases. Test the exact XML in a staging copy of production.

Practical verification: after setup:upgrade, query information_schema.columns to confirm the expected columns and data types. If the table or column is missing, check bin/magento module:status and the setup_module table to ensure the module version was recorded.

Closing

Start with one non-production table in db_schema.xml, run setup:upgrade in staging, and confirm the result in the database before deploying. Keep data changes in DataPatch, keep removals in the whitelist, and avoid mixing old install scripts with the new declarative approach.