This looks comparable to Sequel and seems straightfoward enough. Still, when I need a full-featured tool, I prefer sqitch[1] - does what this does without requiring numbered migrations, treats database changes as commits (very similar to git), and includes the option of running tests to verify schema changes.
That said, this looks good for cases where you have a node project and want to keep your tooling on the same platform.
There was a HN thread on sqitch just yesterday [1], and a lot of people seemed super frustrated with it. Not that this is necessarily any better, I don't have experience with either.
I've found sqitch to be a little heavy. It's basically recreating parts of git as tables. Would love to see a lightweight postgres tool that can keep track of a dag of migrations.
We have a similar migration tool we built a long time ago, and this project has a bug that we had to address.
When you have to branches that each have a migration, you end up with 2 copies of N_N+1.sql, or worse yet, things that subtly merge w/o error, meaning you have committed migrations that might not run. Or the conflict-resolution process cascades painfully as you "re-sequence" your migrations.
We ended up moving to a solution based on a manifest file; there is a single migrations.json that enumerates the exact order in which migrations must be applied. It causes merge conflicts whenever you have migrations that need their "application order" resolved, and it's fixable just by re-arraning the manifest; no file renaming required. It's been pretty robust so far.
I've had similar problems with FluentMigrator, even though it orders things nicely using an attribute on the migration class that has a long value representing the order things should run in.
I just use timestamps for when the migration was created, like 201509010034, and for the most part, things are great. Until we got a high priority ticket, and a migration with a later timestamp got pushed ahead of an earlier one, so it never gave us the option to migrate the earlier one.
Easy fix was just to update the timestamp of the earlier migration when it finally got through QA, since they weren't dependent on each other, but things could've gotten really messy, so I'm not 100% happy with the way migrations currently work.
And that's exactly the point of the migrations.json manifest; you'll get a merge conflict whenever this happens, and all you need to do is resolve the order of named migrations in a JSON array. It works really well!
I have quite a lot of experience using migrations in Rails, and over time I've become just incredibly suspicious of "down migrations."
I have never once been in a situation where I needed to use one, and even if I did, I'd have some reason to hesitate. Let's say everyone on my team wrote one, and they even tested the "down then up" process to ensure it applied (on their machine), unlike the normal "up" migration, that migration code was probably only executed in a single environment with test data.
And even given those two ifs (1 - a down migration exists and 2 - it was tested anywhere), the darn things are still annoying and time consuming to write.
I would much rather write another "up" migration in the event that I needed to roll back a change. I guess that would be "rolling forward" a change.
All that said, my sphere of influence is pretty small. Has a down migration ever saved your skin? What am I not considering?
The only reasonable argument I've heard is performance modifications => moving tables/columns/indexes around to take advantage of the shape of the data.
It all works happily locally, but once applied to the (large) production data set, the optimisations don't help. So the rollback script is useful.
Honestly the only time I use down migrations in Rails is before I commit and push the migration. So I'll migrate up, experiment, realize I need to change something, migrate down, make my change, then migrate back up.
I'll keep doing that while I work through the problem until the complete migration is ready with the solution I was working on. Once it's actually pushed up, I never expect to use the "down" migration again though.
The only time I've had use for rolling back migrations is when I'm working on a feature/develop branch that have modified existing columns and need to switch back to the master branch to start work on a bug fix.
That could potentially be solved in other ways, though - such as keeping separate databases for different git branches, or making database snapshots. Both of those are manual work, though - apart from the upfront effort it takes to write the "down" migration, rolling back migrations seems like the path with least friction.
Unless you don't need any of the data in your database, or it's easily reproducible (database "seeders"), in which case you can just delete the entire database and start from scratch every time.
15 comments
[ 3.2 ms ] story [ 37.7 ms ] threadThat said, this looks good for cases where you have a node project and want to keep your tooling on the same platform.
[1] http://sqitch.org/
[1] https://news.ycombinator.com/item?id=10145933
[1] https://news.ycombinator.com/item?id=10145933
When you have to branches that each have a migration, you end up with 2 copies of N_N+1.sql, or worse yet, things that subtly merge w/o error, meaning you have committed migrations that might not run. Or the conflict-resolution process cascades painfully as you "re-sequence" your migrations.
We ended up moving to a solution based on a manifest file; there is a single migrations.json that enumerates the exact order in which migrations must be applied. It causes merge conflicts whenever you have migrations that need their "application order" resolved, and it's fixable just by re-arraning the manifest; no file renaming required. It's been pretty robust so far.
I just use timestamps for when the migration was created, like 201509010034, and for the most part, things are great. Until we got a high priority ticket, and a migration with a later timestamp got pushed ahead of an earlier one, so it never gave us the option to migrate the earlier one.
Easy fix was just to update the timestamp of the earlier migration when it finally got through QA, since they weren't dependent on each other, but things could've gotten really messy, so I'm not 100% happy with the way migrations currently work.
Then it moved to essentially date formatted migration names in 2.0 and that solved that.
Your manifest idea seems interesting, have to remember that one...
I have never once been in a situation where I needed to use one, and even if I did, I'd have some reason to hesitate. Let's say everyone on my team wrote one, and they even tested the "down then up" process to ensure it applied (on their machine), unlike the normal "up" migration, that migration code was probably only executed in a single environment with test data.
And even given those two ifs (1 - a down migration exists and 2 - it was tested anywhere), the darn things are still annoying and time consuming to write.
I would much rather write another "up" migration in the event that I needed to roll back a change. I guess that would be "rolling forward" a change.
All that said, my sphere of influence is pretty small. Has a down migration ever saved your skin? What am I not considering?
It all works happily locally, but once applied to the (large) production data set, the optimisations don't help. So the rollback script is useful.
I'll keep doing that while I work through the problem until the complete migration is ready with the solution I was working on. Once it's actually pushed up, I never expect to use the "down" migration again though.
That could potentially be solved in other ways, though - such as keeping separate databases for different git branches, or making database snapshots. Both of those are manual work, though - apart from the upfront effort it takes to write the "down" migration, rolling back migrations seems like the path with least friction.
Unless you don't need any of the data in your database, or it's easily reproducible (database "seeders"), in which case you can just delete the entire database and start from scratch every time.