Magento 2

Master db_schema in Magento 2: Your Essential Guide

Dive into the essentials of Magento 2’s db_schema.xml, the streamlined approach for defining database structures in your modules. Discover how this pivotal file simplifies code management, enhances performance, and ensures clean uninstalls, all while saving developers’ time. Embrace the declarative schema method and revolutionize your Magento 2 development experience.

Introduction

What is db_schema in Magento 2?

The db_schema.xml file is a crucial component in creating modules for Magento 2. It allows you to outline the database structure for your module, which includes elements like tables, columns, indexes, and other associated entities.

In the previous versions of Magento, developers needed to write various scripts such as InstallData or InstallSchema and UpgradeSchema.php to create a new table and add/update new data in the database table. However, with the introduction of db_schema.xml, this process has become much more streamlined.

The db_schema.xml file needs to be placed in the etc folder of your module. It follows a specific XML structure, where you define your tables, columns, and pretty much everything you need.

Why is db_schema.xml important?

The db_schema.xml approach offers several advantages:

  • Simplified Code Management: Installing and upgrading a code can be handled in a single XML file.
  • Performance Boost: The declarative schema approach helps Magento determine the differences between the current table structure and what it should look like.
  • Time-Saving: Developers don’t need to write scripts for the latest version of any module.
  • Clean Uninstall: It allows data to be deleted when an extension is uninstalled.

 Understanding db_schema.xml Structure

Schema and Table Elements

The db_schema.xml file needs to be placed in the etc folder of your module. It follows a specific XML structure:

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">
    <!-- Define your tables, columns, indexes, and other entities here -->
</schema>  

In this structure, the root schema element serves as the container for defining your module’s database schema. To set up a table in your module’s structure, you need to use the table component inside the schema component. This table component can have attributes like: name (which is the table’s name), resource (the resource name that fetches a database connection), and engine (the storage engine used for the table, this is optional and defaults to ‘innodb’ if not specified).

Here’s an example of defining a table in the db_schema.xml file:

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="my_module_table" resource="default" engine="innodb">
        <!-- Define your columns, indexes, and other entities here -->
    </table>
</schema>

Attributes and Child Elements

Each <table> element can have several attributes such as:

  •  name: The name of the table.
  •  resource: The resource connection to use (usually “default”).
  •  engine: The storage engine to use (usually “innodb”).
  • comment: A comment describing the table.

Inside <table> element can have several child elements, such as <column>, <constraint>, and <index>. These elements define the structure of the table.

Each <column> element can have several attributes, such as:

  • xsi:type: The data type of the column (e.g., int, varchar, text).
  • name: The name of the column.
  • padding: The padding for integer types.
  • unsigned: Whether the integer type is unsigned.
  • nullable: Whether the column can contain null.
  • identity: Whether the column is auto-increment.
  • comment: A comment describing the column.

Here’s an example of a table with some columns:

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="example_table" resource="default" engine="innodb" comment="Example Table">
    <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity Id"/>
    <column xsi:type="varchar" name="name" nullable="false" length="255" comment="Name"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="id"/>
    </constraint>
</table>
</schema> 

Common Operations: Add Column, Drop Column, Modify Column

Add Column

To add a column, you simply add a new <column> element to the db_schema.xml file. For example, to add a created_at column, you would do:

XML
<column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP" comment="Creation Time"/>

Drop Column

To drop a column, you add the disabled attribute to the <column> element and set it to “true”. For example, to drop the created_at column, you would do:

XML
<column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP" comment="Creation Time" disabled="true"/>

Or remove the corresponding <column> element from the <table>(Not Recommended).

Modify Column

To modify a column, you change the attributes of the <column> element. For example, to change the created_at column to allow null values, you would do:

XML
<column xsi:type="timestamp" name="created_at" nullable="true" default="CURRENT_TIMESTAMP" comment="Creation Time"/>

After making changes in db_schema.xml, you must run php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module followed by php bin/magento setup:upgrade command. This will update the db_schema_whitelist.json and reflect the changes.

Note: Replace the Vendor_Module with the name of your Module

Applying Schema Changes

Manual vs. Declarative Schema Changes

Manual Method: Before Magento 2.3, the manual approach was the primary method for modifying the database structure. Developers had to write various scripts for each new version, such as InstallSchema, UpgradeSchema, and InstallData. However, this manual approach had several drawbacks. It was error-prone, difficult to version control, and lacked rollback capabilities. Moreover, developers had to write different scripts for different types of databases, which increased complexity

Declarative Method: With the release of Magento 2.3, a new declarative schema approach was introduced. This approach allows developers to declare the final desired state of the database in an XML file (db_schema.xml), and the system adjusts to it automatically. This method eliminates the need for writing scripts for each new version. Moreover, it allows data to be deleted when a module is uninstalled. This approach is less error-prone, easier to manage, and supports database rollbacks.

Using db_schema.xml for Schema Updates

The db_schema.xml file is used to apply schema updates in Magento 2. Here are the steps to apply schema changes using db_schema.xml:

  • Define the Changes: Define the desired state of the database in the db_schema.xml file. This includes creating tables, adding or modifying columns, and defining constraints.
  • Generate the Whitelist: After making changes to the db_schema.xml file, generate the db_schema_whitelist.json file by running the command php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module.
  • Apply the Changes: Run the command php bin/magento setup:upgrade to apply the changes.

Remember, any changes made in the db_schema.xml file should be reflected in the db_schema_whitelist.json file. This file shows a history of all tables, columns, and keys added with the declarative schema and is required for drop operations.

Advanced Topics

In this section, we will delve into some advanced topics related to db_schema.xml in Magento 2.

Defining Constraints(primary key, foreign key, etc.)

Constraints act as enforced regulations applied to data columns within a table. Their purpose is to restrict the kind of data that can be inserted into a table, thereby guaranteeing the data’s precision and dependability. In Magento 2, you can define three types of constraints: primary key, foreign key, and unique.

Primary Key Constraint

A primary key refers to a column that serves as a unique identifier for each row in a table. Here’s how to define a primary key in db_schema.xml:

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

In this example, entity_id is the primary key of the table. The xsi:type=”primary” attribute specifies that this is a primary key constraint.

Foreign Key Constraint

A foreign key is a column or a set of columns used to establish a link between the data in two tables. Here’s an example of defining a foreign key:

XML
<constraint xsi:type="foreign" referenceId="FK_MY_TABLE_MY_OTHER_TABLE" table="my_table" column="entity_id" referenceTable="my_other_table" referenceColumn="entity_id" onDelete="CASCADE"/>

In this example, entity_id in my_table is the foreign key that references entity_id in my_other_table. The onDelete=”CASCADE” attribute means that when a referenced row in my_other_table is deleted, also delete the rows in my_table that have a foreign key value corresponding to the referenced row’s primary key.

Unique Key Constraint

A unique constraint is a rule that maintains the uniqueness of all entries in a specific column. Here’s how to define a unique constraint:

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

In this example, the entity_id column in my_table has a unique constraint, meaning all entity_id values in my_table must be unique.

Defining Index

An index is used to speed up the performance of a database by allowing faster retrieval of data. Here’s how to define an index:

XML
<index referenceId="IDX_MY_TABLE_ENTITY_ID" indexType="btree">
    <column name="entity_id"/>
</index>

In this example, an index is created on the entity_id column of my_table. The indexType=”btree” attribute specifies that this is a B-tree index, which is the most common type of index in MySQL

db_schema_whitelist.json and Backward Compatibility

The db_schema_whitelist.json file acts like a roadmap for Magento, showing which tables and columns can be changed without issues using the db_schema.xml file.. This feature was introduced to maintain backward compatibility and is slated for removal in an upcoming version once the install/upgrade script becomes obsolete.

The db_schema_whitelist.json file keeps a record of all the tables, columns, and keys that have been added with the declarative schema. This file can either be manually created or automatically generated using a specific command:

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

Conclusion

In conclusion, Magento 2’s db_schema.xml offers a streamlined, efficient approach to database management. By enabling developers to define the database structure declaratively, it simplifies code management, enhances performance, and saves time. The ability to cleanly uninstall modules and maintain backward compatibility further underscores its significance in modern e-commerce development. Embracing this system is a step forward in optimizing Magento 2’s robust framework for future advancements.

To enhance your knowledge of Magento 2, consider these beneficial resources : Magento 2 Official Documentation

Don’t miss this amazing blog post on our website : Master Magento 2 Plugins

Did you find this post helpful? Leave a comment below if you have any questions!

Leave a Reply

Your email address will not be published. Required fields are marked *