How to create a custom table in Magento 2?

In this blog, I will share how to create a new table in Magento 2.
Previously, We were using InstallData and InstallSchema scripts, which executed at the time of module installation. UpgradeData and UpgradeSchema scripts, for updating and adding new fields to the table. Recurring scripts, which executed every time when you install or upgrade Magento.

The main disadvantage of this approach is that Magento applies changes blindly. For example, in one version a new database column might be introduced, only to be removed in the next. Declarative setup eliminates this type of unnecessary work

So firstly, you need to create a new extension and put all the necessary files in app/code/Magento/Mastering folder for example:

registration.php

<?php
use \Magento\Framework\Component\ComponentRegistrar;

ComponentRegistrar::register(ComponentRegistrar::MODULE, 'Magento_Mastering', __DIR__);

etc/module.xml:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Magento_Mastering" setup_version = "1.0.0">
    </module>
</config>

Once done then run the below command this way your plugin will be installed and listed in the Magento config file.

php bin/magento setup:upgrade

Now you can check if the plugin is enabled or not go to app/code/etc/config.xml. It will show the module with the status “1”.

'Magento_Multishipping' => 1,

Now back to the main topic…. 🙂

db_schema structure:
The <Module_Vendor>/<Module_Name>/etc/db_schema.xml the file declares a module’s database structure. In our case, it will be app/code/Magento/Mastering/etc/db_schema.xml

<?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="declarative_table">
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" comment="Id" identity="true"/>
        <column xsi:type="varchar" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="varchar" name="code" padding="10" comment="Code"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id"/>
        </constraint>
    </table></schema>

To check the table has been created all you need to do is:
Go to SSH => mysql -u root -p
Use  <magentoTable>;
Run this command : desc declarative;

You will see the declaration of your table 🙂

Run this Command after installing table

The following steps help make the conversion go as smoothly as possible. When creating a new table, remember to generate the db_schema_whitelist.json file.  

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

In our case we will run this command:

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

You can find the path in:
app/code/Magento/Mastering/etc/ db_schema_whitelist.json

In conclusion,  if you have added the table creation script after the module is already enabled then try removing the entry of the module from the setup_module table and then run bin/magento module:enable Vendor_Module and bin/magento setup:upgrade commands or try creating a table using the UpgradeSchema script.