[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.