In this article we will explore the difference between the supported options of modified database structure. Up until Magento 2.3 the install schema approach was the main and suggested way to process database updates, for example, creating tables, modifying fields, renaming or changing constraints. With 2.3 a new declarative approach was introduced which resolves many of the limitations that we had before. Most importantly the need for updating the install scripts based on versions and the mess that comes with reverting, re-executing the scripts.

According to official documentation:

Implementing declarative schema is not a requirement for Magento 2.3. However, upgrade scripts will be phased out in favor of declarative schema.

This means that stores using Magento 2.3 and newer should start moving towards utilizing declarative schema approach. In this article we will show you how to create a table using both declarative schema and install schema.

Lets start by inspecting the old approach.

Install Schema

We will create a the install schema script in Setup/InstallSchema.php:

<?php

namespace Techflarestudio\Schema\Setup;

use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\Setup\InstallSchemaInterface;

/**
 * Class InstallSchema
 * @package Techflarestudio\Schema\Setup
 */
class InstallSchema implements InstallSchemaInterface
{
    /**
     * @inheritDoc
     * @throws \Zend_Db_Exception
     */
    public function install(
        \Magento\Framework\Setup\SchemaSetupInterface $setup,
        \Magento\Framework\Setup\ModuleContextInterface $context
    ) {
        $installer = $setup;
        $installer->startSetup();

        $table = $installer->getConnection()->newTable(
            $installer->getTable('techflarestudio_product')
        )->addColumn(
            'entity_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            [ 'identity' => true, 'nullable' => false, 'primary' => true, 'unsigned' => true ],
            'Entity ID'
        )->addColumn(
            'product_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            [ 'nullable' => false,'unsigned' => 'true'],
            'Order ID'
        )->addColumn(
            'custom_data',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            255,
            [ 'nullable' => false ],
            'Custom Data'
        )->addColumn(
            'creation_time',
            \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
            null,
            [ 'nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT ],
            'Creation Time'
        )->addColumn(
            'update_time',
            \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
            null,
            [ 'nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT_UPDATE ],
            'Modification Time'
        )->addForeignKey(
            'techflarestudio_product_catalog_product_entity_product_id',
            'product_id',
            $setup->getTable('catalog_product_entity'),
            'entity_id',
            AdapterInterface::FK_ACTION_CASCADE
        );
        $installer->getConnection()->createTable($table);
        $installer->endSetup();
    }
}

There are a few points to mention here:

  • We are defining a new table techflarestudio_product
  • We will add an entity_id fields to uniquely identify all entries
  • We will reference catalog_product_entity by creating a foreign key to native products table

Make sure to enable your module, flush the cache and run setup upgrade.

Declarative schema

Next lets create the same table using the new declarative schema approach. If you thought that the first option was simple then you will like the second one even more. We do not even have to define a class - the whole table is managed in a single xml file - therefore the declarative approach. Schema files declare what the database structure should be and Magento makes sure to compare and take care of the rest. Here is the example table that will achieve the same result as the the script above:

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="techflarestudio_product" engine="innodb" comment="Sales Flat Order">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="int" name="product_id" unsigned="true" nullable="false" comment="Entity ID"/>
        <column xsi:type="text" name="custom_field" nullable="true" comment="State"/>
        <column xsi:type="timestamp" name="creation_time" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
                comment="Creation Time"/>
        <column xsi:type="timestamp" name="update_time" on_update="true" nullable="false" default="CURRENT_TIMESTAMP"
                comment="Modification Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="techflarestudio_product_catalog_entity_product_id" table="techflarestudio_product" column="product_id" referenceTable="catalog_product_entity" referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>
etc/db_schema.xml

A few important gotchas:

  • You have to define the primary constraint for the identity field.
  • The text type field can not have legth attribute
  • The foreign key field has to have exactly the same type as the reference column
MySQL [magento]> describe techflarestudio_product;
+---------------+------------------+------+-----+-------------------+-----------------------------+
| Field         | Type             | Null | Key | Default           | Extra                       |
+---------------+------------------+------+-----+-------------------+-----------------------------+
| entity_id     | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| product_id    | int(10) unsigned | NO   | MUL | NULL              |                             |
| custom_field  | text             | YES  |     | NULL              |                             |
| creation_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |                             |
| update_time   | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.001 sec)

DB Schema whitelist

In order to allow deleting the defined columns or constraints you have to let Magento know that the fields were created through declarative schema. This is because during this transition period columns can be created from various tables and deleting everything that does not fit the declared schema would not be the correct behavior. You can generated the schema whitelist using the following cli command:

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

This will generate the following json file:

{
    "techflarestudio_product": {
        "column": {
            "entity_id": true,
            "product_id": true,
            "custom_field": true,
            "creation_time": true,
            "update_time": true
        },
        "constraint": {
            "PRIMARY": true,
            "TECHFLARESTUDIO_PRD_PRD_ID_CAT_PRD_ENTT_ENTT_ID": true
        }
    }
}
etc/db_schema_whitelist.json

This basically means that all fields and constraints can be modified by schema. According to official documentation:

This file is a temporary solution. It will be removed in the future, when upgrade scripts are no longer supported.

So you will need to generate this for each release until the support for upgrade scripts is dropped.

Which one to use and when?

Going forward you should definitively work towards using declarative schema approach. However for older Magento versions this is not an option. So if you have to support older versions then the old install schema approach is the safer bet.

Another important factor here is backwards incompatibility. Once a module is converted to the declarative schema approach, it cannot be reverted to upgrade scripts. This means that if you wish to support older versions of Magento you will still need to use the install schema approach.