Magento database schema

In Today’s topic , we will cover about Magento database schema, Before Magento 2.3, we all were using installschema.php and upgradeschema.php to create new or update existing table or add/remove fields in database and installData.php and upgradeData.php to add values in tables. When developers wants to upgrade database or wants to upgrade magento version then all upgrade script is executing. It also creates complexity in upgradeScript file.

But with new declarative schema approach it becomes easy and fast to add/update tables using xml file developers don’t require to write new script for each new version. It also offers developers to delete any field or data easily when module is uninstalled.

Configure declarative Schema

Create a new table

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"             xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="table_name" resource="default" engine="innodb"
           comment="Table Information">
    <column xsi:type="int" name="entity_id" unsigned="false" nullable="false" identity="true" comment="ID"/>
    <column xsi:type="varchar"  length="255" name="name" nullable="true" comment="Name"/>
    
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="id"/>
    </constraint>     
</table>
</schema>

As per above code , Here you need to define table name in name attribute , resource and database engine.comment is an optional attribute.

then you need to add column whatever columns you want to add. Below are some attribute for same.

• xsi:type : Specify the column types.
• name : field name
• length : the length of column
• nullable : define whether field can be null or not.
• default : define default value of field
• disabled : If you want to remove any field , constraint , or even table, just use this value as disabled=”true” and generate whitelist command and run setup upgrade command
• identity : If you want to make your column auto increment , add this attribute with value true
• onCreate : If you are looking to change column name and want to migrate all data from old column to new column then u can write this attribute , and even if you are renaming your table name then also you can use this attribute.

Define Primary Key

<constraint xsi:type="primary" referenceId="PRIMARY">
    <column name="entity_id"/>
</constraint>

Here you can define xsiy:type as primary to define primary key for table.
If you want to define unique key then define xsi:type as unique.

<constraint xsi:type="unique" referenceId="TABLE_NAME_FIELD_NAME">
    <column name="entity_id"/>
</constraint>

How to define foreign key

<constraint xsi:type="foreign" referenceId="TABLE_NAME_FIELD_ID_REFERENCE_TABLE_NAME_FIELD_ID" table="table_name"
                    column="field_id" referenceTable="reference_table_name" referenceColumn="entity_id" onDelete="CASCADE"/>

Here :

• xsi:type : value will be foreign
• referenceId : It is identifier of relation mapping , you can define combination of table field and reference table field name to make it unique easily and easy understanding.
• table : it will be the table name
• column : it will be a field id which u want to make foreign key
• referenceTable : it will be referance table with which you want to make relation.
• referenceColumn : it will be a field of reference table which is mapped with your table’s field id.
• onDelete : It will be an action which will fire on deletion of value from reference table so the record will be deleted from all table where the foreign key is defined with same id.

Once you create db_schema.xml table and run setup upgrade command, generate db_schema_whitelist.json file using below command

php bin/magento setup:db-declaration:generate-whitelist --module-name=Module_name

Here, Module_name is your module name, It is an optional field , if you will not define module name then it will generate db_schema_whitelist.json for all schema for which it is pending.

The advantage of db_schema_whitelist.json generation is when you remove any field from table or want to drop table then without this schema field or table will not remove.

Drop a table

Below is the example how you can drop table, you need to remove all code as below.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
-    <table name="table_name">
-        <column xsi:type="int" name="entity_id" unsigned="false" nullable="false" identity="true" comment="ID"/>
-        <column xsi:type="varchar"  length="255" name="name" nullable="true" comment="Name"/>
     
-        <constraint xsi:type="primary" referenceId="PRIMARY">
-            <column name="entity_id"/>
-        </constraint>
-    </table>
</schema>

When you run setup upgrade command again your table will be droped.

Rename a table

In Declarative schema table name change is very easy you just need to follow below process

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="new_table_name" onCreate="migrateDataFromAnotherTable(table_name)" >
    <column xsi:type="int" name="entity_id" unsigned="false" nullable="false" identity="true" comment="ID"/>
    <column xsi:type="varchar"  length="255" name="name" nullable="true" comment="Name"/>
    
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="id"/>
    </constraint>     
</table>
</schema>

Please note that migrating data from another table and renaming columns at the same time is not supported

After changing table name please regenerate db_schema_whitelist.json json.

Add a column in existing table

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="table_name"  >
    <column xsi:type="int" name="entity_id" unsigned="false" nullable="false" identity="true" comment="ID"/>
    <column xsi:type="varchar"  length="255" name="name" nullable="true" comment="Name"/>
    <column xsi:type="varchar"  length="255" name="new_field" nullable="true" comment="Name"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="id"/>
    </constraint>     
</table>
</schema>

As highlighted in above code you can add new column anytime in same db_schema.xml file or if you are adding a new field in existing table of core magento table or 3rd party extension table then u can add new field as below

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="table_name"  >
     
    <column xsi:type="varchar"  length="255" name="new_field" nullable="true" comment="Name"/>
       
</table>
</schema>

Drop column from table

If you want to remove field from table then you can just remove that column node and run setup upgrade , but please make sure you should have db_schema_whitelist.json json then only you will be able to drop field.

How to Migrate old db schema script to new declarative schema?

Friends, If you have already written your db schema in old way and wants to migrate with new declarative schema without wasting time so use below method

So first of all delete the module entry from setup_module table and then run below command

php bin/magento setup:install --convert-old-scripts=1
bin/magento setup:upgrade --convert-old-scripts=1

You will see db_schema.xml in your etc folder.

In My Next Topic I will cover Data patches. So subscribe us so you don’t miss our next important topic.