Magento 2.3 Declarative Database Schema

In all versions of Magento2, up-to and including Magento 2.2.6, most module developers will be familiar with creating php scripts for creating, updating and manipulating custom tables for their modules. These files would live in the module directory here;

/app/code/NameSpace/ModuleName/Setup/InstallSchema.php
/app/code/NameSpace/ModuleName/Setup/UpgradeSchema.php

Since Magento 2.3.0, you can now use the declarative DB schema XML to create and update your DB tables in a single XML file. This file is placed in your module here;

/app/code/NameSpace/ModuleName/etc/db_schema.xml

This change is incredibly useful once you start playing with it. The php schema scripts can be long winded and difficult to read. The XML is short and neat, making it a relative dream to work with. Here’s an example of creating a simple table for your module;


<?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 comment="Pixie Media Custom Table" engine="innodb" name="pixiemedia_custom_table" resource="default">
<column comment="Entity Id" identity="true" name="custom_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
<constraint referenceId="PRIMARY" xsi:type="primary">
<column name="warehouse_id"/>
</constraint>
<column length="255" name="name" nullable="true" xsi:type="varchar"/>
<column length="255" name="info" nullable="true" xsi:type="varchar"/>
<column name="enabled" nullable="true" xsi:type="boolean"/>	
</table>
</schema>

With this file added to your module, simply run;


php bin/magento setup:upgrade

And the table is created on your Magento database.

Did you forget something? No problem!

This file is verified on each setup:upgrade, and you can add to it at any time. For example, update the file like this;


<?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 comment="Pixie Media Custom Table" engine="innodb" name="pixiemedia_custom_table" resource="default">
<column comment="Entity Id" identity="true" name="custom_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
<constraint referenceId="PRIMARY" xsi:type="primary">
<column name="warehouse_id"/>
</constraint>
<column length="255" name="name" nullable="true" xsi:type="varchar"/>
<column length="255" name="info" nullable="true" xsi:type="varchar"/>
<column name="comments" nullable="true" xsi:type="text"/>
<column name="enabled" nullable="true" xsi:type="boolean"/>	
</table>
</schema>

Note we’ve added the column ‘comments’. Run setup:upgrade again and your table now has that additional column included.

If you now decide you want to remove the ‘info’ column, all you need to do is remove it from the XML, so your db_schema.xml file now looks like this;


<?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 comment="Pixie Media Custom Table" engine="innodb" name="pixiemedia_custom_table" resource="default">
<column comment="Entity Id" identity="true" name="custom_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
<constraint referenceId="PRIMARY" xsi:type="primary">
<column name="warehouse_id"/>
</constraint>
<column length="255" name="name" nullable="true" xsi:type="varchar"/>
<column name="comments" nullable="true" xsi:type="text"/>
<column name="enabled" nullable="true" xsi:type="boolean"/>	
</table>
</schema>

Run setup:upgrade again, and the ‘info’ column is dropped.

This is a great addition for managing custom tables – more of these time savers please Magento!

Happy coding!

Magento 2.3 Declarative DB Schema