Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 30 ยท Data Abstraction Layer

Database Migrations

Create tables for your plugin with Shopware migrations: MigrationStep, timestamps, update versus destructive phases, running commands and safe rollbacks.

An entity definition describes data, but it does not create the table. That job belongs to a migration: a small PHP class that changes the database schema, runs once, and is remembered. Shopware's own core uses the same system, so a plugin's migrations are applied with the same commands and the same rules as the platform's.

In this lesson you will write a MigrationStep, learn why it has two methods (update and updateDestructive), see when a plugin's migrations run, and think about what "rollback" means in a system that mostly moves forward. The real code is read-only, since Shopware cannot run here. Two runnable PHP stand-ins on SQLite show the bookkeeping: a runner that remembers which steps ran, and a transaction that protects a half-finished step.

What a migration is

A migration is a class in your plugin's src/Migration folder. It extends Shopware\Core\Framework\Migration\MigrationStep. It has three methods:

  • getCreationTimestamp(): int returns a Unix timestamp. It decides the order in which migrations run, and it is how Shopware tells new steps from old ones.
  • update(Connection $connection): void makes changes that are safe to apply while the old code still runs.
  • updateDestructive(Connection $connection): void makes changes that would break the old code, such as dropping a column.

The Connection is Doctrine DBAL's connection, so you execute SQL with executeStatement().

PHP
<?php declare(strict_types=1);

namespace Acme\Vendors\Migration;

use Doctrine\DBAL\Connection;
use Shopware\Core\Framework\Migration\MigrationStep;

class Migration1730000000CreateVendorTable extends MigrationStep
{
    public function getCreationTimestamp(): int
    {
        return 1730000000;
    }

    public function update(Connection $connection): void
    {
        $connection->executeStatement('
            CREATE TABLE IF NOT EXISTS `acme_vendor` (
                `id` BINARY(16) NOT NULL,
                `name` VARCHAR(255) NOT NULL,
                `website` VARCHAR(255) NULL,
                `rating` INT NULL,
                `active` TINYINT(1) NOT NULL DEFAULT 0,
                `created_at` DATETIME(3) NOT NULL,
                `updated_at` DATETIME(3) NULL,
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ');
    }

    public function updateDestructive(Connection $connection): void
    {
        // nothing to remove yet
    }
}

By convention the class name is Migration<timestamp><Description>, and the timestamp in the name matches the one returned by the method. Shopware locates the classes by the namespace and folder it expects for the plugin: the plugin base class registers a migration path of src/Migration with the namespace <PluginNamespace>\Migration. Check the base Plugin class of the version you target if your layout differs.

Notice the column types. The id is BINARY(16), matching the IdField from the definition. Timestamps use DATETIME(3), with millisecond precision, as in core tables. The column names are the storage names that your definition declared.

A command exists to create the skeleton for you: bin/console database:create-migration -p AcmeVendors --name CreateVendorTable. It writes a class with the current timestamp. If the command is not available in your version, copy an existing migration and change the timestamp.

Update versus destructive

Why two methods? Think of a deployment where the new code goes live and the old code is still serving requests for a few seconds. If a migration drops a column that the old code reads, those requests fail. Shopware therefore splits changes into two phases:

Phase Typical changes When it runs
update Create tables, add nullable columns, add indexes, insert data Automatically during install and update
updateDestructive Drop columns or tables, rename columns, make columns stricter Only when you ask for it, after the new code is live

For a plugin, the practical rule is: put additive work in update. Put removals in updateDestructive, and run them in a later release, once no running code needs the old structure. A rename is two releases: add the new column and copy data in update, then drop the old column in a later updateDestructive.

flowchart LR
    A["New plugin version<br/>deployed"] --> B["update()<br/>additive changes"]
    B --> C["Old and new code<br/>can both run"]
    C --> D["Later release:<br/>updateDestructive()"]
    D --> E["Old structure<br/>removed"]

Additive changes first, removals later. The gap between them is the safety margin.

When migrations run

Shopware records every executed step in a migration table, with the class name and one timestamp column for each phase. A step runs when it has no recorded update yet. The usual triggers are:

  • bin/console plugin:install --activate AcmeVendors and plugin:update AcmeVendors run the plugin's update migrations as part of the install or update.
  • bin/console database:migrate --all AcmeVendors runs pending update steps of that plugin by hand.
  • bin/console database:migrate-destructive --all AcmeVendors runs the pending destructive steps.

The --all flag means "run everything pending, not just a single step". The second argument is the identifier of the migration source: for a plugin, its name. The destructive command should be a conscious step in your release process and not part of every deployment.

One more lifecycle point: on uninstall, a plugin should drop its own tables, but only if the user did not choose to keep data. The UninstallContext has keepUserData() for that check. A later lesson, Plugin Structure and Lifecycle, covers it in detail. When user data is not kept, the base plugin class also clears the plugin's records from the migration table, so a reinstall can run the steps again.

Safe rollbacks

There is no "down" method. A MigrationStep only moves forward, and that is deliberate: a rollback of a table that already holds customer data is rarely safe. Instead, you protect yourself in other ways:

  • Idempotent SQL. Use CREATE TABLE IF NOT EXISTS, and check whether a column exists before adding it. If a step fails halfway and you rerun it, it should finish the job instead of failing on "table already exists".
  • Small steps. One schema change per migration. A failing step then leaves at most one change to repair.
  • A fix-forward step. If a released migration was wrong, add a new migration with a later timestamp that corrects it. Never edit a migration that others have already run.
  • A database backup before destructive steps. This is your real rollback.
  • Remember MySQL's DDL behavior. In MySQL, statements like CREATE TABLE and ALTER TABLE commit implicitly. A transaction around them does not undo them. So a step with five statements can stop after the third. Idempotence is what lets you recover.

A checking helper for an existing column looks like this:

PHP
private function columnExists(Connection $connection, string $table, string $column): bool
{
    $columns = $connection->fetchFirstColumn(
        'SELECT COLUMN_NAME FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table',
        ['table' => $table]
    );

    return in_array($column, $columns, true);
}

Shopware's MigrationStep also offers helper methods for common cases (such as adding a column only when it is missing). Their names have changed between versions, so look at the base class in the version you target before relying on one.

Try it: a migration runner

This stand-in has the same shape as the real system: steps with timestamps, a bookkeeping table, a normal phase and a destructive phase. It uses SQLite, so the SQL differs from MySQL, and there is no plugin loading or command line. The part to study is the logic: order by timestamp, skip what already ran, and refuse to run the destructive phase for a step whose normal phase has not run.

PHP A migration runner with two phases

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The third step shows the two-release pattern in one file for demonstration. The update phase adds phone and copies the data, and the old fax column stays until the destructive phase. In a real project those two phases would run in different releases.

Try it: protecting a half-finished step

In SQLite, DDL statements take part in transactions, so a failure can be undone completely. MySQL does not behave this way, which is why the real advice is idempotent SQL. This second stand-in shows both ideas side by side: the step wrapped in a transaction (works in SQLite) and the same step written to be safely repeatable (the approach that also works in MySQL).

PHP Transactions versus idempotent steps

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The transaction is a bonus where the database supports it. The idempotent version is the habit that protects you on MySQL, where a partial failure cannot be rolled back.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which method should create a new table for your plugin?

    Additive changes such as creating tables belong in update(). It is safe to run while the old code still serves requests.
  2. 2. What is the purpose of the timestamp returned by getCreationTimestamp()?

    Migrations run in timestamp order, and the value helps the system tell new steps from old ones.
  3. 3. A released migration contains a mistake. What is the recommended fix?

    Migrations move forward only. Editing an old step does not change servers that already ran it, so you fix forward.
  4. 4. Why is idempotent SQL especially important on MySQL?

    Because the changes are committed one by one, a failure can leave a partial result. A step that can be run again finishes the job.
  5. 5. When should a column be dropped?

    Removals are destructive. They wait until the old code that reads the column is gone.

Summary

  • A migration extends MigrationStep and lives in src/Migration, named after its timestamp.
  • update() is additive and runs during plugin install and update. updateDestructive() removes structure and runs on request, later.
  • Executed steps are recorded in the migration table; commands like database:migrate --all AcmeVendors run what is pending.
  • There is no rollback method: write idempotent SQL, keep steps small, back up before destructive steps, and fix forward.
  • Table columns must match the storage names declared in your entity definition.

Next, read and write the data with Reading and Writing with Repositories.