Magento 2: Create Database Table Using Install/Upgrade Script

If you are creating a new custom module in Magento 2, In some cases, you may need to create a new database table, modify existing database table, and you may want to set some initial default values in your database tables. You can achieve this by creating install/upgrade scripts in Magento 2.

Here is the list of install/upgrade SQL script

  • InstallSchema
  • InstallData
  • UpgradeSchema
  • UpgradeData
  • Recurring
  • Uninstall

All of these classes will be located at app/code/<Vendor>/<Module>/Setup folder.

Example file path: app/code/BlogTreat/CustomScript/Setup

The module’s install/upgrade scripts will run when you run the following command in your CLI:

php bin/magento setup:upgrade

InstallSchema:

The InstallSchema is used to change the database schema which means create a new database table or modify an existing database table structure. The InstallSchema class will be run during the module installation.

Example file path: app/code/BlogTreat/CustomScript/Setup/InstallSchema.php

<?php

namespace BlogTreat\CustomScript\Setup;

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();
        if (!$installer->tableExists('comments')) {
            $table = $installer->getConnection()
                     ->newTable($installer->getTable('comments'))
                     ->addColumn(
                         'comment_id',
                         \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                         null,
                         [
                             'identity' => true,
                             'nullable' => false,
                             'primary'  => true,
                             'unsigned' => true,
                         ],
                         'ID'
                     )
                     ->addColumn(
                         'name',
                         \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                         255,
                         ['nullable => false'],
                         'Name'
                     )
                     ->addColumn(
                         'email',
                         \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                         255,
                         [],
                         'Email'
                     )
                     ->addColumn(
                         'url_key',
                         \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                         255,
                         [],
                         'URL Key'
                     )
                     ->addColumn(
                         'message',
                         \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                         '18K',
                         [],
                         'Message'
                     )
                     ->addColumn(
                         'status',
                         \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                         1,
                         [],
                         'Comment Status'
                     )
                     ->addColumn(
                         'created_at',
                         \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                         null,
                         ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT],
                         'Created At'
                     )->addColumn(
                         'updated_at',
                         \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                         null,
                         ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT_UPDATE],
                         'Updated At')
                     ->setComment('Comments Table');
            $installer->getConnection()->createTable($table);

            $installer->getConnection()->addIndex(
                $installer->getTable('comments'),
                $setup->getIdxName(
                    $installer->getTable('comments'),
                    ['name', 'email', 'url_key', 'message'],
                    \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
                ),
                ['name', 'email', 'url_key', 'message'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
            );
        }
        $installer->endSetup();
    }
}

The InstallSchema setup class file must extend \Magento\Framework\Setup\InstallSchemaInterface and must have one public method install() with 2 arguments SchemaSetupInterface and ModuleContextInterface.

  • SchemaSetupInterface: It is the setup object which provides a mechanism to interact with the database server.
  • ModuleContextInterface: It has the method called getVersion() which is used to get the current version of your module.

InstallData:

The InstallData is used to add data to the database table. The InstallData class will also be run during the module installation. But it will be run after the InstallSchema class.

Example file path: app/code/BlogTreat/CustomScript/Setup/InstallData.php

<?php

namespace BlogTreat\CustomScript\Setup;

use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class InstallData implements InstallDataInterface
{
    protected $_commentFactory;

    public function __construct(\BlogTreat\CustomScript\Model\CommentFactory $commentFactory)
    {
        $this->_commentFactory = $commentFactory;
    }

    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $data = [
            'name' => 'Thomus Raj',
            'email' => 'roni_cost@xample.com',
            'url_key' => 'http://www.blogtreat.com',
            'message' => 'My first comment',
            'status' => 1
        ];
        $post = $this->_commentFactory->create();
        $post->addData($data)->save();
    }
}

UpgradeSchema:

The UpgradeSchema is used to create or modify database tables of our existing module. The main difference between install scripts and upgrade script is, the install script will be run during module’s first installation. But the upgrade scripts will run every time of the module upgrade.

Example file path: app/code/BlogTreat/CustomScript/Setup/UpgradeSchema.php

<?php

namespace BlogTreat\CustomScript\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class UpgradeSchema implements UpgradeSchemaInterface
{
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) {
        $installer = $setup;

        $installer->startSetup();

        if(version_compare($context->getVersion(), '1.0.1', '<')) {
            $installer->getConnection()->addColumn(
                $installer->getTable( 'comments' ),
                'user_image',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'nullable' => true,
                    'length' => '254',
                    'comment' => 'Image',
                    'after' => 'url_key'
                ]
            );
        }

        $installer->endSetup();
    }
}

The UpgradeSchema setup class file must extend \Magento\Framework\Setup\UpgradeSchemaInterface and must have one public method upgrade() with 2 arguments SchemaSetupInterface and ModuleContextInterface.

UpgradeData:

The UpgradeData is used to add/remove data from the database table. This is same with the UpgradeSchema class.

Example file path: app/code/BlogTreat/CustomScript/Setup/UpgradeData.php

<?php

namespace BlogTreat\CustomScript\Setup;

use Magento\Framework\Setup\UpgradeDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class UpgradeData implements UpgradeDataInterface
{

    protected $_commentFactory;

    public function __construct(\BlogTreat\CustomScript\Model\CommentFactory $commentFactory)
    {
        $this->_commentFactory = $commentFactory;
    }

    public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            $data = [
                'name' => 'Thomus Raj',
                'email' => 'roni_cost@xample.com',
                'url_key' => 'http://www.blogtreat.com',
                'message' => 'My first comment',
                'status' => 1
            ];
            $post = $this->_commentFactory->create();
            $post->addData($data)->save();
        }
    }
}

Uninstall:

Example file path: app/code/BlogTreat/CustomScript/Setup/Uninstall.php

<?php

namespace BlogTreat\CustomScript\Setup;

use Magento\Framework\Setup\UninstallInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class Uninstall implements UninstallInterface
{
    public function uninstall(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();

        $installer->getConnection()->dropTable($installer->getTable('comments'));

        $installer->endSetup();
    }
}

Hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *