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.

Overview of Install Upgrade SQL script

  • 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_post table:

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

<?php
namespace Mageplaza\HelloWorld\Setup;

class InstallSchema implements \Magento\Framework\Setup\InstallSchemaInterface
{
    /**
     * install tables
     *
     * @param \Magento\Framework\Setup\SchemaSetupInterface $setup
     * @param \Magento\Framework\Setup\ModuleContextInterface $context
     * @return void
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function install(\Magento\Framework\Setup\SchemaSetupInterface $setup, \Magento\Framework\Setup\ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();
        if (!$installer->tableExists('mageplaza_helloworld_post')) {
            $table = $installer->getConnection()->newTable(
                $installer->getTable('mageplaza_helloworld_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(
                'url_key',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                255,
                [],
                'Post URL Key'
            )
            ->addColumn(
                'post_content',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                '64k',
                [],
                'Post Post Content'
            )
            ->addColumn(
                'tags',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                255,
                [],
                'Post Tags'
            )
            ->addColumn(
                'status',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                1,
                [],
                'Post Status'
            )
            ->addColumn(
                'featured_image',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                255,
                [],
                'Post Featured Image'
            )
            ->addColumn(
                'sample_country_selection',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                3,
                [],
                'Post Sample Country Selection'
            )
            ->addColumn(
                'sample_upload_file',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                255,
                [],
                'Post Sample File'
            )
            ->addColumn(
                'sample_multiselect',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                '64k',
                [],
                'Post Sample Multiselect'
            )

            ->addColumn(
                'created_at',
                \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                null,
                [],
                'Post Created At'
            )
            ->addColumn(
                'updated_at',
                \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                null,
                [],
                'Post Updated At'
            )
            ->setComment('Post Table');
            $installer->getConnection()->createTable($table);

            $installer->getConnection()->addIndex(
                $installer->getTable('mageplaza_helloworld_post'),
                $setup->getIdxName(
                    $installer->getTable('mageplaza_helloworld_post'),
                    ['name','url_key','post_content','tags','featured_image','sample_upload_file'],
                    \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
                ),
                ['name','url_key','post_content','tags','featured_image','sample_upload_file'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
            );
        }
        $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_post with columns: post_id, name, post_content, created_at ....

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 $_postFactory;

    public function __construct(\Mageplaza\HelloWorld\Model\BlogFactory $postFactory)
    {
        $this->_postFactory = $postFactory;
    }

    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $data = [
            'name' => "Sample title 1",
            'post_content' => "Sample content by Mageplaza.com"
        ];
        $post = $this->_postFactory->create();
        $post->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_post' ),
				'created_at'
			);
		}

		$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 $_postFactory;

	public function __construct(\Mageplaza\HelloWorld\Model\BlogFactory $postFactory)
	{
		$this->_postFactory = $postFactory;
	}

	public function upgrade( ModuleDataSetupInterface $setup, ModuleContextInterface $context ) {
		if ( version_compare($context->getVersion(), '1.0.1', '<' )) {
			$data = [
				'name' => "Sample title 2",
				'post_content' => "Sample content 2"
			];
			$post = $this->_postFactory->create();
			$post->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_post'));

		$installer->endSetup();
	}
}




Next tutorial:



Enjoyed the tutorial? Spread it to your friends!

magento-2-module-development
hello-world
admin
acl
access
control
lists

Comments for How to Create SQL Setup Script in Magento 2

Please leave comments if you have any questions, feedbacks.

You also may like these Magento 2 Extensions

One Step Checkout

$199
10 reviews

Layered Navigation

$149
12 reviews

Store Locator

$199
3 reviews

Shop By Brand

$99
3 reviews

Zoho CRM

$249
3 reviews

FAQ

$149
10 reviews

Better Blog

FREE
8 reviews

Affiliate

$149
3 reviews

People also searched for:

  • Setup script in Magento 2