Table of Contents
While working with Magento 2, sometimes you need to add an extra column to your already existing Magento 2 table. In this article, we are going to learn creation of custom table and will also tell you different operations on table in magento2.
Magento 2 allows a developer to create, upgrade, or delete a table in the database. In this article, you will learn how to perform different operations on table in Magento 2.
These operations are as mentioned below:
- How to create Table in Magento2
- How to Add multiple Tables in Magento2
- How to rename a Table in Magento2
- How to add a column in Table in Magento2
- How to change the column type in Table in Magento2
- How to rename the column of the Table in Magento2
- How to drop or delete the column of the Table in Magento2
- How to drop or delete the Table in Magento2
Let’s say, we need to create the database table which holds our articles data. I will create a table
thecoachsmb_article
and take the following columns:
article_id– the post unique identifiertitle– the title of the postcontent– the content of the postcreated_at– the date created of the post
The following is the schema for the table we want to create:
| Field | Type | Null | Key | Default |
|---|---|---|---|---|
| article_id | int(6) | NO | PRI | NULL |
| title | text | NO | NULL | |
| content | text | NO | NULL | |
| created_at | timestamp | NO | CURRENT_TIMESTAMP |
In the Magento 2.3 version or later version, we can perform different operations using the db_schema.xml file.
Let’s start with creating table.
1. How to create Table in Magento2
Here’s an easy and step-by-step process for creating table using db_schema.xml file in Magento 2.
Step 1: Create a db_schema.xml file
The first step of the process is to create a db_schema.xml file in the app/code/Vendor/Module/etc/ directory
app/code/Vendor/Module/etc/db_schema.xmland Content for this file is..
<?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="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
<column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title" />
<column xsi:type="varchar" name="content" nullable="false" length="255" comment="Descrition" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="id" />
</constraint>
</table>
</schema>
This example creates the table with four columns. The id column is the primary key.
Step 2: Add the schema to db_whitelist_schema.json file
Now, before executing the upgrade command, you’ll have to add the schema to db_whitelist_schema.json file. This provides a history of all tables, columns, and keys added with declarative schema. It is required to allow drop operations. It can be generated manually or created automatically with the following command:
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
Once you execute the above command, the db_whitelist_schema.json file will be created in the /Vendor/Module/etc directory.
Step 3: Run setup upgrade command
Lastly, you need to run the setup upgrade command given below after the db_schema_whitelist_json file has been successfully generated.
php bin/magento s:up && php bin/magento se:s:d -f
Now, check the output and you’ll see that a new table must have been created in your database table.
Explanation
<table>
-> Engine: SQL engine, this value must be InnoDB or memory.
-> Resource: The database shard on which to install the table. This value must be default, checkout, or sales
<Column>
-> identity: Indicates whether a column is auto incremented.
-> Type: blob,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.
-> Padding: indicates the size of the integer column
-> Length: indicates the length of a column
-> precision: The number of allowed digits in a real data type.
-> scale: The number of digits after the decimal in a real data type.
<Constraints>
-> Type: primary, unique, or foreign
-> Referrence_id: a custom identifier that is used only for relation mapping in the scope of db_schema.xml files
For Remove
-> disabled: Disables or deletes the declared table, column, constraint, or index.
2. How to add multiple Tables in Magento2
To add multiple tables, you need to add table again in the same db_schema.xml file.
Content for this file is..
<?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="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
<column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title" />
<column xsi:type="varchar" name="content" nullable="false" length="255" comment="Descrition" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
<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="id" />
</constraint>
</table>
<table name="thecoachsmb_blog" resource="default" engine="innodb" comment="thecoachsmb_blog is the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
<column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title" />
<column xsi:type="varchar" name="content" nullable="false" length="255" comment="Descrition" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="id" />
</constraint>
</table>
</schema>
Then run the below commands to see the changes.
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento s:up && php bin/magento se:s:d -f
3. How to rename a Table in Magento2
The declarative schema will create a new table with the new name and drop the table with the old name. Renaming a table via RENAME TABLE is NOT supported.
To migrate data from another table, specify the onCreate attribute on the table declaration, and add specify the source table name:
onCreate="migrateDataFromAnotherTable(old_table_name)" |
Please note that migrating data from another table and renaming columns at the same time is not supported.
This declarative process of renaming a table is not fast. If you need to migrate lots of data quickly you can create a CSV table dump using the --safe-mode=1 and add the data manually by using data/recurring patches.
<?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="thecoachsmb_article_new" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name" onCreate="migrateDataFromAnotherTable(old_table_name)">
- <table name="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
<column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title" />
<column xsi:type="varchar" name="content" nullable="false" length="255" comment="Descrition" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
<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="id" />
</constraint>
</table>
</schema>
When renaming a table, remember to regenerate the db_schema_whitelist.json file so it contains the new name in addition to the old one.
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento s:up && php bin/magento se:s:d -f
4. How to add a column in Table in Magento2
The following example adds the updated_at column.
Content for this file is..
<?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="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
<column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title" />
<column xsi:type="varchar" name="content" nullable="false" length="255" comment="Descrition" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
+ <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="id" />
</constraint>
</table>
</schema>
Then run the below commands to see the changes.
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento s:up && php bin/magento se:s:d -f
5. How to change the column type in Table in Magento2
The following example changes the type of the title column from varchar to text.
Content for this file is..
<?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="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
- <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
+ <column xsi:type="text" name="title" nullable="false" comment="Title"/>
<column xsi:type="varchar" name="content" nullable="false" length="255" comment="Descrition" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
<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="id" />
</constraint>
</table>
</schema>
Then run the below commands to see the changes.
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento s:up && php bin/magento se:s:d -f
6. How to rename the column of the Table in Magento2
To rename a column, delete the original column declaration and create a new one. In the new column declaration, use the onCreate attribute to specify which column to migrate data from. Use the following construction to migrate data from the same table.
onCreate="migrateDataFrom(old_column_name)" |
Let’s see the example below:
We will rename the content column to description here.
<?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="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
<column xsi:type="text" name="title" nullable="false" comment="Title"/>
- <column xsi:type="varchar" name="content" nullable="false" length="255" comment="Descrition" />
+ <column xsi:type="varchar" name="description" nullable="false" length="255" comment="Descrition" onCreate="migrateDataFrom(content)" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
<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="id" />
</constraint>
</table>
</schema>
db_schema_whitelist.json file so it contains the new name in addition to the old one.Run the below commands to see the changes:
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento s:up && php bin/magento se:s:d -f
7. How to drop or delete the column of the Table in Magento2
The following example removes the updated_at column by deleting its column node. To drop a column declared in another module, redeclare it with the disabled attribute set to true.
<?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="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
<column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
<column xsi:type="text" name="title" nullable="false" comment="Title"/>
<column xsi:type="varchar" name="description" nullable="false" length="255" comment="Descrition" onCreate="migrateDataFrom(content)" />
<column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
- <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="id" />
</constraint>
</table>
</schema>
db_schema_whitelist.json file.Run the below commands to see the changes:
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento s:up && php bin/magento se:s:d -f
8. How to drop or delete the Table in Magento2
In the following example, the thecoachsmb_article table was completely removed from the db_schema.xml file.
<?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="thecoachsmb_article" resource="default" engine="innodb" comment="thecoachsmb_articleis the table name">
- <column xsi:type="int" name="id" identity="true" unsigned="true" nullable="false" comment="Id"/>
- <column xsi:type="text" name="title" nullable="false" comment="Title"/>
- <column xsi:type="varchar" name="description" nullable="false" length="255" comment="Descrition" onCreate="migrateDataFrom(content)" />
- <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
- <constraint xsi:type="primary" referenceId="PRIMARY">
- <column name="id" />
- </constraint>
- </table>
</schema>
When dropping a table, do not remove it from the db_schema_whitelist.json file, otherwise it will not be dropped.
Run the below commands to see the changes:
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento s:up && php bin/magento se:s:d -f
Conclusion
There are so many operations, we can do it on the table. These operations are explained in this article. Hope this is ver clear now for you to perform these operations.
Feel free to comment below for any questions or the feedback. We would love to hear back from you.
helo sir ,
how r you,
I tried my best and follow all steps.but i could not complete operation.
db_schema_whitelist_json .The given file created in etc but no table visible in the data base nor any error generated by magento2. my version is compatible which is 2.3 or grater.
waiting ur response.
Regards
db_schema_whitelist_json this file will be created, youjust need to run the command