Create Database Tables in Magento 2

Before developing the frontend part of the module, let’s create some tables. It’s a good idea to create the tables beforehand. Because it helps us visualize 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 the 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 uses a declarative schema to create the tables. The new 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 be deleted when a module is uninstalled.

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

Code for etc/db_schema.xml file will be

<?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, the table node/table tag represents the table and the column nodes inside the table node represents each column. We can manage the name, type, length, etc info about a column with the attributes of the tag/node.

To create constraints like Primary key, and 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.

db_schema_whitelist

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

It’s for backward compatibility and it will be removed in the future.

The db_schema_whitelist. json file is a way of telling Magento which tables and columns it can safely alter using the db_schema. xml file.

You can use the following command to generate the db_schema_whitelist.json file

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

It will create db_schema_whitelist.json in the etc folder.

Now to actually process the db_schema.xml and create the tables we need to run the 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 the earlier version of Magento, before 2.3, we had to use the 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 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.

Create New Database Table

Setup Script Files

Folder Structure till now,