Migrations Vs SQL compare tools

What are some good reasons for me to use Migrations versus SQL scripts?

1) SQL scripts can't massage the existing data using domain logic to
jive with the new schema.
2) SQL scripts don't usually have easy ways to back out of a change
(migrate down).
3) SQL scripts don't maintain a schema version automatically, so teams
have to manually deal with when to run what and how.
4) SQL scripts are bound to 1 database, so they're not portable. And
they make it easy to introduce proprietary constructs, so the entire
schema is rendered unportable.
5) SQL scripts are written in SQL, not Ruby.

David Heinemeier Hansson

1 Like

You may want to checkout Agile Web Development 2nd edition which
contains a chapter on migrations. Or the API. Relationships are done
through the model / class files. And there are also some interesting
plugin's for associations as well.

Stuart

Yes. Use the execute command for SQL not covered by the migrate syntax, like this example:

execute "ALTER TABLE items ADD COLUMN cost_unit DECIMAL(5,2) NOT NULL DEFAULT 0"

For relationships, you might want to take a look at the foreign_key_migrations plugin, which will automatically set up the relationships in your schema based on your field names. Has worked well for me and a nice shortcut.

You can add_column, create_index etc. For stuff like adding triggers you
use execute("some sql");

DHH wrote:

What are some good reasons for me to use Migrations versus SQL scripts?

1) SQL scripts can't massage the existing data using domain logic to
jive with the new schema.
2) SQL scripts don't usually have easy ways to back out of a change
(migrate down).
3) SQL scripts don't maintain a schema version automatically, so teams
have to manually deal with when to run what and how.
4) SQL scripts are bound to 1 database, so they're not portable. And
they make it easy to introduce proprietary constructs, so the entire
schema is rendered unportable.
5) SQL scripts are written in SQL, not Ruby.

Thanks for such a clear and concise answer - I had similar concerns to the OP.

I'm interested to know whether/how migrations can be used in a situation where branches exist in the repository:

a) where released code and schema are being maintained on a release branch, while development continues on the trunk

b) where a branch is being used to work on a variant, which may (if successful) end up being merged into the trunk again.

regards

   Justin Forder