Declarative Schema in Magento 2.3

Unlock the unlimited possibilities in eCommerce by building your online store in Magento. Get your Magento website developed with advanced features and integrations.

Magento 2.3has introduced a new feature called

the Declarative Schema, which plays a major role in going beyond simply installing and updating the schema process. Previously, in all Magento versions, developers would write a PHP script for installing and updating data in the database.

Various scripts were required for:

1. Installing and updating the schema.
2. Installing and updating data.
3. Managing other operations when Magento was installed or upgraded.

The Schema Listener Tool converts Magento 2.3 migration scripts into a declarative schema. The declarative schema approach allows developers to declare the final desired state of the database and has the system adjust to it automatically, without performing redundant operations. Developers are no longer forced to write scripts for each new version. In addition, this approach allows data to be deleted when a module is uninstalled.

We can handle the Data Definition Language (DDL) and Data Manipulation Language (DML) in Declarative Schema.

How does Declarative Schema help developers?

1. Installing and upgrading a code can be handled in a single XML file.
2. Time-consuming
3. Performance improvements

How to configure Declarative Schema in Magento 2.3?

We are going to see the entire details of the declarative schema interface in this blog. Using DDL and DML we can perform the following operations:

1. Create a table
2. Drop a table
3. Rename a table
4. Add a column to the table
5. Drop a column to the table
6. Change column type
7. Rename a column
8. Add an Index
9. Create a foreign key
10. Drop a foreign key
11. Recreate a foreign key

Step 1: Create a table

We hope that everyone’s already aware of how to create a custom module in Magento. We are going to be creating a custom module in the format below.

app / code / /

We have created a DCKAP_DeclarativeSchema on the following directory:

/ app / code / DCKAP / DeclarativeSchema

Create db_schema.xml file after creating the custom module in the following directory:

app / code / DCKAP / DeclarativeSchema / etc / db_schema.xml

We have created a table name called custom_table with four different entities (entity_id, reference_id, title, description, timestamp) in db_schema.xml. We must create db_schema_whitelist.json once we create a database table using declarative schema.

Use the following Magento command to create db_schema_whitelist.json file for your custom module:

>> bin/magento setup:db-declaration:generate-whitelist –module-name=DCKAP_DeclarativeSchema

You can create a db_schema_whitelist.json file manually or by using the above command, it depends on the developers choice.

 

Top node

The top node represents the schema node, which is located in the schema.xsd file.

The location of the schema.xsd file is: /vendor/magento/framework/Setup/Declaration/Schema/etc/schema.xsd

Table node

We can create more than one table in the same db_schema.xml file, each table node creates a new table in the database. A table node can contain the following attributes:

1. Name: The name of the table
2. Engine: SQL engine, this value must be InnoDB or memory.
3. Resource: The database shard on which to install the table. This value must be default, checkout, or sales.
4. Comment: Table comment

The table node contains three different types of subnodes:

1. Column
2. Constraints
3. Index

Column node

The column node defines inside the table node, each column node has its own declaration. A column node can contain the following attributes:

1. Type: the column type should contain one of the blob (includes blob, mediumblob, longblob), boolean, date, datetime, int (includes smallint, bigint, tinyint), real (includes decimal, float, double, real), text (includes text, mediumtext, longtext), timestamp, varbinary, and varchar.
2. Name: name of the column 
3. Padding: indicates the size of the integer column
4. Unsigned: indicates if the column contains positive and negative values or only positive values
5. Nullable: indicates if the column can be nullable
6. Comment: indicates the comment of the column 
7. Length: indicates the length of a column

Constraints node

The constraints node can contain the following attributes:

1. Type: primary, unique, or foreign
2. Referrence_id : a custom identifier that is used only for relation mapping in the scope of db_schema.xml files

Step 2: Drop a table

We have to remove the entire table node in the db_schema.xml file, and the table will be removed once you run an upgrade command (php bin/magento setup:upgrade).

The following example is used to remove custom_table in the database:

-	
-		
-		
-		
-		
-		
-		
-			
-		
-

Step 3: Rename a table

We have to change the name of the table or migrate the data from one table to another name using an onCreate attribute in the source table node and also specify the table name in the onCreate attributes itself.

The onCreate attribute declaration should be:

onCreate=”migrateDataFromAnotherTable(table_name)”

Please note that migrating the data from another table and renaming columns at the same time is not supported.

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.

Use the following example for changing new_custom_table instead of custom_table.

+	
-

Step 4: Add a column to the table

We are going to add a new column in the respective database table in the db_schema.xml file. We have added a comment column in the custom_table.

When adding a new column into the table, remember to generate the db_schema_whitelist.json file.

The following example adds the comment column:

+

 Step 5: Drop a column from a table

We have removed the comment column by deleting its column node. To drop a column declared in another module, redeclare it with the disabled attribute set to true.

It is possible to drop a column only if it exists in the db_schema_whitelist.json file.

	
-

Step 6: Change the column type

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

	
-		
+

Step 7: Rename a column

We have changed the description column name to body. The column should be changed in the database, but the data hasn’t been updated to the column yet. 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.

The following example is used to rename the description column to body with migrating the data.

	  
-		
+

Step 8: Add an Index

The following example adds the INDEX_REFERENCE index to the custom_table table.

	
			
		
+		
+			
+

Step 9: Create a foreign key

The following example is used to add a new foreign key using a constraint node. Create a reference type as foreign in the constraint node, which indicates the foreign key for the custom_table from the reference table called reference_custom_table.

	
		
		
+

Step 10: Drop a foreign key

The following example removes the ADD_REF_FOR_CUSTOM_TABLE foreign key by deleting the constraint node. Use disable attribute is true when you are going to declare the constraint node to the other module.

It is possible to drop a foreign key only if it exists in the db_schema_whitelist.json file.

	
		
	
-

Step 11: Recreate a foreign key

We have to add disable attribute is true to the respective constraint node. The following example is used to explain how to recreate a PRIMARY key for the new_entity_id column.

The following example, extracting from the vendor/magento/module-catalog/etc/db_schema.xml file, defines the catalog_product_entity table.

We are at the end of this blog and we hope that everyone now has an idea of how to use Magento 2.3 to perform DDL and DML operations in Magento 2.3 using declarative schema.

<?xml version="1.0"?> 
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd"> 
+ <table name="custom_table" resource="default" comment="Create table using declarative schema">
+ <column xsi:type="int" name="entity_id" padding="10" identity="true" unsigned="true" nullable="false" comment="Entity Id"/>
+ <column xsi:type="int" name="reference_id" padding="10" unsigned="true" nullable="false" comment="Reference Id"/> 
+ <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/> 
+ <column xsi:type="varchar" name="description" nullable="false" length="255" comment="Description"/> 
+ <column xsi:type="timestamp" name="time" comment="Time"/> 
+ <constraint xsi:type="primary" referenceId="PRIMARY"> 
+ <column name="entity_id"/> 
+ </constraint> 
+ </table>
 </schema>