Create a table using InstallSchema and UpgradeSchema in magento 2

In Magento 2 how to create a table for your custom module? or we can say how to use InstallSchema and UpgradeSchema in Magento 2?

InstallSchema and UpgradeSchema in Magento 2

Yes, It’s simple and we are going to see that in this post. So Let’s say you have your custom module ready. If you don’t have click here to see how to create a simple custom module. For example, we have a custom module Vky_Test which we have created before.

To create a table for your custom module you will need to create only one file named InstallSchema.php. We will create this file in a folder named Setup.

Create Vky/Test/Setup/InstallSchema.php

<?php

namespace Vky\Test\Setup;

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

/**
 * @codeCoverageIgnore
 */
class InstallSchema implements InstallSchemaInterface
{
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
		$installer = $setup;
		$installer->startSetup();

		/**
		 * Creating table vky_test
		 */
		$table = $installer->getConnection()->newTable(
			$installer->getTable('vky_test')
		)->addColumn(
			'test_id',
			\Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
			null,
			['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
			'Entity Id'
		)->addColumn(
			'title',
			\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
			255,
			['nullable' => true],
			'Title'
		)->addColumn(
			'author',
			\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
			255,
			['nullable' => true,'default' => null],
			'Author'
		)->addColumn(
			'content',
			\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
			'2M',
			['nullable' => true,'default' => null],
			'Content'
		)->addColumn(
			'created_at',
			\Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
			null,
			['nullable' => false],
			'Created At'
		)->setComment(
            'Vky Test Table'
        );
		$installer->getConnection()->createTable($table);
		$installer->endSetup();
	}
}

Here we are creating a table named vky_test including 5 fields.

Now what you need to do is open the SSH terminal go to the root directory of your project and just run the below command.

php bin/magento setup:upgrade

However, It did not create my table.
Why? Because The installer won’t run again if it was already executed.

So we need to remove the record for our module from setup_module table from our database. Open your database search for the table setup_module and find the record for your module or you can run below query to find your module record entry in the table

SELECT * FROM `setup_module` WHERE module LIKE "%Vky_Test%"

When you find it delete that entry and then run the setup upgrade command. After that you can check the database you should have the table named vky_test

Forgot a field to add to the table?

Of course, it happens most of the time. Or you did not forget but now you need one more field. No problem. At this point comes the use of the UpgradeSchema.php

Create Vky/Test/Setup/UpgradeSchema.php

<?php

namespace Vky\Test\Setup;

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

/**
 * @codeCoverageIgnore
 */
class UpgradeSchema implements UpgradeSchemaInterface
{
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function upgrade(
        SchemaSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        $installer = $setup;

        $installer->startSetup();
        
        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            $installer->getConnection()->addColumn(
                $installer->getTable('vky_test'),
                'using_upgradeschema',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'length' => 255,
                    'nullable' => true,
                    'comment' => 'Using UpgradeSchema'
                ]
            );
        }

        $installer->endSetup();
    }
}

We are adding a new field named using_upgradeschema.

Open Vky/Test/etc/module.xml
Just change the setup_version="1.0.0" to setup_version="1.0.1". And run the setup upgrade command. You will get your new field in your table.

Delete a field from the table?

Yes, sometimes we realize that we don’t need this field now. So why keep it there? Again you can use UpgradeSchema.php. You can place a code to delete the column also in this file. Let’s delete our extra added field using_upgradeschema

Now to delete the unwanted field from the table our edited UpgradeSchema.php file will be like below

<?php

namespace Vky\Test\Setup;

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

/**
 * @codeCoverageIgnore
 */
class UpgradeSchema implements UpgradeSchemaInterface
{
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function upgrade(
        SchemaSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        $installer = $setup;

        $installer->startSetup();

        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            $installer->getConnection()->addColumn(
                $installer->getTable('vky_test'),
                'using_upgradeschema',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'length' => 255,
                    'nullable' => true,
                    'comment' => 'Using UpgradeSchema'
                ]
            );
        }

        if (version_compare($context->getVersion(), '1.0.2', '<')) {
            $installer->getConnection()->dropColumn(
                $installer->getTable('vky_test'),
                'using_upgradeschema'
                );
        }
        $installer->endSetup();
    }
}

Again Open Vky/Test/etc/module.xml
Just change the setup_version="1.0.0" to setup_version="1.0.2". And run the setup upgrade command. Your unwanted field should be deleted now.

That’s how you use InstallSchema and UpgradeSchema to create a table for the custom module.

Next: Insert data into the custom table using Model, Resource Model in Magento 2
(Check the post)