Poorly
Ask HN: How does your development team handle database migrations?
51–60 of 150 posts
Re: Ask HN: How does your development team handle database migrations?
#52Django migrations it’s a truly great tool.
Yes it is. (Except when they made makemigrations be like O(2^N) or something in the number of models around 1.9).
Re: Ask HN: How does your development team handle database migrations?
#53Earlier quoted context omitted.
I call this declarative schema management, since the repo declares the desired state, and the tooling knows how to reach this state. This concept is finally catching on lately, although some huge companies have already been doing it this way for quite some time. Facebook is a key example; they've managed their schema changes in a pure-SQL declarative fashion, company-wide, for nearly a decade. I'm developing a suite…
I'm surprised this isn't more of a thing. It seems like the natural evolution of "[X] as code". I've always been a little turned off by migrations (though they were certainly an improvement over the previous situation, which was basically just indeterministic changes on the fly).
My thoughts exactly. But it's a major paradigm shift for those coming from the world of Rails/Django/etc migrations, and that unfamiliarity understandably leads to some initial resistance and skepticism.
fwiw, other declarative tools are starting to pop up -- besides my tool Skeema, some others I've seen recently are Migra [1] and sqldef [2]. And meanwhile a bunch of enterprise tools for MS SQL Server have operated in the declarative fashion for quite a long time, although usually with GUIs instead of being git / pull-request-driven. So I think/hope it's just a matter of time before this concept becomes more widely known.
Re: Ask HN: How does your development team handle database migrations?
#54At my previous job we either didn't migrate and wrote application level transforms that would update records as they were encountered by users (mongodb) or we had a custom built migration system that ran JavaScript snippets on our shards. The migration system was miserable to work with and it was hard to debug the code on stage in such a way that would allow us to anticipate whatever might be on prod...
This sounds like a nightmare. Why?
This is a fairly typical approach minus the pain points of our specific migration system for Mongodb as far as I know. We favored application level transforms for the sake of safety and speed. Doing that obviously gets really bothersome as time goes on though.
Re: Ask HN: How does your development team handle database migrations?
#55If anyone is looking for a javascript specific solution, I maintain a module originally written (and abandoned) by TJ.
$ npm i migrate
Re: Ask HN: How does your development team handle database migrations?
#56If anyone is looking for a javascript specific solution, I maintain a module originally written (and abandoned) by TJ. $ npm i migrate https://www.npmjs.com/package/migrate
Why does it matter what the tool is written in?
If anything, I'm a lot less likely to use something written in JS when correctness is absolutely paramount.
Re: Ask HN: How does your development team handle database migrations?
#57I've read and reread a great article titled "Evolutionary Database Design" on Martin Fowler's web-site [0]. This article describes database migrations as being a process. We've found that for complex changes, we'll often need a pre-migration and a post-migration (temporally being before the code change and after the code change respectively). We commit the migrations along-side the application code and in our case we…
Thank you, the Fowler article is very helpful. Does your team have someone act as a DBA to oversee each migration?
Re: Ask HN: How does your development team handle database migrations?
#58I've been really happy with how my current company[0] has been doing migrations and I've seen a couple others do it but it seems like it should be more widespread. Database Schema as Code Instead of writing up and down migrations, you define what the end state should look like. Then the computer will figure out how to get here. This is just how the industry started managing server configurations (Puppet) and infrastr…
This sounds nice! One Question: You said that DML changes are handled via "standard check in sql file". Does this simply mean a new SQL file for each migration? And how are DML changes connected to DDL changes? For example, if some code is two versions behind and updated to the current schema, wouldn't this mean that the DDL is updated in one step to the current state, but the DML potentially in two steps, breaking t…
That's correct. The DML changes as part of CI are somewhat new so we haven't ironed it all out yet.
Here's the scenario that I think you're laying out: 1. Commit A creates column foo 2. Commit B has DML that reference column foo 3. Commit C removes column foo
This works fine if our CI deployer does each commit individually. First roll out any schema changes, then run any DML SQL.
However, our deployer might pick up all those changes and since we roll out the schema migrations first (in this case a create + drop -> NOP) and then runs the DML (which will error), this is an issue because of the rollup.
In practice, we have yet to see this case (most of the time, the dev who write the DML is close enough to the code to know if it's going to be dropped soon and we don't drop that many columns - in part because we know that there be dragons) but truthfully, I haven't thought about it much and need to think through what the impact is beyond this example. Thanks for helping me refine my thinking and I'll have something to ponder on this weekend!
Re: Ask HN: How does your development team handle database migrations?
#59I've read and reread a great article titled "Evolutionary Database Design" on Martin Fowler's web-site [0]. This article describes database migrations as being a process. We've found that for complex changes, we'll often need a pre-migration and a post-migration (temporally being before the code change and after the code change respectively). We commit the migrations along-side the application code and in our case we…
> We commit the migrations along-side the application code and in our case we use FlywayDB [1]. The only down-side is that this tool doesn't perform roll-back operations automatically. You can always do them by writing another migration that goes forward to the past.
I've done that too, and agree re: rollbacks... I've also never been a huge fan of the fact that the final, in-production server isn't ever really reified as a source file. What you have instead is the initial state and a bunch of migration files. It works, and it's the most reflective way of how the final schema got to that state, but it's not the final state. I guess that's the trade off of the relatively unsophisticated approach taken by tools like Flyway.
Re: Ask HN: How does your development team handle database migrations?
#60Earlier quoted context omitted.
You can run/test your migrations in a transaction and roll back if it doesn't work.
Not all problems are obvious, though. You can have an app work 100%, your database be completely coherent, and then realize some of the data is missing. It would be cool to not have to be so careful when committing migrations, needing to be absolutely sure that we're not screwing something up. This is similar to how one would be careful of changes done in source code before we learned how to use version control syste…
This is OK for git because updates to a repo happen maybe 100 times a day at the most.
A production DB is changed potentially millions of times a day.
If you have this goal from the beginning, you can create an append-only database, but that's orthogonal to migrations.
To have "append-only" migrations is seemingly out of scope for mainstream database engines.