How to Create SQL Setup Script 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.

InstallSchema - this class will run when the module is installed to setup the database structure
InstallData - this class will run when the module is installed to initial the data for database table..
UpgradeSchema - this class will run when the module is upgraded to setup the database structure
UpgradeData - this class will run when the module is upgraded to add/remove data from table.
Recurring
Uninstall
All of the class will be 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
In this article, we will use the sample module Mageplaza_HelloWorld to create some demo table and data.

InstallSchema / InstallData

The InstallSchema and InstallData classes will be run during the module install.

The InstallSchema setup script in magento 2 will be use to change the database schema (create or change database table). This’s the setup script to create the mageplaza_blog table:

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

<?php
namespace Mageplaza\HelloWorld\Setup;
class InstallSchema implements \Magento\Framework\Setup\InstallSchemaInterface
{
public function install(
\Magento\Framework\Setup\SchemaSetupInterface $setup,
\Magento\Framework\Setup\ModuleContextInterface $context
){
$installer = $setup;
$installer->startSetup();

$table = $installer->getConnection()
->newTable($installer->getTable('mageplaza_blog'))
->addColumn(
'blog_id',
\Magento\Framework\Db\Ddl\Table::TYPE_INTEGER,
null,
['identity' => true, 'nullable' => false, 'primary' => true, 'unsigned' => true],
'Blog Id'
)->addColumn(
'title',
\Magento\Framework\Db\Ddl\Table::TYPE_TEXT,
255,
['nullable' => false],
'Blog Title'
)->addColumn(
'content',
\Magento\Framework\Db\Ddl\Table::TYPE_TEXT,
'2M',
[],
'Blog Content'
)->addColumn(
'creation_time',
\Magento\Framework\Db\Ddl\Table::TYPE_TIMESTAMP,
null,
['nullable' => false, 'default' => \Magento\Framework\Db\Ddl\Table::TIMESTAMP_INIT],
'Blog Creation Time'
)->setComment(
'Mageplaza Blog Table'
);
$installer->getConnection()->createTable($table);
$installer->endSetup();
}
}
Looking into this file we will see:

The class must extend \Magento\Framework\Setup\InstallSchemaInterface

The class must have install() method with 2 arguments SchemaSetupInterface and ModuleContextInterface. The SchemaSetupInterface is the setup object which provide many function to interact with database server. The ModuleContextInterface has only 1 method getVersion() which will return the current version of your module.

In the example above, we create a table named mageplaza_blog with 4 columns: blog_id, title, content and creation_time.

InstallData will be run after the InstallSchema class to add data to the database table.

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

<?php
namespace Mageplaza\HelloWorld\Setup;

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

class InstallData implements InstallDataInterface
{
protected $_blogFactory;

public function __construct(\Mageplaza\HelloWorld\Model\BlogFactor y $blogFactory)
{
$this->_blogFactory = $blogFactory;
}

public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
{
$data = [
'title' => "Sample title 1",
'content' => "Sample content 1"
];
$blog = $this->_blogFactory->create();
$blog->addData($data)->save();
}
}

This class will have the same concept as InstallSchema.

UpgradeSchema/UpgradeData

The both of this files will run when the module is installed or upgraded. This classes is difference with the Install classes because they will run every time the module upgrade. So we will need to check the version and separate the script by each version.

Upgrade Schema:

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

<?php
namespace Mageplaza\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( 'mageplaza_blog' ),
'creation_time'
);
}

$installer->endSetup();
}
}

In this class, we use the upgrade() method which will be run every time the module is upgraded. We also have to compare the version to add the script for each version.

Upgrade Data:

This will same with the UpgradeSchema class

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

<?php
namespace Mageplaza\HelloWorld\Setup;

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

class UpgradeData implements UpgradeDataInterface
{
protected $_blogFactory;

public function __construct(\Mageplaza\HelloWorld\Model\BlogFactor y $blogFactory)
{
$this->_blogFactory = $blogFactory;
}

public function upgrade( ModuleDataSetupInterface $setup, ModuleContextInterface $context ) {
if ( version_compare($context->getVersion(), '1.0.1', '<' )) {
$data = [
'title' => "Sample title 2",
'content' => "Sample content 2"
];
$blog = $this->_blogFactory->create();
$blog->addData($data)->save();
}
}
}

Recurring

The recurring script is a script which 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. This’s the example for this class:

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

<?php
namespace Mageplaza\Example\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('mageplaza_blog'));

$installer->endSetup();
}
}