I want to create table and fields using db_schema.xml rather than InstallSchema in custom module. Can anyone help please?
2 Answers
Create file db_schema.xml
app/code/VendoreName/ModuleName/etc
db_schema.xml
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="declarative_table">
<column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
<column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
<column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
<column xsi:type="timestamp" name="time_occurred" comment="Time of event"/>
<column xsi:type="mediumtext" name="desc" nullable="true" comment="Value"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="id_column"/>
</constraint>
</table>
</schema>
Explanation
<table>
-> Engine: SQL engine, this value must be InnoDB or memory.
-> Resource: The database shard on which to install the table. This value must be default, checkout, or sales
<Column>
-> identity: Indicates whether a column is auto incremented.
-> Type: blob,blob (includes blob, mediumblob, longblob), boolean, date, datetime, int (includes smallint, bigint, tinyint), real (includes decimal, float, double, real), text (includes text, mediumtext, longtext), timestamp, varbinary, and varchar.
-> Padding: indicates the size of the integer column
-> Length: indicates the length of a column
-> precision: The number of allowed digits in a real data type.
-> scale: The number of digits after the decimal in a real data type.
<Constraints>
-> Type: primary, unique, or foreign
-> Referrence_id: a custom identifier that is used only for relation mapping in the scope of db_schema.xml files
For Remove
-> disabled: Disables or deletes the declared table, column, constraint, or index.
Run this Command after installing table
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> The db_schema_whitelist.json will be created in app/code/VenodreName/ModuleName/etc
Drop table
-> First of all, you need to add disabled="true" attribute in your table node.
-> Remove all nodes in between table
<table name="declarative_table" disabled="true">
</table>
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
if padding error come please romove it
Rename table when Mirgrate
This is a DDL trigger that allows you to move data from an existing column to a newly created column. This trigger works only when a column is created
USE THIS-> onCreate=”migrateDataFromAnotherTable(old name)”
-> Please note that migrating data from another table and renaming columns at the same time is not supported.
<table name="new_declarative_table" onCreate="migrateDataFromAnotherTable(declarative_table)">
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
Add a column to table
add simple <column> into table
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
Drop a column from a table
Add simple attribute disabled="true" to <column>
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
Change the column type
directly change <column>
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
Add primary key into ondelete CASEDELETE
<constraint xsi:type="foreign" referenceId="[REFERENCE ID NAME]" table="[Current table name]" column="[Current table column name]" referenceTable="[refernce table name]" referenceColumn="[Refernce column name]" onDelete="CASCADE"/>
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
Add simple Primary key
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="id_column"/>
</constraint>
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
Remove Primary key or Constraint
Simply add to disabled="true" to <constraint>
-> php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
-> php bin/magento setup:upgrade
Note: When you change in db_schema.xml you must to run php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module after php bin/magento setup:upgrade command, Otherwise change not reflect. we must update db_schema_whitelist.json.
NOTE: This is only working with Magento 2.3.X or greater version.
-
Hey, I made a schema, then generated a whitelist in the module.The whitelist.json looked correct based on what my
db_schema.xml
said, but when I runs:up
it does not add a table, does anyone else have the same issue? Magento Open Source 2.3.5 Jan 7, 2021 at 14:17 -
Please read above note here your db_schema_whitelist.json file is must be updated. without updating file no any changes are comes.– MsquareJan 11, 2021 at 4:13
-
My issue was not with the table not updating - it was with the table not being visible in the db at all, even though I had generated the whitelist, anyways it was a silly error - if anyone on PHPStorm has this problem make sure to refresh your database manager! Jan 11, 2021 at 8:26
-
-
@Msquare This information I just found here because I am still search and trying to get assistance with my query here (I created the question yesterday since I am stuck): my question is directly related to 'cached' old db_schema.xml content. May you please try to assist in my question here : db_schema.xml generate-whitelist does not refresh changes. Oct 19, 2022 at 8:41
First, create a file as given below path:
app\code\Vendor\Extension\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="custom_table_name" resource="default" engine="innodb" comment="Comment Here">
<column xsi:type="varchar" name="column_name" nullable="false" length="10" comment="column comment here"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="column_name"/>
</constraint>
</table>
</schema>
After completing the steps of creating the file as per given in the above steps, you need to run the following command:
php bin/magento setup:upgrade
after Check in the database table will be created as per shown :
please check more detail check this link.
NOTE: This is only working with Magento 2.3.X or greater version.