Blog Page

How to Setup and Upgrade Database in Magento 2

In this article, we will find out how to install and upgrade sql script for module in Magento 2. When you install or upgrade a module, you may need to change the database structure or add some new data for current table. To do this, Magento 2 provide you some classes which you can do all of them. Table of content Magento 2 provides some classes in the directory app/code/[Namespace]/[ModuleName]/Setup to create or upgrade database.

• InstallSchema – setup database structure
• InstallData – initial the data for database table.
• UpgradeSchema – upgraded database structure
• UpgradeData – upgraded (add/remove) data from table.

Functions of these classes will be run when we install or upgrade module by this command line:

php bin/magento setup:upgrade

Elaborating each step,

1. Install Schema Create file: app/code/Cozmot/HelloWorld/Setup/InstallSchema.php

<?php
namespace Magestore\DataExample\Setup;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\InstallSchemaInterface;
class InstallSchema implements InstallSchemaInterface
{
    public function install(SchemaSetupInterface $setup,ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();
        $table = $installer->getConnection()->newTable($installer->getTable('data_example'))
            ->addColumn('example_id',\Magento\Framework\Db\Ddl\Table::TYPE_INTEGER,null,
                ['identity' => true, 'nullable' => false, 'primary' => true, 'unsigned' => true],
                'Example Id')
            ->addColumn('title',\Magento\Framework\Db\Ddl\Table::TYPE_TEXT,255,
                ['nullable' => false],
                'Example Title')
            ->addColumn('content',\Magento\Framework\Db\Ddl\Table::TYPE_TEXT,'2M',
                [],
                'Example Content')
            ->addColumn('created_at',\Magento\Framework\Db\Ddl\Table::TYPE_TIMESTAMP,null,
                ['nullable'=>false,'default'=>\Magento\Framework\Db\Ddl\Table::TIMESTAMP_INIT],
                'Created At');
        $installer->getConnection()->createTable($table);
        $installer->endSetup();
    }
}

In this file, we created a table with name “data_example” with four columns: example_id, title, content, created_at with data types are integer, varchar, text, timestamp You can review all data types in this file: \Magento\Framework\Db\Ddl\Table.php 2. Install Data File: app/code/Cozmot/HelloWorld/Setup/InstallData.php

<?php
namespace Meetanshi\HelloWorld\Setup;
use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
class InstallData implements InstallDataInterface
{
    protected $_exampleFactory;
    public function __construct(\Cozmot\HelloWorld\Model\ExampleFactory $exampleFactory)
    {
        $this->_exampleFactory = $exampleFactory;
    }
    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $data = ['title' => "First example title",'content' => "First Example content"];
        $example = $this->_exampleFactory->create();
        $example->addData($data)->save();
    }
}

Here,  we insert a row with value for “title” and “content” columns to data_example table. 3. Upgrade Schema File: app/code/ Cozmot/HelloWorld/Setup/UpgradeSchema.php

<?php
namespace Cozmot\HelloWorld\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()->dropColumn($installer->getTable('data_example'), 'created_at');
        }
        $installer->endSetup();
    }
}

Here, note that we have to check module version in upgrade function. In this example, we upgraded module from version 1.0.0 to 1.0.1. Drop created_at column from the data_example table. 4. Upgrade Data File: app/code/ Cozmot/HelloWorld/Setup/UpgradeData.php

<?php
namespace Cozmot\HelloWorld\Setup;
use Magento\Framework\Setup\UpgradeDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
class UpgradeData implements UpgradeDataInterface
{
    protected $_exampleFactory;
    public function __construct(\Cozmot\HelloWorld\Model\ExampleFactory $exampleFactory)
    {
        $this->_exampleFactory = $exampleFactory;
    }
    public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        if (version_compare($context->getVersion(), '1.0.1', '<')) 
        {
            $data = ['title' => "The second example title", 'content' => "The second example content"];
            $example = $this->_exampleFactory->create();
            $example > addData($dalogta)->save();
        }
    }
}