1

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?

2

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();
    }
}
  • Thanks. Your code is easier to understand. +1 for that – Muhammad Anas Apr 19 at 5:57
  • Worked for me as well. +1 for that – Shoaib Munir Apr 24 at 12:54
1

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.

  • You code is good enough to apply, but other answer is more understandable. +1 for your effort and help. Thanks mate :) – Muhammad Anas Apr 19 at 5:57
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.