Create Magento 2 database table

March 4, 2022April 24, 2023 Hilary howard

From Magento 2.3, we get acquainted with the method of creating a database called declarative schema. In this article, we will use it to create Magento 2 database table for our module. To better understand the structure of db_schema.xml file, I suggest you start looking at the Magento Catalog in the path vendor\magento\module-catalog\etc\db_schema.xml.

Let go! First we create db_schema.xml file put at the path app\code\Magerubik\Simple\etc\db_schema.xml with below content:

<?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="vendor_message" resource="default" engine="innodb" comment="vendor message Table">
        <column xsi:type="int" name="messages_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Messages ID"/>
        <column xsi:type="smallint" name="user_id" padding="5" unsigned="true" nullable="false" identity="false" default="0" comment="User ID"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="mediumtext" name="description" nullable="true" comment="Description"/>
        <column xsi:type="varchar" name="status" nullable="false" length="20" default="not read yet" comment="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="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="messages_id"/>
        </constraint>
        <index referenceId="VENDOR_MESSAGE_USER_ID" indexType="btree">
            <column name="user_id"/>
        </index>
    </table>
</schema>

In the declarative schema method, we will use two files db_schema.xml and db_schema_whitelist.json in the same folder to create and update the database.

In there
– “db_schema.xml”: create or modify database table.
– “db_schema_whitelist.json”: save modified history.

So, we need to run the below command to save modified history after changing file db_schema.xml.

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

OK check it working with our module:

1. Run upgrade command to check database was being created

php bin/magento setup:db-declaration:generate-whitelist --module-name=Magerubik_Simple
php bin/magento setup:upgrade
php bin/magento setup:static-content:deploy -f

Go to CPanel check your database if see the below screenshot everything is ok.

Then go to your module folder check file db_schema_whitelist.json. It should have content like below at this time.

{
    "vendor_message": {
        "column": {
            "messages_id": true,
            "user_id": true,
            "title": true,
            "description": true,
            "status": true,
            "created_at": true,
            "updated_at": true
        },
        "index": {
            "VENDOR_MESSAGE_USER_ID": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

2. Perform modify the database table

Add a column to table

We add line to db_schema.xml like below:

<?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="vendor_message" resource="default" engine="innodb" comment="vendor message Table">
        <column xsi:type="int" name="messages_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Messages ID"/>
        <column xsi:type="smallint" name="user_id" padding="5" unsigned="true" nullable="false" identity="false" default="0" comment="User ID"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="mediumtext" name="description" nullable="true" comment="Description"/>
        <column xsi:type="varchar" name="status" nullable="false" length="20" default="not read yet" comment="Status"/>
        <column xsi:type="timestamp" name="readed_date" on_update="false" nullable="true" comment="Readed Date"/>
        <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="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="messages_id"/>
        </constraint>
        <index referenceId="VENDOR_MESSAGE_USER_ID" indexType="btree">
            <column name="user_id"/>
        </index>
    </table>
</schema>

Then run command generate whitelist and upgrade to check it working.

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

The db_schema_whitelist.json file should have content like below at this time

{
    "vendor_message": {
        "column": {
            "messages_id": true,
            "user_id": true,
            "title": true,
            "description": true,
            "status": true,
            "created_at": true,
            "updated_at": true,
            "readed_date": true
        },
        "index": {
            "VENDOR_MESSAGE_USER_ID": true
        },
        "constraint": {
            "PRIMARY": true
        }
    }
}

remove a column from a table

we will delete the added line then run the upgrade command.

Also you can remove column from other module by redeclare it with the disabled attribute set to true.

You possible to remove a column only if it exists in the db_schema_whitelist.json file.

The db_schema.xml file Should like below:

<?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="vendor_message" resource="default" engine="innodb" comment="vendor message Table">
        <column xsi:type="int" name="messages_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Messages ID"/>
        <column xsi:type="smallint" name="user_id" padding="5" unsigned="true" nullable="false" identity="false" default="0" comment="User ID"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
        <column xsi:type="mediumtext" name="description" nullable="true" comment="Description"/>
        <column xsi:type="varchar" name="status" nullable="false" length="20" default="not read yet" comment="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="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="messages_id"/>
        </constraint>
        <index referenceId="VENDOR_MESSAGE_USER_ID" indexType="btree">
            <column name="user_id"/>
        </index>
    </table>
	<table name="catalog_product_entity_varchar" resource="default" engine="innodb" comment="Catalog Product Varchar Attribute Backend Table">
        <column xsi:type="varchar" name="value" nullable="true" length="255" disabled="true" comment="Value"/>
    </table>
</schema>

It ok, if you can see the below screenshot:

Change the column “type”:

You can modify the attribute except “name” then run the upgrade command to check it.

Change the column “name”:

Change the column “name” consists of two processes: delete the original column declaration and create a new one then migrate data from the original column declaration to the new column with the method onCreate=”migrateDataFrom(old_name)”

Change db_schema.xml file Should like below:

<?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="vendor_message" resource="default" engine="innodb" comment="vendor message Table">
        <column xsi:type="int" name="messages_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Messages ID"/>
        <column xsi:type="smallint" name="user_id" padding="5" unsigned="true" nullable="false" identity="false" default="0" comment="User ID"/>
        <column xsi:type="varchar" name="username" nullable="false" length="255" onCreate="migrateDataFrom(title)" comment="Username"/>
        <column xsi:type="mediumtext" name="description" nullable="true" comment="Description"/>
        <column xsi:type="varchar" name="status" nullable="false" length="20" default="not read yet" comment="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="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="messages_id"/>
        </constraint>
        <index referenceId="VENDOR_MESSAGE_USER_ID" indexType="btree">
            <column name="user_id"/>
        </index>
    </table>
</schema>

Then run command generate whitelist and upgrade to check it working. It ok, if you can see the below screenshot:

Add an index:

See on our example

<index referenceId="VENDOR_MESSAGE_USER_ID" indexType="btree">
	<column name="user_id"/>
</index>

In there
– “referenceId” must be unique
– “indexType” The value must be btree, fulltext, or hash

Create a foreign key:

We use “constraint” node to create a foreign key

Change db_schema.xml file Should like below:

<?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="vendor_message" resource="default" engine="innodb" comment="vendor message Table">
        <column xsi:type="int" name="messages_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Messages ID"/>
        <column xsi:type="smallint" name="user_id" padding="5" unsigned="true" nullable="false" identity="false" default="0" comment="User ID"/>
        <column xsi:type="varchar" name="username" nullable="false" length="255" onCreate="migrateDataFrom(title)" comment="Username"/>
        <column xsi:type="mediumtext" name="description" nullable="true" comment="Description"/>
        <column xsi:type="varchar" name="status" nullable="false" length="20" default="not read yet" comment="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="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="messages_id"/>
        </constraint>
        <index referenceId="VENDOR_MESSAGE_USER_ID" indexType="btree">
            <column name="user_id"/>
        </index>
		<constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" 
			table="vendor_message" column="user_id" 
			referenceTable="customer_entity" referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>

Then run command generate whitelist and upgrade to check it working.

Remove foreign key:

You can easy Remove foreign key by delete constraint node

Also you can remove foreign key from other module by redeclare it with the disabled attribute set to true. like below

<table name="catalog_product_entity_varchar">
	<constraint disabled="true" xsi:type="foreign" referenceId="CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID"
		table="catalog_product_entity_varchar" column="attribute_id" referenceTable="eav_attribute"
		referenceColumn="attribute_id" onDelete="CASCADE"/>
</table>

You possible to remove a foreign key only if it exists in the db_schema_whitelist.json file.

Create a table:

We use “table” node to create a table

Remember to generate whitelist and upgrade to apply the change.

Remove a table:

You can easy Remove table by delete it from the db_schema.xml file.

Rename a table:

Change the table “name” consists of two processes: delete the original table declaration and create a new one then migrate data from the original table declaration to the new table with the method onCreate=”migrateDataFromAnotherTable(old_name)”

You can check it with below code:

<?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="new_vendor_message" resource="default" engine="innodb" comment="vendor message Table" onCreate="migrateDataFromAnotherTable(vendor_message)">
        <column xsi:type="int" name="messages_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Messages ID"/>
        <column xsi:type="smallint" name="user_id" padding="5" unsigned="true" nullable="false" identity="false" default="0" comment="User ID"/>
        <column xsi:type="varchar" name="username" nullable="false" length="255" onCreate="migrateDataFrom(title)" comment="Username"/>
        <column xsi:type="mediumtext" name="description" nullable="true" comment="Description"/>
        <column xsi:type="varchar" name="status" nullable="false" length="20" default="not read yet" comment="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="Update Time"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="messages_id"/>
        </constraint>
        <index referenceId="VENDOR_MESSAGE_USER_ID" indexType="btree">
            <column name="user_id"/>
        </index>
		<constraint xsi:type="foreign" referenceId="FL_ALLOWED_SEVERITIES" 
			table="new_vendor_message" column="user_id" 
			referenceTable="customer_entity" referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>

so we have grasped the declarative schema structure and create Magento 2 database table. In the next posts we will learn how to use Magento 2 UI component. Contact us if you face any problems during the installation process.

You can download the demo code for this entire series from GitHub