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:

magetop helloworld file struture

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

increase version

Run the command

You need to run 2 commands:

php bin/magento setup:upgrade
run setup upgrade
php bin/magento setup:db-schema:upgrade
run setup schema upgrade

Check in the database

Please check on the database and You will see your accomplishment.

check in database 1
check in database

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.

check magetop blog categories table
check new field in magetop blog table
check magetop blog categories in database

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!

3.9 7 votes
Article Rating

Aaron LX

Aaron is a passionate writer, crazy about shopping, eCommerce and trends. Besides his outstanding research skills and a positive mind, Aaron eagerly shares his experience with the readers.

Leave a Reply or put your Question here

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x