How to Create db_schema_whitelist.json in Magento 2

With the release of Magento 2.3, the declarative schema was introduced to ease the installation and upgradation process.

Declarative Schema files declare what the database structure should be. Magento determines the differences between the current table structure and what it should be.

db_schema_whitelist.json file is a history of all tables, columns, and keys added with the declarative schema. It can be generated manually or create db_schema_whitelist.json in Magento 2 using the following method:

Method to create db_schema_whitelist.json in Magento 2:

Db_schema_whitelist file can be created automatically using the below command :

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

If the Modulename is not specified, then the default behaviour is to generate a whitelist for all the modules in a system. You can set options as –module-name = all for all the module.

Module uses declarative schema and use to module setup scripts.

Declare table column using db_schema file.

Suppose you have two columns named Id and foo. When setup:upgrade runs, the table will be created. After that, you need to add one extra column named as bar.

For eg., $table->addColumn(‘sales order’,’bar’)

Now run setup:upgrade.

Now declarative schema is evaluated again and compared again with the existing database. Declarative schema realizes that new additional column is there.

Usually, it would remove the column from db_schema file and you need to also remove from the database.
But that is the wrong assumption, that’s why db_shema_whitelist comes into place.

To create db_schema_whitelist.json first you need to create db_schema.xml file

app/code/vendor/Module/etc/db_schema.xml

<?xml version="1.0"?>

<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_example" resource="default" comment="My table" charset="utf8">
        <column name="id" xsi:type="int" padding="5" unsigned="true" identity="true" nullable="false"></column>
        <column name="name" xsi:type="varchar" nullable="false" length="124"></column>
        <column name="description" xsi:type="text" nullable="true"></column>
        <column name="is_enabled" xsi:type="boolean" nullable="false" default="0"></column>
        <column name="weighing_factor" xsi:type="decimal" precision="5" scale="4"></column>
        <column name="created_at" xsi:type="timestamp" default="CURRENT_TIMESTAMP"></column>
        <column name="updated_at" xsi:type="timestamp" default="CURRENT_TIMESTAMP" on_update="true"></column>
    </table>
</schema>

Run the below command

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

Check you etc folder where db_schema_whitelist.json is generated.

You can also refer to the official document for more details.

That’s it.

Feel free to share the solution with fellow Magento developers via social media.

Thank you.