14

I install the magento 2.3 and I'm creating custom module.

But, I don't know how to create custom database table in magento 2.3 version.

39

First of all, create db_schema.xml file inside /RH/Helloworld/etc and write the following code :

<?xml version="1.0"?>
<!--
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="rh_helloworld" resource="default" engine="innodb" comment="RH Helloworld">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID"/>
        <column xsi:type="varchar" name="author_name" nullable="false" length="25" comment="Name"/>
        <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email"/>
        <column xsi:type="varchar" name="description" nullable="false" length="255" comment="Descrition"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id"/>
        </constraint>
    </table>
</schema>
  • <table> .. </table> = "Use for create and set table name"
  • <column> .. </column> = "Use for create and set column of the table"
  • <constraint> .. </constraint> = "Use for set constraint as like primary key, foreign key, unique key etc."

Before running the upgrade command you need to add your schema to db_whitelist_schema.json file by running the following command :

php bin/magento setup:db-declaration:generate-whitelist --module-name=RH_Helloworld

Now, there are db_whitelist_schema.json file will be create in /RH/Helloworld/etc folder.

Now, run php bin/magento s:up

Table will be create inside database.

=> If you want to renaming a column, you need to set below line in your db_schema.xml at appropriate column :

<column xsi:type="varchar" name="customer_email" onCreate="migrateDataFrom(email)" on_update="false" nullable="false" default="" comment="Customer Email"/>

here, name = "new column name" and onCreate="migrateDataFrom()" = "old column name"

=> If you want to drop table, then you can either remove entire table node from xml file or you can set disabled attribute to true as like below line in your db_schema.xml :

<table name="rh_helloworld" resource="default" engine="innodb" comment="RH Helloworld" disabled="true">
 ..
 </table>

For more details, You can check here.

Hope, It will helpful for you.

|improve this answer|||||
  • 1
    Good one @Rohan – Ramkishan Suthar Nov 30 '18 at 4:36
  • Nice explanation..... Thank you so much.... It is really helpful.... – harsh khandhar Nov 30 '18 at 10:29
  • Happy to help !! Happy coding :) & Thank You @RamkishanSuthar – Rohan Hapani Nov 30 '18 at 10:31
  • Why we need to generate db_whitelist_schema.json ? – Ramanathan Dec 12 '18 at 13:19
  • @RohanHapani How can I create custom product attribute in Magento 2.3.0 using custom extension ? – Kishan Patadia Dec 24 '18 at 7:12
12

Create file named as db_schema.xml under etc folder in your any custom module.

<?xml version="1.0" encoding="UTF-8"?>

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="books_data" resource="default" engine="innodb" comment="Book Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="BOOK ID"/>
        <column xsi:type="varchar" name="book_name" nullable="false" length="255" comment="Book Name"/>
        <column xsi:type="int" name="author" unsigned="true" nullable="true" identity="false" default="" comment="Author"/>
        <column xsi:type="varchar" name="isbn_no" nullable="true" comment="ISBN No"/>
        <column xsi:type="timestamp" name="publish_date" on_update="false" nullable="false" default="CURRENT_TIMESTAMP"
                comment="Publish Date"/>
      <column xsi:type="varchar" name="language" nullable="true" comment="Language"/>
         <column xsi:type="decimal" name="mrp" scale="4" precision="12" unsigned="false" nullable="false"
                default="0" comment="MRP"/>
        <constraint xsi:type="primary" name="PRIMARY">
            <column name="id"/>
        </constraint>
    </table>

    <table name="author_data" resource="default" engine="innodb" comment="Author Table">
        <column xsi:type="smallint" name="id" padding="6" unsigned="false" nullable="false" identity="true" comment="Author ID"/>
        <column xsi:type="varchar" name="author_name" nullable="false" length="255" comment="Author Name"/>
        <column xsi:type="varchar" name="author_email" nullable="false" length="255" comment="Author Email"/>
        <column xsi:type="varchar" name="affliation" nullable="false" length="255" comment="Affliation"/>
        <column xsi:type="int" name="age" unsigned="true" nullable="true" identity="false" default="" comment="Age"/>
        <constraint xsi:type="primary" name="PRIMARY">
            <column name="id"/>
        </constraint>
    </table>
</schema>

Now create db_whitelist_schema.json at same path

php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module

After that just run php bin/magento setup:upgrade. For more information you can check Here . Let me know in case you need more explanation on this.

|improve this answer|||||
  • But ... this is the same answer as above. Why double post? – Jisse Reitsma Jan 13 '19 at 16:03
  • @JisseReitsma I posted before above answer. check time of both answers. – Ramkishan Suthar Jan 14 '19 at 4:50
  • My bad: Both of you answered the question within 14 minutes after the original post was made and you were just a bit quicker. It seems you guys make it a sport to answer a new question as soon as possible :) – Jisse Reitsma Jan 15 '19 at 7:36
  • 1
    The Answer is perfect. I want to add some information more : The main disadvantage of the old approach(InstallSchema) is that Magento applies changes blindly. For example, in one version a new database column might be introduced, only to be removed in the next. Declarative setup eliminates this type of unnecessary work. – HaFiz Umer Jan 8 at 13:45
0

Magento 2.3 core modules used a declarative schema approach instead of the setup upgrade script. This is a new recommended approach in Magento 2.3 and above. Magento 2.3.x is still working with InstallSchema, InstallData,.. etc.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.