5 ms·

I don't know why people are stuck on this model of a long chain/log of successive schema changes when you can simply make a diff. I lay out this approach here:

I don't know why people are stuck on this model of a long chain/log of successive schema changes when you can simply make a diff. I lay out this approach here: https://djrobstep.com/talks/your-migrations-are-bad-and-you-should-feel-bad https://djrobstep.com/talks/your-migrations-are-bad-and-you-...

javagram 7y ago
Hibernate has this model built in as well, I believe. https://stackoverflow.com/a/221422 https://stackoverflow.com/a/221422 hbm2ddl.auto=update But it isn’t recommended for production because the automatic diff-based approach can lead to unpleasant surprises. With individual migration files, you always know exactly what they are going to do and can think and plan for how a rollback might be necessary if needed. It’s fair to say that you don’t necessarily need to keep 100 individual files around once they’ve been deployed to every environment for a while and there’s no chance you’d rollback. But I think people typically do this approach because it is simpler and easier than attempting to export the database structure at #100 or whatever and setting a new baseline every few months.
- djrobstep 7y ago
  I'm certainly not suggesting applying generated diff scripts without reviewing them! In fact, probably the main benefit of the diff approach is that you can rigorously test the outcome, and explicitly confirm a matching schema will result.
  - stephenr 7y ago
    How does this possibly work then, if you have to review the scripts first? You're just storing a migration script in the repo still, but it's generated and works against only a single schema "revision"... ?
    - djrobstep 7y ago
      I don't understand the objection here. You want to apply the same migration script against different schemas?
      - stephenr 7y ago
        The same schema, from potentially different points in time. So it's not really applying to different schemas, each single migration script applies against the same state that the previous one produced - but collectively they can be applied against a database from any point in time while that migration system has been used.
        djrobstep 7y ago
        Again, what exactly are you objecting to? You're just describing how traditional migrations work. There's nothing stopping you saving a sequence of scripts that you use a diff tool to generate, it's just less necessary because you can work against the actual version instead.
        stephenr 7y ago
        ... your comments are both confusing and contradictory. You've simultaneously said that storing scripts is "bad" and that a live "diff" is better, but then also said that those generated "diff" scripts can be reviewed, which means you have to store them. I'm completely aware of using tools to generated scripts by diffing a model schema against a DB, and using a different tool to apply the finalised, reviewed scripts without invoking the "diff" tooling outside of development environments. What I cannot grasp is your convoluted claims that storing scripts is bad, and that diff's can be reviewed before they're applied, without storing them, and will work against some cowboy-esque DB modifications.
        djrobstep 7y ago
        ? Nobody is saying that storing scripts is bad. This is getting silly. I'm skeptical that you even read the original link before attacking it.
ben509 7y ago
Because diffs don't work. If you rename a table, for instance, a diff algorithm will see that as dropping the table and creating a new one.
- djrobstep 7y ago
  Which is straightforwardly solved by editing the generated script accordingly.
  - skore 7y ago
    Huh, this got me thinking. How about you just give a UUID to everything in the schema? The technical challenge with generated scripts (that you could edit by hand, but that just means that you now don't have an automated system) is that they don't understand changes at a deep enough level - they lack the context to see that a table or column has been renamed because they have no understanding of identity. So - just give them identity. Schema at commit 20b1ea23 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 25233812-9a95-4bc3-893e-6accb935fa49 name 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address 83fc34c8-56c7-49d4-94d0-150cd76204bc password Schema at commit c0d07562 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password Now you could diff these two: @@ -1,4 +1,6 @@ 03496418-e44c-42a6-a6a4-6563b7ae7bfb users - 25233812-9a95-4bc3-893e-6accb935fa49 name + 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname + 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address + c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password and every line of the diff has the necessary information to decide what operation you intended.
- evanelias 7y ago
  People love to cite this reason, but practically speaking, it's FUD. Renaming in production -- whether entire tables or just a column -- is operationally complex no matter what. Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live. So either you end up with user-facing errors in the interim, or you can try to write application logic that can interact with both old and new names simultaneously. That's overly complex, typically not supported by ORMS or DAOs, and very risky in terms of bug potential anyway. I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work. Either way, lack of rename support in diff-based schema management isn't really a problem, as long as the tooling has these two properties: 1. Catches unsafe/destructive changes in general and require special confirmation for them (preventing accidental rename-as-drop in the rare case where a rename truly is desired) 2. Offers a way to "pull" from the db side, so that if a rename is actually needed, it can be done "out of band" / manually, and the schema repo can still be updated anyway
stephenr 7y ago
I tried to read your "approach" and I got as far as "have to track version numbers" and gave up. IMO you're inventing problems to justify your own preferences. For context: I use, and am a proponent of the 'series of patches' approach for SQL migrations. I wrote a tool to apply them, because I wasn't happy having to constantly patch the existing one I'd been using. I don't really understand what you mean about "managing versions" - you just need a naming scheme that ensures the items can be applied in order. Dates (as a prefix, with a descriptive suffix) usually works well. I also don't see how you think having the patches over time in in the repo is a problem. If you don't like seeing the full history of changes - just remove the old ones after a while. The "series of patches" approach also has a benefit that I don't see how your "diff" approach would solve (without some overly complex tooling): upgrading a previous version of the database to the current schema. With our current system, I could take a database dump from 2 years and dozens of migrations ago, run the migrations on it, and it would come up to the current schema.
- djrobstep 7y ago
  If you gain some satisfaction from having to track version numbers, then good for you. I and most other people find it tedious. They're also impractical to use in environments where your one versioning system isn't the only use of the data. If you or some DBA needs to make an emergency database change - suddenly the real DB doesn't match your versioning. But if you compare directly against production, it's not a problem at all. Not directly checking against the production schema is rather like flying a plane with no reference to what is actually outside the windows - rather an unreliable experience. Nothing is stopping you from keeping a long chain of these scripts if you want - you'll lose nothing and gain more automation and testability. But in practice nobody wants to keep these files around, and nobody wants to restore 2 year old backups to production. And if you did, a diff-based approach will do just as well, more automatically. Where the diff-based approach probably shines the most is when making experimental changes locally during development. Play around with your models, add a column, add a constraint, rename the column, make it not null, change your mind and drop it again. One command syncs the dev database to your models automatically and instantly.
  - 7y ago

javagram 7y ago

Hibernate has this model built in as well, I believe. https://stackoverflow.com/a/221422 https://stackoverflow.com/a/221422 hbm2ddl.auto=update But it isn’t recommended for production because the automatic diff-based approach can lead to unpleasant surprises. With individual migration files, you always know exactly what they are going to do and can think and plan for how a rollback might be necessary if needed. It’s fair to say that you don’t necessarily need to keep 100 individual files around once they’ve been deployed to every environment for a while and there’s no chance you’d rollback. But I think people typically do this approach because it is simpler and easier than attempting to export the database structure at #100 or whatever and setting a new baseline every few months.

djrobstep 7y ago
I'm certainly not suggesting applying generated diff scripts without reviewing them! In fact, probably the main benefit of the diff approach is that you can rigorously test the outcome, and explicitly confirm a matching schema will result.
- stephenr 7y ago
  How does this possibly work then, if you have to review the scripts first? You're just storing a migration script in the repo still, but it's generated and works against only a single schema "revision"... ?
  - djrobstep 7y ago
    I don't understand the objection here. You want to apply the same migration script against different schemas?
    - stephenr 7y ago
      The same schema, from potentially different points in time. So it's not really applying to different schemas, each single migration script applies against the same state that the previous one produced - but collectively they can be applied against a database from any point in time while that migration system has been used.
      - djrobstep 7y ago
        Again, what exactly are you objecting to? You're just describing how traditional migrations work. There's nothing stopping you saving a sequence of scripts that you use a diff tool to generate, it's just less necessary because you can work against the actual version instead.
        stephenr 7y ago
        ... your comments are both confusing and contradictory. You've simultaneously said that storing scripts is "bad" and that a live "diff" is better, but then also said that those generated "diff" scripts can be reviewed, which means you have to store them. I'm completely aware of using tools to generated scripts by diffing a model schema against a DB, and using a different tool to apply the finalised, reviewed scripts without invoking the "diff" tooling outside of development environments. What I cannot grasp is your convoluted claims that storing scripts is bad, and that diff's can be reviewed before they're applied, without storing them, and will work against some cowboy-esque DB modifications.
        djrobstep 7y ago
        ? Nobody is saying that storing scripts is bad. This is getting silly. I'm skeptical that you even read the original link before attacking it.

ben509 7y ago

Because diffs don't work. If you rename a table, for instance, a diff algorithm will see that as dropping the table and creating a new one.

djrobstep 7y ago
Which is straightforwardly solved by editing the generated script accordingly.
- skore 7y ago
  Huh, this got me thinking. How about you just give a UUID to everything in the schema? The technical challenge with generated scripts (that you could edit by hand, but that just means that you now don't have an automated system) is that they don't understand changes at a deep enough level - they lack the context to see that a table or column has been renamed because they have no understanding of identity. So - just give them identity. Schema at commit 20b1ea23 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 25233812-9a95-4bc3-893e-6accb935fa49 name 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address 83fc34c8-56c7-49d4-94d0-150cd76204bc password Schema at commit c0d07562 03496418-e44c-42a6-a6a4-6563b7ae7bfb users 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password Now you could diff these two: @@ -1,4 +1,6 @@ 03496418-e44c-42a6-a6a4-6563b7ae7bfb users - 25233812-9a95-4bc3-893e-6accb935fa49 name + 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname + 25233812-9a95-4bc3-893e-6accb935fa49 lastname 2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address + c9f0d35d-439e-488c-a6d5-7a144c54335c address2 83fc34c8-56c7-49d4-94d0-150cd76204bc password and every line of the diff has the necessary information to decide what operation you intended.
evanelias 7y ago
People love to cite this reason, but practically speaking, it's FUD. Renaming in production -- whether entire tables or just a column -- is operationally complex no matter what. Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live. So either you end up with user-facing errors in the interim, or you can try to write application logic that can interact with both old and new names simultaneously. That's overly complex, typically not supported by ORMS or DAOs, and very risky in terms of bug potential anyway. I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work. Either way, lack of rename support in diff-based schema management isn't really a problem, as long as the tooling has these two properties: 1. Catches unsafe/destructive changes in general and require special confirmation for them (preventing accidental rename-as-drop in the rare case where a rename truly is desired) 2. Offers a way to "pull" from the db side, so that if a rename is actually needed, it can be done "out of band" / manually, and the schema repo can still be updated anyway

stephenr 7y ago

I tried to read your "approach" and I got as far as "have to track version numbers" and gave up. IMO you're inventing problems to justify your own preferences. For context: I use, and am a proponent of the 'series of patches' approach for SQL migrations. I wrote a tool to apply them, because I wasn't happy having to constantly patch the existing one I'd been using. I don't really understand what you mean about "managing versions" - you just need a naming scheme that ensures the items can be applied in order. Dates (as a prefix, with a descriptive suffix) usually works well. I also don't see how you think having the patches over time in in the repo is a problem. If you don't like seeing the full history of changes - just remove the old ones after a while. The "series of patches" approach also has a benefit that I don't see how your "diff" approach would solve (without some overly complex tooling): upgrading a previous version of the database to the current schema. With our current system, I could take a database dump from 2 years and dozens of migrations ago, run the migrations on it, and it would come up to the current schema.

djrobstep 7y ago
If you gain some satisfaction from having to track version numbers, then good for you. I and most other people find it tedious. They're also impractical to use in environments where your one versioning system isn't the only use of the data. If you or some DBA needs to make an emergency database change - suddenly the real DB doesn't match your versioning. But if you compare directly against production, it's not a problem at all. Not directly checking against the production schema is rather like flying a plane with no reference to what is actually outside the windows - rather an unreliable experience. Nothing is stopping you from keeping a long chain of these scripts if you want - you'll lose nothing and gain more automation and testability. But in practice nobody wants to keep these files around, and nobody wants to restore 2 year old backups to production. And if you did, a diff-based approach will do just as well, more automatically. Where the diff-based approach probably shines the most is when making experimental changes locally during development. Play around with your models, add a column, add a constraint, rename the column, make it not null, change your mind and drop it again. One command syncs the dev database to your models automatically and instantly.
- 7y ago