8 ms·

A couple of real world data points. Two Fortune 500 companies I worked at had projects in Django that used native SQL to do migrations. This led to errors in

by bb88 6y ago

A couple of real world data points. Two Fortune 500 companies I worked at had projects in Django that used native SQL to do migrations. This led to errors in prod during deployment. The order of the scripts had to run in a proscribed order. They had to be listed on the deployment instructions. So the migrations would run, and then fail during a prod deployment for various reasons. Most notably a dependent script hadn't run in prod yet. They had run in the Dev/QA environments and everything had run just fine. Or Dev/QA had been hand tweaked and that change hadn't made it to the script during prod deployment because they were testing it for a month to see performance before moving to prod, etc... etc... It was all ugly. In one of the two companies they finally decided to stop handwriting SQL and started trusting the Django migration system, and the problem with deployments went away. AND they still were able to hand write sql to optimize the performance of the DB if needed.

rubber_duck 6y ago
I don't have experience with Django model migrations so can't comment on that - but running script based migrations without a CI like that in any nontrivial environment sounds like asking for trouble, but how did you not catch the issue when a dev pulls from master and migrations aren't working - everyone just does local patching and ignores that the master is broken ? I've seen this on a .NET project, we didn't have a CI and this one guy had a tendency to commit dirty migration scripts breaking people's databases - I've seen it slip a few times to the QA deploy process before it gets caught because everyone assumed he would fix his shit and was too busy cleaning up his crap so we would just comment it out locally to get it to run.
- bb88 6y ago
  Django migration scripts are run by devs after pulling from Master as well as deploying to prod. That gives everyone the same confidence that the migrations are working. If the migrations are borked, they get fixed or the prod deployment will break. Ideally this should be part of the CI/CD process for the PR. Also it's worth noting that you can run all migrations from the beginning of time to the latest version on a development DB. And I believe the default Django unit testing framework does this by default.
- jordic 6y ago
  The problem sounds like merging things that are not green on CI, not related to migrations with python Vs sql). Think on it, you can just use Django on python, but you can use sql everywhere there is a dB.
  - bb88 6y ago
    > Think on it We did. And with smart people who knew SQL inside and out. It just made sense to not hand roll SQL anymore, because Django caught most of the errors we had.
    - jordic 6y ago
      Learn sql, it's like javascript, it's everywhere.. and will open you expertise from a we framework to a lot of other places..
      - bb88 6y ago
        You make it sound I don't know SQL. That's kind of insulting, you know?
      - jordic 6y ago
        Mmm, sorry, if this is how you see it, what I'm trying to express it's that the dB by itself, it's the source of truth (and the state) of your application, Django it's just an implementation detail that you can swap whenever you need.
        bb88 6y ago
        Think about a cognizant response rather than assuming ignorance, which seems to be a common theme with HN posters.
        theamk 6y ago
        Sometimes yes. And sometimes you have many other things to do and you want your database to just work. In this case it is perfectly fine to rely on ORM. It is like C vs Python: yes, I know C pretty well. Yes, it is significantly faster. I am still going to write the project in Python.
nikisweeting 6y ago
I truly believe the Django migrations system eliminates the need for several full-time DBA positions at larger companies that use it. Things that would take a dedicated team of DBAs days to do are automated so reliably that you don't even realize the amount of engineering effort it would take to do manually.
- justinmeiners 6y ago
  What things does it do besides update the schema, or run a custom python script?
  - abiogenesis 6y ago
    It does exactly that, in a reliable and repeatable manner. Especially if you are deploying to multiple targets (dev, staging, production) you don't care about which state each server is in.
  - acdha 6y ago
    A few other things which are easy to miss manually: identify when you have model changes which are not reflected in the migrations, ensure that the migrations always run, and fail loudly if someone tries to run migrations which started at the same point but haven’t been explicitly merged (which is really handy switching branches or on fast-moving projects with multiple features in-flight simultaneously). None of this is magic which you can’t do by hand but there’s a substantial benefit in being able to rely on it always happening and not having to spend any time thinking about it in most cases.
  - edmundsauto 6y ago
    "Data QA" isn't a term I hear much of, but it's a super important piece of migrations. A migration that runs successfully is the equivalent of a program compiling successfully.
djrobstep 6y ago
I've experienced the exact opposite - no end of problems with Rails/Django/Alembic migrations. Abandoning version numbers and chains of migration files for a diff-based approach that works directly on the database level made things so much simpler and more reliable. Comparing your goal state explicitly with the current production state means you can typically autogenerate the changes you need, and once applied you can compare again to make sure everything matches perfectly. Everything becomes reduced to: production state + pending changes = goal state Where "goal state" might be the state of your ORM models. I did a talk at PostgresOpen a while back about the problems with Django-style migrations and how to do better: https://djrobstep.com/talks/your-migrations-are-bad-and-you-should-feel-bad https://djrobstep.com/talks/your-migrations-are-bad-and-you-...
- GlennS 6y ago
  I've done automated diff-based migrations before using SQLDelta. It caused me a lot of trouble and I think it's a bad approach. These diff tools just don't have the information they need to solve the data problems that are the actual hard part of schema migrations. Simple example: I want to concatenate these two text columns into one. I do think SQLDelta and similar tools can still be useful after doing the real migration using incremental scripts. You can use them to check for problems, and to fix up easy things like views and stored procedures.
  - djrobstep 6y ago
    Of course they don't automatically solve data migrations for you, I'm not sure how any tool could possibly guess your intent like that. If you have an example of how a chain-based migration tool makes this easier, I'd love to hear about it.
    - GlennS 6y ago
      I've never worked on anything that separated data migrations from schema migrations, so I suspect I'm not quite understanding you, but... Your data migration is just part of your chain of incremental patches? For my example you just write: ALTER TABLE my_table ADD COLUMN my_column TEXT NOT NULL DEFAULT ''; UPDATE TABLE my_table SET my_column = my_old_column_a || my_old_column_b; ALTER TABLE my_table ALTER COLUMN MY_COLUMN DROP DEFAULT;
balfirevic 6y ago
> Two Fortune 500 companies I worked at had projects in Django that used native SQL to do migrations. This led to errors in prod during deployment. > The order of the scripts had to run in a proscribed order. They had to be listed on the deployment instructions. You still need to have a proper database migration system, even if migrations are defined using hand-written SQL. I can see why someone would prefer migrations written in SQL and there are plenty of migration system that support that workflow.
MattGaiser 6y ago
>Two Fortune 500 companies I worked at had projects in Django that used native SQL to do migrations. This led to errors in prod during deployment. > The order of the scripts had to run in a proscribed order. They had to be listed on the deployment instructions. So the migrations would run, and then fail during a prod deployment for various reasons. Most notably a dependent script hadn't run in prod yet. They had run in the Dev/QA environments and everything had run just fine. Or Dev/QA had been hand tweaked and that change hadn't made it to the script during prod deployment because they were testing it for a month to see performance before moving to prod, etc... etc... How do companies get this right? My organization has all these problems as well and I lose 10-15% of my time chasing misplaced SQL scripts or SQL scripts with improper tweaks.
- skinnyarms 6y ago
  There are tools that help this: Visual Studio Data Tools, RoundhousE, Flyway/Redgate - I highly recommend investing time in building them into your workflows.
  - AlphaSite 6y ago
    Alembic is pretty great for this as well
  - MattGaiser 6y ago
    They aren't going to give us time to implement those, but thanks. Shall implement them in my own personal projects.
- rtpg 6y ago
  Django migrations solve this by asking for a dependency graph, and verifying that there's only one leaf node, so to speak. So if you merge in separate scripts you will need to linearize by hand. On top of this, there's a table within the database tracking which migrations are run, so you don't accidentally rerun these for example.
  - aptwebapps 6y ago

by bb88 6y ago

rubber_duck 6y ago

I don't have experience with Django model migrations so can't comment on that - but running script based migrations without a CI like that in any nontrivial environment sounds like asking for trouble, but how did you not catch the issue when a dev pulls from master and migrations aren't working - everyone just does local patching and ignores that the master is broken ? I've seen this on a .NET project, we didn't have a CI and this one guy had a tendency to commit dirty migration scripts breaking people's databases - I've seen it slip a few times to the QA deploy process before it gets caught because everyone assumed he would fix his shit and was too busy cleaning up his crap so we would just comment it out locally to get it to run.

bb88 6y ago
Django migration scripts are run by devs after pulling from Master as well as deploying to prod. That gives everyone the same confidence that the migrations are working. If the migrations are borked, they get fixed or the prod deployment will break. Ideally this should be part of the CI/CD process for the PR. Also it's worth noting that you can run all migrations from the beginning of time to the latest version on a development DB. And I believe the default Django unit testing framework does this by default.
jordic 6y ago
The problem sounds like merging things that are not green on CI, not related to migrations with python Vs sql). Think on it, you can just use Django on python, but you can use sql everywhere there is a dB.
- bb88 6y ago
  > Think on it We did. And with smart people who knew SQL inside and out. It just made sense to not hand roll SQL anymore, because Django caught most of the errors we had.
  - jordic 6y ago
    Learn sql, it's like javascript, it's everywhere.. and will open you expertise from a we framework to a lot of other places..
    - bb88 6y ago
      You make it sound I don't know SQL. That's kind of insulting, you know?
    - jordic 6y ago
      Mmm, sorry, if this is how you see it, what I'm trying to express it's that the dB by itself, it's the source of truth (and the state) of your application, Django it's just an implementation detail that you can swap whenever you need.
      - bb88 6y ago
        Think about a cognizant response rather than assuming ignorance, which seems to be a common theme with HN posters.
      - theamk 6y ago
        Sometimes yes. And sometimes you have many other things to do and you want your database to just work. In this case it is perfectly fine to rely on ORM. It is like C vs Python: yes, I know C pretty well. Yes, it is significantly faster. I am still going to write the project in Python.

nikisweeting 6y ago

I truly believe the Django migrations system eliminates the need for several full-time DBA positions at larger companies that use it. Things that would take a dedicated team of DBAs days to do are automated so reliably that you don't even realize the amount of engineering effort it would take to do manually.

justinmeiners 6y ago
What things does it do besides update the schema, or run a custom python script?
- abiogenesis 6y ago
  It does exactly that, in a reliable and repeatable manner. Especially if you are deploying to multiple targets (dev, staging, production) you don't care about which state each server is in.
- acdha 6y ago
  A few other things which are easy to miss manually: identify when you have model changes which are not reflected in the migrations, ensure that the migrations always run, and fail loudly if someone tries to run migrations which started at the same point but haven’t been explicitly merged (which is really handy switching branches or on fast-moving projects with multiple features in-flight simultaneously). None of this is magic which you can’t do by hand but there’s a substantial benefit in being able to rely on it always happening and not having to spend any time thinking about it in most cases.
- edmundsauto 6y ago
  "Data QA" isn't a term I hear much of, but it's a super important piece of migrations. A migration that runs successfully is the equivalent of a program compiling successfully.

djrobstep 6y ago

I've experienced the exact opposite - no end of problems with Rails/Django/Alembic migrations. Abandoning version numbers and chains of migration files for a diff-based approach that works directly on the database level made things so much simpler and more reliable. Comparing your goal state explicitly with the current production state means you can typically autogenerate the changes you need, and once applied you can compare again to make sure everything matches perfectly. Everything becomes reduced to: production state + pending changes = goal state Where "goal state" might be the state of your ORM models. I did a talk at PostgresOpen a while back about the problems with Django-style migrations and how to do better: https://djrobstep.com/talks/your-migrations-are-bad-and-you-should-feel-bad https://djrobstep.com/talks/your-migrations-are-bad-and-you-...

GlennS 6y ago
I've done automated diff-based migrations before using SQLDelta. It caused me a lot of trouble and I think it's a bad approach. These diff tools just don't have the information they need to solve the data problems that are the actual hard part of schema migrations. Simple example: I want to concatenate these two text columns into one. I do think SQLDelta and similar tools can still be useful after doing the real migration using incremental scripts. You can use them to check for problems, and to fix up easy things like views and stored procedures.
- djrobstep 6y ago
  Of course they don't automatically solve data migrations for you, I'm not sure how any tool could possibly guess your intent like that. If you have an example of how a chain-based migration tool makes this easier, I'd love to hear about it.
  - GlennS 6y ago
    I've never worked on anything that separated data migrations from schema migrations, so I suspect I'm not quite understanding you, but... Your data migration is just part of your chain of incremental patches? For my example you just write: ALTER TABLE my_table ADD COLUMN my_column TEXT NOT NULL DEFAULT ''; UPDATE TABLE my_table SET my_column = my_old_column_a || my_old_column_b; ALTER TABLE my_table ALTER COLUMN MY_COLUMN DROP DEFAULT;

balfirevic 6y ago

MattGaiser 6y ago

>Two Fortune 500 companies I worked at had projects in Django that used native SQL to do migrations. This led to errors in prod during deployment. > The order of the scripts had to run in a proscribed order. They had to be listed on the deployment instructions. So the migrations would run, and then fail during a prod deployment for various reasons. Most notably a dependent script hadn't run in prod yet. They had run in the Dev/QA environments and everything had run just fine. Or Dev/QA had been hand tweaked and that change hadn't made it to the script during prod deployment because they were testing it for a month to see performance before moving to prod, etc... etc... How do companies get this right? My organization has all these problems as well and I lose 10-15% of my time chasing misplaced SQL scripts or SQL scripts with improper tweaks.

skinnyarms 6y ago
There are tools that help this: Visual Studio Data Tools, RoundhousE, Flyway/Redgate - I highly recommend investing time in building them into your workflows.
- AlphaSite 6y ago
  Alembic is pretty great for this as well
- MattGaiser 6y ago
  They aren't going to give us time to implement those, but thanks. Shall implement them in my own personal projects.
rtpg 6y ago
Django migrations solve this by asking for a dependency graph, and verifying that there's only one leaf node, so to speak. So if you merge in separate scripts you will need to linearize by hand. On top of this, there's a table within the database tracking which migrations are run, so you don't accidentally rerun these for example.
- aptwebapps 6y ago