Magento 2 Development 03: Creating Tables

 

Before developing frontend part of the module, let’s create some tables. It’s good idea to create the tables beforehand. Because it help us visualise the working of the module, which helps us in developing the module step by step.

We will be developing a simple blog manager module so we need a table to save the blog data and another table to save the comments. If in future, any other tables are needed or if we have to add/modify any columns then we can easily do that in Magento.

Declarative Schema

Magento 2 usage declarative schema to create the tables. To know more about db schema or any topic for that matter please google the topic and you will find multiple blogs which will have explained about that topic. One such great place to learn in detail is Magento Developer Documentation.

For declarative schema we need to create the db_schema.xml under the etc folder.

<?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="blogmanager_blog" resource="default" engine="innodb" comment="Blogs Table">

<column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity Id"/>

<column xsi:type="int" name="user_id" padding="10" unsigned="true" nullable="false" comment="Customer/User Id"/>

<column xsi:type="varchar" name="title" nullable="false" length="255" comment="Blog Title"/>

<column xsi:type="longtext" name="content" nullable="false" comment="Blog Content"/>

<column xsi:type="smallint" name="status" padding="11" unsigned="false" nullable="false" default="0" comment="Blog Status"/>

<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP" comment="Creation Time"/>

<column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false" default="CURRENT_TIMESTAMP" comment="Updated At"/>

<constraint xsi:type="primary" referenceId="PRIMARY">

<column name="entity_id"/>

</constraint>

<index referenceId="BLOGMANAGER_BLOG_USER_ID" indexType="btree">

<column name="user_id"/>

</index>

</table>

<table name="blogmanager_comment" resource="default" engine="innodb" comment="Blog Comments Table">

<column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity Id"/>

<column xsi:type="int" name="blog_id" padding="10" unsigned="true" nullable="false" comment="Blog Id"/>

<column xsi:type="int" name="user_id" padding="10" unsigned="true" nullable="false" comment="User Id"/>

<column xsi:type="varchar" name="screen_name" nullable="false" length="255" comment="Screen Name"/>

<column xsi:type="text" name="comment" nullable="false" comment="Comment"/>

<column xsi:type="smallint" name="status" padding="11" unsigned="false" nullable="false" default="0" comment="Status"/>

<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP" comment="Creation Time"/>

<constraint xsi:type="primary" referenceId="PRIMARY">

<column name="entity_id"/>

</constraint>

<constraint xsi:type="foreign" referenceId="FK_BLOG_COMMENT" table="blogmanager_comment" column="blog_id" referenceTable="blogmanager_blog" referenceColumn="entity_id" onDelete="CASCADE"/>

<index referenceId="BLOGMANAGER_COMMENT_BLOG_ID" indexType="btree">

<column name="blog_id"/>

</index>

</table>

</schema>

As you can see it is pretty self explanatory, table node/table tag represents the table and the column nodes inside table node represents each columns. We can manage the name, type, length, etc info about a column with the attributes of the tag/node. To create constraint like Primary key, Foreign Key we have to use constraint tag. We can also create indexes with index tag. Here we have created two tables blogmanager_blog, blogmanager_comment.

Hello Students...

After creating the db_schema.xml we need to generate the db_schema_whitelist.json which provides a history of all tables, columns, keys added with declarative schema. You don’t have to worry about this file because it can be generated automatically with command. It’s for backward compatibility and it will be removed in future.

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

It will create db_schema_whitelist.json in etc folder.

Now to actually process the db_schema.xml and create the tables we need to run setup upgrade command.

php bin/magento setup:upgrade

After successfully running this command, when you check your database you will find the new tables,

Now suppose that later we decided to change something, we can just do the changes in the db_schema.xml file, regenerate the db_schema_whitelist.json and run the setup upgrade command.

Install Schema and Upgrade Schema

The declarative schema is a new addition to magento. In earlier version of Magento, before 2.3, we had to use InstallSchema.php file to create the tables. And if we later decided to make some changes in the table we had to use UpgradeSchema.php. So first time when we install the module it would run the InstallSchema.php file and for version updates it would run the UpgradeSchema.php.

Please check out these blogs which explain about using these files. Note that it will not work in our module because we have not mentioned the module version number in the module.xml file.

How to create a new database table in Magento 2.0

Setup script files in Magento2

Folder Structure till now,

Thanks For Reading...