Execute SQL queries or code before and after database migrations with Flow Framework

When creating and executing database migration with the Flow Framework (and by that, Doctrine Migrations) you can find yourself wanting to execute code afterwards.

A usecase could be

Add a new field to the database and populate it with values from a different field somewhere else

Create your database migration

First, create your migration file with the ./flow doctrine:migrate command.

Open the file and you will find a up and down method that does the migration.

pre- and post-methods available

For each direction you have pre* and post* methods available, so you can have methods with the following names

public function preUp(Schema $schema) {...}
public function preDown(Schema $schema) {...}
public function postUp(Schema $schema) {...}
public function postDown(Schema $schema) {...}

The same functionality as documented in the Doctrine Migrations documentation is available for Neos Flow created database migrations 🥳

Example: Fill field with data once migration is applied

To give you an example here is what a simplified postUp migration method does in my current Work Registration and invoicing tool, Dafis

public function postUp(Schema $schema): void
{
    $select = $this->connection->executeQuery('SELECT id FROM table WHERE `column` IS NULL');
    while ($row = $select->fetch(\PDO::FETCH_ASSOC)) {
        $id = Identifier::create();
        $insert= '
            INSERT INTO 
                different_table (id) 
            VALUES(' . $this->connection->quote($id) . ')';
        $this->connection->executeQuery($insert);
        $update = 'UPDATE table SET different_table_id = ' . $this->connection->quote($id) . ' WHERE id = ' . $this->connection->quote($row['id']);
        $this->connection->executeQuery($update);
    }
}

The goal of this postUp method is to create a row ($insert query) and update a existing table ($update query) with the identifier of the new ($id variable) created row.

Important to note, that you must use the $connection property and execute the query. The addSql method (from the migration code) has no effect here.