Update module to run alter command

Question

I have created a module Vendor/Module I have added sql setup script in it. The module is working fine.

Now I need to alter table and add come more columns in the table. To do this I need to add new sql setup script and Update my module version.

Can anyone help me out on this? How can I do this?

Answer 1

First Update your moudule.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
    <module name="Your_Module" setup_version="1.0.1" schema_version="1.0.2">
    </module>
</config>

You can use UpgradeSchema.php
Create UpgradeSchema.php in Setup/UpgradeSchema.php

<?php

namespace Your\Module\Setup;

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

class UpgradeSchema implements UpgradeSchemaInterface
{
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;

        $installer->startSetup();

        if(version_compare($context->getVersion(), '1.0.1', '<')) {
        $table = $installer->getTable('your_table');
        //Your alteration logic
        $columns = [
                        'your_column' => 
                        [
                            'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                            'nullable'  => false,
                            'length' => 512,
                            'default'   => null,
                            'comment' => 'You Column Comment',
                        ],
               ];

               $connection = $installer->getConnection();

               foreach ($columns as $name => $definition) {
                     $connection->addColumn($table, $name, $definition);
               }
        }
        $installer->endSetup();
    }
}

Answer 2

Create file name called UpgradeSchema.php from your module Setup folders Ex: app/code/Vendor/Ecommerce/Setup/UpgradeSchema.php
Here we are going to create column called vendor_isallowstatus
Column name called : vendor_isallowstatus
Table name : vendor_ecommerce
adde below code into your file.

Ex:

if (version_compare($context->getVersion(), ‘2.0.5’) < 0) { //My new upgrade file version is 2.0.5 incase if 2.0.6 then set 2.0.6
}

Step:1

<?php

namespace Vendor\Ecommerce\Setup;
use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
class UpgradeSchema implements UpgradeSchemaInterface{
 public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context){
 if (version_compare($context->getVersion(), '2.0.1') < 0) {
 $setup->startSetup();
 $setup->getConnection()->addColumn(
 $setup->getTable('vendor_ecommerce'),
 'vendor_isallowstatus',
 ['type' => \Magento\Framework\DB\Ddl\Table::TYPE_INT,
 'length' => '11',
 'nullable' => false,
 'default' => '0',
 'comment' => 'Vendor Is Allow Status']);
 $setup->endSetup();
 } } }

Step:2 app/code/Vendor/Ecommerce/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="Vendor_Ecommerce" setup_version="2.0.5">
 <sequence><module name="Vendor_Ecommerce"/></sequence>
 </module></config>

Step:3
After saving files, you need to run php bin/magento setup:upgrade. Now check your database and you will be able to find a new custom table and new column in 'vendor_ecommerce' table.

Answer 3

If you're using Magento >= 2.3 it could be much better to use declarative schema for db : https://devdocs.magento.com/guides/v2.3/extension-dev-guide/declarative-schema/db-schema.html