Mrthdrb

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