Declarative Schema in Magento 2: A Practical Guide

Declarative schema became the standard way to manage database structure in Magento 2 core modules starting with Magento 2.3, replacing the older setup upgrade script approach. If you’re still writing manual upgrade scripts for schema changes, this is the approach Magento itself has used for years, and it’s worth switching to.

Declarative schema reduces the unnecessary work of writing upgrade scripts for just a small change in the database. For example, if you want to rename a column, you don’t need to write a setup upgrade script in the next version of that module. You can change the schema in db_schema.xml and run the setup upgrade command.

This allows the developer to declare the final state of the database. As a result, it will reduce redundant operations. It reduces the process of writing code in the next version. Because of this approach, you can delete data when you uninstall the module.

Before you start, you’ll need a Magento 2 module set up with its own `etc` folder, since that’s where the `db_schema.xml` file lives. Once you’ve made changes to this file, you still need to run bin/magento setup:upgrade for Magento to actually apply them to your database.

How to create a table using Declarative Schema in Magento 2.3

In this blog, the process of creating a table using declarative schema is explained.

First of all, Create a db_schema.xml file under the “StageBit/DeclarativeSchema/etc” directory and write the following code.

<table name="my_custom_table" resource="default" engine="innodb" comment="My Custom Table">
    <column xsi:type="int" name="my_id" padding="11" unsigned="false" nullable="false" identity="true" comment="My ID"/>
    <column xsi:type="varchar" name="name" padding="11" nullable="false" comment="Name"/>
    <column xsi:type="datetime" name="updated_date" on_update="false" nullable="true" comment="Updated Date"/>
    
    <constraint xsi:type="primary" referenceId="PRIMARY">
       <column name="my_id"/>
   </constraint>
    <index referenceId="VENDOR_MODULE_ENTITY_INT_MY_ID" indexType="btree">
       <column name="my_id"/>
   </index>
</table>

Declarative Schema XML Node Description:

• table: Each table node represent the table in the database. Table node contains 3 types of sub-nodes i.e column, constraint, index.
  When you run the setup: upgrade command. It will create the table “my_custom_table”.
• column: It defines the column in the table. It requires its own declaration.
• constraint: It is used to define the rules to allow or restrict what values to be stored in the column. It has the following attributes and values:
  1. type: One of primary, unique, or foreign.
     The primary and unique constraints are called “internal” constraints because they are applied only to the scope of the table where they are created.
  2. referenceId: A custom identifier that is used only for relation mapping in the scope of db_schema.xml files.
• index: Indexes are used for speeding up DQL operations. The following attributes define an index:
  1. referenceId: A custom identifier that is used only for relation mapping in the scope of db_schema.xml files.
  2. indexType: The value must be btree, fulltext, or hash.

Common Declarative Schema Operations

Once your table exists, here are the schema operations you’ll use most often day to day.

Adding a New column to the existing table :

The following example adds the “email” column to “my_custom_table”.

<table name="my_custom_table" resource="default" engine="innodb" comment="My Custom Table">
    <column xsi:type="int" name="my_id" padding="11" unsigned="false" nullable="false" identity="true" comment="My ID"/>
    <column xsi:type="varchar" name="name" padding="11" nullable="false" comment="Name"/>
    <column xsi:type="datetime" name="updated_date" on_update="false" nullable="true" comment="Updated Date"/>
    <column xsi:type="varchar" name="email" padding="11" nullable="false" comment="Email"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
       <column name="my_id"/>
   </constraint>
    <index referenceId="VENDOR_MODULE_ENTITY_INT_MY_ID" indexType="btree">
       <column name="my_id"/>
   </index>
</table>

Remove column from an existing table :

If you want to remove the existing column then you can set the disabled attribute to that column or simply delete that node. In the following example remove the “email” column from “my_custom_table”.

<column xsi:type="varchar" name="email" padding="11" nullable="false" comment="Email" disabled="true"/>

Change data type of column :

You can change the column type by changing its type attribute to int, varchar, text, etc. For example, changing a column from varchar to text looks like this:

<column xsi:type="text" name="description" nullable="true" comment="Description"/>

Rename a column :

To rename a column, delete the original column declaration and create a new one. In the new column declaration, use the onCreate attribute to specify which column to migrate data from.

onCreate=”migrateDataFrom(entity_id)”

Add Index to column :

<index referenceId="INDEX_SEVERITY" indexType="btree">
    <column name="my_id"/>
</index>

Create foreign key :

Here’s the code for creating a foreign key constraint.

<constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="my_custom_table" column="my_id" referenceTable="severities" referenceColumn="severity_identifier" onDelete="CASCADE"/>

Drop a foreign key :

Delete the constraint node for dropping the foreign key from the table. For example, removing this constraint from your schema drops it the next time you run setup:upgrade:

<constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" table="my_custom_table" column="my_id" referenceTable="severities" referenceColumn="severity_identifier" onDelete="CASCADE"/>

Create a schema whitelist :

The StageBit/DeclarativeSchema/etc/db_schema_whitelist.json file provides a history of all tables, columns, and keys added with the declarative schema. It is generated manually or created automatically with the following command:

php bin/magento setup:db-declaration:generate-whitelist StageBit_DeclarativeSchema

You must generate a whitelist in each release that contains the changes in db_schema.xml. The following code shows the sample of db_schema_whitelist.json

{
    "my_custom_table": {
        "column": {
            "my_id": true,
            "name": true,
            "updated_date": true
        },
        "index": {
            "VENDOR_MODULE_ENTITY_INT_MY_ID": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

That covers the core declarative schema operations you’ll use day to day: creating tables, adding and removing columns, indexes, foreign keys, and keeping your whitelist current.

If you’re maintaining an older Magento 2 module that’s still running manual upgrade scripts, migrating it to declarative schema is usually a contained piece of work. It’s something we do regularly for clients on Magento 2 and Adobe Commerce. Get in touch if you want a second pair of eyes on a schema migration before you ship it.

That covers the core declarative schema operations you’ll use day to day: creating tables, adding and removing columns, indexes, foreign keys, and keeping your whitelist current.

If you’re maintaining an older Magento 2 module that’s still running manual upgrade scripts, migrating it to declarative schema is usually a contained piece of work. It’s something we do regularly for clients on Magento 2 and Adobe Commerce. Get in touch if you want a second pair of eyes on a schema migration before you ship it.