Magento 2: How To Create Database Table
In the previous article, we learned How To Create A Simple Custom Module. Following the previous tutorial, we will learn about How To Create Database Table in Magento 2.
This is a very basic tutorial but it is very important. You will apply it a lot. Let’s start!
Module File Structure
We updated our module file structure looks as follows:
Create file module.xml and registration.php
The job of creating these 2 files I have detailed instructions and explanations in the previous article. You can read it here.
Create file InstallSchema.php
You create this file according to the path: Magetop/Helloworld/Setup/InstallSchema.php.
We will create a table name magetop_blog with fields: id, title, description, created_at, status.
<?php
namespace Magetop\Helloworld\Setup;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
class InstallSchema implements InstallSchemaInterface
{
public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
{
$installer = $setup;
$installer->startSetup();
$tableName = $installer->getTable('magetop_blog');
//Check for the existence of the table
if ($installer->getConnection()->isTableExists($tableName) != true) {
$table = $installer->getConnection()
->newTable($tableName)
->addColumn(
'id',
Table::TYPE_INTEGER,
null,
[
'identity' => true,
'unsigned' => true,
'nullable' => false,
'primary' => true
],
'ID'
)
->addColumn(
'title',
Table::TYPE_TEXT,
null,
['nullable' => false, 'default' => ''],
'Title'
)
->addColumn(
'description',
Table::TYPE_TEXT,
null,
['nullable' => false, 'default' => ''],
'Description'
)
->addColumn(
'created_at',
Table::TYPE_DATETIME,
null,
['nullable' => false],
'Created At'
)
->addColumn(
'status',
Table::TYPE_SMALLINT,
null,
['nullable' => false, 'default' => '0'],
'Status'
)
//Set comment for magetop_blog table
->setComment('Magetop Blog Table')
//Set option for magetop_blog table
->setOption('type', 'InnoDB')
->setOption('charset', 'utf8');
$installer->getConnection()->createTable($table);
}
$installer->endSetup();
}
}
Create file InstallData.php
Next, we will insert data for the magetop_blog table.
You create this file according to the path:Magetop/Helloworld/Setup/InstallData.php.
<?php
namespace Magetop\Helloworld\Setup;
use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
class InstallData implements InstallDataInterface
{
public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
{
$setup->startSetup();
$tableName = $setup->getTable('magetop_blog');
//Check for the existence of the table
if ($setup->getConnection()->isTableExists($tableName) == true) {
$data = [
[
'title' => 'How to Speed Up Magento 2 Website',
'description' => 'Speeding up your Magento 2 website is very important, it affects user experience. Customers will feel satisfied when your site responds quickly',
'created_at' => date('Y-m-d H:i:s'),
'status' => 1,
],
[
'title' => 'Optimize SEO for Magento Website',
'description' => 'One of the important reasons why many people choose Magento 2 for their website is the ability to create SEO friendly',
'created_at' => date('Y-m-d H:i:s'),
'status' => 1,
],
[
'title' => 'Top 10 eCommerce Websites',
'description' => 'These are the websites of famous e-commerce corporations in the world. With very large revenue contributing to the world economy',
'created_at' => date('Y-m-d H:i:s'),
'status' => 0,
],
];
foreach ($data as $item) {
//Insert data
$setup->getConnection()->insert($tableName, $item);
}
}
$setup->endSetup();
}
}
Increase the version in module.xml
If you have just created this module and you have not declared it on the database yet, you do not need to increase the version in module.xml
If you have already declared this module on the database, please increase the version in module.xml
Run the command
You need to run 2 commands:
php bin/magento setup:upgrade
php bin/magento setup:db-schema:upgrade
Check in the database
Please check on the database and You will see your accomplishment.
Upgrade table in the database
Next, I will guide you to add fields to the created table and create a new table in UpgradeSchema.php.
You create this file according to the path: Magetop/Helloworld/Setup/InstallData.php.
In the following code, I will implement two field image, category_id and create a new table magetop_blog_categories.
<?php
namespace Magetop\Helloworld\Setup;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
class UpgradeSchema implements UpgradeSchemaInterface
{
public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
{
$setup->startSetup();
//Add new fields to the created table
if (version_compare($context->getVersion(), '1.0.2') < 0) {
$table = $setup->getTable('magetop_blog');
//Check for the existence of the table
if ($setup->getConnection()->isTableExists($table) == true) {
// Declare data
$columns = [
'image' => [
'type' => Table::TYPE_TEXT,
['nullable' => true],
'comment' => 'Image',
],
'category_id' => [
'type' => Table::TYPE_INTEGER,
['nullable' => false, 'default' => 0],
'comment' => 'Category ID',
],
];
$connection = $setup->getConnection();
foreach ($columns as $name => $definition) {
$connection->addColumn($table, $name, $definition);
}
}
}
//Create a new table
if (version_compare($context->getVersion(), '1.0.2') < 0) {
$categories = $setup->getTable('magetop_blog_categories');
//Check for the existence of the table
if ($setup->getConnection()->isTableExists($categories) != true) {
$tableCategories = $setup->getConnection()
->newTable($categories)
->addColumn(
'cat_id',
Table::TYPE_INTEGER,
null,
['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
'Category Id'
)
->addColumn(
'status',
Table::TYPE_SMALLINT,
null,
['nullable' => false, 'default' => 1],
'Status'
)
->addColumn(
'cat_title',
Table::TYPE_TEXT,
null,
['nullable' => false, 'default' => ''],
'Category Title'
)
->addColumn(
'created_at',
Table::TYPE_TIMESTAMP,
null,
['nullable' => false],
'Created At'
)
//Set comment for magetop_blog table
->setComment('Magetop Blog Categories')
//Set option for magetop_blog table
->setOption('type', 'InnoDB')
->setOption('charset', 'utf8');
$setup->getConnection()->createTable($tableCategories);
}
}
$setup->endSetup();
}
}
We will insert data for the magetop_blog_categories table.
<?php
namespace Magetop\Helloworld\Setup;
use Magento\Framework\Setup\UpgradeDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
class UpgradeData implements UpgradeDataInterface
{
public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
{
$setup->startSetup();
if (version_compare($context->getVersion(), '1.0.2') < 0) {
$tableName = $setup->getTable('magetop_blog_categories');
//Check for the existence of the table
if ($setup->getConnection()->isTableExists($tableName) == true) {
$data = [
[
'cat_title' => 'News',
'status' => 1,
'created_at' => date('Y-m-d H:i:s'),
],
[
'cat_title' => 'Tutorials',
'status' => 0,
'created_at' => date('Y-m-d H:i:s'),
],
[
'cat_title' => 'Uncategorized',
'status' => 0,
'created_at' => date('Y-m-d H:i:s'),
]
];
foreach ($data as $item) {
//Insert data
$setup->getConnection()->insert($tableName, $item);
}
}
}
$setup->endSetup();
}
}
Do not forget to increase the version in module.xml!.
Finally, run the two commands above and enjoy the result.
I hope through this series you can create your own complete module. Good luck!
In addition to Magento 2: How To Create Database Table, you can read the articles How To Use Model In Magento 2.
Follow us for the more helpful article!
We hope this is a useful series for you.
Thank you for reading!
