Home >How to Create Table Using Database Schemas in Magento 2

How to Create Table Using Database Schemas in Magento 2

In Magento, there are 2 ways to add new or edit data to a table in the database: write a database script in PHP and use the Declarative Schema. 

Today,  we will learn more about how to create table using db schema in Magento 2.

1. Use the PHP script

Magento 2 provides special mechanisms for creating database tables, modifying existing tables, or adding data to them. This mechanism allows changes to be transferred between different settings.

Using this mechanism, there is no need to use manual SQL operations repeatedly when you reinstall the system. Instead, an installation (or upgrade) script that contains data is created. The script runs whenever a module is installed.

Magento 2 includes 4 script types: InstallSchema, InstallData, UpgradeSchema UpgradeData, and Recurring scripts. Installation scripts run only once, while upgrade scripts are executed when the module’s version_module is changed.

InstallSchema

File Setup/InstallSchema.php.

 <?php
/**
* Copyright © 2016 Magento. All rights reserved.
* See COPYING.txt for license details.
*/

namespace Learning\GreetingMessage\Setup;
use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

/**
 * @codeCoverageIgnore
 */
class InstallSchema implements InstallSchemaInterface
{
    /**
    * {@inheritdoc}
    * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
    */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
          /**
          * Create table 'greeting_message'
          */
          $table = $setup->getConnection()
              ->newTable($setup->getTable('greeting_message'))
              ->addColumn(
                  'greeting_id',
                  \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                  null,
                  ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
                  'Greeting ID'
              )
              ->addColumn(
                  'message',
                  \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                  255,
                  ['nullable' => false, 'default' => ''],
                    'Message'
              )->setComment("Greeting Message table");
          $setup->getConnection()->createTable($table);
      }
}

 

The main code stays in the install () method with the $ setup variable. This is the main variable, because it lets connection to the Connection () object  which implements manipulation of the database.

The Connection () object belongs to the class Magento\Framework\DB\Adapter\Pdo\Mysql.

InstallData

File Setup/InstallData.php

 <?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace Learning\GreetingMessage\Setup;

use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;

/**
 * @codeCoverageIgnore
 */
class InstallData implements InstallDataInterface
{

    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.CyclomaticComplexity)
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     * @SuppressWarnings(PHPMD.NPathComplexity)
     */
    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
          /**
           * Install messages
           */
          $data = [
              ['message' => 'Happy New Year'],
              ['message' => 'Merry Christmas']
          ];
          foreach ($data as $bind) {
              $setup->getConnection()
                ->insertForce($setup->getTable('greeting_message'), $bind);
          }
    }
}

UpgradeSchema

 <?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace Learning\GreetingMessage\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

/**
 * Upgrade the Catalog module DB scheme
 */
class UpgradeSchema implements UpgradeSchemaInterface
{
    /**
     * {@inheritdoc}
     */
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        if (version_compare($context->getVersion(), '0.0.2', '<')) {
            $setup->getConnection()->addColumn(
                $setup->getTable('greeting_message'),
                'season',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'length' => 16,
                    'nullable' => false,
                    'default' => '',
                    'comment' => 'Season'
                ]
            );
        }
        $setup->endSetup();
    }
}

UpgradeData

 <?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace Learning\GreetingMessage\Setup;

use Magento\Framework\Setup\UpgradeDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;

/**
 * Upgrade Data script
 */

class UpgradeData implements UpgradeDataInterface
{
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        if ($context->getVersion()
            && version_compare($context->getVersion(), '0.0.2') < 0
        ) {
            $table = $setup->getTable('greeting_message');
            $setup->getConnection()
                ->insertForce($table, ['message' => 'Happy Thanksgiving', 'season' => 'fall']);

            $setup->getConnection()
                ->update($table, ['season' => 'winter'], 'greeting_id IN (1,2)');
        }
        $setup->endSetup();
    }
}

As mentioned above, Upgrade files only run when the version of the module changes. That is the reason why we use the code inside the “if”.

Recurring.php

 
1  class \VendorName\ModuleName\Setup\Recurring implements  
2  \Magento\Framework\Setup\InstallSchemaInterface 
3  { 
4      /** 
5       * {@inheritdoc} 
6       */ 
7       public function install(SchemaSetupInterface $setup, 
8  ModuleContextInterface $context)  
9      { 
10        //Recurring schema event logic
       }
   }

Magento implements the RecurringData class after every stage of data installation or upgrade. This class performs final modifications to the database repository after the data has been installed or updated.

Disadvantages

However, the usage of this mechanism has also revealed many disadvantages. 

Take a look at the following examples: 

1. The chaos in the system: The existence of different script types in the system causes its instability and complexion. One of the most common mistakes many developers make is to change the Install script instead of creating a new one. This results in the ignored changes.

2. The complicated installation: For example, you install a table in a database and make adjustments to that table. It is hard for you to imagine the final outcome of the tables in the database. This is even more difficult when you implement multiple tables and multiple upgrades.

3. There is no update confirmation and predictable errors before the installation process.

4. With the development of many new versions, the size of the UpgradeSchema and UpgradeData files become larger. The reason is parts for the new version are added to the script without deleting the contents of the old versions. 

5. The latest module version usually contains all changes and updates in the code. During the installation process, Magento will go through all the module versions and through all updates until the final version is reached.

2. Declarative Schema

The second ways to create table using database schema in Magento 2 is using declarative schema. When you  install or upgrade using PHP scripts, your stable work is still ensured, but that is not enough. Magento is constantly developing.

In 2018, Magento introduced the Declarative Schema feature to avoid overloading and speed up installation and upgrade. This is a new method to work with the database and you do not need to write different scripts for each new module version. Besides, you can declare the last desired state of the database and the system automatically adjusts it without performing backup operations. In addition, this method allows you to delete data when uninstalling modules.

Magento 2.3 does not require using a Declarative schema. However, upgrade scripts will be removed in favor of the Declarative schema.

To prepare the module for the Declarative Schema, you need:

– Build data / schema patch

– Configure declarative schema for the module

– Convert upgrade script with declarative schema (This step applies to published modules using upgrade script)

* Note: a module converted to a declarative schema will not convert back to an upgrade script.

Hence, after you know the advantages of using a Declarative Schema, you will learn more about the structure and declaration of this feature.

Let’s take an example to make better understandings:

structure and declaration of schema

Db_schema structure

We declare the database structure in the file:

<Module_Vendor>/<Module_Name>/etc/db_schema.xml

High level node

The node schema determines the location of the schema.xsd file.

<schema xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:noNamespaceSchemaLocation=”urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd”>

*Note: The location of the XSD file:

/vendor/magento/framework/Setup/Declaration/Schema/etc

Table node

Each db_schema.xml file must contain one or more table nodes. Each table node represents a table in the database. A table node may contain the following properties:

Attribute Description
name Tabe name
engine SQL engine. This value must be innodb or memory.
resource The segmented database to install the table. This value must be: default, checkout or sales
comment Table comment 

A table node can contain 3 types of subnodes:

  • column
  • constraint
  • index

Column subnode

The Column subnode will declare a column in the table. Each column requires its own declaration.

Each Column can have the following properties:

Attribute Description
xsi:type

Determine the type of the column:

  • blob (includes blob, mediumblob, longblob)
  • boolean
  • date
  • datetime
  • decimal
  • float
  • int (includes smallint, bigint, tinyint)
  • real (includes decimal, float, double, real)
  • smallint
  • text (includes text, mediumtext, longtext)
  • timestamp
  • varbinary
  • varchar
default

Initialize column with specified default value. The default value must have the same data

type defined in xsi: type.

disabled Disable or delete the declared table, column, constraint or index.
identity Indicate whether a column is auto incremented.
length Specifies the length of a column in text and binary
nullable Indicates whether the column can be nullable.
onCreate

This is a DDL trigger that allows you to move data from an existing column to the newly created column.

This trigger only works when a column is created.

padding The size of an integer column.
precision Number of digits allowed in a real data type
scale Number of digits after decimal in a real data type.
unsigned

For numeric data types, specify whether the column can contain positive and negative values or only

positive values.

Constraint subnode

The Constraint subcode always has the following properties:

Attribute Description
type One of primary, unique, or foreign
referenceId

A custom identifier is only used to map relationships within the file db_schema.xml. Names of Entities

in databases are generated by the system. The most convenient way to set the value of this attribute

is to use the value written in the file module db_schema_whitelist.json when you run the command

generate-whitelist.


foreign constraints are similar to foreign keys in SQL. This type of binding connects the two tables together. The following properties define foreign constraints:

Attribute Description
table Table name
column A column in the current table refers to a specific column in another table.
referenceTable Table is referenced
referenceColumn A column in ReferenceTable
onDelete

Foreign key trigger. The value must be CASCADE, SET NULL, or NO ACTION

Index subnode

Index subnodes have the same structure as internal constraints but contain different logic. While constraints are used to define limits, indexes are used to speed up DQL activities. The following properties define an Index:

Attribute Description
referenceId

A custom identifier is only used to map relationships within the file db_schema.xml. Names of Entities

in databases are generated by the system. The most convenient way to set the value of this attribute

is to use the value written in the file module db_schema_whitelist.json when you run the command

generate-whitelist.

indexType The value must be btree, fulltext, or hash

Perform operations with the database

Next, let’s learn about common operations with databases using declarative schemas

Create Table

Take an example of creating a table: catalog_product_entity of the entity_id primary key:

create a table

When you create a new table, remember creating magento 2 db_schema_whitelist.json file by using the command:  php bin/magento setup:db-declaration:generate-whitelist –module-name= ModuleName

create a new table

Clear the table

To delete the catalog_product_entity table in the database, you need to remove the table declaration code from the db_schema.xml file.

clear the table

Rename the table

When renaming, declarative schema will create a table with a new name and remove the old table. Data is transferred from the old table to the new one defined by the onCreate attribute.

For example, rename the catalog_product_entity table to new_catalog_product_entity.

rename table

*Note: You cannot transfer data and rename columns at the same time.

After renaming the table, be sure to recreate the file:  db_schema_whitelist.json.

Add columns to the table

For example, when you add a custom column to the table, you add the code to the file db_schema.xml.

add columns to table

When adding a column to the table, be sure to recreate the file db_schema_whitelist.json.

Delete a column of a table

To delete a column from the table, you delete the code that declares the column in the file db_schema.xml.
(In contrast to the new addition).

* Note: one column can be removed only when that column exists in the file.

Change the type of column

To change the type of the column, you need to modify the property: xsi: type in the declaration code of that column in the file.

Rename the column

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

Use the following structure to move data from the same table.

 1 onCreate="migrateDataFrom(entity_id)" 


Remember to recreate the file: db_schema_whitelist.json

For example, add the index CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID to the catalog_product_entity table.

add the index

Create a foreign key

For example:add a foreign key CATALOG_PRODUCT_ENTITY_STORE_ID_STORE_STORE_ID to the table.

Create constraint:

Create a foreign key

Remove a foreign key

You remove the above declaration code of the foreign key which needs deleting.

*Note: the foreign key can only be removed once it exists in the file: db_schema_whitelist.json

Develop data and schema patches

A data patch is a class that includes instructions for modifying data. It is declared in the <Vendor>/<Module_Name>/Setup/Patch/Data/<Patch_Name>.php file and implements class \Magento\Framework\Setup\Patch\DataPatchInterface.

Develop data and schema patches

A schema patch is a class that contains instructions for modifying schema. It is declared in the <Vendor>/<Module_Name>/Setup/Patch/Schema/<Patch_Name>.php file and  implements class \Magento\Framework\Setup\Patch\SchemaPatchInterface.

schema patch

Unlike declarative schema, patches are applied only once. A list of patches is stored in the patch_list table of the database. Each unapplied patch will be applied when running setup: upgrade.

If you plan to enable rollback for your patch during the module uninstallation, you must deploy \ Magento \ Framework \ Setup \ Patch \ PatchRevertableInterface.

The declarationative schema removes the version from the setup_module table, leaving the Composer version only. Therefore, you can create all patches and new modules without specifying the setup_module version.

The order of installing patches is handled through a dependency-based approach. The patches may be independent or dependent on other patches. The independent patch can be installed in any sequence. A dependency patch requires a minimum number of patches to install successfully.

To identify a dependency in a patch, add the public static function getDependencies() method to the patch class and return the class name of the other patches which this patch depends on. The dependence can exist in any module.

1 public static function getDependencies()
 2 {
 3     return [
 4 \SomeVendor\SomeModule\Setup\Patch\Data\SomePatch::class
 5     ];
 6 }

Run the following command to revert all installed data patch of composer:

bin/magento module:uninstall Vendor_ModuleName

Run the following command to revert all installed non-composer data patch:

bin/magento module:uninstall Vendor_ModuleName

Old scripts will work with new versions of Magento. However, if you want to convert the old script to a new format, deploy \ Magento \ Framework \ Setup \ Patch \ PatchVersionInterface. This interface allows you to specify the setup version of the module in your database.

If the module version is higher or equal to the version specified in your patch, the patch will be ignored. If the version in the database is lower, then the patch will install.

3. Conclusion

After learning more about the Declarative Schema we can see more advantages of this feature compared to writing using PHP.

  • Avoid missed / repeated SQL operations: we’ll just install what is needed
  • There is a dry-run mode for trial installation which helps detect errors before installation
  • Optimize performance: Declarative helps save considerable time by moving from the current version to the last point
  • Support for rollback: We can go back to the previous version.

    In this article, we already show you how to create table using database schema in Magento 2. We hope our article is helpful and good luck to you!

    BSS Commerce is one of the leading Magento 2 Plugins providers and  Magento 2 development service in the world. With experienced and certified Magento developers, we commit to bringing high-quality products and services to optimize your business effectively. Furthermore, we offer FREE Installation – FREE 1-year Support and FREE Lifetime Update for every Magento extension.

< Previous Post
Next Post >
+ posts