Magento 2: Create Database Table Using Install/Upgrade Script

While creating a new custom module in Magento 2, In some cases, you may need to create a new database table, modify existing database table (i.e, add/remove existing table’s fields), and you may need 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 in Magento 2,

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

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

Sample 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 (i.e, add/remove existing table’s fields). The InstallSchema class will be run during the module installation.

Sample 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.

Sample 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. So it’s very important to compare your module’s version while using UpgradeSchema in your module like the below example code.

Sample 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.

Sample 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:

Sample 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 *