Magento 2: Declarative Database Schema

Introduced in 2018 with Magento 2.3.0 the declarative schema is intended to provide easier installation and upgrade routes for the core Magento software and extension modules, removing the need for install and upgrade scripts which was the norm for developers prior to this change. Declarative schemas also enabled Magento to roll out database schema changes in patch releases which was not possible with the previous scripting method.

We will now add a basic database table to our example module created in the post Creating a Magento 2 Module.

Add Database Schema File

In the last post we created and registered a basic module, now we need to add a new file app/code/Examples/FirstModule/etc/db_schema.xml

Add the code below within the newly created db_schema.xml file:

<?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="examples_firstmodule_post" resource="default" engine="innodb" comment="Firstmodule posts table">
      <column xsi:type="int" name="post_id" unsigned="false" nullable="false" identity="true" comment="Post ID" />
      <column xsi:type="varchar" name="author_name" nullable="false" length="20" comment="Author Name" />
      <column xsi:type="varchar" name="email" nullable="false" length="20" comment="Email" />
      <column xsi:type="varchar" name="content" nullable="false" comment="Post Content" />
      <column xsi:type="timestamp" name="created" default="CURRENT_TIMESTAMP" />
      <column xsi:type="timestamp" name="updated" default="CURRENT_TIMESTAMP" />
      <constraint xsi:type="primary" referenceId="PRIMARY">
         <column name="post_id" />
      </constraint>
   </table>
</schema>

Full details for available options and configuring the db_schema.xml file can be found on the Magento dev docs.

Generate Schema Whitelist

Because traditional scripting methods can still be used alongside decalrative schemas it is recommended to generate a schema whitelist. When dropping tables or columns the declarative schema does not do this automatically in case they are declared somewhere else, such as an UpgradeSchema.php file.

Run the following CLI command to auto generate a schema whitelist for the module.

bin/magento setup:db-declaration:generate-whitelist --module-name Examples_FirstModule

Magento recommends that as a best practice, you should generate a new whitelist file for each release, and you must generate the whitelist in any release that contains changes in the db_schema.xml file.

Apply the changes

Finally with the new files in place we need to notify Magento of the changes, run the following CLI command

bin/magento setup:upgrade

Providing all is well no errors should be reported and the new table will have been created, you can check this by querying the database.

What’s next?

Now that we have somewhere to store data you could look at creating some CRUD repository models and interfaces to interact with the database.