Create Custom Database Table in Magento 2

Posted on 10 February, 2017

After introducing our first article on How To Create a Magento 2 Module. I hope you understand and created helloworld module easily. In this note, we will go little bit deeper on create a custom table in Magento 2.

Let’s start!

As per our first article How To Create a Magento 2 Module you already created module.xml file at app/code/Emipro/HelloWorld/etc/module.xml. So, just open module.xml file and you'll see your code look like below code. If you don't know about that file then please refer our another article which is How To Create a Magento 2 Module.

1
2
3
4
5
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
    <module name="Emipro_HelloWorld" schema_version="0.0.1" setup_version="0.0.1">
    </module>
</config>            

In Above code set the schema_version and setup_version as you want for Ex: setup_version="1.0.0". Here setup_version is use to check the module version. And later on I'll explain you where module version is stored on database.

Create the installation file.

Before create the file you need to create the Setup folder at app/code/Emipro/HelloWorld/Setup
After created the folders you need create a InstallSchema.php file in app/code/Emipro/HelloWorld/Setup/InstallSchema.php And paste the below code. In this file will write a code for create your custom table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
<?php
  
namespace Emipro\HelloWorld\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
{
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();
  
        // Get emipro_sampletable table
        $tableName = $installer->getTable('emipro_sampletable');
        // Check if the table already exists
        if ($installer->getConnection()->isTableExists($tableName) != true) {
            // Create emipro_sampletable table
            $table = $installer->getConnection()
                ->newTable($tableName)
                ->addColumn(
                    'id',
                    Table::TYPE_INTEGER,
                    null,
                    [
                        'identity' => true,
                        'unsigned' => true,
                        'nullable' => false,
                        'primary' => true
                    ],
                    'ID'
                )
                ->addColumn(
                    'title',
                    Table::TYPE_TEXT,
                    null,
                    ['nullable' => false, 'default' => ''],
                    'Title'
                )
                ->addColumn(
                    'description',
                    Table::TYPE_TEXT,
                    null,
                    ['nullable' => false, 'default' => ''],
                    'Description'
                )
                ->addColumn(
                    'summary',
                    Table::TYPE_TEXT,
                    null,
                    ['nullable' => false, 'default' => ''],
                    'Summary'
                )                
                ->addColumn(
                    'created_at',
                    Table::TYPE_DATETIME,
                    null,
                    ['nullable' => false],
                    'Created At'
                )
                ->addColumn(
                    'status',
                    Table::TYPE_SMALLINT,
                    null,
                    ['nullable' => false, 'default' => '0'],
                    'Status'
                )
                ->setComment('Emipro Table')
                ->setOption('type', 'InnoDB')
                ->setOption('charset', 'utf8');
            $installer->getConnection()->createTable($table);
        }
  
        $installer->endSetup();
    }
}

Open Command line in folder root of magento and run both commands

php bin/magento setup:upgrade

php bin/magento cache:flush

For more informations about Magento 2 Useful Commands List Click Here and read our another technical note.

Finally, we are done. Let’s check your database and you will see the emipro_sampletable table created successfully. After setup upgrade command your module version stored on setup_module table in data_version field.

After complete above process, one question arrive on your mind that is how to get data from the database and display them on frontend. And also you think how insert some sample data on the custom database table. So, no worry about that we have written another technical note for that which is How to Create Model, Resource Model and Collection in Magento 2. 

I hope this article is useful for you to create custom table on custom module in Magento 2 and see you again in our next Magento 2 technical note, which is “How to Create Model, Resource Model and Collection in Magento 2”.

Bookmark it for your future reference. If you have any problem or ideas don’t hesitate to leave comment below.

P.S. Do share this note with your team.


Mayank Zalavadia , eCommerce Project Manager
Magento Technical Notes

Post Your Review

X

Your Review has been posted

0 Comment(s)