5 ms·

That sounds reasonable. But what about the case where the DB migration of version 2 would be incompatbile with code version 1, e.g. a column was dropped?

jaimebuelta 3y ago
You NEVER do that in one go, you need to split it in several deployments. Dropping a column is relatively straightforward, in two steps. First deploy a version of the code that doesn’t use the column, then release the migration dropping the column. The typical example is the renaming of a column, which needs to be done in several steps: 1. Create the new column, copying the data from the old column (DB migration) both columns exist, but only the old one is used 2. Deploy new code that works with both columns, reading from old and writing new and old 3. Deploy data migration (DB migration) that ensures old and new columns has the same values (to ensure data consistency). At this point, there are no “old column only” writes by the code deployed in previous step 4. Deploy new code using only new column. Old column is deprecated 5. Delete old column At any given point, code versions N (current) and N-1 (previous) are compatible with the DB. Any change on the DB is done in advance in a backwards compatible way.
- steffres 3y ago
  I see. Thanks for the clarifications. And these DB migrations, did your team keep a history of them? If so, did you manage them yourselves, or did you use some tools like flyway? I'm asking because I'm starting a project where we will manage the persistence SQL layer without any ORM (always did it so far with Django's migrations), but might consider some third party tools for DB migrations.
  - merb 3y ago
    btw. it's also bad to drop a column if you have multiple people in a team when they switch between branches. it's always a headache, so the best thing is to delay dropping/deleting. renaming stuff with that gets a little bit tricky, but you can workaround that with database triggers if you really need to rename things.
    - ljm 3y ago
      The problem I've seen a lot, particularly with Rails, is when migrations generate a schema dump after running them, which can get really messy if people blindly commit the diff or if you rely on running several years of migrations in your local environment from scratch (many of which may have been edited or removed if they directly referenced application code). Given the migrations are executed through a DSL and the dump is just a structural snapshot of the DB at the end of the run, they're not quite as reproducible as plain SQL migrations. You just end up with weird, unpredictable DB states that don't reflect production at all. Especially when you're dealing with old versions of MySQL and the character encoding and collation are all over the place.
  - mrweasel 3y ago
    The way I've seen it work is hand written SQL for the migrations, numbered and tracked in Git. There shouldn't be any reason that you can't do it with Flyway, but I would be concerned about fighting Flyway a bit. I use Django a fair bit and I honestly don't see a good way to make this approach work for Django, not suggesting that you can't, but you would be fighting Django a fair bit, it's not really how it's designed to work. If you don't have en ORM, then this is actually much much easier to do right. I'd design the initial schema, either by hand or using some pgAdmin, TOAD or whatever you database has. For there on everything is just hand written migrations.
    - robertlagrant 3y ago
      Django has migrations; why would this be harder with Django?
      - mrweasel 3y ago
        The migrations is fairly tightly couple to the code. You can apply the migrations without deploying new code, if you extract the migrations, but now you have at least two branches in your version control, both of which are technically in production. You have the version that's actually running, and you have the version with the model changes and the migrations, from which you extracted the migrations and applied to the database. I'd argue that because you're making the migrations from the model, it's also easier to do accidentally create migrations that are not independent of the code version.
        robertlagrant 3y ago
        Hm, but isn't that right? You make your change in code, which doesn't touch your models (except you're no longer using the column you'd like to deprecate), and deploy that; show it's working. Then you make another change to actually remove the column from the models and generate a migration. Then you deploy that version, which migrates the db and runs your new model code? (You could in theory remove the column but not merge the migration if you wanted to show your code worked fully without that column in your ORM model before removing it from the DB as well?)
  - tianzhou 3y ago
    You can also check out Bytebase, it's GitLab for database schema migrations (Disclaimer: I am one of the authors)
  - skimdesk 3y ago
    We've used dbmate[1] outside of the Django/alembic ecosystem. [1] https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate
- zaphirplane 3y ago
  What about new rows added during step 0 - 2
nicoburns 3y ago
You do it in two stages. Add as new column, deploy code that uses it and no longer use the old column. Then later drop the column once nothing is using it anymore.

jaimebuelta 3y ago

You NEVER do that in one go, you need to split it in several deployments. Dropping a column is relatively straightforward, in two steps. First deploy a version of the code that doesn’t use the column, then release the migration dropping the column. The typical example is the renaming of a column, which needs to be done in several steps: 1. Create the new column, copying the data from the old column (DB migration) both columns exist, but only the old one is used 2. Deploy new code that works with both columns, reading from old and writing new and old 3. Deploy data migration (DB migration) that ensures old and new columns has the same values (to ensure data consistency). At this point, there are no “old column only” writes by the code deployed in previous step 4. Deploy new code using only new column. Old column is deprecated 5. Delete old column At any given point, code versions N (current) and N-1 (previous) are compatible with the DB. Any change on the DB is done in advance in a backwards compatible way.

steffres 3y ago
I see. Thanks for the clarifications. And these DB migrations, did your team keep a history of them? If so, did you manage them yourselves, or did you use some tools like flyway? I'm asking because I'm starting a project where we will manage the persistence SQL layer without any ORM (always did it so far with Django's migrations), but might consider some third party tools for DB migrations.
- merb 3y ago
  btw. it's also bad to drop a column if you have multiple people in a team when they switch between branches. it's always a headache, so the best thing is to delay dropping/deleting. renaming stuff with that gets a little bit tricky, but you can workaround that with database triggers if you really need to rename things.
  - ljm 3y ago
    The problem I've seen a lot, particularly with Rails, is when migrations generate a schema dump after running them, which can get really messy if people blindly commit the diff or if you rely on running several years of migrations in your local environment from scratch (many of which may have been edited or removed if they directly referenced application code). Given the migrations are executed through a DSL and the dump is just a structural snapshot of the DB at the end of the run, they're not quite as reproducible as plain SQL migrations. You just end up with weird, unpredictable DB states that don't reflect production at all. Especially when you're dealing with old versions of MySQL and the character encoding and collation are all over the place.
- mrweasel 3y ago
  The way I've seen it work is hand written SQL for the migrations, numbered and tracked in Git. There shouldn't be any reason that you can't do it with Flyway, but I would be concerned about fighting Flyway a bit. I use Django a fair bit and I honestly don't see a good way to make this approach work for Django, not suggesting that you can't, but you would be fighting Django a fair bit, it's not really how it's designed to work. If you don't have en ORM, then this is actually much much easier to do right. I'd design the initial schema, either by hand or using some pgAdmin, TOAD or whatever you database has. For there on everything is just hand written migrations.
  - robertlagrant 3y ago
    Django has migrations; why would this be harder with Django?
    - mrweasel 3y ago
      The migrations is fairly tightly couple to the code. You can apply the migrations without deploying new code, if you extract the migrations, but now you have at least two branches in your version control, both of which are technically in production. You have the version that's actually running, and you have the version with the model changes and the migrations, from which you extracted the migrations and applied to the database. I'd argue that because you're making the migrations from the model, it's also easier to do accidentally create migrations that are not independent of the code version.
      - robertlagrant 3y ago
        Hm, but isn't that right? You make your change in code, which doesn't touch your models (except you're no longer using the column you'd like to deprecate), and deploy that; show it's working. Then you make another change to actually remove the column from the models and generate a migration. Then you deploy that version, which migrates the db and runs your new model code? (You could in theory remove the column but not merge the migration if you wanted to show your code worked fully without that column in your ORM model before removing it from the DB as well?)
- tianzhou 3y ago
  You can also check out Bytebase, it's GitLab for database schema migrations (Disclaimer: I am one of the authors)
- skimdesk 3y ago
  We've used dbmate[1] outside of the Django/alembic ecosystem. [1] https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate
zaphirplane 3y ago
What about new rows added during step 0 - 2

nicoburns 3y ago

You do it in two stages. Add as new column, deploy code that uses it and no longer use the old column. Then later drop the column once nothing is using it anymore.