4 ms·
So how do you deal with it when you actually need to "migrate" data? Getting a correct schema is only part of the migration process. Many times a refactoring r
by jjeaff 8y ago
So how do you deal with it when you actually need to "migrate" data?
Getting a correct schema is only part of the migration process. Many times a refactoring requires a new table or field to be populated with data from the old table or field.
For example, a single table has a one to one relationship that we must select from to insert data into a newly created table so we can have a one to many relationship.
- paulddraper 8y agoProgram: code, dockerfile, Kubernetees config Persistent data: databases tables, disk snapshots program != persistent data
- recursive 8y agoBut a particular version of a program expects a certain schema in the persistent data store. So when the program gets upgraded, the persistent data store must be migrated, unless you're able to lose all your data. In that case, you don't need to care about migrations, and it's much easier.
- paulddraper 8y agoExactly.
- zepolen 8y agoIf only there was some way to query the data from the old tables and insert them into the new ones...
- jjeaff 8y agoOn, dev, local, staging, live, and where else? If you have to manually access all of the databases and run queries on them, then that defeats the purpose of a ci/cd migration tool. Of course, the migrations that this method is purporting to replace handles that.
- wolco 8y agoI'm confused. Wouldn't this type of statement cover those cases? INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
- jjeaff 8y agoYes, but on how many different systems and dev setups do you have to run that?
- zepolen 8y agoAll of them. It would be part of your migration process. Let's say you want users to have multiple emails, your scripts would be as follows: multiple_emails.up.sql: CREATE TABLE user_emails ( user_id INT NOT NULL REFERENCES (users.id), email VARCHAR NOT NULL ); INSERT INTO user_emails SELECT id, email FROM users; ALTER TABLE users DROP COLUMN email; multiple_emails.down.sql: ALTER TABLE users ADD COLUMN email VARCHAR; INSERT INTO users SELECT id, email FROM users; DROP TABLE user_emails;