Msg#: 4686529 posted 3:12 am on Jul 10, 2014 (gmt 0)
I've avoided it for a long time, but now I can't deny: I need to get my schema into version control. It used to be sufficient to add columns and fiddle with indexes in a dev environment, then do the same changes in production when the new code is deployed. But now that I've got multiple environments, including some that are created and destroyed frequently, I need a solution.
I know that Ruby/Rails/Rake has a db:migrate thing, and I've used a plugin for CakePHP that does something similar. Essentially, any change to the database is accomplished with a migration class, with an "up" and "down" method, and the migrations are date stamped or versioned so you can migrate up or down as you may need, just like you can deploy and rollback a code deployment.
My stack is plain LAMP, and I would prefer to stay insulated in those technologies on this project, and not have to fuss with Java or .NET
What I'll need ultimately is a migration tool that will allow me to spark up a vagrant instance, and it'll build the MySQL schema based on the SQL diffs that are in the current GIT branch that I'm deploying; and that I can also use to upgrade the production databases as part of a deployment script.
Is there a de facto preferred solution for database migrations and schema management? I've done some research and found that there are many half-baked solutions, but none have overwhelming industry support.
I know this is not a free solution, but I know of more then a few developers I work with, who us Redgate software for MS SQL comparisons/updates. I do see on their website they have a similar tool for MYSQL.