How to Setup and Upgrade Database in Magento 2

February 2, 2021
By Cozmot

Deprecated: pathinfo(): Passing null to parameter #1 ($path) of type string is deprecated in /var/www/html/wp-content/plugins/urvanov-syntax-highlighter/class-urvanov-syntax-highlighter-langs.php on line 86

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:

1
2
3
 
php bin/magento setup:upgrade
 

Elaborating each step,

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

1
2
3
 
<?php namespace MagestoreDataExampleSetup; use MagentoFrameworkSetupSchemaSetupInterface; use MagentoFrameworkSetupModuleContextInterface; use MagentoFrameworkSetupInstallSchemaInterface; 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',MagentoFrameworkDbDdlTable::TYPE_INTEGER,null, ['identity' => true, 'nullable' => false, 'primary' => true, 'unsigned' => true], 'Example Id') ->addColumn('title',MagentoFrameworkDbDdlTable::TYPE_TEXT,255, ['nullable' => false], 'Example Title') ->addColumn('content',MagentoFrameworkDbDdlTable::TYPE_TEXT,'2M', [], 'Example Content') ->addColumn('created_at',MagentoFrameworkDbDdlTable::TYPE_TIMESTAMP,null, ['nullable'=>false,'default'=>MagentoFrameworkDbDdlTable::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: MagentoFrameworkDbDdlTable.php

2. Install Data
File: app/code/Cozmot/HelloWorld/Setup/InstallData.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
<?php
namespace MeetanshiHelloWorldSetup;
use MagentoFrameworkSetupInstallDataInterface;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupModuleDataSetupInterface;
class InstallData implements InstallDataInterface
{
    protected $_exampleFactory;
    public function __construct(CozmotHelloWorldModelExampleFactory $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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
<?php
namespace CozmotHelloWorldSetup;
use MagentoFrameworkSetupUpgradeSchemaInterface;
use MagentoFrameworkSetupSchemaSetupInterface;
use MagentoFrameworkSetupModuleContextInterface;
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
<?php
namespace CozmotHelloWorldSetup;
use MagentoFrameworkSetupUpgradeDataInterface;
use MagentoFrameworkSetupModuleDataSetupInterface;
use MagentoFrameworkSetupModuleContextInterface;
class UpgradeData implements UpgradeDataInterface
{
    protected $_exampleFactory;
    public function __construct(CozmotHelloWorldModelExampleFactory $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();
        }
    }
}
 

]]>

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!