3 ms·

Everyone who does DB update should understand idempotence and how to undo their changes. I like the approach the author provides, but calling it version contro

Everyone who does DB update should understand idempotence and how to undo their changes. I like the approach the author provides, but calling it version control is inaccurate. This is change management and it's a completely different discipline with its own body of knowledge.

bsaul 13y ago
I failed to understand how undo script would work for big schema modifications ( changing 1-n to n-n or adding intermediate tables). To me, the safest way is definitely a backup / restore if things go wrong ( and you should definitely have backups anyway), but i'm not an expert. Could you elaborate ? Also, the posts mentions ORMs as a solution. Well, clearly i would never want to let anything automaticaly generate and perform migration scripts apart for simple "new nullable column" types of change.
- nercury 13y ago
  Some ORMs do a decent job generating a correct sequence of DB queries for quite complex schema changes, including relationship changes. However, the process is a bit more manual than you might think. For example, to change from 1-n to n-n you would first generate n-n for first migration, then a manual migration to move data, and then for the final migration you would remove 1-n. The first and last one can be auto-generated by making changes to entities, including the reverse migration. Of course, manual review is absolutely necessary and we write queries ourselves if they involve moving the data.
- zorlem 13y ago
  You do it by breaking up the potentially destructive changes so you could rollback your deployment to the previous working version. Typically all destructive modification of the schema is done in several passes: eg. in each release/deploy: # copy the data in a new table and add code to place it in both tables # change the code to reference only the new data but still store it in both tables # rename the old table # remove the old table On top of that you have changes that are supposed to be applied before the new code is deployed (eg. creating a table, renaming a table) and after the code is deployed (eg. dropping unused columns). As somebody else mentions in the comments this is more about Change Control than Versioning, but the two things go together to insure that you have as much idempotent and reversible changes as possible. Of course, if you have the resources you can always adopt a "never delete data" policy, so you're always copying instead of modifying the data, making sure you have a way to retract the changes. Clean-up can be done on regular, distant intervals. Using ORM doesn't prevent you from doing a review on the changes that are about to be implemented. ActiveRecord for example, could provide you with a SQL file that will be representing your schema after the ORM is done. Usually the ORM schema changes are executed automatically by your CI upon deployment on Dev, QA and possibly Staging environments (you do have at least two of those, don't you? :) and need manual intervention (maybe to control performance degradation of your live env - locks, increased disk I/O, etc). This way you get plenty of testing of any DB changes and you should be able to spot any problems as early as possible.

bsaul 13y ago

I failed to understand how undo script would work for big schema modifications ( changing 1-n to n-n or adding intermediate tables). To me, the safest way is definitely a backup / restore if things go wrong ( and you should definitely have backups anyway), but i'm not an expert. Could you elaborate ? Also, the posts mentions ORMs as a solution. Well, clearly i would never want to let anything automaticaly generate and perform migration scripts apart for simple "new nullable column" types of change.

nercury 13y ago
Some ORMs do a decent job generating a correct sequence of DB queries for quite complex schema changes, including relationship changes. However, the process is a bit more manual than you might think. For example, to change from 1-n to n-n you would first generate n-n for first migration, then a manual migration to move data, and then for the final migration you would remove 1-n. The first and last one can be auto-generated by making changes to entities, including the reverse migration. Of course, manual review is absolutely necessary and we write queries ourselves if they involve moving the data.
zorlem 13y ago
You do it by breaking up the potentially destructive changes so you could rollback your deployment to the previous working version. Typically all destructive modification of the schema is done in several passes: eg. in each release/deploy: # copy the data in a new table and add code to place it in both tables # change the code to reference only the new data but still store it in both tables # rename the old table # remove the old table On top of that you have changes that are supposed to be applied before the new code is deployed (eg. creating a table, renaming a table) and after the code is deployed (eg. dropping unused columns). As somebody else mentions in the comments this is more about Change Control than Versioning, but the two things go together to insure that you have as much idempotent and reversible changes as possible. Of course, if you have the resources you can always adopt a "never delete data" policy, so you're always copying instead of modifying the data, making sure you have a way to retract the changes. Clean-up can be done on regular, distant intervals. Using ORM doesn't prevent you from doing a review on the changes that are about to be implemented. ActiveRecord for example, could provide you with a SQL file that will be representing your schema after the ORM is done. Usually the ORM schema changes are executed automatically by your CI upon deployment on Dev, QA and possibly Staging environments (you do have at least two of those, don't you? :) and need manual intervention (maybe to control performance degradation of your live env - locks, increased disk I/O, etc). This way you get plenty of testing of any DB changes and you should be able to spot any problems as early as possible.