There are two tables in my project:
Legacy table
| col id | col B | col C |
|---|---|---|
| x | y | z |
| a | b | c |
New table (slightly different schema)
| col id | col B | another col |
|---|---|---|
| x | y | bla |
As you can see, the legacy table has some data that the new table does not. This is because the code only recently started populating the new table. As a result, I have a task to move data from the legacy table to the new table where the old record's id is not present.
My app is a simple Spring Boot Kotlin API, with Postgres DB technology using Flyway.
My question is: Could an extra Flyway script to handle this data migration cause any technical issues? I have seen Flyway SQL scripts used to construct/alter the schema, but haven't seen them applied to actually move data from one table to another. Is there any reason not to do this?
Using Flyway to move data is considered a suitable practice. In fact, Flyway is well-suited for this purpose, as long as the scripts are carefully crafted and there are no other factors outside the scope of the original question that would prevent the data from being moved.
When compared to complex data operations I have encountered in the past, moving data without any further transformations appears relatively straightforward.
Flyway scripts have broad applicability, including tasks such as data population, data removal, data updates, and schema operations. These operations may involve adding or removing columns, changing data types, or creating and deleting tables. Often, these tasks are interconnected, with actions like adding a new column requiring the population of data in that column, which may involve querying existing data.