[mrbig4545]

I used to use sqitch. It drive me mad, it wants to do too much. I have got for version control, I don't need sqitch to do it as well. And it doesn't play nice with other developers. If you add migration a in one branch, and someone adds migration b in another, then they merge theirs before yours, you're in for a world of pain whne you try to delpoy yours.

In the end I wrote a replacement that did was I needed in under 100 lines of perl. DB management should be simple. No point over complicating it

Not forgetting the guy who writes it is not that friendly, we sent a patch to change the default answer to revert to no, instead of yes. First off he said it was useless, then after some prodding over several months he copied it in and committed it in his name, no mention of where it came from. Real classy

[eli]

Are there other database migration systems that make dealing with migrations in conflicting branches really easy?

[jcoby]

It's been my experience that simpler is better. At least when it comes to teams up to around 12-15 people. After that politics will dictate how you migrate.

Adopting three rules has pretty much made migrations a non issue: 1. migrations should be timestamped, tracked, and applied in time order (rails-style migrations; this allows for the migrator to determine which migrations have not been applied regardless of when they get added to the run list) 2. migrations should be committed separate from logic changes (this way you can bring in migration a if migration b depends on it even when feature a isn't ready to be merged) 3) migrations are always forward. It's great to be able to revert during development but production is always forward. If a migration fails it always requires investigation; there is no automatic recovery.

Using the above rules you can put together a migration system in any language in about an hour by simply storing files that issue DDL/SQL directly. With a few hours more work you can abstract it out and be cross-database but that's rarely worth it.

I've tried sequentially versioned migrations and they are a major bear to work with when branching and multiple developers are involved. You end up having one guy be the "database migration guy" and responsible for keeping everything in order.

The intelligent migrators that do diffs of the schema vs the db always have issues plus the very real potential to lose data.

[sandGorgon]

We actually use rails just to manage our db. Rails migrations are so good and sane (including the rake generator tasks) that I highly recommend it...even if you are using nodejs or something else as your actual stack.

[jcoby]

That's a great idea. I never would have considered that.

Do you create a full rails project to handle migrations or what? How do you integrate that in with your other code?

[stevepike]

I've used https://github.com/thuss/standalone-migrations with success before. It's the rails migration system as a standalone gem.

[sandGorgon]

A full rails project is just a few KB. All you need to do each time is run "bundle exec rails generate migration AddNameToUsers"

And then follow the migration syntax to modify your schema. Rails will take care if the timesramping, rollbacks (in most cases),etc. You can manage your indexes, primary,etc everything here. You don't need to write any other rails code.

Check this whole directory as a part of your main source repo.

Another commenter pointed out a standalone gem for migrations. That might be OK, but I'd rather use mainline rails in this case.

[joesb]

I could not agree more.

Rails's migration is probably the only one that works in any project condition we've been through. No problem working with legacy DB. No problem using it in the DB where other team change unrelated table. You always know what change is in each migration.

[zimbatm]

Something I understood only recently is that a DB is an API. You can add fields/tables to it easily but changes should always be backwards-compatible (if you care about availability). Only when all the clients have been upgraded can the old fields/tables be removed.

Most deploys should be:

1. Adding fields/tables

2. Replace all clients

3. Remove old fields/tables

In the case of a rename triggers should be used to duplicate data between step 1 and 3.

[idunno246]

We've been using alembic in Python. It makes a dag, and has a concept of merge points so you Don't grow leaves indefinitely. Handles branching well, our history mostly just looks like a line of diamonds.

You're sol if the branches touch the same column or directly conflict, but that's never happened to us. Rarely dev Dbs end up hosed, if someone commits a bad change for instance and you pull it in before fixed, but since the latest alembic bug fixes and solid pr ci t hasn't happened.

[subleq]

Django. Migrations are modeled as a DAG instead of a linear sequence, so you can create migrations in conflicting branches as much as you want, and just create a 'merge' migration (that does nothing but combine the two branches) when you merge the branches.

[shkkmo]

I've found the doctrine migrations library to work nicely, if you are already using doctrine as an ORM. In fact, I find the simplicity of using doctrine migrations one the my favorite features of doctrine. I find myself wishing that the other parts of doctrine were as well designed and implemented.

1) It identifies migrations based on the timestamp when the migration was created. This means that it checks for any missing migrations regardless of migration 'order' while still running all needed migrations in order. It also checks for any migrations that have been run on the DB that are not in your current code base and warns you about them.

2) The migrations are simple PHP scripts that run SQL. This means that you can put whatever you want in them, easily edit them, adjust the timestamp, and manage them in your version control of choice.

3) Merging two branches with different migrations is not a problem, as long as the migrations themselves don't conflict (i.e. one changes a table or column name that the other also wants to modify). You will however have to identify the conflicts by testing the migrations together rather than just relying on your merge tool.

I'd have a hard time moving to any other migration tool that didn't offer this level of simplicity. The auto-generation of migrations based on comparing the XML schema and the DB is nice as well, but I could live without it as long as I have the other features.

[dmmalam]

Currently using pg-migrator [1]. Seems to have the right balance between power and simplicity. Though numbered migrations can be a little annoying to manage cross team.

[1] https://github.com/aphel-bilisim-hizmetleri/pg-migrator

[mrbig4545]

I don't know about conflicting branches, but sqitch didn't like out of order migrations, even if they have nothing to do with each other. The authors response is for the developer with the issue to revert back to a sane point and then redeploy, which is not suitable if you've spent a hour filling that table with data!