We use https://github.com/cakephp/phinx works pretty well. It just runs on deployment. Thankfully I've never had to try using the rollback feature.
Ask HN: How does your development team handle database migrations?
111–120 of 150 posts
Re: Ask HN: How does your development team handle database migrations?
#112Earlier 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…
Curious, how do you deal with renaming fields or tables? This is a (minor) pain point for traditional migration systems.
If you use ms sql server ssdt you use refactor/rename and it finds all references and changes them and then when you go to deploy it generates a sp_rename - 100% killer feature right there :)
Re: Ask HN: How does your development team handle database migrations?
#113We use the RedGate SQL compare tools [1] to compare our new schema to our old one and auto-apply the diffs to the production DB (this is done automatically by our deployment process). To reduce the chance of error we don’t destroy columns or tables. Our application then has an update step which runs on startup for any data migrations (or new data additions), and then updates a version number stored in the DB. The dat…
I've got a fair amount of experience with it and the Change Automation tool they have. Both are fantastic products, the latter for certain scenarios over SSDT itself. Sounds like have had it sorted for a while, my main concerns are when it wants to rebuild certain tables when a simple sp_rename or other step would be sufficient.
This is such a common complaint!
Re: Ask HN: How does your development team handle database migrations?
#114We use migrate[1]. [1]: https://github.com/golang-migrate/migrate
I second this. migrate works great for me.
Re: Ask HN: How does your development team handle database migrations?
#115Earlier quoted context omitted.
Curious, how do you deal with renaming fields or tables? This is a (minor) pain point for traditional migration systems.
Excellent question! The short answer is Skeema doesn't directly support renames yet. Renames are inherently more imperative than declarative, so they don't fit in well with the model. I've thought about handling them via tracking their history/state, but it would be hacky. Two workarounds exist in Skeema currently: * You can do the rename "out of band" (e.g. manually, rather than via `skeema push`), and then update y…
How about a column named x_no_wait_y declares a column named y, but if a column named x exists it's renamed?
Re: Ask HN: How does your development team handle database migrations?
#116This doesn't fit everywhere, but... 1) Never modify existing tables (okay, almost never). 2) Add new tables at will. 3) Pay a consultant to write PL/SQL that glues it all together and blame them for any and all issues.
3) ha ha
Re: Ask HN: How does your development team handle database migrations?
#117I work at a Perl shop and we use Sqitch [1]. It helps manage your migrations, you write three raw SQL scripts per change: deploy, verify and revert. The tool works with any DB or backend, since it only does the schema.
It does NOT integrate with your server framework so you need to figure an ORM solution out, independent of Sqitch.
Re: Ask HN: How does your development team handle database migrations?
#118Earlier quoted context omitted.
The one weakness of this system is that it doesn't understand or handle foreign key constraints. If you have those you have to manage it the old fashioned way (whatever that is for you)
That's true. Most large-scale MySQL shops, including Facebook, discourage or outright forbid foreign key constraints. This is sacrilege to many relational db purists, but there are a number of solid reasons: Foreign keys aren't shard-aware, greatly reducing their utility. They introduce performance bottlenecks due to extra locking. In an insanely-high-write-volume OLTP environment, such as a social network, this real…
We were a large MS-SQL shop and we had the same. No FKeys in test or prod and we were "only" a billion and change e-commerce, nowhere near a social media site level of traffic.
To the original question: hand-written ALTER scripts, each taggable as pre, during, or post release actions. We had standard patterns for adding non-null columns (pre to add a nullable column and a cursor-based/batched update, then another ALTER to make the nullable column (now populated) non-nullable). Also had a set of rules to ensure version N of the code (web and DB) could run on the DB at version N or N+1.)
Re: Ask HN: How does your development team handle database migrations?
#119At 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.
Re: Ask HN: How does your development team handle database migrations?
#120Earlier quoted context omitted.
In fact these are the only questions I'm really interested in asking of other developers any more, and most of the reason why I almost never ask or reply on SO, despite 37k rep. It was looser in the earlier days, but I guess moderators wanted easier to evaluate rules, and it's easier for moderators to decide to come down on the side of moderators than people who ask questions and start discussions.
Makes sense, though, right? They want a Q&A site where you can get definitive answers. This sort of discussion-oriented thing is better suited to a forum with threaded replies and whatnot.
I actually think there ought to be levels, where you can access different kinds of conversations at higher rep levels. Or some other way of gating out clueless people. Noise drowns out signal otherwise.