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 :
|
1 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<?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
|
1 |
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.
Any doubts about the topic can be mentioned in the Comments section below. I’d be happy to help.
Feel free to share the solution with fellow Magento developers via social media.
Thank you.
Still need help? Hire our Adobe-certified Magento experts.
![Magento 2.3.5: All About The Latest Version [April 28, 2020]](png/magento-2.3.5-all-about-the-latest-version-april-28-2020-150x150.png)
6 Comments
Thanks for the proper explanation, Hence i am new in the Magento 2 and i have one doubt.I didn’t able to grasp this sentence which you wrote in the blog. “Now declarative schema is evaluated again and compared again with the existing database. Declarative schema realizes that new additional column is there.” Can you explain me why magento will remove the column even though developer wants to add the column in table
Hello Shreyas,
That sentence is for the actual functionality of Magento.
The db_schema_whitelist.json file is a way of telling Magento which tables and columns it can safely alter using the db_schema.xml file.
db_schema.xml provides the list of performed actions(add, delete or update) in the table.
Thank You
Hi,first of all, that was a wonderful explanation about whitelists and their relations with db_schema.xml file. I have encountered an issue that even after renaming a column the initial name of the column is present in the whitelist even after i delete the whitelist.json file and re-create it using the command. Although the column name is updated in the table but my initial column name persists in the whitelist. Can you tell me the reason for it?
Hello Aman,
Thank you for your appreciation.
The db_schema_whitelist.json file is a way of telling Magento which tables and columns it can safely alter using the db_schema.xml file.
It displays the changes you have made using any method.
Thanks.
if i am developing a module and need to add another column to a table introduced by another module via db_schema. I need to create db_schema.xml or db_schema_whitelist.json for it?
Thanks!
Hello,
Add new column with db_schema.xml.
db_schema_whitelist.json shows the history of the column or table added.
Thank you.