How to Create Database Tables in Magento 2?

To add a new table to the Magento 2 database, you need to create a file in the module folder:

app/code/<VendorName>/<ModuleName>/Setup/InstallSchema.php

add the following code to it:

<?php

namespace VendorName\ModuleName\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;

class InstallSchema implements InstallSchemaInterface
{
     public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
     {
         $installer = $setup;
         $installer->startSetup();

        //new table script will be there

        $installer->endSetup();
     }
}

InstallSchema.php — is a file that is responsible for modifying the database structure during module installation. When executing the CLI command php bin/magento setup:upgrade Magento 2 checks whether a new module has appeared in the system and if it contains the InstallSchema.php file. If it does, this file calls its install method ($ setup, context).

Note: InstallSchema.php will not work if the module has been installed before.

Data on the versions of the installed modules is stored in the table "setup_module", located in the Magento 2 database. If there was created an unexpected structure in the database during the test installation of the module, delete it together with a record of your module from the table "setup_module".

Correct the code and run the php bin/magento setup:upgrade command again.

In the example, we will create a table for Frequently Asked Question (FAQ) and a table for storing FAQ dependencies — Store View, in other words, which languages ​​the FAQ article will be available in.

Replace the "// new table script will be there" comment in the InstallSchema.php file with the following code:

/**
 * Create table 'magefan_faq'
 */
 $table = $installer->getConnection()->newTable(
     $installer->getTable('magefan_faq')
 )->addColumn(
     'faq_id',
     \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
     null,
     ['identity' => true, 'nullable' => false, 'primary' => true],
     'FAQ ID'
 )->addColumn(
     'title',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     255,
     ['nullable' => true],
     'FAQ Title'
 )->addColumn(
     'meta_keywords',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     '64k',
     ['nullable' => true],
     'FAQ Meta Keywords'
 )->addColumn(
     'meta_description',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     '64k',
     ['nullable' => true],
     'FAQ Meta Description'
 )->addColumn(
     'identifier',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     100,
     ['nullable' => true, 'default' => null],
     'FAQ String Identifier'
 )->addColumn(
     'content_heading',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     255,
     ['nullable' => true],
     'FAQ Content Heading'
 )->addColumn(
     'content',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     '2M',
     [],
     'FAQ Content'
 )->addColumn(
     'creation_time',
     \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
     null,
     [],
     'FAQ Creation Time'
 )->addColumn(
     'update_time',
     \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
     null,
     [],
     'FAQ Modification Time'
 )->addColumn(
     'is_active',
     \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
     null,
     ['nullable' => false, 'default' => '1'],
     'Is FAQ Active'
     )->addIndex(
     $installer->getIdxName('magefan_faq', ['identifier']),
     ['identifier']
)->setComment(
     'Magefan FAQ Table'
);
$installer->getConnection()->createTable($table);

/**
 * Create table 'magefan_faq_store'
 */
 $table = $installer->getConnection()->newTable(
     $installer->getTable('magefan_faq_store')
 )->addColumn(
     'faq_id',
     \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
     null,
     ['nullable' => false, 'primary' => true],
     'FAQ ID'
 )->addColumn(
     'store_id',
     \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
     null,
     ['unsigned' => true, 'nullable' => false, 'primary' => true],
     'Store ID'
 )->addIndex(
     $installer->getIdxName('magefan_faq_store', ['store_id']),
     ['store_id']
)->addForeignKey(
     $installer->getFkName('magefan_faq_store', 'faq_id', 'magefan_faq', 'faq_id'),
     'faq_id',
     $installer->getTable('magefan_faq'),
     'faq_id',
     \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
 )->addForeignKey(
     $installer->getFkName('magefan_faq_store', 'store_id', 'store', 'store_id'),
     'store_id',
     $installer->getTable('store'),
     'store_id',
     \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
 )->setComment(
     'Magefan FAQ To Store Linkage Table'
 );
 $installer->getConnection()->createTable($table);

The following is a specification of the methods.

Method addColumn:

/**
 * Adds a column to the table.
 *
 * $options contains additional options for the column. Allowed parameters:
 * - 'unsigned', is used only for numeric types. By default, gets the FALSE value;
 * - 'precision', is used only for numeric types. By default, gets a value from the $ size parameter, otherwise 0;
 * - 'scale', is used only for numeric types. By default, gets a value from the $ size parameter, otherwise equal to 10;
 * - 'default', no default value;
 * - 'nullable', default value: TRUE;
 * - 'primary', adds a column to the primary index. By default, the column is not added to the primary index;
 * - 'primary_position', the column from the primary index is used. The default value: number of primary columns + 1;
 * - 'identity' or 'auto_increment'. no default value;
 *
 * @param string $name - column name;
 * @param string $type - column data type;
 * @param string|integer|array $size - length (size) of the column;
 * @param array $options array with additional options;
 * @param string $comment - comment to the column;
 * @return $this
 */
 public function addColumn($name, $type, $size = null, $options = [], $comment = null)

 Method setComment:

/**
 * Sets a comment to the table.
 *
 * @param string $comment - text of the comment;
 * @return $this
 */
 public function setComment($comment)

Method addIndex:

/**
 * Adds an index to the table.
 *
 * @param string $indexName - index name;
 * @param array or string $fields  - an array of column names or a column name;
 * @param array $options - an array of additional options;
 * @return $this
 */
 public function addIndex($indexName, $fields, $options = [])

Method addForeignKey:

/**
 * Adds foreign key to the table.
 *
 * @param string $fkName - the name of the foreign key;
 * @param string $column  - the name of the column key for the foreign key;
 * @param string $refTable - the name of the foreign table;
 * @param string $refColumn - name of foreign columns;
 * @param string $onDelete - event to delete a line;
 * @return $this
 */
 public function addForeignKey($fkName, $column, $refTable, $refColumn, $onDelete = null)

All these methods are declared in the vendor/magento/framework/DB/Ddl/Table.php file.

Save the InstallSchema.php file. If your module is in the 'setup_module' table, remove it.

Start the module installation process, run the CLI command:

php bin/magento setup:upgrade

Check the structure of the database, for example with phpMyAdmin. You will see new tables.

The Magento 2 database contains about 200 tables, if you can't find yours, go to the next page of the list.

You can view the modified files of the Magefan_Faq module on GitHub.