Magento 2 Declarative Schema: Creating Tables With db_schema.xml

Since Magento 2.3, database tables are defined declaratively. Instead of writing install and upgrade scripts, you describe the final state of your tables in etc/db_schema.xml, and Magento works out what to create, alter or drop during setup:upgrade.

In this tutorial we create an FAQ table for a module called MageServices_Faq, add the model, resource model and collection, and look at how to change the table safely later.

Step 1: Define the Table

<?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="mageservices_faq" resource="default" engine="innodb" comment="MageServices FAQ">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <column xsi:type="varchar" name="question" length="255" nullable="false" comment="Question"/>
        <column xsi:type="text" name="answer" nullable="false" comment="Answer"/>
        <column xsi:type="smallint" name="store_id" unsigned="true" nullable="false" default="0" comment="Store ID"/>
        <column xsi:type="smallint" name="is_active" unsigned="true" nullable="false" default="1" comment="Is Active"/>
        <column xsi:type="int" name="sort_order" unsigned="true" nullable="false" default="0" comment="Sort Order"/>
        <column xsi:type="timestamp" name="created_at" nullable="false" default="CURRENT_TIMESTAMP" on_update="false" comment="Created At"/>
        <column xsi:type="timestamp" name="updated_at" nullable="false" default="CURRENT_TIMESTAMP" on_update="true" comment="Updated At"/>

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="MAGESERVICES_FAQ_STORE_ID_STORE_STORE_ID"
                    table="mageservices_faq" column="store_id"
                    referenceTable="store" referenceColumn="store_id"
                    onDelete="CASCADE"/>

        <index referenceId="MAGESERVICES_FAQ_IS_ACTIVE_SORT_ORDER" indexType="btree">
            <column name="is_active"/>
            <column name="sort_order"/>
        </index>
        <index referenceId="MAGESERVICES_FAQ_QUESTION_ANSWER" indexType="fulltext">
            <column name="question"/>
            <column name="answer"/>
        </index>
    </table>
</schema>

Step 2: Generate the Whitelist

Magento only drops or changes columns that are listed in db_schema_whitelist.json. This protects tables from accidental changes. Generate it whenever you change db_schema.xml and commit it with your code:

bin/magento setup:db-declaration:generate-whitelist --module-name=MageServices_Faq
bin/magento setup:upgrade --dry-run=1
bin/magento setup:upgrade

The --dry-run=1 option writes the SQL Magento would run to var/log/dry-run-installation.log without touching the database — useful before deploying schema changes to production.

Step 3: Model, Resource Model and Collection

<?php
declare(strict_types=1);

namespace MageServices\Faq\Model;

use Magento\Framework\Model\AbstractModel;
use MageServices\Faq\Model\ResourceModel\Faq as FaqResource;

class Faq extends AbstractModel
{
    protected $_eventPrefix = 'mageservices_faq';

    protected function _construct(): void
    {
        $this->_init(FaqResource::class);
    }
}

<?php
declare(strict_types=1);

namespace MageServices\Faq\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Faq extends AbstractDb
{
    protected function _construct(): void
    {
        $this->_init('mageservices_faq', 'entity_id');
    }
}

<?php
declare(strict_types=1);

namespace MageServices\Faq\Model\ResourceModel\Faq;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use MageServices\Faq\Model\Faq;
use MageServices\Faq\Model\ResourceModel\Faq as FaqResource;

class Collection extends AbstractCollection
{
    protected $_idFieldName = 'entity_id';

    protected function _construct(): void
    {
        $this->_init(Faq::class, FaqResource::class);
    }

    public function addActiveFilter(): self
    {
        return $this->addFieldToFilter('is_active', 1);
    }
}

Step 4: Insert Default Rows With a Data Patch

Schema describes structure; data patches insert data. This patch adds two starter questions using the resource model's connection:

<?php
declare(strict_types=1);

namespace MageServices\Faq\Setup\Patch\Data;

use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;

class AddDefaultFaqs implements DataPatchInterface
{
    public function __construct(
        private readonly ModuleDataSetupInterface $moduleDataSetup
    ) {
    }

    public function apply(): self
    {
        $connection = $this->moduleDataSetup->getConnection();
        $connection->insertMultiple(
            $this->moduleDataSetup->getTable('mageservices_faq'),
            [
                ['question' => 'How long does delivery take?', 'answer' => 'Most orders arrive within 2-3 working days.', 'sort_order' => 10],
                ['question' => 'Can I return an item?', 'answer' => 'Yes, within 30 days of delivery.', 'sort_order' => 20],
            ]
        );

        return $this;
    }

    public static function getDependencies(): array
    {
        return [];
    }

    public function getAliases(): array
    {
        return [];
    }
}

Changing the Table Later

To add a column, add it to db_schema.xml, regenerate the whitelist and run setup:upgrade. To remove a column, delete it from the XML — because it is in the whitelist, Magento drops it.

Renaming needs care: if you simply change the name, Magento drops the old column and creates a new empty one. Use onCreate="migrateDataFrom(old_name)" to copy data across:

<column xsi:type="varchar" name="title" length="255" nullable="false" onCreate="migrateDataFrom(question)" comment="Title"/>

Supported Column Types

Good Practices

• Prefix table names with your vendor to avoid clashes: mageservices_faq, not faq.
• Always add indexes for columns you filter or sort by in collections.
• Use foreign keys with onDelete="CASCADE" so rows are cleaned up when a store or product is deleted.
• Commit both db_schema.xml and db_schema_whitelist.json.
• Run setup:upgrade --dry-run=1 before deploying schema changes to production.

Schema Patches for Special Cases

A few changes cannot be expressed in db_schema.xml, such as creating database triggers or complex data transformations that must run alongside a structural change. For these, Magento provides schema patches in Setup/Patch/Schema, implementing SchemaPatchInterface. Use them sparingly; most modules never need one.

Frequently Asked Questions

Do I still need InstallSchema or UpgradeSchema?

No. Declarative schema replaces them. Use data patches for data and schema patches only for changes db_schema.xml cannot express.

Why did my column not get removed?

Magento only removes columns listed in db_schema_whitelist.json. Regenerate the whitelist before removing the column from db_schema.xml.

Can I modify a core table with db_schema.xml?

Yes. Declare the same table name in your module's db_schema.xml and add columns; Magento merges the declarations. Avoid changing core columns.