Csdrhrt

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?

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 YourModuleSetup;



use MagentoFrameworkSetupModuleContextInterface;

use MagentoFrameworkSetupSchemaSetupInterface;

use MagentoFrameworkSetupUpgradeSchemaInterface;



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' => MagentoFrameworkDBDdlTable::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();

    }

}

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 VendorEcommerceSetup;

use MagentoFrameworkSetupUpgradeSchemaInterface;

use MagentoFrameworkSetupModuleContextInterface;

use MagentoFrameworkSetupSchemaSetupInterface;

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' => MagentoFrameworkDBDdlTable::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.

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