SQL Setup Script Magento 2

Some time its necessary to create DB tables and add data to tables. Mageto provides some classes using which you can create DB table structure or add data to tables. Following are the important classes

InstallSchema – This class can be used to setup DB structure when modules is instaled for the first time.
InstallData – This class can be used to load the initial data when modules is insttale for the first time.
UpgradeSchema – This class can be used to setup DB structure when modules is upgraded.
UpgradeData – This class can be used to load the initial data when modules is upgraded.
Recurring – This class can be used to run some script that need to executed whenever we issue php bin/magento setup:upgrade irrespective of version change.
Uninstall – This class can be used to remove the tables when the module is uintsalled.

All these classes are located at app/code/Vendor/Module/Setup folder. The module install/upgrade script will run when you run the following command line:

php bin/magento setup:upgrade
InstallSchema

The InstallSchema will be run during the module install and its used to cretae DB table structure.

File : app/code/PHPCodez/First/Setup/InstallSchema .php

<?php
 namespace PHPCodez\First\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
 use Magento\Framework\Setup\SchemaSetupInterface;
 use Magento\Framework\Setup\ModuleContextInterface;

class File : app/code/PHPCodez/First/Setup/InstallSchema .php implements InstallSchemaInterface
 {
 public function install( SchemaSetupInterface $setup, ModuleContextInterface $context ) {
 $installer = $setup;

$installer->startSetup();

if(version_compare($context->getVersion(), '1.1.0', '<')) {
 if (!$installer->tableExists('phpcodez_post')) {
 $table = $installer->getConnection()->newTable(
 $installer->getTable('phpcodez_post')
 )
 ->addColumn(
 'post_id',
 \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
 null,
 [
 'identity' => true,
 'nullable' => false,
 'primary' => true,
 'unsigned' => true,
 ],
 'Post ID'
 )
 ->addColumn(
 'name',
 \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
 255,
 ['nullable => false'],
 'Post Name'
 )
 ->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('Post Table');
 $installer->getConnection()->createTable($table);

$installer->getConnection()->addIndex(
 $installer->getTable('phpcodez_post'),
 $setup->getIdxName(
 $installer->getTable('phpcodez_post'),
 ['name'],
 \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
 ),
 ['name'],
 \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
 );
 }
 }

$installer->endSetup();
 }
 }
InstallData

The InstallData will be run during the module install and its used to load initial data to DB table.

File : app/code/PHPCodez/First/Setup/InstallData .php

<?php

namespace PHPCodez\First\Setup;

use Magento\Framework\Setup\InstallDataInterface;
 use Magento\Framework\Setup\ModuleContextInterface;
 use Magento\Framework\Setup\ModuleDataSetupInterface;

class InstallData implements InstallDataInterface
 {
 public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
 {
 $setup->startSetup();

if (version_compare($context->getVersion(), '0.0.3', '<=')) {
 $setup->getConnection()->query("INSERT INTO phpcodez_post SET name = 'Post 1'");
 }

$setup->endSetup();
 }
 }
UpgradeSchema

The UpgradeSchema will be run during module upgrade and its used to alter the table structure.

We will need to check the attribute setup_version in module.xml at app/code/PHPCodez/v/etc/ and separate the script by each version.

In this example, we will change the attrubute setup_version to higher level.

File: app/code/PHPCodez/First/etc/module.xml

<?xml version="1.0"?>
 <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
 <module name="PHPCodez_First" setup_version="0.0.2">
 </module>
 </config>

File : app/code/PHPCodez/First/Setup/UpgradeSchema .php

<?php
 namespace PHPCodez\First\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(), '0.0.2', '<')) {
 if (!$installer->tableExists('phpcodez_post')) {
 $table = $installer->getConnection()->newTable(
 $installer->getTable('phpcodez_post')
 )
 ->addColumn(
 'post_id',
 \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
 null,
 [
 'identity' => true,
 'nullable' => false,
 'primary' => true,
 'unsigned' => true,
 ],
 'Post ID'
 )
 ->addColumn(
 'name',
 \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
 255,
 ['nullable => false'],
 'Post Name'
 )
 ->addColumn(
 'created_at',
 \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
 null,
 ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT],
 'Created At'
 )
 ->setComment('Post Table');
 $installer->getConnection()->createTable($table);

$installer->getConnection()->addIndex(
 $installer->getTable('phpcodez_post'),
 $setup->getIdxName(
 $installer->getTable('phpcodez_post'),
 ['name'],
 \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
 ),
 ['name'],
 \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
 );
 }
 }

$installer->endSetup();
 }
 }
UpgradeData

The UpgradeData will be run during module upgrade and its used to add/remove table data.

We will need to check the attribute setup_version in module.xml at app/code/PHPCodez/v/etc/ and separate the script by each version.

In this example, we will change the attrubute setup_version to higher level.

File: app/code/PHPCodez/First/etc/module.xml

<?xml version="1.0"?>
 <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
 <module name="PHPCodez_First" setup_version="0.0.2">
 </module>
 </config>

File : app/code/PHPCodez/First/Setup/UpgradeData.php

<?php

namespace PHPCodez\First\Setup;

use Magento\Framework\Setup\UpgradeDataInterface;
 use Magento\Framework\Setup\ModuleContextInterface;
 use Magento\Framework\Setup\ModuleDataSetupInterface;

class UpgradeData implements UpgradeDataInterface
 {
 public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
 {
 $setup->startSetup();

if (version_compare($context->getVersion(), '0.0.4', '<=')) {
 $setup->getConnection()->query("INSERT INTO phpcodez_post SET name = 'Post 1'");
 }

$setup->endSetup();
 }
 }
Recurring

The recurring script will be run after the module setup script every time the command line php bin/magento setup:upgrade run.

This script will be defined same as InstallSchema class but difference in name of the class. The example for this class you can see in vendor/magento/module-indexer/Setup/Recurring.php

Uninstall

Magento 2 provide us the uninstall module feature which will remove all of the table, data like it hadn’t installed yet.

File: app/code/Mageplaza/HelloWorld/Setup/Uninstall.php

<?php

namespace PHPCodez\First\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('phpcodez_post'));

$installer->endSetup();
 }
 }
Share

Leave a Reply

Your email address will not be published. Required fields are marked *