How to create a new database table using InstallSchema in Adobe Commerce (Magento 2).

Here we will learn, How to create a database table by InstallSchema in Adobe Commerce (Magento 2). In Adobe Commerce (Magento) 2.3.x and later versions, Adobe Commerce (Magento 2) also provided the functionality to create a new table using Declarative Schema.

To know more about Declarative Schema, you can check our another article for Create Tables in Adobe Commerce (Magento 2).

We start by defining the app/code/Webkul/CreateTable/Setup/InstallSchema.php

<?php
namespace Webkul\CreateTable\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Ddl\Table;

class InstallSchema implements InstallSchemaInterface
{
    /**
     * Installs DB schema for a module
     *
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     * @return void
     */
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;

        $installer->startSetup();

        $table = $installer->getConnection()
            ->newTable($installer->getTable('my_database_table'))
            ->addColumn(
                'entity_id',
                Table::TYPE_INTEGER,
                null,
                ['identity' => true, 'nullable' => false, 'primary' => true],
                'ID'
            )
            ->addColumn(
                'user_name',
                Table::TYPE_TEXT,
                null,
                ['nullable' => false, 'default' => ''],
                'User Name'
            )
            ->addColumn(
                'email',
                Table::TYPE_TEXT,
                null,
                ['nullable' => false, 'default' => ''],
                'User Email'
            )
            ->addColumn(
                'value',
                Table::TYPE_DECIMAL,
                '12,4',
                [],
                'Value'
            )
            ->setComment('About Your Table');
        $installer->getConnection()->createTable($table);

        $installer->endSetup();
    }
}

The install method is all that is required in above code. Within this method, we would add any relevant code we might have to create the tables and columns we need.

The addColumn method is the used to create column. It takes five parameters,
from column name, data type, column length, array of additional options,
and description.

Accepted column data types are:
TYPE_BOOLEAN, TYPE_SMALLINT, TYPE_INTEGER, TYPE_BIGINT, TYPE_FLOAT, TYPE_NUMERIC, TYPE_DECIMAL, TYPE_DATE, TYPE_TIMESTAMP, TYPE_DATETIME, TYPE_TEXT, TYPE_BLOB, TYPE_VARBINARY

Note: The module’s module.xml file must have setup_version to create database table using InstallSchema.

You can check our another blog for Create Hello World Module in Adobe Commerce (Magento 2) step by step.

Now Open the Terminal/cmd and from the Magento’s root directory run the below command
php bin/magento setup:upgrade

For technical assistance, please email us at [email protected]. Additionally, explore different solutions to optimize your online store by visiting the Adobe Commerce extensions section.

If you need specialized support or wish to develop custom features, consider hiring Adobe Commerce Developers for your project.