
How to Setup and Upgrade Database in Magento 2
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,
- 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(); } } } |
]]>