Magento 2: Create/Alter Database Table & Insert Data while Installing/Upgrading Module

This article shows how you can create a new database table and install data into the table while installing a custom module or upgrading the module in Magento 2.

The install and upgrade setup script file should be inside the Setup folder of your module. The install script should be named InstallSchema.php & InstallData.php and upgrade script should be named UpgradeSchema.php & UpgradeData.php.

I will be:

– Creating a database table from Install Schema Script (InstallSchema.php)
– Adding some rows/data to the table through the Install Data Script (InstallData.php)
– Altering the table (adding a new column to the table) from Install Schema Script (UpgradeSchema.php)
– Adding some rows/data again to the table through the Upgrade Data Script (UpgradeData.php)

Here, I am using my custom module named Chapagain_HelloWorld.

At first, my module’s setup version is 1.0.0.

File: app/code/Chapagain/HelloWorld/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="Chapagain_HelloWorld" setup_version="1.0.0">
        
    </module>
</config>

Create table from Setup Install Schema Script

In the below code, I have created a table named my_custom_table.

File: app/code/Chapagain/HelloWorld/Setup/InstallSchema.php

<?php
namespace Chapagain\HelloWorld\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) 
    {
        $setup->startSetup();
        $conn = $setup->getConnection();
 
        $tableName = $setup->getTable('my_custom_table');
        
        if($conn->isTableExists($tableName) != true){
            $table = $conn->newTable($tableName)
                            ->addColumn(
                                'id',
                                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                                null,
                                ['identity'=> true, 'unsigned'=> true, 'nullable'=> false, 'primary'=> true]
                            )
                            ->addColumn(
                                'name',
                                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                                255,
                                ['nullable'=> false, 'default'=> ''],
                                'User Name'
                            )
                            ->addColumn(
                                'email',
                                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                                255,
                                ['nullable'=> false, 'default'=> ''],
                                'Email'
                            )
                            ->addColumn(
                                'dob',
                                \Magento\Framework\DB\Ddl\Table::TYPE_DATE,
                                null,
                                ['nullable'=> false],
                                'Date of Birth'
                            )
                            ->addColumn(
                                'created_at',
                                \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                                null,
                                ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT],
                                'Created At'
                            )->addColumn(
                                'updated_at',
                                \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                                null,
                                ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT_UPDATE],
                                'Updated At'
                            )
                            ->setOption('charset','utf8');
            $conn->createTable($table);
        }
 
        $setup->endSetup();
    }
}

Insert data/rows to the table from Setup Install Data Script

I will be adding some rows of data into the table that I have created via the InstallSchema.php file. The data insertion code should be written in InstallData.php file.

You can use your module’s Model class or the DB Adapter class to insert the data into the table. In the below example, I have used both the classes to insert the data.

File: app/code/Chapagain/HelloWorld/Setup/InstallData.php

<?php 
namespace Chapagain\HelloWorld\Setup;

use Chapagain\HelloWorld\Model\PostFactory;
use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
 
class InstallData implements InstallDataInterface
{
    /**
     * @var \Chapagain\HelloWorld\Model\PostFactory
     */
    protected $postFactory;
 
    /**
     * @param \Chapagain\HelloWorld\Model\PostFactory $postFactory
     */
    public function __construct(
        PostFactory $postFactory
    )
    {
        $this->postFactory = $postFactory;
    }
 
    public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();

        /** 
         * \Magento\Framework\DB\Adapter\AdapterInterface 
         */
        $conn = $setup->getConnection(); 

        $tableName = $setup->getTable('my_custom_table');

        /**
         * Inserting data using the module's Model class
         */
        $data = [
            'name' => 'Mukesh Chapagain',
            'dob' => '1900-01-01',
            'email' => 'mukesh@example.com'
        ];

        $post = $this->postFactory->create();
        $post->addData($data)->save();


        /**
         * Inserting data using the DB Adapter class
         */
        $data = [
            'name' => 'Muk Cha',
            'dob' => '1900-05-05',
            'email' => 'mukcha@example.com'
        ];

        /**
         * Insert single row of data into the table
         * 
         * @param array $data Column-value pairs
         * @return int The number of affected rows.
         */ 
        $conn->insert($tableName, $data);

        $setup->endSetup();
    }
}

Enable the Module

You run the following commands to enable and upgrade the setup:

php bin/magento module:enable Chapagain_HelloWorld
php bin/magento setup:upgrade

After that, the module will be installed. The module’s name and version can be found in the setup_module table.

mysql> select * from setup_module where module = 'Chapagain_HelloWorld';
+----------------------+----------------+--------------+
| module               | schema_version | data_version |
+----------------------+----------------+--------------+
| Chapagain_HelloWorld | 1.0.0          | 1.0.0        |
+----------------------+----------------+--------------+
1 row in set (0.00 sec)

As the module is installed, the new table named my_custom_table is also created.

mysql> describe my_custom_table;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field      | Type             | Null | Key | Default           | Extra                       |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255)     | NO   |     |                   |                             |
| email      | varchar(255)     | NO   |     |                   |                             |
| dob        | date             | NO   |     | NULL              |                             |
| created_at | timestamp        | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

Let’s view the data present in the table.

mysql> select * from my_custom_table;
+----+------------------+--------------------+------------+---------------------+---------------------+
| id | name             | email              | dob        | created_at          | updated_at          |
+----+------------------+--------------------+------------+---------------------+---------------------+
|  1 | Mukesh Chapagain | mukesh@example.com | 1900-01-01 | 2018-11-15 09:12:02 | 2018-11-15 09:12:02 |
|  2 | Muk Cha          | mukcha@example.com | 1900-05-05 | 2018-11-15 09:12:02 | 2018-11-15 09:12:02 |
+----+------------------+--------------------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Alter table from Setup Upgrade Schema Script

I will be altering the table by adding a new column named status after column dob in the table (my_custom_table) that I created above.

The below code will be executed while upgrading the module to version 1.0.1.

File: app/code/Chapagain/HelloWorld/Setup/UpgradeSchema.php

<?php
namespace Chapagain\HelloWorld\Setup;
 
use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
 
class UpgradeSchema implements UpgradeSchemaInterface
{
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) 
    {
        $installer = $setup; 
        $installer->startSetup();
        $conn = $setup->getConnection();
 
        // this code runs while upgrading the module to version 1.0.1
        // upgrade scripts are added after the module is installed
        if(version_compare($context->getVersion(), '1.0.1', '<')) {
            $installer->getConnection()->addColumn(
                $installer->getTable( 'my_custom_table' ),
                'status',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
                    'nullable' => false,
                    'unsigned' => true,
                    'default' => '0',
                    'comment' => 'Status',
                    'after' => 'dob'
                ]
            );
        }
 
        $installer->endSetup();
    }
}

Insert data/rows to the table from Setup Upgrade Data Script

The below code will be executed while upgrading the module to version 1.0.1. It will add more rows of data to the table my_custom_table.

File: app/code/Chapagain/HelloWorld/Setup/UpgradeData.php

<?php 
namespace Chapagain\HelloWorld\Setup;

use Chapagain\HelloWorld\Model\PostFactory;
use Magento\Framework\Setup\UpgradeDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
 
class UpgradeData implements UpgradeDataInterface
{
    /**
     * @var \Chapagain\HelloWorld\Model\PostFactory
     */
    protected $postFactory;
 
    /**
     * @param \Chapagain\HelloWorld\Model\PostFactory $postFactory
     */
    public function __construct(
        PostFactory $postFactory
    )
    {
        $this->postFactory = $postFactory;
    }
    
    public function upgrade(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();

        /** 
         * \Magento\Framework\DB\Adapter\AdapterInterface 
         */
        $conn = $setup->getConnection(); 

        $tableName = $setup->getTable('my_custom_table');

        // this code runs while upgrading the module to version 1.0.1
        // upgrade scripts are added after the module is installed
        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            /**
             * Inserting data using the module's Model class
             */
            $data = [
                'name' => 'A A',
                'dob' => '1800-08-08',
                'email' => 'aa@example.com',
                'status' => 0
            ];
            $post = $this->postFactory->create();
            $post->addData($data)->save();

            /**
             * Inserting data using the DB Adapter class
             */
            $data = [
                'name' => 'B B',
                'dob' => '1801-08-08',
                'email' => 'bb@example.com',
                'status' => 1
            ];

            /**
             * Insert single row of data into the table
             * @param array $data Column-value pairs
             * @return int The number of affected rows.
             */ 
            $conn->insert($tableName, $data);

            $data = [
                [
                    'name' => 'C C',
                    'dob' => '1802-08-08',
                    'email' => 'cc@example.com',
                    'status' => 1
                ],
                [
                    'name' => 'D D',
                    'dob' => '1803-08-08',
                    'email' => 'dd@example.com',
                    'status' => 2
                ]
            ];

            /**
             * Insert Multiple Rows of data into the table
             * @param array $data Column-value pairs or array of Column-value pairs.
             * @return int The number of affected rows.
             */ 
            $conn->insertMultiple($tableName, $data);

            /**
             * In the \Magento\Framework\DB\Adapter\AdapterInterface interface
             * we can find more functions like the following:
             */

            /**
             * Update is done if there's a duplicate entry for the unique or primary key
             * @param array $data Column-value pairs or array of column-value pairs.
             * @param array $fields update fields pairs or values
             * @return int The number of affected rows.
             */
            // insertOnDuplicate($table, array $data, array $fields = [])

            /**
             * @param  array        $bind  Column-value pairs.
             * @param  mixed        $where UPDATE WHERE clause(s).
             * @return int          The number of affected rows.
             */
            // update($table, array $bind, $where = '')
            // delete($table, $where = '')

            /**
             * @param string|\Magento\Framework\DB\Select $sql An SQL SELECT statement.
             * @param mixed $bind Data to bind into SELECT placeholders.
             */
            // query($sql, $bind = [])
            // fetchAll($sql, $bind = [], $fetchMode = null)
            // fetchRow($sql, $bind = [], $fetchMode = null)
            // fetchAssoc($sql, $bind = [])
            // fetchCol($sql, $bind = [])
            // fetchOne($sql, $bind = [])
        }

        $setup->endSetup();
    }
}

Update the module version

As you can see in the above upgrade scripts, the code is set to run when the module version is 1.0.1. Hence, we have to update our module’s setup version to 1.0.1.

File: app/code/Chapagain/HelloWorld/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="Chapagain_HelloWorld" setup_version="1.0.1">
        
    </module>
</config>

Now, you need to clear the cache and upgrade the module with the following commands:

php bin/magento cache:clean
php bin/magento setup:upgrade

After that, the module will be upgraded to the version specified in the etc/module.xml file. The module’s name and version can be found in the setup_module table.

mysql> select * from setup_module where module = 'Chapagain_HelloWorld';
+----------------------+----------------+--------------+
| module               | schema_version | data_version |
+----------------------+----------------+--------------+
| Chapagain_HelloWorld | 1.0.1          | 1.0.1        |
+----------------------+----------------+--------------+
1 row in set (0.00 sec)

As the module is upgraded, a new field named status is also added to the table my_custom_table.

mysql> describe my_custom_table;
+------------+----------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                 | Null | Key | Default           | Extra                       |
+------------+----------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned     | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255)         | NO   |     |                   |                             |
| email      | varchar(255)         | NO   |     |                   |                             |
| dob        | date                 | NO   |     | NULL              |                             |
| status     | smallint(5) unsigned | NO   |     | 0                 |                             |
| created_at | timestamp            | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

Let’s view the data present in the table.

mysql> select * from my_custom_table;
+----+------------------+--------------------+------------+--------+---------------------+---------------------+
| id | name             | email              | dob        | status | created_at          | updated_at          |
+----+------------------+--------------------+------------+--------+---------------------+---------------------+
|  1 | Mukesh Chapagain | mukesh@example.com | 1900-01-01 |      0 | 2018-11-15 09:12:02 | 2018-11-15 09:12:02 |
|  2 | Muk Cha          | mukcha@example.com | 1900-05-05 |      0 | 2018-11-15 09:12:02 | 2018-11-15 09:12:02 |
|  3 | A A              | aa@example.com     | 1800-08-08 |      0 | 2018-11-15 10:03:04 | 2018-11-15 10:03:04 |
|  4 | B B              | bb@example.com     | 1801-08-08 |      1 | 2018-11-15 10:03:04 | 2018-11-15 10:03:04 |
|  5 | C C              | cc@example.com     | 1802-08-08 |      1 | 2018-11-15 10:03:04 | 2018-11-15 10:03:04 |
|  6 | D D              | dd@example.com     | 1803-08-08 |      2 | 2018-11-15 10:03:04 | 2018-11-15 10:03:04 |
+----+------------------+--------------------+------------+--------+---------------------+---------------------+
6 rows in set (0.01 sec)

Hope this helps. Thanks.