How to Create and Upgrade Database for extension in Magento 2

You must follow these steps to create and upgrade for extension:

• 1. Install Schema
• 2. Install Data
• 3. Upgrade Schema
• 4. Upgrade Schema

1. Install Schema

In step, you can setup database structure

app/code/[Your_NameSpace]/[ModuleName]/Setup/InstallSchema.php

			
<?php
namespace [Your_NameSpace]\[ModuleName]\Setup;

use Magento\Framework\Db\Ddl\Table;

class InstallSchema implements \Magento\Framework\Setup\InstallSchemaInterface{

	public function install(
		\Magento\Framework\Setup\SchemaSetupInterface $setup,
		\Magento\Framework\Setup\ModuleContextInterface $context
	){
		$installer = $setup;
		$installer->startSetup();

		$table = $installer->getConnection()
					->newTable($installer->getTable('your_table_name'))
						->addColumn('id',Table::TYPE_INTEGER,null,['identity' => true, 'nullable' => false, 'primary' => true, 'unsigned' => true],'Id')
						->addColumn('title',Table::TYPE_TEXT,255,['nullable' => false],'Title')						
						->addColumn('created_at',Table::TYPE_TIMESTAMP,null,['nullable' => false, 'default' => Table::TIMESTAMP_INIT],'Created At');
						
		$installer
			->getConnection()
			->createTable($table);
			
		$installer->endSetup();
	}
}
?>
			
		

2. Install Data

Next, We can install data.

app/code/[Your_NameSpace]/[ModuleName]/Setup/InstallData.php

			
<?php 
namespace [Your_NameSpace]\[ModuleName]\Setup;

use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;

class InstallData implements InstallDataInterface{
	public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context){
	
		$setup->startSetup();
		
        $setup->getConnection()->insertForce(
            $setup->getTable('your_table_name'),
            ['title' => 'Title 1']
        );
        $setup->getConnection()->insertForce(
            $setup->getTable('your_table_name'),
            ['title' => 'Title 2']
        );
        $setup->getConnection()->insertForce(
            $setup->getTable('your_table_name'),
            ['title' => 'Title 3']
        );
        $setup->getConnection()->insertForce(
            $setup->getTable('your_table_name'),
            ['title' => 'Title 4']
        );
		
		$setup->endSetup();

	}
}

?>
			
		

3. Upgrade Schema

app/code/[Your_NameSpace]/[ModuleName]/Setup/UpgradeSchema.php

			
<?php 
namespace [Your_NameSpace]\[ModuleName]\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();
		 
		if (version_compare($context->getVersion(), '1.0.1', '<')) {
			$installer = $setup;
            $installer->getConnection()->dropColumn($installer->getTable('your_table_name'),'created_at');
        }
		
		$setup->endSetup();
		
	}
}

?>
			
		

4. Upgrade Data

app/code/[Your_NameSpace]/[ModuleName]/Setup/UpgradeData.php

			
<?php 
namespace [Your_NameSpace]\[ModuleName]\Setup;

use Magento\Framework\Setup\UpgradeDataInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;

class UpgradeSchema implements UpgradeSchemaInterface{
	public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context){
		$setup->startSetup();
		if (version_compare($context->getVersion(), '1.0.1', '<')) {
			$installer = $setup;
            
			$setup->getConnection()->update(
				$setup->getTable('your_table_name'),
				['title' => 'Upgrade title'],
				['id = ?' => 1]
			);
        }
		$setup->endSetup();
	}
}

?>

			
		

Please use command line for apply: php bin/magento setup:upgrade