Changing an Existing Table
Adding, renaming and dropping columns in db_schema.xml, and the onCreate attribute that carries the data across a rename.
<column xsi:type="varchar" name="customer_reference" length="64" onCreate="migrateDataFrom(customer_ref)"/>A change to a table is a change to its declaration. There is no migration step to write; you edit db_schema.xml so it describes the table you want, regenerate the whitelist, and run bin/magento setup:upgrade.
Add
Add a <column> node. Magento issues an ALTER TABLE ... ADD COLUMN. Existing rows get the default, or NULL when the column is nullable and has no default.
Rename
Declarative schema matches columns by name, so renaming by editing the name attribute reads as two separate instructions: drop the old column, create a new empty one. The data goes. To keep it, give the new column an onCreate attribute:
onCreate="migrateDataFrom(old_name)"
Magento then creates the new column, copies the values across, and drops the old one. For a whole table, the equivalent on the <table> node is onCreate="migrateDataFromAnotherTable(old_table)".
Drop
Delete the node from your own declaration. To suppress something another module declared, re-declare it with disabled="true" rather than deleting their file. Either way the element must be in db_schema_whitelist.json or nothing happens.
What trips people up
Changing a type in place is an ALTER TABLE that MySQL may perform lossily. Narrowing a varchar length, or moving from varchar to int, truncates existing values. Read the dry-run SQL before doing it on real data.
Example
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="soundscode_enquiry" resource="default" engine="innodb" comment="Storefront enquiry">
<column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true"
comment="Entity ID"/>
<!-- was: <column xsi:type="varchar" name="customer_ref" nullable="false" length="64"/> -->
<column xsi:type="varchar" name="customer_reference" nullable="false" length="64"
onCreate="migrateDataFrom(customer_ref)" comment="Customer reference"/>
<!-- new column; existing rows get 0 -->
<column xsi:type="smallint" name="is_archived" unsigned="false" nullable="false" default="0"
comment="Is archived"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="entity_id"/>
</constraint>
</table>
</schema>When to use it
- Rename a badly named column without losing a year of rows.
- Add a flag column to a table that already has data in it.
- Drop a column a third-party module added to your table.
- Widen a varchar before a bulk import of longer values.
More examples
Renaming a table and keeping its rows
The table-level counterpart of migrateDataFrom. Magento creates soundscode_contact_request, copies the rows out of soundscode_enquiry, and drops the old table. The copy is run as INSERT INTO new_table SELECT * FROM old_table, so the two tables must have the same columns in the same order: a mismatch makes the statement fail rather than carrying over the columns the two tables share. Rename a table rarely, and only once, because the old name is gone afterwards and every repository, collection and raw query that mentions it has to change in the same release.
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<!-- was: <table name="soundscode_enquiry"> -->
<table name="soundscode_contact_request" resource="default" engine="innodb"
onCreate="migrateDataFromAnotherTable(soundscode_enquiry)"
comment="Storefront contact request">
<column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true"
comment="Entity ID"/>
<column xsi:type="varchar" name="email" nullable="false" length="255" comment="Customer email"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="entity_id"/>
</constraint>
</table>
</schema>Suppressing another module's column
disabled="true" removes an element that is declared somewhere else in the merged schema, which is how you drop a column contributed by a third-party module without editing its files. Note that the index is disabled too: an index over a dropped column has to go in the same change, or the generated SQL fails. The two tags also show the shorthand and longhand of an empty XML element, which parse identically.
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="soundscode_enquiry">
<!-- self-closing and paired forms are the same XML -->
<column name="legacy_source" disabled="true"/>
<index referenceId="SOUNDSCODE_ENQUIRY_LEGACY_SOURCE" disabled="true"></index>
</table>
</schema>The order of commands after an edit
The sequence worth memorising. Step 2 before step 4 matters, because a drop is skipped silently if the whitelist has not caught up. Step 3 is optional locally and not optional on anything with production-sized data. The final cache:flush is for the schema metadata Magento caches, not for the DDL itself.
# 1. edit app/code/Soundscode/Enquiry/etc/db_schema.xml
# 2. record the current declarations so drops are permitted
bin/magento setup:db-declaration:generate-whitelist --module-name=Soundscode_Enquiry
# 3. see the SQL without touching the database
bin/magento setup:upgrade --dry-run=1
less var/log/dry-run-installation.log
# 4. apply it
bin/magento setup:upgrade
# Upgrade completed successfully.
bin/magento cache:flush
Discussion