How to create table and field using db_schema.xml in magento 2

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

Above answers are written correctly. To save time and reduce errors, you can try this schema generator tool. It supports inputs through an admin form or by parsing a MySQL query. You can check it out here.

This tool could be particularly useful for simplifying the process of generating complex database schemas and ensuring compliance with latest Magento 2.4.