How to add New Column in Magento 2 beginning of the table using Magento 2 way?

Question

I have to add a New column in Beginning of Existing Table on Magento 2 way.

Answer 1

Finally, I have found the solution we can add this way

$installer->getConnection()->addColumn(
               $installer->getTable($table),
               'id',
               'INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`)'
           );

Answer 2

Create file name called UpgradeSchema.php from your module Setup folders

app/code/Vendor/Module/Setup/UpgradeSchema.php

Here we are going to create column called isallowstatus

Column name called : isallowstatus

Table name : vendor_moduel

adde below code into your file.

Step:1

<?php
/**
 * @package Vendor_Module
 */
namespace Vendor\Module\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_moduel'),
 'isallowstatus',
 ['type' => \Magento\Framework\DB\Ddl\Table::TYPE_INT,
 'length' => '11',
 'nullable' => false,
 'default' => '0',
 'comment' => 'Allow Status']);
 $setup->endSetup();
 } } }

Step:2

app/code/Vendor/Module/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_Module" setup_version="2.0.5">
 <sequence><module name="Vendor_Module"/></sequence>
 </module></config>

Step:3 Run upgrade setup in putty or terminal

php bin/magento setup:upgrade

Step:4 Clear cache and check the table

php bin/magento cache:flush

Answer 3

You can use UpgradeSchema to add new column.

app/code/Stack/Custom/Setup/UpgradeSchema.php

namespace Stack\Custom\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) {
        $setup->startSetup();

        $tableName = $setup->getTable('custom_table_name');
        $connection = $setup->getConnection();
        $connection->addColumn(
                $tableName,
                'new_column_name',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'default' => null,
                    'comment' => 'COMMENT',
                    'after' => 'first_column_name'
                ]
            );


        $setup->endSetup();
    }    
}

On the above code, Update table name, column name and after option to mention first column name.

Answer 4

You can add a column like this Example Table: quote_item

namespace [Name]\[Modulename]\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
class InstallSchema implements InstallSchemaInterface
{

/**
 * {@inheritdoc}
 * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
 */
public function install(SchemaSetupInterface $setup, ModuleContextInterface    $context)
  {
    $installer = $setup;

    $installer->startSetup();

    $eavTable = $installer->getTable('quote_item');

    $columns = [
        'customname' => [
            'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            'nullable' => false,
            'comment' => 'customname',
        ],

    ];

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

    $installer->endSetup();
}
}

Using this code you can add column in quote_item table