Part 3: to continue series of database operation, how to add a column or delete a column from database table in Magento 2 using declarative schema.
Add a column to Table:
Modify your module db_schema.xml file to add a new column in the database table.
db_schema.xml:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?xml version="1.0"?> <!-- /** * Copyright © Magento, Inc. All rights reserved. * See COPYING.txt for license details. */ --> <schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd"> <table name="custom_form" resource="default" engine="innodb" comment="creating custom form table"> <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID"/> <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name"/> <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email"/> <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message"/> + <column xsi:type="timestamp" name="created_at" padding="10" comment="Created At"/> <constraint xsi:type="primary" referenceId="PRIMARY"> <column name="entity_id"/> </constraint> </table> </schema> |
Generate db_schema_whitelist.json file:
When adding a new column to the table, remember to regenerate the db_schema_whitelist.json file so it contains the new column name too.
1 |
php bin/magento setup:db-declaration:generate-whitelist --module-name=MM_Db |
Here MM_Db is my module name. Please replace it with your Module name.
Run upgrade command:
1 |
php bin/magento setup:upgrade |
Validate your DB table for the new column, it should already be created in the table.
Drop a column from Table:
To drop a column from the DB table you can modify that module db_schema.xml file. There are 2 ways to delete a column.
- Delete itself that column node from db_schema.xml file and save the file.
- Add an attribute to that column node disabled=”true” and save the file.
Db_schema.xml: in given example deleting created_at column from table by adding disabled attribute in column.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?xml version="1.0"?> <!-- /** * Copyright © Magento, Inc. All rights reserved. * See COPYING.txt for license details. */ --> <schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd"> <table name="custom_form" resource="default" engine="innodb" comment="creating custom form table"> <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID"/> <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name"/> <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email"/> <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message"/> <column xsi:type="timestamp" name="created_at" padding="10" comment="Created At" disabled="true"/> <constraint xsi:type="primary" referenceId="PRIMARY"> <column name="entity_id"/> </constraint> </table> </schema> |
Run upgrade command:
1 |
php bin/magento setup:upgrade |
NOTE: You can not delete a table column if it does not exist in the db_schema_whitelist.json file of that module.