Declarative Schema Overview in Magento 2 - Part 2

Declarative Schema Overview in Magento 2 - Part 2
How to migrate your Install/Upgrade scripts to declarative schema using safe mode and rollbacks in Magento 2.

Published by Rick Daalhuizen

In the first part we discussed what declarative schemes are, why you should use declarative schemes instead of install / upgrade scripts, how to create, edit and eventually delete a declarative. If you haven’t already read the first part, I suggest you take a moment to read this first. As an example, I created a simple module containing a InstallSchema.php that looks like this.

 1<?php
 2/**
 3* Copyright (c) 2016 Magento. All rights reserved.
 4* See COPYING.txt for license details.
 5*/
 6
 7namespace Rick\Example\Setup;
 8
 9use Magento\Framework\DB\Ddl\Table;
10use Magento\Framework\Setup\InstallSchemaInterface;
11use Magento\Framework\Setup\ModuleContextInterface;
12use Magento\Framework\Setup\SchemaSetupInterface;
13
14/**
15* @codeCoverageIgnore
16*/
17class InstallSchema implements InstallSchemaInterface
18{
19   /**
20    * {@inheritdoc}
21    * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
22    */
23   public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
24   {
25       /**
26        * Create table 'quote_item_file'
27        */
28       $table = $setup->getConnection()
29           ->newTable($setup->getTable('quote_item_file'))
30           ->addColumn(
31               'entity_id',
32               Table::TYPE_INTEGER,
33               null,
34               ['nullable' => false, 'primary' => true, 'identity' => true],
35               'Entity ID'
36           )
37           ->addColumn(
38               'filename',
39               Table::TYPE_TEXT,
40               255,
41               ['nullable' => false],
42               'Filename'
43           )->addColumn(
44               'location',
45               Table::TYPE_TEXT,
46               255,
47               ['nullable' => false],
48               'Location of file'
49           )->addColumn(
50               'quote_item_item_id',
51               Table::TYPE_INTEGER,
52               null,
53               ['padding' => 10, 'nullable' => false, 'unsigned' => true],
54               'Item Id'
55           )->addForeignKey(
56               $setup->getFkName(
57                   $setup->getTable('quote_item_file'),
58                   'quote_item_item_id',
59                   'quote_item',
60                   'item_id'
61               ),
62               'quote_item_item_id',
63               $setup->getTable('quote_item'),
64               'item_id',
65               Table::ACTION_CASCADE
66           )->setComment('Quote Item File Table');
67
68       $setup->getConnection()->createTable($table);
69   }
70}

Migrate install/upgrade scripts to a declarative scheme

Now it is also possible to convert existing installation / upgrade scripts to a declarative scheme with the “Schema Listener Tool”. It is useful to know that using the Schema listener tool can only be used in developer mode, as it will make changes to the code. In addition, it does not take into account raw SQL data, and custom DDL operations outside of \Magento\Framework\DB\Adapter\Pdo\Mysql.

To perform the migration, run the following command:

bin/magento s:up --convert-old-scripts=1

For Magento versions less than 2.4 use the - convert_old_scripts flag

Note that this only works during an installation or upgrade of your modules. If you have issues generating the db_schema.xml, try removing the module entry in the setup_module table and then run setup:upgrade again.

This will generate a db_schema.xml in your module etc directory.

 1<?xml version="1.0"?>
 2<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
 3 <table name="quote_item_file" resource="default" engine="innodb" comment="Quote Item File Table">
 4   <column xsi:type="int" name="entity_id" padding="11" unsigned="false" nullable="false" identity="true" comment="Entity ID"/>
 5   <column xsi:type="varchar" name="filename" nullable="false" length="255" comment="Filename"/>
 6   <column xsi:type="varchar" name="location" nullable="false" length="255" comment="Location of file"/>
 7   <column xsi:type="int" name="quote_item_item_id" padding="10" unsigned="true" nullable="false" identity="false" comment="Item Id"/>
 8   <constraint xsi:type="primary" referenceId="PRIMARY">
 9     <column name="entity_id"/>
10   </constraint>
11   <constraint xsi:type="foreign" referenceId="QUOTE_ITEM_FILE_QUOTE_ITEM_ITEM_ID_QUOTE_ITEM_ITEM_ID" table="quote_item_file" column="quote_item_item_id" referenceTable="quote_item" referenceColumn="item_id" onDelete="CASCADE"/>
12 </table>
13</schema>

Then we add some data

INSERT INTO `magento`.`quote_item_file` (`entity_id`, `filename`, `location`, `quote_item_item_id`) VALUES ('1', 'examole.png', 'media/foo/bar/example.png', '1');

What are schema whitelist and how to create them

Scheme whitelist is for backward compatibility. This keeps track of which adjustments have been made to the declarative schemes to prevent data loss, without finding out when this took place. When using an Install/Upgrade script this can often be found in the code.

The whitelist is generated in <Module_Vendor>/<Module_Name>/etc/db_schema_whitelist.json and can be created as follows:

bin/magento setup:db-declaration:generate-whitelist --module-name=<Module_Name>

And looks like this

{
   "quote_item_file": {
       "column": {
           "entity_id": true,
           "filename": true,
           "location": true,
           "quote_item_item_id": true
       },
       "constraint": {
           "PRIMARY": true,
           "QUOTE_ITEM_FILE_QUOTE_ITEM_ITEM_ID_QUOTE_ITEM_ITEM_ID": true
       }
   }
}

As a best practice, you should generate a new whitelist file for each release. You must generate the whitelist for any release that contains changes in the db_schema.xml file.

Make use of dry-runs

Now we’ve created our db_schema.xml and we want to alter the table in any way like adding a column, we can use dry-runs. Dry-runs assure that you don’t have to worry about the script breaking something. No changes are made to the database during a dry run.

Let’s remove one of the columns

 1<?xml version="1.0"?>
 2<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
 3  <table name="quote_item_file" resource="default" engine="innodb" comment="Quote Item File Table">
 4    <column xsi:type="int" name="entity_id" padding="11" unsigned="false" nullable="false" identity="true" comment="Entity ID"/>
 5    <column xsi:type="varchar" name="filename" nullable="false" length="255" comment="Filename"/>
 6    <!-- <column xsi:type="varchar" name="location" nullable="false" length="255" comment="Location of file"/> -->
 7    <column xsi:type="int" name="quote_item_item_id" padding="10" unsigned="true" nullable="false" identity="false" comment="Item Id"/>
 8    <constraint xsi:type="primary" referenceId="PRIMARY">
 9      <column name="entity_id"/>
10    </constraint>
11    <constraint xsi:type="foreign" referenceId="QUOTE_ITEM_FILE_QUOTE_ITEM_ITEM_ID_QUOTE_ITEM_ITEM_ID" table="quote_item_file" column="quote_item_item_id" referenceTable="quote_item" referenceColumn="item_id" onDelete="CASCADE"/>
12  </table>
13</schema>

Run the following commands to use dry-runs:

bin/magento s:up --dry-run=1

After you have performed the operation, a log file will be generated in <Magento_Root>/var/log/ dry-run-installation.log. This file contains raw SQL statements and can be used to optimize or modify your script where necessary.

www-data@example-php-fpm:06:57 PM:/var/www/html$ cat var/log/dry-run-installation.log
ALTER TABLE `quote_item_file` DROP COLUMN `location`

How to perform rollbacks

If for some reason something went wrong during the migration, a rollback can be used. For those who are not yet familiar with rollbacks, it ensures that the recent changes that have been made can be reversed.

Using the safe mode option will create a CSV file each time a destructive operation for a table or column occurs.

bin/magento s:up --safe-mode=1

This will create a csv file under one of these locations:

  • <Magento_root>/var/declarative_dumps_csv/{column_name_column_type_other_dimensions}.csv
  • <Magento_root>/var/declarative_dumps_csv/{table_name}.csv

The csv file contains information from your database table, rows, columns, and values that have been modified for the installation. In our example it looks like this

www-data@example-php-fpm:07:40 PM:/var/www/html$ cat var/declarative_dumps_csv/quote_item_file_column_location.csv
entity_id,location
1,media/foo/bar/example.png

After verifying that everything is oke, we can remove the column from the table and run:

bin/magento s:up

To revert everything back to the way it was, we just need to put the column we deleted back into db_schema.xml.

Finally we run the following to perform the rollback.

bin/magento s:up --data-restore=1

-data-restore=1 is only possible with the setup:upgrade command

Conclusion

I hope I’ve informed you enough about what declarative schemes in Magento are and how you can use them. I also recommend that you read the Magento documentation about “Migrate install/upgrade scripts to declarative schema” if you want to know more about it. For feedback or comments, please leave a message below.