3min.

Do not drop your database content when adding Translatable to an existing project

I’ve been introducing content translation to a Symfony application initially built in a single language. As the project grew, the client wants to be able to translate the content in multiple languages so we added the great knplabs/doctrine-behaviors library 💛.

It provides a Translatable behavior which allows us to very easily make our content translatable via a new table (is there a pun here? 🤔).

Section intitulée tweak-your-doctrine-migration-to-keep-your-existing-contentTweak your Doctrine Migration to keep your existing content

When adding Translatable to an existing project, Doctrine Migration is going to blindly remove your table columns as you moved them to Translatable dedicated entities.

Here is how you can edit your Doctrine Migration to keep your contributed database content.

Let’s see an example with a Product entity owning a description property. You moved this property to the ProductTranslation entity. Your initial Doctrine migration will look like this:

final class Version20221111111111 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        $this->addSql('CREATE TABLE product_translation (id INT AUTO_INCREMENT NOT NULL, translatable_id INT DEFAULT NULL, description LONGTEXT DEFAULT NULL, locale VARCHAR(5) NOT NULL, INDEX IDX_BE61F5EA2C2AC5D3 (translatable_id), UNIQUE INDEX product_translation_unique_translation (translatable_id, locale), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('ALTER TABLE product_translation ADD CONSTRAINT FK_BE61F5EA2C2AC5D3 FOREIGN KEY (translatable_id) REFERENCES product (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE product DROP description');
    }

    // ...
}

The last SQL is problematic: DROP description will permanently delete all the contributed product descriptions.

All you need to do is add a new SQL statement manually, between the CREATE TABLE and the DROP description, to move your content using the INSERT … SELECT notation:

INSERT INTO product_translation (translatable_id, description, locale) SELECT id, description, 'en' FROM product;

The modification is as follows:

public function up(Schema $schema): void
{
    $this->addSql('CREATE TABLE product_translation (id INT AUTO_INCREMENT NOT NULL, translatable_id INT DEFAULT NULL, description LONGTEXT DEFAULT NULL, locale VARCHAR(5) NOT NULL, INDEX IDX_BE61F5EA2C2AC5D3 (translatable_id), UNIQUE INDEX product_translation_unique_translation (translatable_id, locale), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
    $this->addSql('ALTER TABLE product_translation ADD CONSTRAINT FK_BE61F5EA2C2AC5D3 FOREIGN KEY (translatable_id) REFERENCES product (id) ON DELETE CASCADE');
+    $this->addSql('INSERT INTO product_translation (translatable_id, description, locale) SELECT id, description, 'en' FROM product;');
    $this->addSql('ALTER TABLE product DROP description');
}

Make sure to add all your fields and edit the locale if you don’t come from “en” like in the example.

Section intitulée last-wordLast word

As usual, never fear to read and tweak your Doctrine Migrations.

This comment added systematically says it all:

// this up() migration is auto-generated, please modify it to your needs

You own your migrations, and you must review them. In that example, it could have been a huge production data loss 💥 practically invisible to the developers locally because we run Doctrine Fixtures.

This small tips has been contributed to the upstream documentation.

Commentaires et discussions