I wrote my own schema as code library and then switched back to normal migrations with no down method. It takes some care to not delete anything for a while until it’s definitely not needed ever again.
Ask HN: How does your development team handle database migrations?
121–130 of 150 posts
Re: Ask HN: How does your development team handle database migrations?
#122Earlier quoted context omitted.
Data migrations? Denormalizing columns from one table to one or more child tables, possibly more than one relation away? Switching one set of fields in a single table to be in a different table via a relation, converting something from 1:1 to 1:n? The concept appeals to me, but it only seems to work for trivial migrations.
A totally valid point, but I'd argue those should be handled by a separate tool or process. Data migrations tend to be fully programmatic; tools and frameworks can help reduce the code required, but cannot handle every possible case. (having performed numerous multi-billion-row data migrations, I learned this painfully first-hand...) For simpler cases, where it may make sense to run a data migration immediately after…
I honestly like the way Rails does it: both capturing the imperative deltas and dumping the final schema which gets checked in. Not a big fan of down migrations, usually a waste of time.
Otherwise I like Percona's OSC, particularly how it can tune down table rewrites when there's competing work, or replication is lagging too much. We're just at the point where we need to automate the OSC tool rather than using it as a point solution for migrating our bigger tenants.
Re: Ask HN: How does your development team handle database migrations?
#123At my .NET shop we use FluentMigrator [1] which allows you to just run up/down migrations. Our deployments are completely automated and, in all honesty, while this has been a great and a painless way to manage migrations there really is no silver bullet. Minor releases are dead simple. Larger releases require quite a bit of planning to make sure things are deployed in correct order and SQL updates are backwards-compa…
How do you apply your migrations? I was setting up a project a few weeks ago and reached for FluentMigrator as it used to be my go to tool... but apparently the command line tool is now deprecated and they recommend writing custom code to do migrations. I eventually decided to give EF Core migrations a try, which is working pretty well so far.
We use the CLI you're referring to on ci/cd servers. Frankly, I had no idea it was deprecated and couldn't find much on this after a little bit of googling. Would mind sharing a link?
Re: Ask HN: How does your development team handle database migrations?
#124Scripts.
And I hate to tell you but despite what you’ll read on HN every single company I’ve seen and every single vendor I’ve dealt with - is using scripts.
Just simple scripts, in source control, amended by hand, lord hopes applied as part of a CI system, often without any kind of version numbering (so once it’s in the database you don’t know where it came from) and with no rollback.
Declarative migrations do exist. If it’s in .NET then it’s invisible to me, but I imagine it’s pretty rare. Using SSDT (Microsoft) for tooling is possible but has so many edge cases and is poorly documented so coming up with an architecture requires the kind of expert where there may literally only be a dozen in the world, so it isn’t done outside of demos that fail and get thrown out the second you’d ever try to implement them.
Re: Ask HN: How does your development team handle database migrations?
#125For my current project I have not needed to migrate data.
I have a system called timequerylog and a tool tql-cli where data is just appended to JSONL files organized by key, date, and time.
Re: Ask HN: How does your development team handle database migrations?
#126I'm skeptical of tools that claim to handle schema migrations automatically or declaratively. I've had them fail in the past at critical moments. I also don't believe that they can handle the most important cases.
I think that numbered sequential migration steps are still the correct way to go. They should be written at the time the feature/change is developed, ideally by the person doing that development.
Whether they are in SQL or some sort of ORM thing probably doesn't matter that much.
I don't know how to handle branching/merging issues with this approach beyond "pay attention, be careful, talk to your colleagues about database changes you're making".
At my first company we stored the whole schema instead of the incremental steps to get there. We used SQL Delta to synchronize the real database to what we wanted.
SQL Delta is a good tool, but one time we ran into a case it couldn't handle during a big deployment at a large hospital and had to abort and roll everything back. Very embarrassing. We switched to incremental scripts after that.
SQL Delta is still a good tool if you have lots of views, user defined functions, or stored procedures. As long as they aren't needed for the migration itself, you can just synchronize all of those at the end.
It's also a great tool for actually inspecting if the result of your migration is what you expected.
I do like the idea of having easy access to the full schema text as well as the migrations (someone mentioned that Rails does this). Ideally I think this should be generated from the migrations and not checked into version control.
Re: Ask HN: How does your development team handle database migrations?
#127My team works with five database systems containing more or less the same data because we "switch" to a new DB every year without investing enough in migrating off the old one.
Re: Ask HN: How does your development team handle database migrations?
#128There is a great writing from Stripe which explains their process for database migration :
- Dual writing to the existing and new tables to keep them in sync.
- Changing all read paths in our codebase to read from the new table.
- Changing all write paths in our codebase to only write to the new table.
- Removing old data that relies on the outdated data model.
Re: Ask HN: How does your development team handle database migrations?
#129My team works with five database systems containing more or less the same data because we "switch" to a new DB every year without investing enough in migrating off the old one.
It's "job security" ;)
Re: Ask HN: How does your development team handle database migrations?
#130Earlier quoted context omitted.
How do you apply your migrations? I was setting up a project a few weeks ago and reached for FluentMigrator as it used to be my go to tool... but apparently the command line tool is now deprecated and they recommend writing custom code to do migrations. I eventually decided to give EF Core migrations a try, which is working pretty well so far.
We use the CLI you're referring to on ci/cd servers. Frankly, I had no idea it was deprecated and couldn't find much on this after a little bit of googling. Would mind sharing a link?
Perhaps "deprecated" isn't quite accurate. I could have sworn one of their documentation pages basically said, "there's a good chance you'll run into problems if you use the CLI, and we don't recommend using it." If there was something that explicit I can't find it at the moment. There is [0] that says "please use the in-process runner if possible," [1] discussing problems with the cli migrators, and [2] where the maintainer seems to have a preference for removing the cli's in the long run.
[0]: https://fluentmigrator.github.io/articles/migration-runners....
[1]: https://github.com/fluentmigrator/fluentmigrator/issues/1006
[2]: https://github.com/fluentmigrator/fluentmigrator/issues/1016