Cookies setting

Cookies help us enhance your experience on our site by storing information about your preferences and interactions. You can customize your cookie settings by choosing which cookies to allow. Please note that disabling certain cookies might impact the functionality and features of our services, such as personalized content and suggestions. Cookie Policy

Cookie Policy
Essential cookies

These cookies are strictly necessary for the site to work and may not be disabled.

Information
Always enabled
Advertising cookies

Advertising cookies deliver ads relevant to your interests, limit ad frequency, and measure ad effectiveness.

Information
Analytics cookies

Analytics cookies collect information and report website usage statistics without personally identifying individual visitors to Google.

Information
mageplaza.com

How To Create Table Using Magento Database Schema

Vinh Jacker | 3 days ago

How To Create Table Using Magento Database Schema How To Create Table Using Magento Database Schema

The Most Popular Extension Builder for Magento 2

With a big catalog of 224+ extensions for your online store

In Magento 2, the db_schema.xml file is used to define the database schema for custom modules or extensions. It specifies the structure of database tables, columns, indexes, and other related elements. To understand how it works and how to use it, we will break it down into 4 smaller steps. Let’s dive in!

Step 1: Create the db_schema.xml file

The first step in creating a table with Magento Database Schema is to create a schema file.

In your module’s directory, create a folder named etc if it doesn’t already exist. Inside the etc folder, create a file named db_schema.xml.

Create the db_schema.xml file

Step 2: Define the database structure

Open the db_schema.xml file and define the structure of your database tables, columns, and constraints. You can use XML tags to define tables, columns, data types, lengths, and constraints. Here’s an example:

<?xml version="1.0"?>

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="mageplaza_helloworld_post" resource="default" engine="innodb" comment="Post Table">
        <column xsi:type="smallint" name="post_id" padding="6" unsigned="false" nullable="false" identity="true" comment="Post ID"/>
        <column xsi:type="varchar" name="name" nullable="false" length="255" comment="Post Name"/>
        <column xsi:type="varchar" name="url_key" nullable="false" length="255" comment="Post URL Key"/>
        <column xsi:type="text" name="post_content" nullable="false" comment="Post Post Content"/>
        <column xsi:type="varchar" name="tags" nullable="false" length="255" comment="Post Tags"/>
        <column xsi:type="int" name="status" nullable="false" comment="Post Status"/>
        <column xsi:type="varchar" name="featured_image" nullable="false" length="255" comment="Post Featured Image'"/>
        <column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP" comment="Created At"/>
        <column xsi:type="timestamp" name="updated_at" nullable="false" default="CURRENT_TIMESTAMP" comment="Updated At"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="post_id"/>
        </constraint>
        <index referenceId="MAGEPLAZA_HELLOWORLD_POST_FT_INDEX" indexType="fulltext">
            <column name="name"/>
            <column name="url_key"/>
            <column name="post_content"/>
            <column name="tags"/>
            <column name="featured_image"/>
        </index>
    </table>
</schema>

Understanding the elements:

  • The <table> tag represents a database table
  • defines a column within a table
  • specifies a constraint like a primary key or foreign key
  • The tag is the root element of the db_schema.xml file

The column subnode defines a column in a table. Each column requires its own declaration.

A column can have the following attributes:

ATTRIBUTE DESCRIPTION xsi:type Specifies the column type. Must be one of the following: blob (includes blob, mediumblob, longblob) boolean date datetime decimal float int (includes smallint, bigint, tinyint) json real (includes decimal, float, double, real) smallint text (includes text, mediumtext, longtext) timestamp varbinary varchar default Initializes the column with the specified default value. The default value should have the same datatype defined in xsi:type. disabled Disables or deletes the declared table, column, constraint, or index. identity Indicates whether a column is auto-incremented length Specifies the length of a column. Can be used for char, varchar, and varbinary types. nullable Indicates whether column can be nullable. onCreate This is a DDL trigger that allows you to move data from an existing column to a newly created column. This trigger works only when a column is created. padding The size of an integer column. precision The number of allowed digits in a real data type. scale The number of digits after the decimal in a real data type. unsigned For numeric data types, specify whether the column can contain positive and negative values or only positive values.

ATTRIBUTE DESCRIPTION
xsi:type Specifies the column type. Must be one of the following:

- blob (includes blob, mediumblob, longblob)
- boolean
- date
- datetime
- decimal
- float
- int (includes smallint, bigint, tinyint)
- json
- real (includes decimal, float, double, real)
- smallint
- text (includes text, mediumtext, longtext)
- timestamp
- varbinary
- varchar
default Initializes the column with the specified default value. The default value should have the same datatype defined in xsi:type.
disabled Disables or deletes the declared table, column, constraint, or index.
identity Indicates whether a column is auto-incremented
length Specifies the length of a column. Can be used for char, varchar, and varbinary types.
nullable Indicates whether column can be nullable.
onCreate This is a DDL trigger that allows you to move data from an existing column to a newly created column. This trigger works only when a column is created.
padding The size of an integer column.
precision The number of allowed digits in a real data type.
scale The number of digits after the decimal in a real data type.
unsigned For numeric data types, specify whether the column can contain positive and negative values or only positive values.

Step 3: Declare the schema version

In your module’s etc/module.xml file, declare the version of your module’s schema using the node. For example:

<?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="Mageplaza_HelloWorld" setup_version="1.0.1">
    </module>
</config>

Step 4: Apply the schema changes

After creating or modifying the db_schema.xml file, run the Magento setup upgrade command to apply the changes to the database. From the command line in your Magento installation directory, run:

php bin/magento setup:upgrade

Magento will read the db_schema.xml file and make the necessary changes to the database structure.

The db_schema.xml file provides a declarative approach to managing the database schema in Magento 2. By defining the structure in XML, Magento can handle the installation, upgrade, and removal of the database elements automatically. It ensures consistency and simplifies the management of database changes in Magento modules or extensions.

Special points to remember

Here are a few more points to further understand the workings and usage of the db_schema.xml file in Magento 2:

  • Data Types and Attributes: When defining columns in the db_schema.xml file, you can specify various data types and attributes. Some commonly used data types include int, varchar, text, datetime, and decimal. You can also specify attributes such as nullable, length, default, and comment for each column.

  • Constraints: In addition to defining columns, you can specify constraints such as primary keys, unique keys, and foreign keys using the tag. Constraints help enforce data integrity and relationships between tables.

  • Multiple Tables: You can define multiple tables within the db_schema.xml file by adding additional <table> tags. Each table can have its own set of columns and constraints.

  • Whitelist Generation: After creating or modifying the db_schema.xml file, you need to generate a whitelist file that includes the schema information. This step ensures that the schema changes are recognized by Magento. To generate the whitelist file, run the following command from the command line:

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

This command creates a db_schema_whitelist.json file in the etc directory of your module.

Wrap up

Hope that our blog has given you a clear understanding of how to create a schema file to make a new database table in Magento 2. Just follow it step-by-step and you will succeed. If you have any questions, don’t hesitate to contact us.

Table of content
    Jacker

    With over a decade of experience crafting innovative tech solutions for ecommerce businesses built on Magento, Jacker is the mastermind behind our secure and well-functioned extensions. With his expertise in building user-friendly interfaces and robust back-end systems, Mageplaza was able to deliver exceptional Magento solutions and services for over 122K+ customers around the world.



    Related Post

    Website Support
    & Maintenance Services

    Make sure your store is not only in good shape but also thriving with a professional team yet at an affordable price.

    Get Started
    mageplaza services