Database, Magento2

Declarative Schema in Magento 2.3

Declarative Schema in Magento 2.3

What is declarative schema in Magento 2?
Declarative Schema aims to simplify the Magento installation and upgrade processes. Previously, developers had to write database scripts in PHP for each new version of Magento. Various scripts were required for

  • Installing and upgrading the database schema
  • Installing and upgrading data
  • Invoking other operations that are required each time Magento was installed or upgraded

The Schema Listener Tool converts Magento 2.3 migration scripts into a declarative schema. Also From Magento 2.3 We don’t need to create InstallSchema.php and UpgradeSchema.php

Why declarative schema?
In short, before it, we had to write database scripts in PHP which is not a perfect solution.

We can handle the Data Definition Language (DDL) and Data Manipulation Language (DML) in Declarative Schema.Using DDL and DML we can perform the following operations:

  • Create a table
  • Drop a table
  • Rename a table
  • Add a column to the table
  • Drop a column to the table
  • Change column type
  • Rename a column
  • Add an Index
  • Create a foreign key
  • Drop a foreign key
  • Recreate a foreign key

Create a table : To create new table in Magento 2 we have to use below code. As per your need you can customise.

Path : app/code/<vendor_name>/<module_name>

First of all, create db_schema.xml file inside app/code/V4U/Helloworld/etc and write the following code :

Before running the upgrade command you need to add your schema to db_whitelist_schema.json file by running the following command : php bin/magento setup:db-declaration:generate-whitelist --module-name=V4U_Helloworld or php bin/magento setup:db-declaration:generate-whitelist

This will create a file inside your etc folder called db_schema_whitelist.json. For instance, our db_schema_whitelist.json

Dry run : With dry run you can examine all the DDL SQL statements generated during the installation. Your data and schema’s will remain the same so this is essentially a testing tool. By running the following command : php bin/magento setup:install --dry-run=1 or php bin/magento setup:upgrade --dry-run=1

After running it, Magento creates a log at var/log/dry-run-installation.log.

The table node contains three different types of sub nodes:

  • Column
  • Constraints
  • Index

Drop a table : If you want to drop table, then you can either remove entire table node from xml file or you can set disabled attribute to true as like below line in your db_schema.xml :

Rename a table : To rename a table in table node need to add this attribute onCreate="migrateDataFromAnotherTable(table_name)"

When renaming a table, remember to regenerate the db_schema_whitelist.json file because the old database name should be present in the old db_schema_whitelist.json file.

Add a column to the table : When adding a new column into the table, remember to generate the db_schema_whitelist.json file. Now I am adding new column named remarks.

Drop a column from a table : Table with a list of entry and some of the columns you don’t require, You can remove those columns using attribute disabled=true in a db_schema.xml file.

Dropped Column :

Change the column type : We have changed the type of the title column from varchar to text.

From :

To :

Rename a column : For that, we have to migrate the data from one column to another using an onCreate attribute, onCreate="migrateDataFrom(entity_id)"

When renaming a column, remember to regenerate the db_schema_whitelist.json file so it contains the new name in addition to the old one.

Old Column Name :

New Column Name :

Create a foreign key : To create foreign key, first we need to create one new table then we can create.

Drop a foreign key : Use disable attribute is true when you are going to declare the constraint node to the other module.

Recreate a foreign key : We can recreate a foreign key in two ways either remove constraint node from xml of make it disabled=”false”

How to create new field in Magento default created Table like sales_order ?

We can also create new field in Magento default created table like below ways.

Note : Whenever you change in xml file need to run below command to see the changes in database.

php bin/magento setup:db-declaration:generate-whitelist --module-name=V4U_Helloworld or php bin/magento setup:db-declaration:generate-whitelist command and then php bin/magento setup:upgrade

Tagged , , , ,