Day 6: Create custom Database Table with Data

Welcome to Day 6 of the Magento Advent Calendar! Yesterday we created a CLI command. Today we’ll go one step deeper — we’ll create a custom database table using Declarative Schema, and then we’ll add initial data into that table using a Data Patch.

Declarative Schema is the recommended Magento approach to database structure.
Instead of InstallSchema or UpgradeSchema classes, you describe your table once in db_schema.xml, and Magento keeps your DB in sync.

Step 1: Create db_schema.xml file

File: app/code/MageMastery/Module/etc/db_schema.xml

<?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="magemastery_log" resource="default" engine="innodb" comment="MageMastery Log Table">
        <column name="log_id" xsi:type="int" unsigned="true" nullable="false" identity="true" comment="Log ID"/>
        <column name="message" xsi:type="text" nullable="false" comment="Log Message"/>
        <column name="created_at" xsi:type="timestamp" nullable="false" default="CURRENT_TIMESTAMP" comment="Created At"/>

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="log_id"/>
        </constraint>
    </table>

</schema>

Step 2: Add Initial Data Using a Data Patch

File: app/code/MageMastery/Module/Setup/Patch/Data/AddInitialLogs.php

<?php

declare(strict_types=1);

namespace MageMastery\Module\Setup\Patch\Data;

use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\App\ResourceConnection;

class AddInitialLogs implements DataPatchInterface
{
    public function __construct(
        private readonly ResourceConnection $resourceConnection
    ) {
    }

    public function apply()
    {
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName('magemastery_log');

        $connection->insert($tableName, [
            'message' => 'First log entry from Data Patch!'
        ]);

        $connection->insert($tableName, [
            'message' => 'Second log entry added on install.'
        ]);
    }

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

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

Step 3: Run setup:upgrade to create the table

In the terminal, run the below command to create table:

php bin/magento setup:upgrade

Step 4: Verify the inserted data

Run the SQL to see the records:

SELECT * FROM magemastery_log;

Today you learned how to create a custom database table and insert initial data using a Data Patch. Come back tomorrow for Day 7 — we’ll continue building on top of this module.

I’ve also created a video for Day 6 of Magento Advent Calendar, enjoy!