0

I want to create table and fields using db_schema.xml rather than InstallSchema in custom module. Can anyone help please?

5

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"/>
        <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.

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.