How to Create and Upgrade Database in Magento 2

Managing database is essential for any platform and Magento 2 E-commerce store is no exception! One may want to add a new database or update the existing ones. Add new rows or columns or delete particular data from the database and manage the database easily in Magento 2.

I have come up with a process to create and upgrade database in Magento 2 the easy way. Hope it is helpful.

Steps to create a database and upgrade in Magento 2:

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:

Elaborating each step,

1. Install Schema
   Create file: app/code/Meetanshi/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(); } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

   <?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/Meetanshi/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(\Meetanshi\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(); } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

   <?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(\Meetanshi\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/ Meetanshi/HelloWorld/Setup/UpgradeSchema.php
   
   <?php namespace Meetanshi\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(); } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   <?php namespace Meetanshi\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/ Meetanshi/HelloWorld/Setup/UpgradeData.php
   
   <?php namespace Meetanshi\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(\Meetanshi\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(); } } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   <?php namespace Meetanshi\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(\Meetanshi\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();         }     } }

Play with your database and control it easily in your Magento 2 store:) You may also love to read our blog post on How to Change Database Name in Magento 2.

Let me know if you have any doubts in the Comments below and I’ll help you out to solve it.

Rate my article with 5 stars if you find it useful.

Thank you.