The Whitelist

db_schema_whitelist.json records what your module declared, and declarative schema will not drop anything missing from it.

Syntaxbin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module

Next to db_schema.xml sits etc/db_schema_whitelist.json. It is a record of every table, column, constraint and index your module has ever declared, as a nested map of names to true.

Why it exists

Declarative schema works by comparing your declarations with the live database. That comparison cannot tell the difference between a column you deliberately removed from the XML and a column that was never yours in the first place, perhaps added by an old install script or by a database administrator. Dropping a column destroys data, so Magento refuses to guess. It performs destructive operations only on elements that appear in the whitelist. Everything listed there is something your module introduced, so removing it from the XML is a clear instruction to drop it.

How you maintain it

You do not hand-write the file. After editing db_schema.xml, run:

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

The command reads your current declarations and merges them into the existing JSON. It only adds; old entries stay, because they are the history that makes future drops possible. Commit the JSON in the same change as the XML, then run bin/magento setup:upgrade.

What trips people up

Forgetting the command is quiet. The column you removed stays in the database, setup:upgrade succeeds, and nothing tells you why.

Example

Example · json
{
    "soundscode_enquiry": {
        "column": {
            "entity_id": true,
            "reference": true,
            "email": true,
            "message": true,
            "created_at": true
        },
        "constraint": {
            "PRIMARY": true,
            "SOUNDSCODE_ENQUIRY_REFERENCE": true
        },
        "index": {
            "SOUNDSCODE_ENQUIRY_EMAIL": true
        }
    },
    "soundscode_enquiry_reply": {
        "column": {
            "reply_id": true,
            "enquiry_id": true,
            "body": true
        },
        "constraint": {
            "PRIMARY": true,
            "SOUNDSCODE_ENQUIRY_REPLY_ENQUIRY_ID": true
        }
    }
    }

When to use it

  • Commit a schema change that a reviewer will not send back.
  • Drop a column a previous release added and no longer uses.
  • Work out why a removed column is still in the database.
  • Keep a release branch's schema history intact across modules.

More examples

The two command forms, and what changes

The =value and space-separated forms of a long option are interchangeable in every bin/magento command, because they all run on Symfony Console. Checking git status straight afterwards is the habit worth forming: exactly two files should have changed, and a third means you forgot --module-name.

Example · bash
# these two are the same command; Symfony Console accepts either form
bin/magento setup:db-declaration:generate-whitelist --module-name=Soundscode_Enquiry
bin/magento setup:db-declaration:generate-whitelist --module-name Soundscode_Enquiry

# only one file is touched
git status --short
#  M app/code/Soundscode/Enquiry/etc/db_schema.xml
#  M app/code/Soundscode/Enquiry/etc/db_schema_whitelist.json

bin/magento setup:upgrade

A drop that needs the whitelist entry first

Say legacy_source has just been deleted from db_schema.xml. Because the whitelist still lists it, Magento knows the column was yours and setup:upgrade issues the DROP COLUMN. Keep the entry: generate-whitelist never removes it, and that is deliberate. If the entry were absent, the column would simply stay in the table for ever.

Example · json
{
    "soundscode_enquiry": {
        "column": {
            "entity_id": true,
            "email": true,
            "legacy_source": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

The symptom of a stale whitelist

This is the failure to recognise. Both Magento commands report success, because from their point of view there is nothing to do: an element outside the whitelist is not a candidate for a destructive operation. The fix is to add the column back to db_schema.xml, run generate-whitelist, remove the column again, then run setup:upgrade.

Example · bash
# db_schema.xml no longer declares legacy_source, but the whitelist was never regenerated
bin/magento setup:upgrade
# Upgrade completed successfully.

bin/magento setup:db:status
# All modules are up to date.

# the column is still there
mysql -e "SHOW COLUMNS FROM soundscode_enquiry LIKE 'legacy_source'" magento
# legacy_source | varchar(64) | YES | | NULL |

Related

m2 schema declarativem2 schema tablesm2 schema changesm2 schema upgradem2 review checklist

Discussion

  • Be the first to comment on this lesson.