How to create new custom table in magento 2?

Welcome to Kishan Savaliya's Magento 2 Blog. In Previous tutorials, you learned how to install Magento 2 using Composer and how to install sample data after Magento 2 installation. Hope you liked that articles.
In this blog post, I will provide you step by step guidance, how to create new custom table in Magento 2? In Magento 2 you can create new custom tables, you can customize existing tables and you can insert update data.

In Magento 2, we can use InstallSchema, InstallData, UpgradeSchema and UpgradeData. Install scripts only run once while installing module while we can use upgrade scripts multiple times. Upgrade scripts can be used every time whenever module's version get changed.

So let's start, how to create new custom table in Magento 2? We will try to learn how to use all 4 type of scripts and it's usage via example.

We need to follow below steps to take to accomplish these tasks are :

• Create new custom module.
• Create InstallSchema.php file.
• Create InstallData.php file.
• Register and enable new module and verify table and data created.
• Create UpgradeSchema.php file.
• Create UpgradeData.php file.
• Run Upgrade command and verify table and data has updated.

Step - 1 : Create new custom module.

Create new custom module called "SK_CustomTable".

Navigate to your Magento 2 root directory and then app/code directory, and create your Namespace(Vendor) directory here we will create SK directory and navigate inside that directory, and create CustomTable directory there, this is your module directory. You can do that via command-line(terminal) as well, follow below steps in your terminal.

cd <your-magento-2-root-directory>/app/code

mkdir SK

mkdir SK/CustomTable

Now, create registration.php file inside your CustomTable directory.

Content for that file is..

<?php
/**
 * SK Create custom table Magento 2 with the use of
 * InstallSchema, InstallData, UpgradeSchema and UpgradeData
 */
\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'SK_CustomTable',
    __DIR__
);

Now, create etc directory inside CustomTable directory and navigate into that and create module.xml file.

Content for that file is..

<?xml version="1.0"?>
<!--
/**
 * SK Create custom table Magento 2 with the use of
 * InstallSchema, InstallData, UpgradeSchema and UpgradeData
 */
-->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
	<module name="SK_CustomTable" setup_version="1.0.0" />
</config>

Above both files will be useful to register and enable new module.

Step - 2 : Create InstallSchema.php file.

Navigate to your Magento 2 root directory, and go to app/code/SK/CustomTable and create Setup directory inside it. Create InstallSchema.php named file inside setup directory.

app/code/SK/CustomTable/Setup/InstallSchema.php

Content for this file is..

<?php
/**
* SK Create custom table Magento 2 with the use of
* InstallSchema, InstallData, UpgradeSchema and UpgradeData
*/

namespace SK\CustomTable\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

class InstallSchema implements InstallSchemaInterface
{
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
      $table = $setup->getConnection()
          ->newTable($setup->getTable('sk_custom_table'))
          ->addColumn(
            'test_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
            'Test ID'
          )
          ->addColumn(
            'test_content',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            255,
            ['nullable' => false, 'default' => ''],
            'Test Content'
          )->setComment("SK Custom table");
          $setup->getConnection()->createTable($table);
      }
}

Here in above file, I have created `sk_custom_table` named table, and added ID and Content 2 fields in that table.

Step - 3 : Create InstallData.php file

Let's create InstallData.php file inside Setup directory. We will try to install sample data with this new table while we install new module. (Note : If you don't want to add sample data with new table then you can skip this step.)

app/code/SK/CustomTable/Setup/InstallData.php

Content for this file is..

<?php
/**
* SK Create custom table Magento 2 with the use of
* InstallSchema, InstallData, UpgradeSchema and UpgradeData
*/

namespace SK\CustomTable\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)
    {
      $contentArray = [
        ['test_content' => 'What is Lorem Ipsum?'],
        ['test_content' => 'Lorem Ipsum is simply dummy text.']
      ];
      foreach ($contentArray as $content) {
        $setup->getConnection()->insertForce($setup->getTable('sk_custom_table'), $content);
      }
    }
}

Step - 4 : Register and enable new module and verify table and data created.

In this step, we will register and enable newly created module via below command. Open terminal and go to your magento 2 root directory, and run below command there.

php bin/magento setup:upgrade

Above command will register and enable our new module in Magento 2, and it will create our custom table with sample data too. So let's verify newly created table with data. You can run below command to check your table is created or not in your database, and also you can check your custom table's data there. Run below command and check your output.

echo "use magento2; SHOW TABLES LIKE '%custom_table%'" | mysql -uroot -p

Here in above command, I have used my table name called magento2, there you can put your database name. You can use your table name where I used custom_table and you can use your MySQL username and password, MySQL username is root, so I used it you can put your MySQL username instead.

In above output, I can see my new table created successfully there in my database, now we can verify data that we had inserted via InstallData.php file through below command.

echo "use magento2; SELECT * FROM sk_custom_table;" | mysql -uroot -p

My data also inserted successfully inside new table.

Whenever we will create new custom module, Magento add that new module's entry in setup_module table, so you can verify your module's name added there or not, using below command.

echo "use magento2; SELECT * FROM setup_module WHERE module='SK_CustomTable';" | mysql -uroot -p

You can replace your module name with SK_CustomTable.

Step - 5 : Create UpgradeSchema.php file.

First you can upgrade module's version in your etc/module.xml file. We had used 1.0.0 module version whenever we had created new module, so now we can upgrade it with 1.1.0 in module.xml file.

<module name="SK_CustomTable" setup_version="1.1.0">

After changing version, you can create UpgradeSchema.php file inside Setup directory.

app/code/SK/CustomTable/Setup/UpgradeSchema.php

Content for this file is..

<?php
/**
* SK Create custom table Magento 2 with the use of
* InstallSchema, InstallData, UpgradeSchema and UpgradeData
*/

namespace SK\CustomTable\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.1.0', '<')) {
            $setup->getConnection()->addColumn(
                $setup->getTable('sk_custom_table'),
                'test_new_custom_field',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'length' => 255,
                    'nullable' => false,
                    'default' => '',
                    'comment' => 'Test New Custom Field'
                ]
            );
        }
        $setup->endSetup();
    }
}

We will try to add new field now in our custom table, so I added new field test_new_custom_field in sk_custom_table. You must review version_compare line, as we can execute upgrade scripts multiple times so we can manage versioning here. So I had defined 1.1.0 version in module.xml file, so I added new version here so whenever we will run upgrade command it'll not use previous upgrades, we can manage it via if conditions.

Step - 6 : Create UpgradeData.php file.

Let's create Upgrade data file

app/code/SK/CustomTable/Setup/UpgradeData.php

Content for this file is..

<?php
/**
* SK Create custom table Magento 2 with the use of
* InstallSchema, InstallData, UpgradeSchema and UpgradeData
*/

namespace SK\CustomTable\Setup;

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

class UpgradeData implements UpgradeDataInterface
{
    public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        if ($context->getVersion()
            && version_compare($context->getVersion(), '1.1.0') < 0
        ) {
            $table = $setup->getTable('sk_custom_table');
            $setup->getConnection()->insertForce($table, ['test_content' => 'Test line 3..!', 'test_new_custom_field' => 'New Custom Field Value 3.']);

            $setup->getConnection()->update($table, ['test_new_custom_field' => 'New Custom Field Value 1.'], 'test_id = 1');
            $setup->getConnection()->update($table, ['test_new_custom_field' => 'New Custom Field Value 2.'], 'test_id = 2');
        }
        $setup->endSetup();
    }
}

In above file, I have added new raw via upgrade data script, and updated newly created field's value for previously created 2 raws. Here also we have version_compare condition so we can use UpgradeData.php file multiple time same as UpgradeSchema.php.

Step - 7 : Run Upgrade command and verify table and data has updated.

Now we need to run setup upgrade command once again, so run below command once again.

php bin/magento setup:upgrade

Now, you can verify your module's version in setup_module table using below command.

echo "use magento2; SELECT * FROM setup_module WHERE module='SK_CustomTable';" | mysql -uroot -p

Now you can verify new field is created in your table with sample data or not, using below command.

echo "use magento2; SELECT * FROM sk_custom_table;" | mysql -uroot -p

Hope you may like this article and can understand this easily. You can add comments below in case if you have any questions regarding this article or if I missed anything here. I will check and get back to you with proper solution.

If you enjoyed this blog post, share it with friends!