Implementing a data migration class to transform existing data in columns to new types

[Smurrlawa]

Good evening,
I am currently struggling with migrations. Especially with the data which is already in my database.

As far as I know we currently only provide structural information for our tables correct?
When I change an column e.g. from an string to an json (that's the case I encountered), mysql gives me an error that the existing values in my database cannot be migrated to the json structure when executing the migration.
Manual migration is possible but also a pain to do for many entries.

Invalid JSON text: "Invalid value." at position 0 in value for column

I found an old package about 2 years ago which tries this but I find it rather hard to believe that there is no better way.

I thought about maybe defining the data change inside of the affected migration to tell the migration to transform the value of each row to the according format when migration is processing or has finished.

e.g. old value:
john-maxwell

new value:
{ "identifier": "john-maxwell", "priority": 12, "anyotherfieldyouwanttoadd": "12345" }

I think this would reduce the need to manual migrate the data in the database when an feature is deployed.

What do you think about this? Is there a use case to further dive into this topic?
I have more ideas on how to make this a robust and safe solution but first I wanted to ask you guys about it.

You can see an possible usage example for the migration attached to this message.

[noefleury]

Hi,

One of the solution if your column (A) is not compatible with the new type should be to :

• add a new column (B) with json type
• apply your transformation row by row using chunks (A to B)
• drop the old column (A)
• then finally rename the one you've just filled to the original column name (B -> A)

To be safe about data consistency you should run this logic inside a transaction. I think an implicit commit shouldn't occurred during these actions but you should try it before on some dummy data. Note that It will lock the database during the process.

[Smurrlawa]

Yes, I think this is a great approach. Do you think there is a need for an class (inside core) which does the most transaction and column handling itself and the user only uses the class and explains the transformation, and affected types + columns inside of the migration?

Cheers

[noefleury]

I personally think that this situation is too specific to be proposed here, even if it’s as great idea 😀

[Smurrlawa]

Alrighty thank you for your opinion about this topic. Maybe I'll dive into this topic more for my own projects and make a package out of the solution.

Cheers